रैंकिंग विंडो फंक्शन

Snowflake में Window Functions

Jake Roach

Field Data Engineer

RANK()

SELECT
    user_id,
    event_name,
    distance_traveled,

    -- Find the closest attendees
    RANK() OVER(
        ORDER BY km_traveled
    ) AS closest_concert_goer

FROM CONCERTS.attendance;

RANK() किसी फ़ील्ड के आधार पर रिकॉर्ड्स को "रैंक" देता है

ROW_NUMBER() जैसा
टाईज़ को संभालता है
एक ORDER BY शामिल करें

RANK()


  user_id  |    event_name   | km_traveled | closest_attendees 
 --------- | --------------- | ----------- | ----------------- 
  user_81  |  Lunar Drift    |     0.5     |         1         
  user_02  |  Lunar Drift    |     1.1     |         2         
  user_33  |  Crimson Arc    |     8.6     |         3         
  user_33  |  Neon Prophet   |     8.6     |         3         
  user_15  |  VibeStorm      |     17      |         5         
  user_94  |  The Dusk Owls  |     41      |         6         
  user_47  |  Lunar Drift    |     61      |         7         
  user_56  |  Crimson Arc    |     116     |         8

DESC के साथ RANK()

SELECT
    user_id,
    event_name,
    km_traveled,

    RANK() OVER(
        ORDER BY km_traveled DESC  -- Add DESC to ORDER BY
    ) AS furthest_concert_goer

FROM CONCERTS.attendance;

DESC के साथ RANK()


  user_id  |    event_name   | km_traveled | closest_attendees -> furthest_attendees
 --------- | --------------- | ----------- | ----------------- -> ------------------
  user_81  |  Lunar Drift    |     0.5     |         1         ->          8        
  user_02  |  Lunar Drift    |     1.1     |         2         ->          7        
  user_33  |  Crimson Arc    |     8.6     |         3         ->          6        
  user_33  |  Neon Prophet   |     8.6     |         3         ->          6        
  user_15  |  VibeStorm      |     17      |         5         ->          4        
  user_94  |  The Dusk Owls  |     41      |         6         ->          3        
  user_47  |  Lunar Drift    |     61      |         7         ->          2        
  user_56  |  Crimson Arc    |     116     |         8         ->          1

FIRST_VALUE और LAST_VALUE

FIRST_VALUE और LAST_VALUE क्रमशः किसी विंडो के पहले-रैंक और आखिरी-रैंक वाले मान खोजते हैं

किसी कॉलम के रिकॉर्ड्स की उसी कॉलम के पहले/आखिरी मान से तुलना
फ़ील्ड का नाम लेते हैं
किसी फ़ील्ड पर ORDER BY, ये मेल खाना ज़रूरी नहीं

SELECT
    <other-fields>,

    -- FIRST_VALUE and LAST_VALUE
    -- both take a field

    [FIRST/LAST]_VALUE(<field>) OVER(
        ORDER BY <field>
    ) AS <alias>

FROM SCHEMA.table
...;

The Good and the Bad

SELECT
    user_id, event_name, satisfaction_score,


    FIRST_VALUE(satisfaction_score) OVER(
        ORDER BY satisfaction_score DESC
    ) AS most_satisfied,


    LAST_VALUE(satisfaction_score) OVER(
        ORDER BY satisfaction_score DESC
    ) AS least_satisfied


FROM CONCERTS.attendance;

The Good and the Bad


 user_id |  event_name  | satisfaction_score | most_satisfied | least_satisfied 
 ------- | ------------ | ------------------ | -------------- | --------------- 
 user_26 | Pulse Theory |         71         |       98       |        4        
 user_71 | Echo Valley  |          9         |       98       |        4        
 user_26 | Echo Valley  |         82         |       98       |        4        
 user_57 | Nova Sway    |          4         |       98       |        4        
 user_39 | Nova Sway    |         22         |       98       |        4        
 user_38 | Bass Ritual  |         76         |       98       |        4        
 user_92 | Pulse Theory |         98         |       98       |        4        
 user_44 | Nova Sway    |         62         |       98       |        4

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

Snowflake में Window Functions