การแบ่งพาร์ติชันข้อมูลใน window function

Window Functions ใน Snowflake

Jake Roach

Field Data Engineer

การจัดอันดับข้อมูล

ตอนนี้กำลังจัดอันดับทุก record ในผลลัพธ์!

      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

การจัดอันดับข้อมูลด้วยพาร์ติชัน

ทีนี้เราต้องการจัดอันดับข้อมูลในแต่ละ window ที่กำหนด

ตารางแสดงการจัดอันดับผู้เข้าร่วมคอนเสิร์ตตามระยะทางที่เดินทางมายังแต่ละสถานที่

PARTITION BY

SELECT
    user_id,
    event_name,
    distance_traveled,

    RANK() OVER(
        -- Create window by event_name
        PARTITION BY event_name
        ORDER BY km_traveled
    ) AS closest_concert_goer

FROM CONCERTS.attendance;

PARTITION BY ช่วยสร้าง window ของ record เพื่อนำฟังก์ชันไปใช้

PARTITION BY ต้องอยู่ก่อน ORDER BY ใน OVER(...)
คล้ายกับ GROUP BY แต่ไม่ "ยุบ" record

การจัดอันดับข้อมูลด้วยพาร์ติชัน

SELECT
    level,
    price,

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

FROM CONCERTS.attendance;

PARTITION BY สร้าง window

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

                  ...

       200   |    212    |      1
       200   |    207    |      2

                  ...

การสร้าง summary metric ด้วย FIRST_VALUE

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

FIRST_VALUE ใช้หาค่าแรกใน window

<1>: คอลัมน์ที่ต้องการคืนค่า

<2>: field สำหรับแบ่งพาร์ติชัน

<3>: field สำหรับกำหนด record แรก

การสร้าง summary metric ด้วย AVG

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

AVG หาค่าเฉลี่ยของ field ในแต่ละ window

<1>: คอลัมน์ที่ต้องการหาค่าเฉลี่ย

<2>: field สำหรับแบ่งพาร์ติชัน

... ไม่จำเป็นต้องใช้ ORDER BY!

ความพึงพอใจของลูกค้า

SELECT
    user_id, event_name, satisfaction_score,


    FIRST_VALUE(satisfaction_score) OVER(
        PARTITION BY event_name   -- Satisfaction score for the closest concert-goer
        ORDER BY km_traveled
    ) AS first_score,


    -- Find the average satisfcation score for a "window" of records
    AVG(satisfaction_score) OVER(
        PARTITION BY event_name
    ) AS average_score


FROM CONCERTS.attendance;

ความพึงพอใจของลูกค้า


      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      

                                          ...

มาฝึกกันเถอะ!

Window Functions ใน Snowflake