عبارات الإطار: 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.

خلاصة

تحقق سريع