窗口函數(shù)([參數(shù)]) OVER (
  [PARTITION BY <分組列>] 
  [ORDER BY <排序列 ASC/DESC>]
  [ROWS BETWEEN 開(kāi)始行 AND 結(jié)束行]
)

-- 創(chuàng)建測(cè)試數(shù)據(jù)
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    salesperson VARCHAR(50),
    region VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10, 2)
);
INSERT INTO sales (salesperson, region, sale_date, amount) VALUES
('張三', '北京', '2024-01-01', 1000.00),
('張三', '北京', '2024-01-02', 1500.00),
('李四', '上海', '2024-01-01', 2000.00),
('李四', '上海', '2024-01-02', 2500.00),
('王五', '北京', '2024-01-01', 1200.00),
('王五', '北京', '2024-01-03', 1800.00),
('趙六', '廣州', '2024-01-02', 2200.00);
-- 按銷(xiāo)售員分區(qū)計(jì)算
SELECT 
    salesperson,
    sale_date,
    amount,
    -- 每個(gè)銷(xiāo)售員的銷(xiāo)售總額
    SUM(amount) OVER (PARTITION BY salesperson) AS total_by_person,
    -- 每個(gè)地區(qū)的銷(xiāo)售總額
    SUM(amount) OVER (PARTITION BY region) AS total_by_region,
    -- 不分區(qū)（全局總額）
    SUM(amount) OVER () AS grand_total
FROM sales
ORDER BY salesperson, sale_date;

SELECT 
    salesperson,
    sale_date,
    amount,
    -- 按金額排序（分區(qū)內(nèi)）
    ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS rn,
    -- 累計(jì)金額（分區(qū)內(nèi)按日期排序）
    SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS running_total,
    -- 移動(dòng)平均值（最近3行的平均值）
    AVG(amount) OVER (PARTITION BY salesperson ORDER BY sale_date 
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM sales
ORDER BY salesperson, sale_date;

-- 各種窗口幀的示例
SELECT 
    salesperson,
    sale_date,
    amount,
    -- 默認(rèn)：分區(qū)內(nèi)所有行
    SUM(amount) OVER (PARTITION BY salesperson) AS total_all,
    -- ROWS模式：物理行
    SUM(amount) OVER (
        PARTITION BY salesperson 
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_rows,
    -- RANGE模式：邏輯值范圍（相同值的行視為同一幀）
    SUM(amount) OVER (
        PARTITION BY salesperson 
        ORDER BY sale_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_range,
    -- 滑動(dòng)窗口：當(dāng)前行及前2行
    SUM(amount) OVER (
        PARTITION BY salesperson 
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS sum_last_3,
    -- 前后各一行
    SUM(amount) OVER (
        PARTITION BY salesperson 
        ORDER BY sale_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS sum_neighbors
FROM sales
ORDER BY salesperson, sale_date;

-- 創(chuàng)建測(cè)試數(shù)據(jù)
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
INSERT INTO employees (name, department, salary) VALUES
('張三', '技術(shù)部', 8000.00),
('李四', '技術(shù)部', 9000.00),
('王五', '技術(shù)部', 9500.00),
('趙六', '技術(shù)部', 9000.00),
('錢(qián)七', '銷(xiāo)售部', 7000.00),
('孫八', '銷(xiāo)售部', 8500.00),
('周九', '銷(xiāo)售部', 8500.00),
('吳十', '銷(xiāo)售部', 7500.00);
-- 1. ROW_NUMBER()：連續(xù)不重復(fù)的序號(hào)
SELECT 
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
-- 2. RANK()：有間隔的排名（相同值排名相同，下一個(gè)排名跳躍）
SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num
FROM employees;
-- 3. DENSE_RANK()：無(wú)間隔的排名（相同值排名相同，下一個(gè)排名連續(xù)）
SELECT 
    name,
    department,
    salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_num
FROM employees;
-- 4. NTILE(n)：將數(shù)據(jù)分為n組
SELECT 
    name,
    department,
    salary,
    NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;

-- 5. PERCENT_RANK()：百分比排名 (rank - 1) / (total_rows - 1)
SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary) AS rank_num,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) AS percent_rank
FROM employees;
-- 6. CUME_DIST()：累計(jì)分布（小于等于當(dāng)前值的行數(shù) / 總行數(shù)）
SELECT 
    name,
    department,
    salary,
    CUME_DIST() OVER (PARTITION BY department ORDER BY salary) AS cume_dist
FROM employees;
-- 7. PERCENTILE_CONT()：連續(xù)百分位數(shù)（需要MySQL 8.0.2+）
SELECT 
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) 
        OVER (PARTITION BY department) AS median_salary
FROM employees
GROUP BY department, salary;
-- 8. PERCENTILE_DISC()：離散百分位數(shù)
SELECT 
    department,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) 
        OVER (PARTITION BY department) AS median_salary
FROM employees
GROUP BY department, salary;

-- 9. LAG(column, n, default)：獲取前n行的值
SELECT 
    name,
    department,
    salary,
    LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS prev_salary,
    salary - LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS salary_diff
FROM employees;
-- 10. LEAD(column, n, default)：獲取后n行的值
SELECT 
    name,
    department,
    sale_date,
    amount,
    LEAD(amount, 1, 0) OVER (PARTITION BY salesperson ORDER BY sale_date) AS next_amount,
    LEAD(sale_date, 1, NULL) OVER (PARTITION BY salesperson ORDER BY sale_date) AS next_date
FROM sales;
-- 11. FIRST_VALUE(column)：窗口內(nèi)第一個(gè)值
SELECT 
    name,
    department,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY salary 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_salary,
    salary - FIRST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY salary 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS diff_from_lowest
FROM employees;
-- 12. LAST_VALUE(column)：窗口內(nèi)最后一個(gè)值（注意默認(rèn)窗口幀?。?
SELECT 
    name,
    department,
    salary,
    -- 錯(cuò)誤用法：默認(rèn)窗口幀是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS wrong_last_value,
    -- 正確用法：指定完整的窗口幀
    LAST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY salary 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS correct_last_value,
    -- 或者使用NTH_VALUE
    NTH_VALUE(salary, 1) OVER (
        PARTITION BY department 
        ORDER BY salary 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_salary,
    NTH_VALUE(salary, 2) OVER (
        PARTITION BY department 
        ORDER BY salary 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_salary
FROM employees;

-- 所有聚合函數(shù)都可以作為窗口函數(shù)使用
SELECT 
    salesperson,
    region,
    sale_date,
    amount,
    -- 聚合函數(shù)
    COUNT(*) OVER (PARTITION BY salesperson) AS total_transactions,
    SUM(amount) OVER (PARTITION BY salesperson) AS total_amount,
    AVG(amount) OVER (PARTITION BY salesperson) AS avg_amount,
    MAX(amount) OVER (PARTITION BY salesperson) AS max_amount,
    MIN(amount) OVER (PARTITION BY salesperson) AS min_amount,
    -- 標(biāo)準(zhǔn)差和方差（MySQL 8.0+）
    STDDEV(amount) OVER (PARTITION BY salesperson) AS std_amount,
    VARIANCE(amount) OVER (PARTITION BY salesperson) AS var_amount,
    -- 累計(jì)聚合
    SUM(amount) OVER (
        PARTITION BY salesperson 
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    -- 移動(dòng)平均
    AVG(amount) OVER (
        PARTITION BY salesperson 
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3,
    -- 百分比
    amount * 100.0 / SUM(amount) OVER (PARTITION BY salesperson) AS percentage
FROM sales
ORDER BY salesperson, sale_date;