SELECT month, sales,
SUM(sales) OVER w AS sum_sales,
AVG(sales) OVER w AS avg_sales
FROM sales
WINDOW w AS (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);
SELECT month, sales,
RANK() OVER w1 AS rank_quarter,
ROW_NUMBER() OVER w2 AS rank_year
FROM sales
WINDOW w1 AS (PARTITION BY CEIL(month / 3) ORDER BY sales DESC),
w2 AS (ORDER BY sales DESC);
SELECT month, sales, profit,
SUM(sales) OVER w AS cumulative_sales,
AVG(profit) OVER w AS avg_profit,
MAX(sales) OVER w AS max_sales,
MIN(sales) OVER w AS min_sales
FROM sales
WINDOW w AS (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
SELECT month, sales,
LAG(sales, 1) OVER w AS prev_sales,
LEAD(sales, 1) OVER w AS next_sales,
sales - LAG(sales, 1) OVER w AS sales_change
FROM sales
WINDOW w AS (ORDER BY month);
SELECT month, sales,
DENSE_RANK() OVER w AS dense_rank,
RANK() OVER w AS rank,
ROW_NUMBER() OVER w AS row_num,
PERCENT_RANK() OVER w AS percent_rank
FROM sales
WINDOW w AS (ORDER BY sales DESC);
SELECT month, sales, profit,
FIRST_VALUE(sales) OVER w AS first_sales,
LAST_VALUE(sales) OVER w AS last_sales,
NTH_VALUE(sales, 2) OVER w AS second_sales
FROM sales
WINDOW w AS (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
-- 准备含 NULL 的数据
CREATE TABLE w_nulls (id INT, v INT);
INSERT INTO w_nulls VALUES (1, 10), (2, NULL), (3, 30), (4, NULL), (5, 50);
SELECT id, v,
LAST_VALUE(v) IGNORE NULLS OVER (ORDER BY id) AS fill_down,
LAST_VALUE(v) OVER (ORDER BY id) AS keep_null
FROM w_nulls
ORDER BY id;
SELECT month, sales,
SUM(sales) OVER w AS range_sum,
COUNT(*) OVER w AS range_count
FROM sales
WINDOW w AS (ORDER BY sales RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING);
SELECT
month,
sales,
-- 排名相关
ROW_NUMBER() OVER w1 AS row_num,
RANK() OVER w1 AS sales_rank,
-- 累积统计
SUM(sales) OVER w2 AS ytd_sales,
AVG(sales) OVER w2 AS ytd_avg,
-- 环比对比
LAG(sales) OVER w1 AS prev_month_sales,
sales - LAG(sales) OVER w1 AS mom_change,
-- 占比分析
ROUND(sales * 100.0 / SUM(sales) OVER w3, 2) AS pct_of_total
FROM sales
WINDOW
w1 AS (ORDER BY month),
w2 AS (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
w3 AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
SELECT
month,
sales,
NTILE(3) OVER w AS sales_tier,
CASE
WHEN NTILE(3) OVER w = 1 THEN 'High'
WHEN NTILE(3) OVER w = 2 THEN 'Medium'
ELSE 'Low'
END AS tier_label
FROM sales
WINDOW w AS (ORDER BY sales DESC);
SELECT month,
SUM(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
AVG(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM sales;
✅ 推荐（使用WINDOW子句）:
SELECT month,
SUM(sales) OVER w,
AVG(sales) OVER w
FROM sales
WINDOW w AS (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);
2. 合理使用PARTITION BY
对于分组分析，使用PARTITION BY可以在不破坏数据结构的情况下进行组内计算：
-- 示例：按部门分别计算薪资排名
SELECT dept, name, salary,
RANK() OVER w AS rank_in_dept
FROM doc_test.employees
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);
执行结果:
+-------------+-------+----------+--------------+
| dept | name | salary | rank_in_dept |
+-------------+-------+----------+--------------+
| Engineering | Alice | 12000.00 | 1 |
| Engineering | Bob | 9500.00 | 2 |
| HR | Eve | 6000.00 | 1 |
| Marketing | Carol | 8500.00 | 1 |
| Marketing | Dave | 6500.00 | 2 |
+-------------+-------+----------+--------------+
说明：每个部门内独立计算薪资排名，互不影响。
3. 注意LAST_VALUE的窗口范围
如果要获取真正的最后一个值，必须使用
UNBOUNDED FOLLOWING
UNBOUNDED FOLLOWING
：
WINDOW w AS (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
