เงื่อนไขกรอบ: ROWS เทียบกับ RANGE

กรอบภายใน OVER

OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN <start> AND <end>     -- or RANGE BETWEEN ...
)

กรอบเริ่มต้น

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- "From the first row up to all rows tied with the current row".

ROWS นับตามตำแหน่งจริง

SUM(x) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- The current row plus the previous 2.  Exactly 3 rows.

RANGE นับตามค่า

SUM(x) OVER (
  ORDER BY day
  RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
)
-- All rows within 2 days (by calendar) of the current row.

เหตุใดความแตกต่างนี้จึงสำคัญ

-- Days: 1, 3, 5 (no 2 or 4)
-- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for day 5: rows 1,3,5 = 3 days
-- RANGE BETWEEN INTERVAL '2 days' PRECEDING for day 5: only days 3,5 = 2 days

กรอบ GROUPS (PG 11 ขึ้นไป)

SUM(x) OVER (
  ORDER BY tier
  GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
)
-- "The current tier and the previous tier" — useful when ORDER BY has many ties.

ขอบเขตของกรอบ

ผลรวมสะสม

SUM(x) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- Same as the default frame.

หน้าต่างย้อนหลัง

AVG(revenue) OVER (
  ORDER BY day
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS avg_7d

หน้าต่างแบบมีจุดศูนย์กลาง

AVG(revenue) OVER (
  ORDER BY day
  ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
) AS centred_7d

เรื่องน่าประหลาดใจของ LAST_VALUE

LAST_VALUE(name) OVER (
  PARTITION BY group ORDER BY day
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

ข้อคำสั่ง EXCLUDE (PG 11 ขึ้นไป)

SUM(x) OVER (
  ORDER BY day
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  EXCLUDE CURRENT ROW
)
-- All previous 6 days but not today.

สรุปทบทวน

ตรวจสอบอย่างรวดเร็ว