Partitionner les données dans une fonction fenêtre

Fonctions de fenêtre dans Snowflake

Jake Roach

Field Data Engineer

Classer des données

Ici, nous classons tous les enregistrements du jeu de résultats.

      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

Classer avec partitions

Nous voulons maintenant classer les données pour chaque fenêtre définie.

Un tableau qui classe les participant·e·s aux concerts selon la distance parcourue jusqu’au lieu

PARTITION BY

SELECT
    user_id,
    event_name,
    distance_traveled,

    RANK() OVER(
        -- Créer une fenêtre par event_name
        PARTITION BY event_name
        ORDER BY km_traveled
    ) AS closest_concert_goer

FROM CONCERTS.attendance;

PARTITION BY permet de créer des fenêtres d’enregistrements pour appliquer des fonctions

PARTITION BY vient avant ORDER BY dans OVER(...)
Semblable à GROUP BY, mais ne « compresse » pas les lignes

Classer avec partitions

SELECT
    level,
    price,

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

FROM CONCERTS.attendance;

PARTITION BY crée des fenêtres

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

                  ...

       200   |    212    |      1
       200   |    207    |      2

                  ...

Générer des métriques avec FIRST_VALUE

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

FIRST_VALUE permet d’obtenir la première valeur dans une fenêtre

<1>: colonne à renvoyer

<2>: champ pour partitionner les données

<3>: champ pour déterminer le premier enregistrement

Générer des métriques avec AVG

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

AVG calcule la moyenne d’un champ pour chaque fenêtre

<1>: colonne à moyenner

<2>: champ pour partitionner les données

... pas besoin de ORDER BY !

Satisfaction client·e·s

SELECT
    user_id, event_name, satisfaction_score,


    FIRST_VALUE(satisfaction_score) OVER(
        PARTITION BY event_name   -- Score de satisfaction du participant le plus proche
        ORDER BY km_traveled
    ) AS first_score,


    -- Trouver la moyenne de satisfaction pour une « fenêtre » d’enregistrements
    AVG(satisfaction_score) OVER(
        PARTITION BY event_name
    ) AS average_score


FROM CONCERTS.attendance;

Satisfaction client·e·s


      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      

                                          ...

Passons à la pratique !

Fonctions de fenêtre dans Snowflake