     
 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

Read My Stories

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

Make resilience your competitive advantage

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

About Author

BÌNH LUẬN

chuyên mục

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

Related Stories

309 Stories To Learn About Smart Contracts

94 Stories To Learn About John Locke

184 Stories To Learn About Psychology

581 Stories To Learn About Non Fiction

309 Stories To Learn About Smart Contracts

94 Stories To Learn About John Locke

184 Stories To Learn About Psychology

581 Stories To Learn About Non Fiction

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps