NTILE और CUME_DIST

Snowflake में Window Functions

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,

    -- डेटा के 50 बराबर आकार के बकेट बनाएँ

    NTILE(50) OVER(
        ORDER BY calories_burned   -- हर बकेट के रिकॉर्ड तय करता है
    ) AS marketing_group


FROM FITNESS.workouts
ORDER BY marketing_group, calories_burned;  -- अंतिम परिणाम सेट को ORDER करें

फिटनेस डेटा का बकेटिंग

         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(

        -- प्रत्येक 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

                                    ...

वितरण को समझना

प्रत्येक मेंबर के वर्कआउट के लिए calories burned का वितरण क्या है?

इस वितरण में कोई खास वर्कआउट कहाँ आता है?

किसी खास मेंबर जितनी या उससे कम कैलोरी जलाने वाले मेंबर्स का अनुपात कितना है?

  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,   -- प्रत्येक स्थान का अपना वितरण बनाएँ
        ORDER BY calories_burned
    ) AS cd

FROM FITNESS.workouts

ORDER BY gym_location, cd;  -- अंतिम परिणाम सेट को ORDER करें

CUME_DIST

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


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


...;

हर रिकॉर्ड की उस कॉलम/फील्ड के वितरण से तुलना करता है, cumulative distribution

<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

                                          ...

अभ्यास करते हैं!

Snowflake में Window Functions