Phân vùng dữ liệu trong hàm cửa sổ

Window Functions trong Snowflake

Jake Roach

Field Data Engineer

Xếp hạng dữ liệu

Ở đây, chúng ta xếp hạng tất cả bản ghi trong tập kết quả!

      user_id  |    event_name   |  km_traveled  |  closest_attendees 
      -------  | --------------- | ------------- | ----------------- 
      user_81  |  Lunar Drift    |      0.5      |         1         
      user_02  |  Lunar Drift    |      1.1      |         2       
      user_33  |  Crimson Arc    |      8.6      |         3          
      user_33  |  Neon Prophet   |      8.6      |         3         
      user_15  |  VibeStorm      |      17       |         5          
      user_94  |  The Dusk Owls  |      41       |         6        
      user_47  |  Lunar Drift    |      61       |         7   
      user_56  |  Crimson Arc    |      116      |         8

Xếp hạng dữ liệu với phân vùng

Giờ, chúng ta muốn xếp hạng dữ liệu theo từng cửa sổ xác định.

Một bảng xếp hạng người dự concert theo quãng đường họ di chuyển đến từng địa điểm

PARTITION BY

SELECT
    user_id,
    event_name,
    distance_traveled,

    RANK() OVER(
        -- Tạo cửa sổ theo event_name
        PARTITION BY event_name
        ORDER BY km_traveled
    ) AS closest_concert_goer

FROM CONCERTS.attendance;

PARTITION BY giúp tạo các cửa sổ bản ghi để áp dụng hàm

PARTITION BY đứng trước ORDER BY trong OVER(...)
Tương tự GROUP BY, nhưng không "gộp" bản ghi

Xếp hạng dữ liệu với phân vùng

SELECT
    level,
    price,

    RANK() OVER(
        PARTITION BY level
        ORDER BY price DESC
    ) AS price_rank

FROM CONCERTS.attendance;

PARTITION BY tạo các cửa sổ

      level  |   price   | price_rank 
    -------- | --------- | -----------
       100   |    765    |      1
       100   |    617    |      2
       100   |    490    |      3
       100   |    490    |      3

                  ...

       200   |    212    |      1
       200   |    207    |      2

                  ...

Tạo số liệu tóm tắt với FIRST_VALUE

    FIRST_VALUE(<1>) OVER(
        PARTITION BY <2>
        ORDER BY <3>
    ) AS <alias>

FIRST_VALUE giúp tìm giá trị đầu tiên trong một cửa sổ

<1>: cột cần trả về

<2>: trường dùng để phân vùng dữ liệu

<3>: trường xác định bản ghi đầu tiên

Tạo số liệu tóm tắt với AVG

AVG(<1>) OVER(
    PARTITION BY <2>
    -- No need to ORDER BY!
) AS <alias>

AVG tính giá trị trung bình của một trường cho mỗi cửa sổ

<1>: cột cần lấy trung bình

<2>: trường dùng để phân vùng dữ liệu

... không cần ORDER BY!

Mức độ hài lòng của khách hàng

SELECT
    user_id, event_name, satisfaction_score,


    FIRST_VALUE(satisfaction_score) OVER(
        PARTITION BY event_name   -- Điểm hài lòng của khán giả gần nhất
        ORDER BY km_traveled
    ) AS first_score,


    -- Tính điểm hài lòng trung bình cho một "cửa sổ" bản ghi
    AVG(satisfaction_score) OVER(
        PARTITION BY event_name
    ) AS average_score


FROM CONCERTS.attendance;

Mức độ hài lòng của khách hàng


      user_id  |   event_name   |  satisfaction_score  |  first_score  |  average_score 
     --------- | -------------- | -------------------- | ------------- | ---------------

      user_26  |  Pulse Theory  |          71          |       98      |      84.5      
      user_92  |  Pulse Theory  |          98          |       98      |      84.5      

                                          ...

      user_57  |   Nova Sway    |           4          |       22      |      29.3      
      user_39  |   Nova Sway    |          22          |       22      |      29.3      
      user_44  |   Nova Sway    |          62          |       22      |      29.3      

                                          ...

Ayo berlatih!

Window Functions trong Snowflake