Daten in einer Window-Funktion partitionieren

Window Functions in Snowflake

Jake Roach

Field Data Engineer

Daten ranken

Hier ranken wir alle Zeilen im Resultset!

      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

Daten mit Partitionen ranken

Jetzt wollen wir die Daten je definiertem Window ranken.

Eine Tabelle, die Konzertbesucher nach der zum jeweiligen Veranstaltungsort zurückgelegten Distanz rankt

PARTITION BY

SELECT
    user_id,
    event_name,
    distance_traveled,

    RANK() OVER(
        -- Window nach event_name bilden
        PARTITION BY event_name
        ORDER BY km_traveled
    ) AS closest_concert_goer

FROM CONCERTS.attendance;

PARTITION BY erstellt Windows von Zeilen, auf die Funktionen angewendet werden

PARTITION BY steht vor ORDER BY in OVER(...)
Ähnlich wie GROUP BY, kollabiert Zeilen aber nicht

Daten mit Partitionen ranken

SELECT
    level,
    price,

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

FROM CONCERTS.attendance;

PARTITION BY erstellt Windows

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

                  ...

       200   |    212    |      1
       200   |    207    |      2

                  ...

Kennzahlen mit FIRST_VALUE erzeugen

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

FIRST_VALUE findet den ersten Wert in einem Window

<1>: Spalte, deren Wert zurückgegeben wird

<2>: Feld zum Partitionieren der Daten

<3>: Feld zur Bestimmung des ersten Datensatzes

Kennzahlen mit AVG erzeugen

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

AVG berechnet den Mittelwert eines Felds je Window

<1>: Spalte für den Durchschnitt

<2>: Feld zum Partitionieren der Daten

... ORDER BY ist nicht nötig!

Kundenzufriedenheit

SELECT
    user_id, event_name, satisfaction_score,


    FIRST_VALUE(satisfaction_score) OVER(
        PARTITION BY event_name   -- Zufriedenheitswert des nächstgelegenen Konzertbesuchers
        ORDER BY km_traveled
    ) AS first_score,


    -- Durchschnittlichen Zufriedenheitswert für ein "Window" von Zeilen ermitteln
    AVG(satisfaction_score) OVER(
        PARTITION BY event_name
    ) AS average_score


FROM CONCERTS.attendance;

Kundenzufriedenheit


      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      

                                          ...

Lass uns üben!

Window Functions in Snowflake