Năm chức năng hàng đầu của cửa sổ SQL mà bạn nên biết cho các cuộc phỏng vấn về khoa học dữ liệu

SQL là ngôn ngữ phổ biến trong thế giới dữ liệu và là kỹ năng quan trọng nhất để trở thành một chuyên gia dữ liệu.

Lý do SQL rất quan trọng vì nó là kỹ năng chính cần có trong giai đoạn xử lý dữ liệu. Rất nhiều thăm dò dữ liệu, thao tác dữ liệu, phát triển đường ống và tạo bảng điều khiển được thực hiện thông qua SQL.

Điều ngăn cách các nhà khoa học dữ liệu vĩ đại với các nhà khoa học dữ liệu giỏi là các nhà khoa học dữ liệu vĩ đại có thể xử lý dữ liệu nhiều như khả năng của SQL cho phép. Một phần quan trọng để có thể sử dụng đầy đủ tất cả những gì SQL cung cấp là nhờ biết cách sử dụng các hàm cửa sổ.

Với điều đó đã nói, chúng ta hãy đi sâu vào nó!

1. Các vùng có LEAD () và LAG ()

 
LEAD() và LAG() chủ yếu được sử dụng khi so sánh một khoảng thời gian với khoảng thời gian trước đó đối với một số liệu nhất định. Để đưa ra một vài ví dụ…

• Bạn có thể nhận được tỷ số bằng giữa doanh số bán hàng của mỗi năm và doanh số bán hàng của năm trước
• Bạn có thể nhận được phần tử đồng bằng về số lượng đăng ký / chuyển đổi / lượt truy cập trang web trên cơ sở hàng tháng
• Bạn có thể so sánh tỷ lệ bỏ cuộc của người dùng hàng tháng

Ví dụ:

Truy vấn sau đây cho biết cách bạn có thể truy vấn phần trăm thay đổi hàng tháng trong chi phí

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. Tổng tích lũy với SUM () hoặc COUNT ()

 
Tính tổng đang chạy có thể được thực hiện đơn giản thông qua một hàm windows bắt đầu bằng SUM () hoặc COUNT (). Đây là một công cụ mạnh mẽ khi bạn muốn hiển thị sự tăng trưởng của một số liệu cụ thể theo thời gian. Cụ thể hơn, nó hữu ích trong các trường hợp sau:

• Nhận tổng doanh thu và chi phí chạy theo thời gian
• Nhận tổng thời gian dành cho ứng dụng đang chạy trên mỗi người dùng
• Nhận tổng số chuyển đổi đang chạy theo thời gian

Ví dụ:

Ví dụ sau đây cho thấy cách bạn có thể bao gồm cột tổng chi phí hàng tháng tích lũy:

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. Đường trung bình động với AVG ()

 
AVG () thực sự mạnh mẽ trong các chức năng của windows vì nó cho phép bạn tính toán di chuyển trung bình tăng ca.

Đường trung bình động là một cách đơn giản nhưng hiệu quả để dự báo các giá trị trong ngắn hạn. Chúng cũng cực kỳ hữu ích trong việc làm mịn các đường cong dễ bay hơi trên biểu đồ. Nói chung, đường trung bình được sử dụng để đánh giá hướng chung của nơi mọi thứ đang di chuyển.

Cụ thể hơn…

• Chúng có thể được sử dụng để nhận được xu hướng chung của doanh số hàng tuần (mức trung bình có tăng lên theo thời gian không?). Điều này cho thấy sự phát triển của một công ty.
• Chúng cũng có thể được sử dụng để nhận được xu hướng chung của chuyển đổi hàng tuần hoặc lượt truy cập trang web.

Ví dụ:

Truy vấn sau đây là một ví dụ về việc nhận được đường trung bình động trong 10 ngày cho các lượt chuyển đổi.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. ROW_NUMBER ()

 
ROW_NUMBER () đặc biệt hữu ích khi bạn muốn lấy bản ghi đầu tiên hoặc bản ghi cuối cùng. Ví dụ: nếu bạn có một bảng về thời gian các thành viên phòng tập thể dục đến phòng tập thể dục và bạn muốn lấy ngày của ngày đầu tiên họ đến phòng tập thể dục, bạn có thể THAM GIA THEO khách hàng (tên / id) và ĐẶT HÀNG THEO ngày mua hàng. Sau đó, để có được hàng đầu tiên, bạn có thể chỉ cần lọc các hàng có rowNumber bằng một.

Ví dụ:

Ví dụ này cho thấy cách bạn có thể sử dụng ROW_NUMBER () để biết ngày đầu tiên mỗi thành viên (người dùng) đến thăm.

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

Tóm lại, nếu bạn cần lấy bản ghi đầu tiên hoặc bản ghi cuối cùng, ROW_NUMBER () là một cách tuyệt vời để đạt được điều đó.

5. Xếp hạng kỷ lục với DENSE_RANK ()

 
DENSE_RANK () tương tự như ROW_NUMBER () ngoại trừ việc nó trả về cùng một thứ hạng cho các giá trị bằng nhau. Xếp hạng dày đặc khá hữu ích khi nói đến việc truy xuất các bản ghi hàng đầu, ví dụ:

• Nếu bạn muốn lấy top 10 chương trình Netflix được xem nhiều nhất tuần này
• Nếu bạn muốn có được 100 người dùng hàng đầu dựa trên số đô la đã chi tiêu
• Nếu bạn muốn xem hành vi của 1000 người dùng ít hoạt động nhất

Ví dụ:

Nếu bạn muốn xếp hạng những khách hàng hàng đầu của mình theo tổng doanh số, DENSE_RANK () sẽ là một hàm thích hợp để sử dụng.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

Cảm ơn vì đã đọc!

 
Và đó là tất cả! Tôi hy vọng rằng điều này sẽ giúp bạn trong quá trình chuẩn bị phỏng vấn - Tôi chắc chắn rằng nếu bạn biết từ trong ra ngoài 5 khái niệm này, bạn sẽ làm rất tốt khi gặp hầu hết các vấn đề về hàm cửa sổ SQL ngoài đó.

Như mọi khi, tôi chúc bạn đạt được kết quả tốt nhất trong nỗ lực học tập của mình!

 
 
Terence Shin là một người đam mê dữ liệu với hơn 3 năm kinh nghiệm về SQL và hơn 2 năm kinh nghiệm về Python, đồng thời là một blogger về Hướng tới Khoa học Dữ liệu và KDnuggets.

Nguyên. Đăng lại với sự cho phép.

Nguồn: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html