Particionando dados em uma função de janela

Funções de janela no Snowflake

Jake Roach

Field Data Engineer

Ranqueando dados

Aqui, estamos ranqueando todos os registros no conjunto de resultados!

      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

Ranqueando dados com partições

Agora, queremos ranquear os dados em cada janela definida.

Uma tabela que ranqueia participantes do show pela distância viajada até cada local

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 cria janelas de registros para aplicar funções

PARTITION BY vem antes de ORDER BY em OVER(...)
Parecido com GROUP BY, mas não “colapsa” registros

Ranqueando dados com partições

SELECT
    level,
    price,

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

FROM CONCERTS.attendance;

PARTITION BY cria janelas

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

                  ...

       200   |    212    |      1
       200   |    207    |      2

                  ...

Gerando métricas resumidas com FIRST_VALUE

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

FIRST_VALUE ajuda a achar o primeiro valor em uma janela

<1>: qual coluna do registro retornar

<2>: campo para particionar os dados

<3>: campo que define o primeiro registro

Gerando métricas resumidas com AVG

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

AVG calcula a média de um campo em cada janela

<1>: coluna para tirar a média

<2>: campo para particionar os dados

... sem precisar de ORDER BY!

Satisfação do cliente

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;

Satisfação do cliente


      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      

                                          ...

Vamos praticar!

Funções de janela no Snowflake