विंडो फंक्शंस से शीर्ष आइटम

PostgreSQL में Time Series Analysis

Jasmin Ludolf

Content Developer, DataCamp

टेबल विवरण

temperatures_monthly टेबल
फ़ील्ड्स:
- station_id - वेदर स्टेशन ID
- year_month - साल और महीना
- t_monthly_min - मासिक न्यूनतम तापमान (Celsius)
- t_monthly_max - मासिक अधिकतम तापमान (Celsius)
- t_monthly_avg - मासिक औसत तापमान (Celsius)

पारंपरिक एग्रीगेशन

SELECT station_id, year_month, t_monthly_min
FROM temperatures_monthly AS tm
JOIN

(   
  SELECT
        station_id, min(t_monthly_min) AS t_monthly_min
    FROM temperatures_monthly
    WHERE year_month BETWEEN '2018-01-01' AND '2018-12-31'
    GROUP BY station_id
) as p
USING(station_id, t_monthly_min)
WHERE year_month BETWEEN '2018-01-01' AND '2018-12-31'
ORDER BY station_id, t_monthly_min;

पारंपरिक एग्रीगेशन

|station_id|year_month|t_monthly_min|
|----------|----------|-------------|
|         1|2018-12-01|          5.4|
|         2|2018-12-01|          6.6|
|         3|2018-01-01|          1.6|
|         3|2018-02-01|          1.6|
|         4|2018-02-01|        -19.0|
|         6|2018-01-01|          2.6|
|         8|2018-02-01|        -16.3|

समस्याएँ:

स्टेशन 5 गायब है, 2018 के लिए डेटा नहीं था
स्टेशन 3 के दो रो, दो रो में वही न्यूनतम तापमान 3 था
यह सिर्फ सबसे ठंडा एक महीना देता है. अगर हमें दो सबसे ठंडे महीने चाहिए?

row_number का उपयोग

हमें हर स्टेशन के लिए हर रो को नंबर देना है
ROW_NUMBER() : हर पार्टिशन में रो को आरोही क्रम में नंबर देता है

SELECT station_id, year_month, t_monthly_min,
    ROW_NUMBER() OVER (PARTITION BY station_id ORDER BY t_monthly_min) AS rank
FROM temperatures_monthly AS tm
WHERE year_month BETWEEN '2018-01-01' AND '2018-12-31'
ORDER BY station_id, t_monthly_min;

रो की नंबरिंग

|station_id|year_month|t_monthly_min|rank|
|----------|----------|-------------|----|
|         1|2018-12-01|          5.4|   1|
|         1|2018-02-01|          6.2|   2|
|         1|2018-01-01|          7.4|   3|
|         1|2018-11-01|          9.0|   4|
|         1|2018-03-01|         10.6|   5| 
|         1|2018-04-01|         14.7|   6|
|         1|2018-10-01|         16.6|   7|
|         1|2018-05-01|         17.2|   8|
|         1|2018-06-01|         21.9|   9|
|         1|2018-09-01|         24.4|  10|
|         1|2018-07-01|         28.1|  11|
|         1|2018-08-01|         28.1|  12|
|         2|2018-12-01|          6.6|   1|
|         2|2018-01-01|          7.6|   2|
|         2|2018-02-01|          8.1|   3|

स्टेशन 2 पर नंबरिंग फिर से शुरू होती है
क्योंकि हमने station_id से पार्टिशन किया है

सबसे कम तापमान

SELECT * FROM
(
    SELECT station_id, year_month, t_monthly_min,
    ROW_NUMBER() OVER 
          (PARTITION BY station_id ORDER BY t_monthly_min) AS rank
    FROM temperatures_monthly
    WHERE year_month BETWEEN '2018-01-01' AND '2018-12-31'
) AS q
WHERE rank < 3
ORDER BY station_id, rank;

सबसे कम तापमान

|station_id|year_month|t_monthly_min|rank|
|----------|----------|-------------|----|
|         1|2018-12-01|          5.4|   1|
|         1|2018-02-01|          6.2|   2|
|         2|2018-12-01|          6.6|   1|
|         2|2018-01-01|          7.6|   2|
|         3|2018-01-01|          1.6|   1|
|         3|2018-02-01|          1.6|   2|

सबसे बड़े मान

क्रम उल्टा करने के लिए, DESC जोड़ें:

ROW_NUMBER() OVER (PARTITION BY station_id ORDER BY t_monthly_min DESC)

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

PostgreSQL में Time Series Analysis