NTILE e CUME_DIST

Funções de janela no Snowflake

Jake Roach

Field Data engineer

Criando grupos de linhas

Como podemos classificar os membros da academia com base em seus treinos para oferecer as aulas certas?

             member_id  |  gym_location  |  calories_burned  |  marketing_group
            ----------- | -------------- | ----------------- | -----------------
               m_192    |    Miami       |         45        |         1
               m_74     |    Miami       |         59        |         1
               m_233    |    Portland    |         60        |         1

               m_14     |    Cleveland   |         72        |         2
               m_346    |    Portland    |         77        |         2
               m_289    |    Cleveland   |         81        |         2

               m_565    |    Miami       |        1085       |         50
                                        ...

NTILE

SELECT
    <fields>,
    <2>,
    <1>,


    NTILE(<n>) OVER(
        PARTITION BY <2>
        ORDER BY <1>
    )


...;

NTILE é usado para criar "N" grupos de tamanho igual

<n>: número de grupos

<1>: campo usado para criar grupos

<2>: campo usado para distribuir registros com PARTITION BY

Agrupando dados de fitness

SELECT
    member_id,
    gym_location,
    calories_burned,

    -- Criar 50 grupos de tamanho igual

    NTILE(50) OVER(
        ORDER BY calories_burned   -- Decide os registros em cada grupo
    ) AS marketing_group


FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;  -- Ordenar o resultado final

Agrupando dados de fitness

         member_id  |  gym_location  |  calories_burned  |  marketing_group
        ----------- | -------------- | ----------------- | -----------------
           m_192    |    Miami       |         45        |         1
           m_74     |    Miami       |         59        |         1
           m_233    |    Portland    |         60        |         1

           m_14     |    Cleveland   |         72        |         2
           m_346    |    Portland    |         77        |         2
           m_289    |    Cleveland   |         81        |         2

           m_565    |    Miami       |        1085       |         50
                                        ...

Distribuição uniforme de dados de fitness

SELECT
    member_id,
    gym_location,
    calories_burned,

    NTILE(50) OVER(

        -- Distribuir registros igualmente por gym_location
        PARTITION BY gym_location

        ORDER BY calories_burned
    ) AS marketing_group

FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;

Distribuição uniforme de dados de fitness

         member_id  |  gym_location  |  calories_burned  |  marketing_group
        ----------- | -------------- | ----------------- | -----------------
           m_192    |    Miami       |         45        |         1
           m_233    |    Portland    |         60        |         1
           m_14     |    Cleveland   |         72        |         1

           m_74     |    Miami       |         59        |         2
           m_346    |    Portland    |         77        |         2
           m_289    |    Cleveland   |         81        |         2

                                    ...

Entendendo uma distribuição

Qual é a distribuição das calorias queimadas por treino de cada membro?

Onde um treino específico se encaixa nessa distribuição?

Qual proporção de membros queimou a mesma ou menos calorias que um membro específico?

  member_id  |  cals_burned  |   cd   
 ----------- | ------------- | ------
    m_192    |       45      |  .016
    m_74     |       59      |  .033
    m_233    |       60      |  .049
    m_14     |       72      |  .066
    m_346    |       77      |  .082
    m_289    |       81      |  .098

                    ....

    m_565    |      1085     |  1.000

CUME_DIST

SELECT
    member_id,
    gym_location,
    calories_burned,

    CUME_DIST() OVER(
        PARTITION BY gym_location,   -- Criar uma distribuição para cada local
        ORDER BY calories_burned
    ) AS cd

FROM FITNESS.workouts

ORDER BY gym_location, cd;  -- Ordenar o resultado final

CUME_DIST

SELECT
    <fields>,
    <1>,
    <2>,


    CUME_DIST() OVER(
        PARTITION BY <1>
        ORDER BY <2>
    )


...;

Compara cada registro com a distribuição para aquela coluna/campo, distribuição cumulativa

<1>: campo que determina a janela para avaliar

<2>: campo para criar a distribuição

Qual proporção de registros é menor ou igual a este?

CUME_DIST

               member_id  |  gym_location  | calories_burned  |   cd   
              ----------- | -------------- | ---------------- | -------
                 m_192    |    Miami       |        45        |  .033
                 m_74     |    Miami       |        59        |  .066
                 m_288    |    Miami       |        83        |  .098
                 m_541    |    Miami       |        85        |  .131

                                          ...

                 m_233    |    Portland    |        60        |  .071
                 m_346    |    Portland    |        77        |  .142

                                          ...

Vamos praticar!

Funções de janela no Snowflake