Gegevens partitioneren in een windowfunctie

Vensterfuncties in Snowflake

Jake Roach

Field Data Engineer

Data rangschikken

Hier rangschikken we alle rijen in de 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

Data rangschikken met partities

Nu willen we data per opgegeven window rangschikken.

Een tabel die concertbezoekers rangschikt op afstand die ze naar elke locatie hebben afgelegd

PARTITION BY

SELECT
    user_id,
    event_name,
    distance_traveled,

    RANK() OVER(
        -- Maak window per event_name
        PARTITION BY event_name
        ORDER BY km_traveled
    ) AS closest_concert_goer

FROM CONCERTS.attendance;

PARTITION BY maakt windows van rijen waarop we functies toepassen

PARTITION BY komt vóór ORDER BY in OVER(...)
Lijkt op GROUP BY, maar "voegt" rijen niet samen

Data rangschikken met partities

SELECT
    level,
    price,

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

FROM CONCERTS.attendance;

PARTITION BY maakt windows

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

                  ...

       200   |    212    |      1
       200   |    207    |      2

                  ...

Samenvattingsstatistieken met FIRST_VALUE

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

FIRST_VALUE vindt de eerste waarde in een window

<1>: kolom waarvan je de waarde wilt

<2>: veld om op te partitioneren

<3>: veld dat de eerste rij bepaalt

Samenvattingsstatistieken met AVG

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

AVG berekent het gemiddelde van een veld per window

<1>: kolom om het gemiddelde van te nemen

<2>: veld om op te partitioneren

... ORDER BY is niet nodig!

Klanttevredenheid

SELECT
    user_id, event_name, satisfaction_score,


    FIRST_VALUE(satisfaction_score) OVER(
        PARTITION BY event_name   -- Tevredenheidsscore van de dichtstbijzijnde bezoeker
        ORDER BY km_traveled
    ) AS first_score,


    -- Vind de gemiddelde tevredenheidsscore voor een "window" van rijen
    AVG(satisfaction_score) OVER(
        PARTITION BY event_name
    ) AS average_score


FROM CONCERTS.attendance;

Klanttevredenheid


      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      

                                          ...

Laten we oefenen!

Vensterfuncties in Snowflake