16 kỹ thuật SQL mà mọi người mới bắt đầu cần biết

Trên thang điểm từ 1 đến 10, kỹ năng lưu trữ dữ liệu của bạn tốt đến mức nào?

Bạn muốn đi trên 7/10? Bài viết này là dành cho bạn sau đó.

SQL của bạn tốt như thế nào? Bạn muốn sẵn sàng cho một cuộc phỏng vấn việc làm càng sớm càng tốt?

Bài đăng trên blog này giải thích chi tiết các kỹ thuật SQL kho dữ liệu phức tạp nhất. Tôi sẽ sử dụng phương ngữ SQL tiêu chuẩn của BigQuery để viết nguệch ngoạc một vài suy nghĩ về chủ đề này.

1. Bảng gia tăng và HỢP NHẤT

Cập nhật bảng là quan trọng. Nó thực sự quan trọng. Tình huống lý tưởng là khi bạn có các giao dịch là khóa CHÍNH, số nguyên duy nhất và số gia tự động. Cập nhật bảng trong trường hợp này rất đơn giản:

Điều đó không phải lúc nào cũng đúng khi làm việc với bộ dữ liệu lược đồ hình sao không chuẩn hóa trong kho dữ liệu hiện đại. bạn có thể được giao nhiệm vụ tạo các phiên với SQL và/hoặc cập nhật dần dần bộ dữ liệu chỉ với một phần dữ liệu. transaction_id có thể không tồn tại nhưng thay vào đó, bạn sẽ phải xử lý mô hình dữ liệu trong đó khóa duy nhất phụ thuộc vào transaction_id (hoặc dấu thời gian) mới nhất đã biết. Ví dụ: user_id trong tập dữ liệu last_online phụ thuộc vào dấu thời gian kết nối đã biết mới nhất. Trong trường hợp này, bạn muốn update những người dùng hiện có và insert những người dùng mới.

MERGE và cập nhật gia tăng

Bạn có thể sử dụng MERGE hoặc bạn có thể chia hoạt động thành hai hành động. Một để cập nhật các bản ghi hiện có bằng các bản ghi mới và một để chèn các bản ghi hoàn toàn mới không thoát (tình huống TRÁI THAM GIA).

MERGE là một câu lệnh thường được sử dụng trong cơ sở dữ liệu quan hệ. Lệnh MERGE của Google BigQuery là một trong các câu lệnh Ngôn ngữ thao tác dữ liệu (DML). Nó thường được sử dụng để thực hiện nguyên tử ba chức năng chính trong một câu lệnh duy nhất. Các chức năng này là CẬP NHẬT, CHÈN và XÓA.

• Mệnh đề CẬP NHẬT hoặc XÓA có thể được sử dụng khi hai hoặc nhiều dữ liệu khớp nhau.
• Mệnh đề INSERT có thể được sử dụng khi hai hoặc nhiều dữ liệu khác nhau và không khớp.
• Mệnh đề CẬP NHẬT hoặc XÓA cũng có thể được sử dụng khi dữ liệu đã cho không khớp với nguồn.

Điều này có nghĩa là Lệnh MERGE của Google BigQuery cho phép bạn hợp nhất dữ liệu Google BigQuery bằng cách cập nhật, chèn và xóa dữ liệu khỏi các bảng Google BigQuery của bạn.

Hãy xem xét SQL này:

2. Đếm từ

Thực hiện UNNEST() và kiểm tra xem từ bạn cần có trong danh sách bạn cần có hữu ích trong nhiều tình huống hay không, tức là phân tích tình cảm của kho dữ liệu:

3. Sử dụng câu lệnh IF() bên ngoài câu lệnh SELECT

Điều này mang lại cho chúng tôi cơ hội để lưu một số dòng mã và trở nên khôn ngoan hơn về mã. Thông thường, bạn sẽ muốn đặt câu hỏi này vào truy vấn phụ và thêm bộ lọc vào mệnh đề where nhưng thay vào đó, bạn có thể thực hiện việc này :

Một ví dụ khác về cách KHÔNG sử dụng nó với các bảng được phân vùng . Đừng làm điều này . Đây là một ví dụ tồi bởi vì các hậu tố của bảng phù hợp có thể được xác định động (dựa trên thứ gì đó trong bảng của bạn), bạn sẽ bị tính phí khi quét toàn bộ bảng.

Bạn cũng có thể sử dụng nó trong mệnh đề HAVING và các hàm AGGREGATE .

4. Sử dụng GROUP BY ROLLUP

Hàm ROLLUP được sử dụng để thực hiện tổng hợp ở nhiều cấp độ. Điều này rất hữu ích khi bạn phải làm việc với biểu đồ kích thước.

Truy vấn sau đây trả về tổng chi tiêu tín dụng mỗi ngày theo loại giao dịch (is_gift) được chỉ định trong mệnh đề where , đồng thời, truy vấn này cũng hiển thị tổng chi tiêu cho mỗi ngày và tổng chi tiêu trong tất cả các ngày có sẵn.

5. Chuyển đổi bảng thành JSON

Hãy tưởng tượng bạn được yêu cầu chuyển đổi bảng của mình thành đối tượng JSON trong đó mỗi bản ghi là một phần tử của mảng lồng nhau. Đây là lúc chức năng to_json_string() trở nên hữu ích:

Sau đó, bạn có thể sử dụng nó ở bất cứ đâu: ngày tháng, kênh tiếp thị, chỉ số, biểu đồ biểu đồ, v.v.

6. Sử dụng PHÂN PHẦN BỞI

Đưa ra các cột user_id , date và total_cost . Đối với MỖI ngày, làm cách nào để bạn hiển thị tổng giá trị doanh thu cho MỖI khách hàng trong khi vẫn giữ tất cả các hàng? Bạn có thể đạt được điều này như vậy:

7. Đường trung bình động

Rất thường xuyên, các nhà phát triển BI được giao nhiệm vụ thêm đường trung bình động vào các báo cáo và bảng điều khiển tuyệt vời của họ. Đây có thể là biểu đồ đường MA 7, 14, 30 ngày/tháng hoặc thậm chí cả năm. Vì vậy, làm thế nào để chúng ta làm điều đó?

8. Mảng ngày tháng

Trở nên thực sự tiện dụng khi bạn làm việc với việc giữ chân người dùng hoặc muốn kiểm tra một số tập dữ liệu để tìm các giá trị bị thiếu, tức là ngày tháng. BigQuery có chức năng gọi là GENERATE_DATE_ARRAY :

9. Hàng_số()

Điều này hữu ích để lấy thông tin mới nhất từ dữ liệu của bạn, tức là bản ghi được cập nhật mới nhất, v.v. hoặc thậm chí để xóa các bản sao:

10. NTILE()

Một chức năng đánh số khác. Thực sự hữu ích để theo dõi những thứ như Login duration in seconds nếu bạn có ứng dụng dành cho thiết bị di động. Ví dụ: tôi đã kết nối Ứng dụng của mình với Firebase và khi người dùng login , tôi có thể xem họ mất bao lâu để đăng nhập.

Hàm này chia các hàng thành các nhóm constant_integer_expression dựa trên thứ tự hàng và trả về số nhóm dựa trên 1 được gán cho mỗi hàng. Số lượng hàng trong các nhóm có thể khác nhau nhiều nhất là 1. Các giá trị còn lại (phần còn lại của số hàng chia cho các nhóm) được phân phối một cho mỗi nhóm, bắt đầu với nhóm 1. Nếu constant_integer_expression ước tính thành NULL, 0 hoặc âm, một lỗi được cung cấp.

11. Xếp hạng / xếp hạng dày đặc

Chúng còn được gọi là các hàm đánh số . Tôi có xu hướng sử dụng DENSE_RANK làm chức năng xếp hạng mặc định vì nó không bỏ qua xếp hạng có sẵn tiếp theo trong khi RANK sẽ làm. Nó trả về các giá trị xếp hạng liên tiếp. Bạn có thể sử dụng nó với một phân vùng chia kết quả thành các nhóm riêng biệt. Các hàng trong mỗi phân vùng nhận được cùng thứ hạng nếu chúng có cùng giá trị. Ví dụ:

Một ví dụ khác với giá sản phẩm:

12. Xoay / bỏ xoay

Pivot chuyển hàng thành cột. Đó là tất cả những gì nó làm. Unpivot làm ngược lại .

13. Giá_trị_đầu/giá_trị_cuối

Đó là một chức năng hữu ích khác giúp lấy delta cho mỗi hàng so với giá trị đầu tiên/cuối cùng trong phân vùng cụ thể đó.

14. Chuyển đổi một bảng thành Mảng cấu trúc và chuyển chúng sang UDF

Điều này hữu ích khi bạn cần áp dụng một hàm do người dùng xác định (UDF) với một số logic phức tạp cho mỗi hàng hoặc một bảng. Bạn luôn có thể coi bảng của mình là một mảng các đối tượng TYPE STRUCT và sau đó chuyển từng đối tượng trong số chúng sang UDF. Nó phụ thuộc vào logic của bạn. Ví dụ: tôi sử dụng nó để tính thời gian hết hạn mua hàng:

Theo cách tương tự, bạn có thể tạo bảng mà không cần sử dụng UNION ALL . Ví dụ: tôi sử dụng nó để thử nghiệm một số dữ liệu thử nghiệm cho các bài kiểm tra đơn vị. Bằng cách này, bạn có thể thực hiện rất nhanh chỉ bằng cách sử dụng Alt + Shift + Down trong trình chỉnh sửa của mình.

15. Tạo phễu sự kiện bằng cách sử dụng FOLLOWING AND UNBOUNDED FOLLOWING

Ví dụ điển hình có thể là các kênh tiếp thị. Tập dữ liệu của bạn có thể chứa các sự kiện lặp lại liên tục cùng loại nhưng lý tưởng nhất là bạn muốn xâu chuỗi từng sự kiện với một sự kiện tiếp theo thuộc loại khác. Điều này có thể hữu ích khi bạn cần lấy danh sách thứ gì đó, chẳng hạn như các sự kiện, giao dịch mua, v.v. để tạo tập dữ liệu kênh. Làm việc với PARTITION BY nó cho bạn cơ hội để nhóm tất cả các sự kiện tiếp theo cho dù có bao nhiêu sự kiện tồn tại trong mỗi phân vùng.

16. Biểu thức chính quy

Bạn sẽ sử dụng nó nếu bạn cần trích xuất thứ gì đó từ dữ liệu phi cấu trúc, chẳng hạn như tỷ giá hối đoái, nhóm tùy chỉnh, v.v.

Làm việc với tỷ giá hối đoái bằng cách sử dụng regrec

Xem xét ví dụ này với dữ liệu tỷ giá hối đoái:

Làm việc với các phiên bản Ứng dụng bằng regrec

Đôi khi, bạn có thể muốn sử dụng regexp để nhận các phiên bản chính , phát hành hoặc sửa đổi cho ứng dụng của mình và tạo báo cáo tùy chỉnh:

Phần kết luận

SQL là một công cụ mạnh giúp thao tác dữ liệu. Hy vọng rằng những trường hợp sử dụng SQL này từ tiếp thị kỹ thuật số sẽ hữu ích cho bạn. Đó thực sự là một kỹ năng tiện dụng và có thể giúp bạn trong nhiều dự án. Những đoạn mã SQL này làm cho cuộc sống của tôi dễ dàng hơn rất nhiều và tôi sử dụng tại nơi làm việc gần như hàng ngày. Hơn nữa, SQL và kho dữ liệu hiện đại là những công cụ cần thiết cho khoa học dữ liệu. Các tính năng phương ngữ mạnh mẽ của nó cho phép lập mô hình và trực quan hóa dữ liệu một cách dễ dàng. Bởi vì SQL là ngôn ngữ mà các kho dữ liệu và chuyên gia kinh doanh thông minh sử dụng nên đây là một lựa chọn tuyệt vời nếu bạn muốn chia sẻ dữ liệu với họ. Đây là cách phổ biến nhất để giao tiếp với hầu hết mọi giải pháp kho/hồ dữ liệu trên thị trường.

Được xuất bản lần đầu trên mydataschool.com bởi datamike

Mike là một cá nhân đam mê và tập trung vào kỹ thuật số với vô số động lực và sự nhiệt tình, yêu thích những thách thức mà sự kết hợp đầy đủ của tiếp thị kỹ thuật số đưa ra. Sống ở Vương quốc Anh, hoàn thành bằng MBA của Đại học Newcastle năm 2015.