SQL 窗口函数Explained

27 of 27 rows

函数 / 子句↕	返回值↕	示例↕
排名
⤢ROW_NUMBER()	窗口内每行一个唯一的连续整数。	ROW_NUMBER() OVER (ORDER BY total DESC)
⤢RANK()	带间隔的排名；并列的行共享名次并跳过后续数字。	RANK() OVER (ORDER BY total DESC)
⤢DENSE_RANK()	无间隔的排名；并列的行共享名次，不跳过任何数字。	DENSE_RANK() OVER (ORDER BY total DESC)
⤢NTILE(n)	将有序窗口切分成 n 个大致相等的桶。	NTILE(4) OVER (ORDER BY total DESC)
查看相邻行
⤢LAG(expr, n, default)	向前 n 行的值；不存在该行时用默认值替代。	LAG(total, 1, 0) OVER (ORDER BY created_at)
⤢LEAD(expr, n, default)	向后 n 行的值；不存在该行时用默认值替代。	LEAD(total, 1, 0) OVER (ORDER BY created_at)
窗口聚合
⤢SUM() OVER	不折叠行的累计或分区总和。	SUM(total) OVER (ORDER BY created_at)
⤢AVG() OVER	累计或分区平均值。	AVG(total) OVER (PARTITION BY user_id)
⤢COUNT() OVER	累计或分区行数。	COUNT(*) OVER (PARTITION BY user_id)
⤢MIN() OVER	整个窗口或分区中的最小值。	MIN(total) OVER (PARTITION BY user_id)
⤢MAX() OVER	整个窗口或分区中的最大值。	MAX(total) OVER (PARTITION BY user_id)
⤢Running total	带 ORDER BY 且帧从起点敞开的 SUM；每行累加到自身为止的所有值。	SUM(total) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING)
⤢Moving average	在当前行前后各 n 行的帧上求 AVG。	AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
首值、末值与百分位
⤢FIRST_VALUE()	窗口排序中的第一个值。	FIRST_VALUE(total) OVER (ORDER BY total DESC)
⤢LAST_VALUE()	帧中的最后一个值；没有 UNBOUNDED FOLLOWING 时帧止于当前行。	LAST_VALUE(total) OVER (ORDER BY total ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
⤢NTH_VALUE(expr, n)	窗口排序中第 n 行的值。	NTH_VALUE(total, 2) OVER (ORDER BY total DESC)
⤢PERCENT_RANK()	行的相对排名，从 0 到 1。	PERCENT_RANK() OVER (ORDER BY total)
⤢CUME_DIST()	位于当前行及之前的行所占比例，从 1/n 到 1。	CUME_DIST() OVER (ORDER BY total)
OVER 子句
⤢OVER (...)	定义每个函数读取的窗口。	<fn>() OVER (PARTITION BY user_id ORDER BY created_at)
⤢PARTITION BY	为每一组行重新开始窗口。	PARTITION BY user_id
⤢ORDER BY	在每个分区内为行排序。	ORDER BY created_at
⤢ROWS BETWEEN	限定滑动帧的边界，例如从前几行到后几行。	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
⤢RANGE BETWEEN	按值而非行位置限定帧；CURRENT ROW 包含并列的行，且这是有 ORDER BY 时的默认帧。	RANGE BETWEEN 100 PRECEDING AND CURRENT ROW
⤢GROUPS BETWEEN	按 ORDER BY 产生的并列行组限定帧。	GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
⤢EXCLUDE CURRENT ROW	将当前行移出帧；EXCLUDE TIES 则改为移除与之并列的行。	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW
⤢WINDOW	为窗口在每个查询中命名一次，供多个 OVER 子句复用。	WINDOW w AS (PARTITION BY user_id ORDER BY created_at)
⤢FILTER (WHERE ...)	在应用窗口之前，收窄窗口聚合读取的行。	SUM(total) FILTER (WHERE status = 'paid') OVER (ORDER BY created_at)