Công nghệ xây dựng

Các chức năng của cửa sổ SQL trong các cuộc phỏng vấn về khoa học dữ liệu được hỏi bởi Airbnb, Netflix, Twitter và Uber – Bài viết mới cập nhật năm 2024

Các chức năng của cửa sổ SQL trong các cuộc phỏng vấn về khoa học dữ liệu được hỏi bởi Airbnb, Netflix, Twitter và Uber
– Cập nhật kiến thức mới nhất năm 2023

Diễn đàn duy nhất dành cho toàn ngành, đa lĩnh vực để tìm kiếm và chia sẻ kiến thức về quy hoạch, thiết kế, kiến trúc, công nghệ xây dựng và vận hành các tài sản được xây dựng.

Các hàm cửa sổ là một nhóm các hàm sẽ thực hiện các phép tính trên một tập hợp các hàng có liên quan đến hàng hiện tại của bạn. Chúng được coi là sql nâng cao và thường được hỏi trong các cuộc phỏng vấn khoa học dữ liệu. Nó cũng được sử dụng rất nhiều trong công việc để giải quyết nhiều dạng vấn đề khác nhau. Hãy tóm tắt 4 loại chức năng cửa sổ khác nhau và giải thích lý do tại sao và khi nào bạn sử dụng chúng.

4 loại chức năng của cửa sổ

1. Hàm tổng hợp thông thường

o Đây là các tổng hợp như AVG, MIN / MAX, COUNT, SUM

o Bạn sẽ muốn sử dụng những thứ này để tổng hợp dữ liệu của mình và nhóm nó theo một cột khác như tháng hoặc năm

2. Chức năng xếp hạng

o ROW_NUMBER, RANK, RANK_DENSE

o Đây là những chức năng giúp bạn xếp hạng dữ liệu của mình. Bạn có thể xếp hạng toàn bộ tập dữ liệu của mình hoặc xếp hạng chúng theo các nhóm như theo tháng hoặc quốc gia

o Cực kỳ hữu ích để tạo chỉ số xếp hạng trong nhóm

3. Tạo thống kê

o Đây là những điều tuyệt vời nếu bạn cần tạo các thống kê đơn giản như NTILE (phần trăm, phần tư, trung bình)

o Bạn có thể sử dụng điều này cho toàn bộ tập dữ liệu của mình hoặc theo nhóm

4. Xử lý dữ liệu chuỗi thời gian

o Một hàm cửa sổ rất phổ biến, đặc biệt nếu bạn cần tính toán các xu hướng như trung bình luân phiên hàng tháng hoặc số liệu tăng trưởng

o LAG và LEAD là hai chức năng cho phép bạn thực hiện điều này.

1. Hàm tổng hợp thông thường

Các hàm tổng hợp thông thường là các hàm như trung bình, đếm, tổng, tối thiểu / tối đa được áp dụng cho các cột. Mục tiêu là áp dụng hàm tổng hợp nếu bạn muốn áp dụng tổng hợp cho các nhóm khác nhau trong tập dữ liệu, chẳng hạn như tháng.

Điều này tương tự như kiểu tính toán có thể được thực hiện với một hàm tổng hợp mà bạn tìm thấy trong mệnh đề SELECT, nhưng không giống như các hàm tổng hợp thông thường, các hàm cửa sổ không nhóm một số hàng thành một hàng đầu ra duy nhất, chúng được nhóm lại với nhau hoặc giữ lại danh tính riêng của họ, tùy thuộc vào cách bạn tìm thấy chúng.

Ví dụ trung bình ():

Hãy xem một ví dụ về hàm cửa sổ avg () được triển khai để trả lời câu hỏi phân tích dữ liệu. Bạn có thể xem câu hỏi và viết mã trong liên kết bên dưới:

platform.stratascratch.com/coding-question?id=10302&python=

Đây là một ví dụ hoàn hảo về việc sử dụng hàm cửa sổ và sau đó áp dụng trung bình () cho nhóm tháng. Ở đây, chúng tôi đang cố gắng tính toán khoảng cách trung bình trên mỗi đô la theo tháng. Điều này khó thực hiện trong SQL mà không có chức năng cửa sổ này. Ở đây, chúng tôi đã áp dụng hàm cửa sổ avg () cho cột thứ 3, nơi chúng tôi đã tìm thấy giá trị trung bình của tháng-năm cho mỗi tháng-năm trong tập dữ liệu. Chúng tôi có thể sử dụng số liệu này để tính toán sự khác biệt giữa mức trung bình của tháng và mức trung bình của ngày cho mỗi ngày yêu cầu trong bảng.

Mã để triển khai chức năng cửa sổ sẽ giống như sau:

CHỌN a.request_date,

a.dist_to_cost,

AVG (a.dist_to_cost) HẾT (PHẦN BỞI a.request_mnth) NHƯ avg_dist_to_cost

TỪ

(LỰA CHỌN *,

to_char (request_date :: date, ‘YYYY-MM’) AS request_mnth,

(distance_to_travel / money_cost) AS dist_to_cost

FROM uber_request_logs) a

ĐẶT HÀNG THEO request_date

2. Chức năng Xếp hạng

Chức năng xếp hạng là một tiện ích quan trọng đối với một nhà khoa học dữ liệu. Bạn luôn xếp hạng và lập chỉ mục dữ liệu của mình để hiểu rõ hơn những hàng nào là tốt nhất trong tập dữ liệu của bạn. Các hàm cửa sổ SQL cung cấp cho bạn 3 tiện ích xếp hạng – RANK (), DENSE_RANK (), ROW_NUMBER () – tùy thuộc vào trường hợp sử dụng chính xác của bạn. Các chức năng này sẽ giúp bạn liệt kê dữ liệu của mình theo thứ tự và theo nhóm dựa trên những gì bạn mong muốn.

Xếp hạng () Ví dụ:

Hãy xem một ví dụ về hàm cửa sổ xếp hạng để xem cách chúng ta có thể xếp hạng dữ liệu trong các nhóm bằng cách sử dụng các hàm cửa sổ SQL. Theo dõi tương tác với liên kết này: platform.stratascratch.com/coding-question?id=9898&python=

Ở đây chúng tôi muốn tìm mức lương cao nhất theo bộ phận. Chúng tôi không thể chỉ tìm thấy 3 mức lương cao nhất mà không có chức năng cửa sổ vì nó sẽ chỉ cung cấp cho chúng tôi 3 mức lương cao nhất trên tất cả các phòng ban, vì vậy chúng tôi cần xếp hạng mức lương theo từng phòng ban. Điều này được thực hiện theo cấp bậc () và phân vùng theo bộ phận. Từ đó, thực sự dễ dàng để lọc 3 người hàng đầu trên tất cả các phòng ban

Đây là mã để xuất ra bảng này. Bạn có thể sao chép và dán trong trình soạn thảo SQL trong liên kết ở trên và xem kết quả tương tự.

CHỌN bộ phận,

lương,

RANK () HẾT (PHẦN BỞI a. Bộ phận

ĐẶT HÀNG BẰNG a.salary DESC) AS rank_id

TỪ

(CHỌN bộ phận, tiền lương

FROM twitter_employee

NHÓM THEO bộ phận, mức lương

ĐẶT HÀNG THEO bộ phận, mức lương) a

ĐẶT HÀNG THEO bộ phận,

lương DESC

3. NTILE

NTILE là một chức năng rất hữu ích cho những người phân tích dữ liệu, phân tích kinh doanh và khoa học dữ liệu. Thông thường khi đến hạn với dữ liệu thống kê, bạn có thể cần phải tạo các thống kê mạnh mẽ như tứ phân vị, ngũ phân vị, trung vị, thập phân trong công việc hàng ngày của mình và NTILE giúp dễ dàng tạo các kết quả đầu ra này.

NTILE lấy đối số về số lượng thùng (hoặc về cơ bản là bao nhiêu nhóm bạn muốn chia dữ liệu của mình thành), sau đó tạo số lượng thùng này bằng cách chia dữ liệu của bạn thành số lượng thùng đó. Bạn đặt cách dữ liệu được sắp xếp và phân vùng, nếu bạn muốn các nhóm bổ sung.

Ví dụ NTILE (100)

Trong ví dụ này, chúng ta sẽ học cách sử dụng NTILE để phân loại dữ liệu của chúng ta thành các phần trăm. Bạn có thể theo dõi tương tác trong liên kết tại đây: platform.stratascratch.com/coding-question?id=10303&python=

Những gì bạn đang cố gắng làm ở đây là xác định 5% xác nhận quyền sở hữu hàng đầu dựa trên điểm mà thuật toán đưa ra. Nhưng bạn không thể chỉ tìm 5% hàng đầu và đặt hàng bởi vì bạn muốn tìm 5% hàng đầu theo tiểu bang. Vì vậy, một cách để thực hiện việc này là sử dụng hàm xếp hạng NTILE () và sau đó là PARTITION theo trạng thái. Sau đó, bạn có thể áp dụng bộ lọc trong mệnh đề WHERE để nhận được 5% hàng đầu.

Đây là mã để xuất toàn bộ bảng trên. Bạn có thể sao chép và dán nó vào liên kết ở trên.

SELECT policy_num,

tiểu bang,

đòi_cost,

điểm_bán,

phần trăm

TỪ

(LỰA CHỌN *,

NTILE (100) HẾT (PHẦN THEO tiểu bang

ĐẶT HÀNG THEO LỆNH_score DESC) Dưới dạng phân vị

FROM cheat_score) a

Phân vị WHERE <= 5

4. Xử lý dữ liệu chuỗi thời gian

LAG và LEAD là hai hàm cửa sổ hữu ích để xử lý dữ liệu chuỗi thời gian. Sự khác biệt duy nhất giữa LAG và LEAD là bạn muốn lấy từ hàng trước hay hàng sau, gần giống như lấy mẫu từ dữ liệu trước đó hoặc dữ liệu trong tương lai.

Bạn có thể sử dụng LAG và LEAD để tính toán mức tăng trưởng hàng tháng hoặc mức trung bình luân phiên. Là một nhà khoa học dữ liệu và nhà phân tích kinh doanh, bạn luôn xử lý dữ liệu chuỗi thời gian và tạo các số liệu thời gian đó.

LAG () Ví dụ:

Trong ví dụ này, chúng tôi muốn tìm tỷ lệ phần trăm tăng trưởng hàng năm, đây là một câu hỏi rất phổ biến mà các nhà khoa học dữ liệu và nhà phân tích kinh doanh trả lời hàng ngày. Câu lệnh sự cố, dữ liệu và trình soạn thảo SQL có trong liên kết sau nếu bạn muốn thử tự viết mã giải pháp: platform.stratascratch.com/coding-question?id=9637&python=

Điều khó khăn về vấn đề này là dữ liệu được thiết lập – bạn cần sử dụng giá trị của hàng trước đó trong chỉ số của mình. Nhưng SQL không được xây dựng để làm điều đó. SQL được xây dựng để tính toán bất cứ thứ gì bạn muốn miễn là các giá trị nằm trên cùng một hàng. Vì vậy, chúng ta có thể sử dụng hàm cửa sổ lag () hoặc lead () sẽ lấy các hàng trước đó hoặc tiếp theo và đặt nó vào hàng hiện tại của bạn, đó là những gì câu hỏi này đang làm.

Đây là mã để xuất toàn bộ bảng trên. Bạn có thể sao chép và dán mã trong trình soạn thảo SQL trong liên kết ở trên:

CHỌN năm,

current_year_host,

trước_year_host,

round (((current_year_host – pres_year_host) / (cast (pres_year_host AS số))) * 100) ước tính phát triển

TỪ

(CHỌN năm,

current_year_host,

LAG (current_year_host, 1) OVER (ORDER THEO năm) AS trước_year_host

TỪ

(Trích xuất CHỌN (năm

FROM host_since :: date) NHƯ năm,

count (id) current_year_host

TỪ airbnb_search_details

WHERE host_since KHÔNG ĐẦY ĐỦ

NHÓM THEO trích xuất (năm

FROM host_since :: date)

ĐẶT HÀNG THEO năm) t1) t2

Kết thúc
Ngoài các bài viết tin tức, bài báo hàng ngày của SEMTEK, nguồn nội dung cũng bao gồm các bài viết từ các cộng tác viên chuyên gia đầu ngành về chuỗi kiến thức kỹ thuật, công nghệ xây dựng, kiến trúc và kiến thức quản lý, phát triển tổ chức doanh nghiệp,.. được chia sẽ chủ yếu từ nhiều khía cạnh liên quan chuỗi kiến thức này.
Bạn có thể dành thời gian để xem thêm các chuyên mục nội dung chính với các bài viết tư vấn, chia sẻ mới nhất, các tin tức gần đây từ chuyên gia và đối tác của Chúng tôi. Cuối cùng, với các kiến thức chia sẻ của bài viết, hy vọng góp phần nào kiến thức hỗ trợ cho độc giả tốt hơn trong hoạt động nghề nghiệp cá nhân!
* Ý kiến được trình bày trong bài viết này là của tác giả khách mời và không nhất thiết phải là SEMTEK. Nhân viên tác giả, cộng tác viên biên tập sẽ được liệt kê bên cuối bài viết.
Trân trọng,
Các chuyên mục nội dung liên quan

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Back to top button