NTILE và CUME_DIST

Window Functions trong Snowflake

Jake Roach

Field Data engineer

Tạo các nhóm hàng (buckets)

Phân loại hội viên theo lượt tập để tiếp thị lớp phù hợp như thế nào?

             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 dùng để tạo N "bucket" có kích thước bằng nhau

<n>: số bucket

<1>: trường dùng để tạo bucket

<2>: trường dùng để phân bổ đều bản ghi bằng PARTITION BY

Phân nhóm dữ liệu thể hình

SELECT
    member_id,
    gym_location,
    calories_burned,

    -- Tạo 50 bucket có kích thước bằng nhau

    NTILE(50) OVER(
        ORDER BY calories_burned   -- Quyết định bản ghi trong mỗi bucket
    ) AS marketing_group


FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;  -- SẮP XẾP kết quả cuối

Phân nhóm dữ liệu thể hình

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

Nhóm dữ liệu thể hình phân bổ đều

SELECT
    member_id,
    gym_location,
    calories_burned,

    NTILE(50) OVER(

        -- Phân bổ đều bản ghi vào các nhóm theo từng gym_location
        PARTITION BY gym_location

        ORDER BY calories_burned
    ) AS marketing_group

FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;

Nhóm dữ liệu thể hình phân bổ đều

         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

                                    ...

Hiểu phân phối

Phân phối calories tiêu hao của mỗi lượt tập như thế nào?

Một lượt tập cụ thể nằm ở đâu trong phân phối này?

Tỷ lệ thành viên đốt cháy bằng hoặc ít hơn một thành viên cụ thể là bao nhiêu?

  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,   -- Tạo phân phối cho từng địa điểm
        ORDER BY calories_burned
    ) AS cd

FROM FITNESS.workouts

ORDER BY gym_location, cd;  -- SẮP XẾP kết quả cuối

CUME_DIST

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


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


...;

So sánh từng bản ghi với phân phối của cột/trường đó, phân phối tích lũy

<1>: trường xác định cửa sổ đánh giá

<2>: trường tạo phân phối

Tỷ lệ bản ghi nhỏ hơn hoặc bằng bản ghi này là bao nhiêu?

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!

Window Functions trong Snowflake