NTILE dan CUME_DIST

Fungsi Window di Snowflake

Jake Roach

Field Data engineer

Membuat bucket baris

Bagaimana kita dapat mengklasifikasikan anggota gym berdasarkan latihan mereka untuk memasarkan kelas yang tepat?

             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 digunakan untuk membuat "N" jumlah "bucket" yang berukuran sama

<n>: jumlah bucket

<1>: field yang digunakan untuk membuat bucket

<2>: field yang digunakan untuk mendistribusikan catatan secara merata menggunakan PARTITION BY

Membuat bucket data kebugaran

SELECT
    member_id,
    gym_location,
    calories_burned,

    -- Buat 50 bucket data yang berukuran sama

    NTILE(50) OVER(
        ORDER BY calories_burned   -- Menentukan catatan dalam setiap bucket
    ) AS marketing_group


FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;  -- URUTKAN set hasil akhir

Membuat bucket data kebugaran

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

Bucket data kebugaran yang terdistribusi merata

SELECT
    member_id,
    gym_location,
    calories_burned,

    NTILE(50) OVER(

        -- Sebarkan catatan secara merata dalam bucket berdasarkan setiap gym_location
        PARTITION BY gym_location

        ORDER BY calories_burned
    ) AS marketing_group

FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;

Bucket data kebugaran yang terdistribusi merata

         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

                                    ...

Memahami distribusi

Bagaimana distribusi kalori yang dibakar untuk setiap latihan anggota?

Di mana posisi latihan tertentu dalam distribusi ini?

Berapa proporsi anggota yang membakar kalori sama atau lebih sedikit dari anggota tertentu?

  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,   -- Buat distribusi untuk setiap lokasi
        ORDER BY calories_burned
    ) AS cd

FROM FITNESS.workouts

ORDER BY gym_location, cd;  -- URUTKAN set hasil akhir

CUME_DIST

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


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


...;

Membandingkan setiap catatan dengan distribusi untuk kolom/field tersebut, distribusi kumulatif

<1>: field yang menentukan jendela untuk evaluasi

<2>: field untuk membuat distribusi

Berapa proporsi catatan yang kurang dari atau sama dengan ini?

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

                                          ...

Ayo berlatih!

Fungsi Window di Snowflake