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: https://Gist.github.com/mshakhomirov/18775cbbe8288af864ad79247c0de63d?embedable=true#file-1-1-sql Đ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 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. 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 (hoặc dấu thời gian) mới nhất đã biết. Ví dụ: trong tập dữ liệu 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 những người dùng hiện có và những người dùng mới. các phiên transaction_id transaction_id user_id last_online update insert MERGE và cập nhật gia tăng Bạn có thể sử dụng 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. MERGE 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: https://Gist.github.com/mshakhomirov/5ad1a7518c54bc030d1c78b56fe3cf82?embedable=true#file-1-2-sql 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: https://Gist.github.com/mshakhomirov/694e040539b0d1b556f8e053d315a3bf?embedable=true#file-2-sql 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 đề nhưng thay vào đó, bạn có thể thực hiện : where việc này https://Gist.github.com/mshakhomirov/933e6a358e49dcccd4e547a5509c8fda?embedable=true#file-3-sql Một ví dụ khác về cách sử dụng nó với các bảng . . Đâ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 KHÔNG được phân vùng Đừng làm điều này sẽ bị tính phí khi quét toàn bộ bảng. https://Gist.github.com/mshakhomirov/1c62d79cd9690140c569cd047b9d491f?embedable=true#file-3-2-sql Bạn cũng có thể sử dụng nó trong mệnh đề và các hàm . HAVING 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 đề , đồ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. where https://Gist.github.com/mshakhomirov/4cf738aaad967fe92c4fb7192874fadf?embedable=true#file-4-sql 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 trở nên hữu ích: to_json_string() https://Gist.github.com/mshakhomirov/aac1f93312ae305ba80c915fe4a2a386?embedable=true#file-5-sql 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 , và . Đố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: user_id date total_cost https://Gist.github.com/mshakhomirov/e4f11721eb5a3182150df08f25b70d64?embedable=true#file-6-sql 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 đó? https://Gist.github.com/mshakhomirov/ebf5488d0036bc9b84ae05889346d986?embedable=true#file-7-sql 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 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à : việc giữ chân người dùng GENERATE_DATE_ARRAY https://Gist.github.com/mshakhomirov/2ba5a67053f85794462dab98e56ad74d?embedable=true#file-8-sql 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: https://Gist.github.com/mshakhomirov/05d0c04c5975207d98552ffd436add8b?embedable=true#file-9-sql 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ư 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 , tôi có thể xem họ mất bao lâu để đăng nhập. Login duration in seconds login Hàm này chia các hàng thành các nhóm 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 ước tính thành NULL, 0 hoặc âm, một lỗi được cung cấp. constant_integer_expression constant_integer_expression https://Gist.github.com/mshakhomirov/16fe941aa8c4ed79e4aad8b7049b307a?embedable=true#file-10-sql 11. Xếp hạng / xếp hạng dày đặc Chúng còn được gọi là các hàm . Tôi có xu hướng sử dụng vì nó không bỏ qua xếp hạng có sẵn tiếp theo trong khi 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ị. đánh số DENSE_RANK làm chức năng xếp hạng mặc định RANK Ví dụ: https://Gist.github.com/mshakhomirov/459b68c5f3d1e8284c01e516db1d8dcb?embedable=true#file-11-1-sql Một ví dụ khác với giá sản phẩm: https://Gist.github.com/mshakhomirov/4c90a6fc8516d8264e172676a83a1048?embedable=true#file-11-2-sql 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 https://Gist.github.com/mshakhomirov/f90b035ba259e672d4d51a669e0cd1fc?embedable=true#file-12-sql 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ể đó. https://Gist.github.com/mshakhomirov/ea4de9144b97bf8c196cab07609c309e?embedable=true#file-13-sql 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: https://Gist.github.com/mshakhomirov/35d956fa9db86b12b44ab62c00f42a40?embedable=true#file-14-sql Theo cách tương tự, bạn có thể tạo bảng mà không cần sử dụng . 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 + + trong trình chỉnh sửa của mình. UNION ALL Alt Shift Down https://Gist.github.com/mshakhomirov/6ea226c1b5b789d4a31691ce065c20d7?embedable=true#file-14-2-sql 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. https://Gist.github.com/mshakhomirov/05fd7d79d8acf3b173181a5d950ab6e7?embedable=true#file-15-sql 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: https://Gist.github.com/mshakhomirov/9ca6e153da19c491034bd57995875308?embedable=true#file-16-1-sql 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 để nhận các phiên bản , hoặc cho ứng dụng của mình và tạo báo cáo tùy chỉnh: regexp chính phát hành sửa đổi https://Gist.github.com/mshakhomirov/b1f442a296ffef52c7baa1245e1dc316?embedable=true#file-16-2-sql 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 bởi mydataschool.com 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.

Read me on Medium.com

Nghe bài viết này bằng Tiếng Anh, đọc bởi robot thông minh của HackerNoon

dài quá đọc không nổi

Making security fun one episode at a time

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

dài quá đọc không nổi

Mike Shakhomirov

About Author

chuyên mục

Languages

BÀI VIẾT NÀY CŨNG CÓ MẶT TẠI...

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

dài quá đọc không nổi

Mike Shakhomirov

About Author

chuyên mục

Languages

BÀI VIẾT NÀY CŨNG CÓ MẶT TẠI...

NHỮNG BÀI VIẾT LIÊN QUAN