उपयोगी फंक्शनों का सर्वेक्षण

SQL में बिज़नेस डेटा का विश्लेषण

Michel Semaan

Data Scientist

तारीखों से निपटना

DATE_TRUNC('quarter', '2018-08-13') → '2018-07-01 00:00:00+00:00'
'2018-07-01 00:00:00+00:00' :: DATE → '2018-07-01'

रिपोर्ट्स में तारीखें

रिपोर्टिंग में human-readable तारीखें ज़रूरी हैं
डिफॉल्ट फ़ॉर्मेट '2018-08-13' बहुत पठनीय नहीं है
'2018-08-13' को 'Friday 13, August 2018' में कैसे बदलें?

समाधान

TO_CHAR('2018-08-13', 'FMDay DD, FMMonth YYYY') → 'Friday 13, August 2018'

TO_CHAR()

TO_CHAR(DATE, TEXT) → TEXT (फ़ॉर्मेट किया हुआ date string)
उदाहरण: Dy → दिन का संक्षिप्त नाम (Mon, Tues, आदि)
- TO_CHAR('2018-06-01', 'Dy') → 'Fri'
- TO_CHAR('2018-06-02', 'Dy') → 'Sat'
फ़ॉर्मेट string में दिए पैटर्न date के संबंधित हिस्सों से बदले जाते हैं; बाकी अक्षर वैसे ही रहते हैं
उदाहरण: DD → दिन संख्या (01 - 31)
- TO_CHAR('2018-06-01', 'Dy - DD') → 'Fri - 01'
- TO_CHAR('2018-06-02', 'Dy - DD') → 'Sat - 02'

Pattern	विवरण
`FMDay`	दिन का पूरा नाम (`Monday`, `Tuesday`, आदि)
`MM`	वर्ष का महीना (`01` - `12`)
`Mon`	महीने का संक्षिप्त नाम (`Jan`, `Feb`, आदि)
`FMMonth`	महीने का पूरा नाम (`January`, `February`, आदि)
`YY`	वर्ष के आखिरी 2 अंक (`18`, `19`, आदि)
`YYYY`	पूरा 4-अंकीय वर्ष (`2018`, `2019`, आदि)

Documentation: https://www.postgresql.org/docs/9.6/functions-formatting.html

क्वेरी

SELECT DISTINCT
  order_date,
  TO_CHAR(order_date,
          'FMDay DD, FMMonth YYYY') AS format_1,
  TO_CHAR(order_date,
          'Dy DD Mon/YYYY') AS format_2
FROM orders
ORDER BY order_date ASC
LIMIT 3;

परिणाम

order_date  format_1                format_2       
----------  ----------------------  ---------------
2018-06-01  Friday 01, June 2018    Fri 01/Jun 2018
2018-06-02  Saturday 02, June 2018  Sat 02/Jun 2018
2018-06-02  Sunday 03, June 2018    Sun 03/Jun 2018

विंडो फंक्शंस दोबारा

SUM(...) OVER (...): किसी कॉलम का running total निकालता है
- उदाहरण: SUM(registrations) OVER (ORDER BY registration_month) पंजीकरणों का running total देता है
LAG(...) OVER (...): पिछली row का मान लाता है
- उदाहरण: LAG(mau) OVER (ORDER BY active_month) पिछले महीने के active users (MAU) देता है
RANK() OVER (...): sorted क्रम में स्थिति के आधार पर प्रत्येक row को रैंक देता है
- उदाहरण: RANK() OVER (ORDER BY revenue DESC) उपयोगकर्ताओं, eateries, या महीनों को उनकी कमाई के अनुसार रैंक करता है

क्वेरी

SELECT
  user_id,
  SUM(meal_price * order_quantity) AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 3;

परिणाम

user_id  revenue
-------  -------
18       626
76       553.25
73       537

क्वेरी

WITH user_revenues AS (
  SELECT
    user_id,
    SUM(meal_price * order_quantity) AS revenue
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
  GROUP BY user_id)

SELECT
  user_id,
  RANK() OVER (ORDER BY revenue DESC)
    AS revenue_rank
FROM user_revenues
ORDER BY revenue_rank DESC
LIMIT 3;

परिणाम

user_id  revenue_rank
-------  ------------
18       1
76       2
73       3

उपयोगी फंक्शनों का सर्वेक्षण

SQL में बिज़नेस डेटा का विश्लेषण