NTILE และ CUME_DIST

Window Functions ใน Snowflake

Jake Roach

Field Data engineer

การสร้างกลุ่มของแถวข้อมูล

จะจำแนกสมาชิกยิมตามการออกกำลังกายเพื่อทำการตลาดคลาสที่เหมาะสมได้อย่างไร?

             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 ใช้สร้างกลุ่ม "N" กลุ่มที่มีขนาดเท่ากัน

<n>: จำนวนกลุ่ม

<1>: ฟิลด์ที่ใช้แบ่งกลุ่ม

<2>: ฟิลด์ที่ใช้กระจายระเบียนอย่างสม่ำเสมอด้วย PARTITION BY

การแบ่งกลุ่มข้อมูลฟิตเนส

SELECT
    member_id,
    gym_location,
    calories_burned,

    -- Create 50 equally-sized buckets of data

    NTILE(50) OVER(
        ORDER BY calories_burned   -- Decides the records in each bucket
    ) AS marketing_group


FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;  -- ORDER the final result set

การแบ่งกลุ่มข้อมูลฟิตเนส

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

การแบ่งกลุ่มข้อมูลฟิตเนสอย่างสม่ำเสมอ

SELECT
    member_id,
    gym_location,
    calories_burned,

    NTILE(50) OVER(

        -- Evenly distribute records in bucket by each gym_location
        PARTITION BY gym_location

        ORDER BY calories_burned
    ) AS marketing_group

FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;

การแบ่งกลุ่มข้อมูลฟิตเนสอย่างสม่ำเสมอ

         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

                                    ...

ทำความเข้าใจการกระจาย

การกระจายของแคลอรีที่เผาผลาญในแต่ละการออกกำลังกายเป็นอย่างไร?

การออกกำลังกายครั้งนั้นอยู่ในตำแหน่งใดของการกระจาย?

สมาชิกสัดส่วนเท่าใดที่เผาผลาญแคลอรีเท่ากับหรือน้อยกว่าสมาชิกคนนั้น?

  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,   -- Create a distribution for each location
        ORDER BY calories_burned
    ) AS cd

FROM FITNESS.workouts

ORDER BY gym_location, cd;  -- ORDER the final result set

CUME_DIST

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


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


...;

เปรียบเทียบแต่ละระเบียนกับการกระจายของคอลัมน์นั้น (การกระจายสะสม)

<1>: ฟิลด์ที่กำหนดหน้าต่างสำหรับการประเมิน

<2>: ฟิลด์ที่ใช้สร้างการกระจาย

ระเบียนสัดส่วนเท่าใดที่มีค่าน้อยกว่าหรือเท่ากับระเบียนนี้?

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

                                          ...

มาฝึกกันเถอะ!

Window Functions ใน Snowflake