WINDOW Functions

BigQuery परिचय

Matthew Forrest

Field CTO

WINDOW functions क्या हैं

रोलिंग SUM वाले WINDOW का एक चित्रण

WINDOW functions कब उपयोग करें

प्रकार के अनुसार समूहित WINDOW functions

¹ https://towardsdatascience.com/a-guide-to-advanced-sql-window-functions-f63f2642cbf9

WINDOW संरचना, PARTITION और ORDER BY

SELECT
  customer_id,
  order_date,
  order_total,
  ROW_NUMBER() OVER(

    PARTITION BY customer_id

    ORDER BY order_date

  ) AS order_sequence
FROM orders;

ROW_NUMBER(): वह window function जो पंक्ति संख्या देता है
OVER(): window frame परिभाषित करता है
PARTITION BY customer_id: डेटा को ग्राहक के आधार पर समूहित करता है
ORDER BY order_date: हर partition के भीतर क्रम तय करता है
order_sequence: window function का परिणाम

RANK और PERCENT_RANK

SELECT
  product_id,
  product_photos_qty,
  -- हर पंक्ति के लिए क्रमिक रैंक
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank,
  -- हर पंक्ति के लिए पर्सेंटाइल रैंक
  PERCENT_RANK() OVER(
    ORDER BY product_photos_qty
  ) as percent
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

RANK और PERCENT_RANK क्वेरी के परिणाम

LAG और LEAD

SELECT
  product_id,
  -- पिछली पंक्ति का मान देता है
  LAG(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lag,
  product_photos_qty,
  -- अगली पंक्ति का मान देता है
  LEAD(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lead
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

क्वेरी परिणामों में LAG और LEAD दिखाती छवि

RANGE BETWEEN और CURRENT ROW

SELECT
  order_id,
  order_timestamp,
  SUM(cost) OVER(
    ORDER BY order_timestamp 
    ROWS BETWEEN 2 PRECEDING 
    AND CURRENT ROW) as rolling_avg
FROM sales_data
ORDER BY order_timestamp

Row-आधारित bounding विकल्प:

UNBOUNDED PRECEDING: इससे पहले की सभी पंक्तियाँ
UNBOUNDED FOLLOWING: इसके बाद की सभी पंक्तियाँ
[INT] ROWS PRECEDING: पहले की निर्दिष्ट पंक्तियाँ
[INT] ROWS FOLLOWING: बाद की निर्दिष्ट पंक्तियाँ

QUALIFY

SELECT
  product_id,
  product_photos_qty,
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank
FROM ecommerce.ecomm_products 
-- QUALIFY का उपयोग कर फ़िल्टर करें
QUALIFY rank < 4
ORDER BY product_photos_qty DESC;

हमारी क्वेरी के परिणाम: QUALIFY से रैंक 3 या अधिक वाले मान ढूँढना

HAVING उपयोग नहीं कर सकते क्योंकि हम aggregate नहीं कर रहे हैं

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

BigQuery परिचय