और रैंकिंग फंक्शंस

Snowflake में Window Functions

Jake Roach

Field Data Engineer

RANK बनाम DENSE_RANK

SELECT
    workout_duration,

    RANK() OVER(
        ORDER BY workout_duration DESC
    ) AS r,


    -- बिना गैप के!
    DENSE_RANK() OVER(
        ORDER BY workout_duration DESC
    ) AS dr


FROM FITNESS.workouts;

   workout_duration  |   r   |   dr
  ------------------ | ----- | -----
         78          |   1   |   1
         71          |   2   |   2
         71          |   2   |   2

    ***  68          |   4   |   3  ***

         67          |   5   |   4
         67          |   5   |   4
         67          |   5   |   4
         63          |   8   |   5
         61          |   9   |   6

NTH_VALUE

SELECT
    <field>,
    <another-field>,


    NTH_VALUE(<1>, <n>) OVER(

        PARTITION BY <2>
        ORDER BY <3>

    ) AS <alias>


FROM <SCHEMA>.<table>;

NTH_VALUE एक विंडो में "N'th" रिकॉर्ड से दी गई वैल्यू लौटाता है, FIRST/LAST_VALUE जैसा

<1>: जिस रो से वैल्यू लेनी है

<n>: जिस रो नंबर से लेना है

<2>: वैकल्पिक फील्ड जिस पर पार्टिशन करें

<3>: ORDER BY रिकॉर्ड्स की रैंकिंग तय करता है

NTH_VALUE

SELECT
    gym_location,
    workout_duration,


    -- हर जिम लोकेशन के लिए दूसरी सबसे लंबी वर्कआउट अवधि लौटाएँ
    NTH_VALUE(workout_duration, 2) OVER(

        PARTITION BY gym_location
        ORDER BY workout_duration DESC

    ) AS second_longest_workout


FROM FITNESS.workouts;

NTH_VALUE

      gym_location   |  workout_duration  |  second_longest_workout 
     --------------- | ------------------ | ------------------------
        New York     |        71          |           68            
        New York     |        68          |           68
        New York     |        67          |           68

        Los Angeles  |        78          |           67
        Los Angeles  |        67          |           67
        Los Angeles  |        67          |           67
        Los Angeles  |        63          |           67

        Miami        |        71          |           61
        Miami        |        61          |           61

सब कुछ साथ में

SELECT
    gym_location, workout_duration,

    NTH_VALUE(workout_duration, 2) OVER(
        PARTITION BY gym_location
        ORDER BY workout_duration DESC
    ) AS second_longest_workout,

    RANK() OVER(PARTITION BY gym_location ORDER BY workout_duration DESC) AS r,

    DENSE_RANK() OVER(PARTITION BY gym_location ORDER BY workout_duration DESC) AS dr,

FROM FITNESS.workouts;

सब कुछ साथ में

      gym_location   |  workout_duration  |  second_longest_workout  |  r  |   dr  
     --------------- | ------------------ | ------------------------ | --- | -----
        New York     |        71          |           68             |  1  |   1
        New York     |        68          |           68             |  2  |   2
        New York     |        67          |           68             |  3  |   3

        Los Angeles  |        78          |           67             |  1  |   1
        Los Angeles  |        67          |           67             |  2  |   2
        Los Angeles  |        67          |           67             |  2  |   2
        Los Angeles  |        63          |           67             |  4  |   3

        Miami        |        71          |           61             |  1  |   1
        Miami        |        61          |           61             |  2  |   2

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

Snowflake में Window Functions