NTILE und CUME_DIST

Window Functions in Snowflake

Jake Roach

Field Data engineer

Erstellen von Daten-Buckets

Wie können wir Mitglieder basierend auf ihren Workouts klassifizieren, um die richtigen Kurse zu vermarkten?

             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 wird verwendet, um "N" gleich große "Buckets" zu erstellen

<n>: Anzahl der Buckets

<1>: Feld zur Erstellung der Buckets

<2>: Feld zur gleichmäßigen Verteilung der Datensätze mit PARTITION BY

Fitnessdaten in Buckets

SELECT
    member_id,
    gym_location,
    calories_burned,

    -- Erstelle 50 gleich große Daten-Buckets

    NTILE(50) OVER(
        ORDER BY calories_burned   -- Bestimmt die Datensätze in jedem Bucket
    ) AS marketing_group


FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;  -- Ergebnis sortieren

Fitnessdaten in Buckets

         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
                                        ...

Gleichmäßig verteilte Fitnessdaten

SELECT
    member_id,
    gym_location,
    calories_burned,

    NTILE(50) OVER(

        -- Gleichmäßige Verteilung der Datensätze pro gym_location
        PARTITION BY gym_location

        ORDER BY calories_burned
    ) AS marketing_group

FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;

Gleichmäßig verteilte Fitnessdaten

         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

                                    ...

Verteilung verstehen

Wie ist die Verteilung der verbrannten Kalorien pro Workout?

Wo liegt ein bestimmtes Workout in dieser Verteilung?

Welcher Anteil der Mitglieder hat gleich viele oder weniger Kalorien verbrannt?

  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,   -- Verteilung pro Standort erstellen
        ORDER BY calories_burned
    ) AS cd

FROM FITNESS.workouts

ORDER BY gym_location, cd;  -- Ergebnis sortieren

CUME_DIST

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


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


...;

Vergleicht jeden Datensatz mit der Verteilung für diese Spalte/Feld, kumulative Verteilung

<1>: Feld, das das Fenster bestimmt

<2>: Feld zur Erstellung der Verteilung

Welcher Anteil der Datensätze ist kleiner oder gleich diesem?

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

                                          ...

Lass uns üben!

Window Functions in Snowflake