想把 SQL 窗口函数吃透？这 20 道题可别错过！从基础的排名、累计和，到复杂的会话划分、留存率计算，全是电商场景应用里的实用技能。不管是给订单排名、算用户生命周期，还是做滚动平均分析，只要跟着练下来，保准我们对 ROW_NUMBER、LAG 这些函数熟得不能再熟，写起分析报表（文末附有：自动化报表 SQL 脚本模板）也能得心应手。(所有代码块或表格均可左右滚动)

一、窗口函数专项训练 20 题

目标：从基础到精通，协助我们掌握 ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD, SUM/OVER, FIRST/LAST_VALUE 等窗口函数在电商应用场景下的语法技巧。

基础（1-5）

1、为每个订单按金额降序排名（允许并列，跳过名次）。

场景：销售业绩分析，确定订单金额在所有订单中的排名。

-- 按订单金额从高到低排名，允许并列，名次会跳过SELECT order_id, amount, RANK() OVER (ORDER BY amount DESC) AS rankFROM orders;

解析：RANK()函数按金额降序对所有订单进行排名。当出现金额一样的订单时，会给予一样的排名，下一个不同金额的订单会跳过中间的名次（例如：1,2,2,4……）。OVER (ORDER BY amount DESC)定义了排名的范围（所有订单）和排序方式。

2、为每个用户的订单按时间升序编号（1,2,3……）。

场景：用户行为分析，跟踪用户的订单顺序和购买频率。

-- 为每个用户的订单按时间顺序分配序号SELECT user_id, order_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS seqFROM orders;

解析：PARTITION BY user_id按用户分组，每个用户形成独立的窗口。ROW_NUMBER()在每个用户组内按订单日期升序排序，为订单分配唯一序号（1表明用户的第一个订单，2表明第二个，以此类推）。与RANK()不同，ROW_NUMBER()始终生成连续的唯一序号，即使日期一样也不会并列。

3、计算“所有订单金额的累计和”。

场景：销售趋势分析，查看销售额随时间的累积增长情况。

-- 按时间顺序计算订单金额的累计总和SELECT order_date, amount,       SUM(amount) OVER (ORDER BY order_date) AS cumulative_sumFROM orders;

解析：SUM(amount) OVER (ORDER BY order_date)是一个累加窗口函数，按订单日期排序，计算从第一条记录到当前记录的金额总和。cumulative_sum列显示截至该订单日期的累计销售额，直观展示销售增长趋势。窗口范围默认为从第一条到当前行，无需额外指定。

4、找出“每个城市最年轻的用户”。

场景：用户画像分析，了解各城市的年轻用户特征。

-- 查找每个城市中年龄最小的用户SELECT * FROM (    SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY age) AS rn    FROM users) t WHERE rn = 1;

解析：子查询中，PARTITION BY city按城市分组，ORDER BY age按年龄升序排序，ROW_NUMBER()为每个城市的用户分配序号（1表明该城市最年轻的用户）。外部查询筛选出序号为1的记录，即为每个城市最年轻的用户。如果有多个同龄且最小的用户，ROW_NUMBER()会随机选择一个，此时可用RANK()替代以返回所有并列的最年轻用户。如果需要所有并列的最年轻用户，可以使用 RANK()或 DENSE_RANK()并筛选 rnk = 1，示例：

-- 查找每个城市中所有年龄最小的用户SELECT * FROM (    SELECT *, RANK() OVER (PARTITION BY city ORDER BY age) AS rnk    FROM users) t WHERE rnk = 1;

5、显示“当前订单金额与上一笔订单金额的差”。

场景：销售波动分析，监控订单金额的变化情况。

-- 计算当前订单与上一笔订单的金额差值SELECT order_id, amount,
       amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS diff_vs_prevFROM orders;

解析：LAG(amount, 1, 0)函数获取按日期排序的上一笔订单金额，参数1表明向前取1行，0表明当没有上一笔订单（第一行）时的默认值。当前订单金额减去上一笔订单金额，得到金额差值，正数表明增长，负数表明下降。该分析有助于我们识别销售趋势的突变点。

进阶（6-10）

6、计算“7天滚动平均订单金额”。

场景：销售趋势分析，平滑短期波动以观察长期趋势。

-- 计算包括当天在内的过去7天订单金额的平均值SELECT order_date, amount,       AVG(amount) OVER (           ORDER BY order_date 
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_avg_7dayFROM orders;

解析：AVG(amount) OVER (...)计算7天滚动平均值，ROWS BETWEEN 6 PRECEDING AND CURRENT ROW定义窗口范围为当前行及之前的6行（共7天）。按订单日期排序后，每个日期的平均值基于过去7天的数据，有助于消除单日波动的影响，更清晰地展示销售趋势。

如果“同一日期多笔订单” ，示例代码可优化为：

WITH daily_amount AS (    -- 先按日期汇总每日订单金额（避免同一日期多笔订单导致的行级偏差）    SELECT 
        order_date,        SUM(amount) AS daily_total  -- 每日总金额    FROM orders    GROUP BY order_date
)SELECT 
    order_date,
    daily_total,    -- 按日期范围计算7天滚动平均（当前日期+前6天）    AVG(daily_total) OVER (        ORDER BY order_date 
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW  -- 关键：用RANGE而非ROWS
    ) AS rolling_avg_7dayFROM daily_amount;

7、找出“每个用户第一笔订单的金额”。

场景：用户价值分析，让我们了解用户首次购买的金额特征。

-- 获取每个用户的第一笔订单金额SELECT user_id, 
       FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS first_order_amountFROM orders;

解析：PARTITION BY user_id按用户分组，ORDER BY order_date按订单日期排序，FIRST_VALUE(amount)返回每个用户组内的第一个订单金额（即用户的第一笔订单金额）。该指标可用于我们分析新用户的初始购买行为，与后续订单金额对比可评估用户价值的变化。

8、计算“订单金额的百分位数”（0-1之间）。

场景：订单价值分布分析，确定订单金额在整体分布中的位置。

-- 计算每个订单金额在所有订单中的相对位置（0-1）SELECT order_id, amount, PERCENT_RANK() OVER (ORDER BY amount) AS pctFROM orders;

解析：PERCENT_RANK()计算每行在排序后的结果聚焦的相对位置，返回值范围为0到1。对于金额最小的订单，pct为0；对于金额最大的订单，pct为1。例如，某订单的pct为0.75表明其金额高于75%的订单。该指标有助于我们快速识别高价值订单和低价值订单。

9、显示“下一个订单的金额”（按时间）。

场景：销售趋势分析，预测订单金额的变化方向。

-- 获取按时间排序的下一个订单金额SELECT order_id, amount,       LEAD(amount, 1, NULL) OVER (ORDER BY order_date) AS next_amountFROM orders;

解析：LEAD(amount, 1, NULL)函数获取按日期排序的下一笔订单金额，参数1表明向后取1行，NULL表明当没有下一笔订单（最后一行）时的默认值。该分析有助于我们观察订单金额的连续变化，识别增长或下降趋势。

10、为每个分组内的值分配“四分位”（1-4）。

场景：数据分布分析，将订单金额划分为均等的四个区间。

-- 将订单金额按大小分为4个等级（四分位）SELECT user_id, amount,
       NTILE(4) OVER (ORDER BY amount) AS quartileFROM orders;

解析：NTILE(4)将排序后的结果集平均分配到4个桶中，每个桶分配一个编号（1-4），即四分位。ORDER BY amount确保按金额大小排序后再分配。四分位分析有助于快速了解数据分布特征，如：Q1（1分位）表明最低的25%订单，Q4表明最高的25%订单。

复杂（11-15）

11、找出“每个用户金额最高的订单”，并显示排名。

场景：用户价值分析，识别用户的最高消费订单。

-- 查找每个用户金额最高的订单，并显示其在该用户订单中的排名SELECT * FROM (    SELECT *,           DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS dr    FROM orders
) t WHERE dr = 1;

解析：子查询中，PARTITION BY user_id按用户分组，DENSE_RANK()按订单金额降序对每个用户的订单进行排名（允许并列，不跳过名次）。外部查询筛选出排名为1的记录，即为每个用户金额最高的订单。与RANK()不同，DENSE_RANK()在出现并列时不会跳过后续名次（例如：1,1,2,3……），确保我们正确识别所有最高金额的订单（可能有多个）。

12、计算“同比去年同期增长率”（假设数据跨年）。

场景：年度销售对比分析，评估业务的同比增长情况。

-- 计算每月销售额与去年同期的增长率WITH monthly_data AS (    -- 按月汇总销售额    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS amt    FROM orders GROUP BY month)SELECT month, amt,       -- 获取去年同期的销售额（向前取12行）
       LAG(amt, 12) OVER (ORDER BY month) AS amt_last_year,       -- 计算同比增长率（保留两位小数）       ROUND((amt - LAG(amt, 12) OVER (ORDER BY month)) * 100.0 / LAG(amt, 12) OVER (ORDER BY month), 2) AS yoy_growthFROM monthly_data;

解析：CTE monthly_data按月汇总销售额。主查询中，LAG(amt, 12)获取12个月前（去年同期）的销售额，通过计算当前月与去年同期销售额的差值并除以上年销售额，得到同比增长率。该指标消除了季节性因素的影响，更准确地反映业务增长趋势。

13、找出“连续3次订单金额递增”的用户。

场景：用户消费行为分析，识别消费能力持续提升的用户。

-- 找出有连续3次订单金额递增记录的用户WITH trend AS (    -- 判断当前订单是否比前1次和前2次金额都高    SELECT user_id, order_date, amount,
           amount > LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY order_date) AS inc1,
           amount > LAG(amount, 2) OVER (PARTITION BY user_id ORDER BY order_date) AS inc2    FROM orders
),
streak AS (    -- 统计每个用户连续3次递增的次数    SELECT user_id,           SUM(CASE WHEN inc1 AND inc2 THEN 1 ELSE 0 END) AS inc_streak    FROM trend    GROUP BY user_id
)-- 筛选出至少有1次连续3次递增的用户SELECT user_id FROM streak WHERE inc_streak >= 1;

解析：该查询通过多层CTE实现复杂逻辑：

（1）trend CTE：使用LAG函数获取前1次和前2次订单金额，判断当前订单是否比这两次都高（inc1和inc2为true）。
（2）streak CTE：统计每个用户满足连续3次递增的次数。
（3）主查询：筛选出至少有1次连续3次递增的用户。

该分析有助于我们识别消费能力上升的用户，可针对性地提供更高价值的产品或服务。

14、计算“移动总和”（最近5笔订单金额和）。

场景：销售波动分析，观察短期内的销售总额变化。

-- 计算包括当前订单在内的最近5笔订单的金额总和SELECT order_id, amount,       SUM(amount) OVER (           ORDER BY order_date 
           ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
       ) AS moving_sum_5FROM orders;

解析：SUM(amount) OVER (...)计算5笔订单的移动总和，ROWS BETWEEN 4 PRECEDING AND CURRENT ROW定义窗口范围为当前行及之前的4行（共5笔订单）。按订单日期排序后，每个订单的移动总和反映了最近5笔交易的总金额，有助于我们观察短期销售趋势。

15、找出“每个分组内的中位数”（近似）。

场景：用户分布分析，让我们了解各城市用户年龄的中间水平（不受极端值影响）。

-- 计算每个城市用户年龄的中位数（近似值）WITH ranked AS (    -- 为每个城市的用户按年龄排序，并计算总人数    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY city ORDER BY age) AS rn,           COUNT(*) OVER (PARTITION BY city) AS cnt    FROM users)-- 取排序后中间位置的年龄作为中位数SELECT city, age AS median_ageFROM rankedWHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2));

解析：该方法通过排序和位置计算近似中位数：

（1）ranked CTE：按城市分组，按年龄排序，为每个用户分配序号（rn），并计算每个城市的总人数（cnt）。
（2）主查询：选择序号为FLOOR((cnt+1)/2)和CEIL((cnt+1)/2)的记录，分别对应奇数和偶数人数时的中位数位置。

对于奇数人数，返回中间位置的年龄；对于偶数人数，返回中间两个位置的年龄，实现了中位数的近似计算。如果某城市有偶数个用户时，取中间两个年龄的平均值作为中位数，示例如下：

WITH ranked AS (    SELECT 
        city,
        age,        -- 按城市分组，年龄升序排序（明确排序方向）
        ROW_NUMBER() OVER (PARTITION BY city ORDER BY age ASC) AS rn,        -- 计算每个城市的用户总数（去重，避免重复用户影响中位数）        COUNT(DISTINCT user_id) OVER (PARTITION BY city) AS cnt  -- 假设users表有user_id字段    FROM users),
median_candidates AS (    SELECT 
        city,
        age,
        cnt    FROM ranked    -- 筛选中间位置的记录（奇数取中间1条，偶数取中间2条）    WHERE rn IN (FLOOR((cnt + 1)/2), CEIL((cnt + 1)/2))
)-- 按城市聚合，得到最终中位数（偶数个用户时取平均）SELECT 
    city,    -- 处理整数年龄：若为偶数个用户，取平均后保留1位小数；奇数则直接取整    CASE WHEN cnt % 2 = 1 THEN MAX(age)  -- 奇数个用户，中间1条记录的年龄即为中位数         ELSE ROUND(AVG(age), 1)        -- 偶数个用户，取中间2条记录的平均年龄    END AS median_ageFROM median_candidatesGROUP BY city, cnt;  -- 必须按cnt分组，否则CASE判断会出错

挑战（16-20）

16、实现“会话划分”（30分钟无活动视为新会话）。

场景：用户行为分析，将用户的连续操作划分为独立会话，评估用户参与度。

-- 将用户行为按30分钟间隔划分为不同会话WITH time_diff AS (    -- 计算当前行为与上一次行为的时间间隔（分钟）    SELECT user_id, timestamp,           TIMESTAMPDIFF(MINUTE, LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp), timestamp) AS gap    FROM user_actions
),
session_start AS (    -- 标记是否为新会话（首次行为或间隔>30分钟）    SELECT user_id, timestamp,           CASE WHEN gap IS NULL OR gap > 30 THEN 1 ELSE 0 END AS is_new_session    FROM time_diff
),
session_id AS (    -- 累加新会话标记，生成会话ID    SELECT user_id, timestamp,           SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY timestamp) AS session_id    FROM session_start
)SELECT * FROM session_id;

解析：该查询通过多层CTE实现会话划分：

（1）time_diff CTE：计算当前行为与上一次行为的时间间隔（分钟）。
（2）session_start CTE：标记新会话起点（首次行为或与上次间隔>30分钟）。
（3）session_id CTE：累加新会话标记，生成唯一的会话ID（同一会话内的行为具有一样ID）。

会话划分是用户行为分析的基础，可用于计算会话时长、会话深度等指标。

17、计算“用户生命周期”（首末次登录间隔）。

场景：用户留存分析，评估用户在平台的活跃周期。

-- 计算每个用户的生命周期（首次登录到最后一次登录的天数）SELECT user_id,       MIN(login_time) AS first_login,  -- 首次登录时间       MAX(login_time) AS last_login,   -- 最后一次登录时间       DATEDIFF(MAX(login_time), MIN(login_time)) AS lifespan_days  -- 生命周期（天）FROM user_loginsGROUP BY user_id;

解析：MIN(login_time)获取用户的首次登录时间，MAX(login_time)获取最后一次登录时间，DATEDIFF计算两者之间的天数差，即为用户的生命周期。该指标反映了用户从注册到流失（或当前）的活跃时长，有助于我们评估平台对用户的长期吸引力。

如果 login_time 是准确到秒的时间戳（如：2025-08-24 09:00:00），MAX(login_time) 会返回当天最后一次登录时间，而用户生命周期应按 “日期” 计算（而非准确时间），否则会导致 “同一用户两天内的登录，因时间差 1 秒被误算为 2 天生命周期”。此时，示例应为：

SELECT 
    user_id,    -- 按日期去重后，取首次登录日期（而非准确时间）    MIN(DATE(login_time)) AS first_login_date,    -- 按日期去重后，取末次登录日期    MAX(DATE(login_time)) AS last_login_date,    -- 计算日期差（按天），若首末次为同一天则生命周期为0天    DATEDIFF(MAX(DATE(login_time)), MIN(DATE(login_time))) AS lifespan_daysFROM user_logins-- 先按用户+日期去重，避免重复登录记录影响首末次判断GROUP BY user_id, DATE(login_time)  -- 先按日期去重GROUP BY user_id;  -- 再按用户聚合，计算首末次日期

18、找出“每个产品类别的销售额 Top 3”。

场景：产品销售分析，识别每个类别中的畅销产品。

-- 找出每个产品类别中销售额排名前三的产品SELECT * FROM (    SELECT 
        p.category, p.product_name, SUM(oi.quantity * oi.unit_price) AS revenue,        RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS rk    FROM products p    JOIN order_items oi ON p.product_id = oi.product_id    GROUP BY p.category, p.product_name
) t WHERE rk <= 3;

解析：子查询中，PARTITION BY p.category按产品类别分组，SUM(oi.quantity * oi.unit_price)计算每个产品的销售额，RANK()按销售额降序排名。外部查询筛选出排名前三的产品，即为每个类别的Top 3畅销产品。该分析有助于我们管理库存和营销策略制定。

19、计算“滚动留存率”（第1,3,7天留存）。

场景：这是用户增长分析和产品健康度评估的主要指标，用于衡量用户对产品的粘性。我们通过追踪用户在首次使用（新增）后的第1、3、7天是否继续活跃，可评估产品价值、用户体验和留存策略的有效性，广泛应用在互联网产品的日常运营监控、版本迭代效果分析和市场推广ROI计算。

模拟表结构：

-- 用户首次登录表（新增用户）user_install (
    user_id BIGINT PRIMARY KEY,  -- 用户唯一标识，确保每个用户只记录一次
    install_date DATE  -- 首次登录日期，即用户"新增"日期)-- 用户每日活跃表user_active (
    user_id BIGINT,  -- 用户唯一标识
    active_date DATE,  -- 用户活跃日期（登录、操作等行为）
    PRIMARY KEY (user_id, active_date)  -- 主键约束确保每个用户每天只保留一条记录（去重）)

SQL 实现：滚动留存率（第1,3,7天）：

-- 计算每日新增用户的第1、3、7天留存率WITH new_users AS (    -- 获取近30天的新增用户（可根据业务需求调整时间范围）    SELECT 
        user_id,
        install_date    FROM user_install    WHERE install_date >= CURRENT_DATE - INTERVAL 30 DAY  -- 起始时间：当前日期往前推30天      AND install_date < CURRENT_DATE  -- 结束时间：当前日期（不含当天，确保数据完整性）),

retention_flags AS (    -- 关联活跃数据，标记每个用户在 +1, +3, +7 天是否活跃    SELECT 
        nu.install_date,  -- 保留新增日期，用于后续分组统计
        nu.user_id,  -- 保留用户ID，确保每个用户只统计一次        -- 第1天留存标记：如果用户在新增日+1天有活跃，标记为1，否则为0        MAX(CASE WHEN ua.active_date = DATE_ADD(nu.install_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) AS retained_d1,        -- 第3天留存标记：如果用户在新增日+3天有活跃，标记为1，否则为0        MAX(CASE WHEN ua.active_date = DATE_ADD(nu.install_date, INTERVAL 3 DAY) THEN 1 ELSE 0 END) AS retained_d3,        -- 第7天留存标记：如果用户在新增日+7天有活跃，标记为1，否则为0        MAX(CASE WHEN ua.active_date = DATE_ADD(nu.install_date, INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS retained_d7    FROM new_users nu    -- 左连接活跃表，只关联目标留存日的记录（减少数据处理量）    LEFT JOIN user_active ua 
        ON nu.user_id = ua.user_id 
        AND ua.active_date IN (            DATE_ADD(nu.install_date, INTERVAL 1 DAY),  -- 目标留存日：新增后第1天            DATE_ADD(nu.install_date, INTERVAL 3 DAY),  -- 目标留存日：新增后第3天            DATE_ADD(nu.install_date, INTERVAL 7 DAY)   -- 目标留存日：新增后第7天
        )    -- 按新增日期和用户ID分组，确保每个用户在每个新增日只产生一条记录    GROUP BY nu.install_date, nu.user_id
),

retention_summary AS (    -- 按新增日期汇总留存率    SELECT 
        install_date AS cohort_date,  -- 新增日期，也称为"同期群日期"        COUNT(*) AS new_users,  -- 该日期的新增用户总数（去重）        -- 第1天留存率 = 第1天留存用户数 ÷ 新增用户总数 × 100%        SUM(retained_d1) * 100.0 / COUNT(*) AS retention_d1_pct,        -- 第3天留存率 = 第3天留存用户数 ÷ 新增用户总数 × 100%        SUM(retained_d3) * 100.0 / COUNT(*) AS retention_d3_pct,        -- 第7天留存率 = 第7天留存用户数 ÷ 新增用户总数 × 100%        SUM(retained_d7) * 100.0 / COUNT(*) AS retention_d7_pct    FROM retention_flags    GROUP BY install_date  -- 按新增日期分组汇总)-- 最终结果：按新增日期倒序展示，保留两位小数便于阅读SELECT 
    cohort_date,
    new_users,    ROUND(retention_d1_pct, 2) AS d1_retention,    ROUND(retention_d3_pct, 2) AS d3_retention,    ROUND(retention_d7_pct, 2) AS d7_retentionFROM retention_summaryORDER BY cohort_date DESC;

模拟输出：

解析：

性能优化：

（1）索引优化

-- 加速新增用户筛选ALTER TABLE user_install ADD INDEX idx_install_date (install_date);-- 加速活跃记录关联ALTER TABLE user_active ADD INDEX idx_user_date (user_id, active_date);

（2）数据分区

• 对user_active表按active_date进行分区，将历史数据和近期数据分开存储，提升查询效率。

（3）结果预计算

• 将每日计算结果写入report_retention_daily表，供BI工具直接查询，减少重复计算。

• 可添加etl_time字段记录计算时间，便于数据追溯。

（4）异常处理

• 当某天新增用户为0时，COUNT(*)为0，可能导致除数为0错误，可优化为：SUM(retained_d1) * 100.0 / NULLIF(COUNT(*), 0) AS retention_d1_pct

（5）扩展性增强

• 如需计算更多天的留存（如：14天、30天），只需在retention_flags中增加对应标记即可。

• 可增加筛选条件（如：渠道、地区），分析不同维度的留存差异。

常见问题：

（1）如何处理用户在目标日的多次登录？
通过MAX(CASE ...)实现：即使一个用户在第1天登录10次，MAX()函数也会将其标记为1（留存），避免重复计数，确保我们统计的是“用户是否留存”而非“登录次数”。

（2）为什么使用LEFT JOIN而非INNER JOIN？
LEFT JOIN会保留所有新增用户，包括那些在目标日未活跃的用户（这些用户会被标记为0），确保分母（新增用户总数）的准确性。若使用INNER JOIN，未留存用户会被过滤，会导致计算结果错误。

（3）为什么限定install_date < CURRENT_DATE？
确保我们统计的新增用户有完整的留存观察期：例如，8月20日的新增用户，需要到8月21日才能计算第1天留存，若包含当天数据可能导致结果不准确。

（4）如何处理 “用户重复新增” 的异常数据？
user_install 表假设 user_id 是主键，但实际业务中可能因数据同步错误导致 “同一用户多次插入安装记录”（如：用户卸载后重装），此时 MIN(install_date) 会取最早日期，但留存率计算应基于 “最近一次安装”（否则会将老用户误判为新用户）。此时，示例部分修正为：

WITH latest_install AS (    -- 先获取每个用户的最新安装日期（排除重复安装记录）    SELECT 
        user_id,        MAX(install_date) AS install_date  -- 取最新一次安装日期作为“新增日期”    FROM user_install    GROUP BY user_id
),
new_users AS (    SELECT 
        user_id,
        install_date    FROM latest_install    WHERE install_date >= CURRENT_DATE - INTERVAL 30 DAY 
      AND install_date < CURRENT_DATE)-- 后续retention_flags、retention_summary逻辑不变，仅将new_users的数据源改为latest_install

20、实现“加权移动平均”（近期权重更高）。

场景：销售预测分析，对近期数据赋予更高权重，更准确地反映最新趋势。

-- 计算加权移动平均，近期数据权重更高（当前*3, 前1*2, 前2*1）SELECT order_date, amount,
       (3*amount + 2*LAG(amount,1,0) OVER w + 1*LAG(amount,2,0) OVER w) / 6.0 AS wmaFROM ordersWINDOW w AS (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

解析：加权移动平均相比普通移动平均，对近期数据赋予更高权重，更能反映最新趋势。该查询中，当前订单金额权重为3，前1笔为2，前2笔为1，总和为6（权重之和）。WINDOW w定义了窗口范围并重复使用，简化了查询。加权移动平均常用于销售预测和趋势分析。

对于“前 1 行 / 前 2 行不存在” 的边界情况，例如第 1 条记录（无前 1、前 2 行），LAG(amount,1,0) 和 LAG(amount,2,0) 会返回 0，此时加权平均为 (3*amount + 0 + 0)/6，但实际应只计算 “当前行”（权重为 3，分母为 3），否则会导致前 2 条记录的加权平均被低估。此时，示例应为：

SELECT 
    order_date,
    amount,    -- 动态计算权重总和：根据当前行的位置，避免分母固定为6导致的误差
    (3*amount + 2*LAG(amount,1,0) OVER w + 1*LAG(amount,2,0) OVER w) 
    / 
    -- 权重总和：当前行必存在（3），前1行存在则加2，前2行存在则加1
    (3 + CASE WHEN LAG(amount,1) OVER w IS NOT NULL THEN 2 ELSE 0 END + CASE WHEN LAG(amount,2) OVER w IS NOT NULL THEN 1 ELSE 0 END) 
    AS wmaFROM orders-- 窗口范围不变，但需确保按日期排序（若有重复日期，提议加order_id确保顺序唯一）WINDOW w AS (ORDER BY order_date, order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

二、自动化报表 SQL 脚本模板

场景：日报、周报、月报自动生成（可集成到调度系统，如：Airflow），用于我们定期监控业务指标、用户增长和系统健康状态，支持数据驱动决策。

1、用户增长日报

-- 文件名：daily_user_growth.sql-- 调度：每天 00:10 执行（统计昨日数据）-- 向用户增长日报表插入昨日数据INSERT INTO report_daily_user_growth (report_date, new_users, active_users, retention_rate)SELECT 
    CURRENT_DATE - INTERVAL 1 DAY AS report_date,  -- 报表日期为昨天    -- 统计昨日新增用户数（创建时间为昨天）    COUNT(CASE WHEN DATE(created_at) = CURRENT_DATE - INTERVAL 1 DAY THEN 1 END) AS new_users,    -- 统计昨日活跃用户数（最后登录时间为昨天）    COUNT(CASE WHEN DATE(last_login) = CURRENT_DATE - INTERVAL 1 DAY THEN 1 END) AS active_users,    -- 计算次日留存率：前天新增用户中昨天仍登录的比例
    (SELECT COUNT(*) FROM users 
     WHERE DATE(created_at) = CURRENT_DATE - INTERVAL 2 DAY  -- 前天新增的用户       AND DATE(last_login) = CURRENT_DATE - INTERVAL 1 DAY   -- 昨天仍登录
    ) * 100.0 / NULLIF(  -- NULLIF避免除以0        COUNT(CASE WHEN DATE(created_at) = CURRENT_DATE - INTERVAL 2 DAY THEN 1 END), 0
    ) AS retention_rateFROM usersWHERE created_at >= CURRENT_DATE - INTERVAL 3 DAY;  -- 限定日期范围，提升查询性能

解析：该脚本用于我们每日自动统计用户增长的主要指标，包括：新增用户数、活跃用户数和次日留存率。通过CURRENT_DATE - INTERVAL动态计算日期，确保每天执行时自动获取前一天的数据。NULLIF函数处理了除数为0的特殊情况（当某天没有新增用户时）。限定查询范围（最近3天）避免全表扫描，提高执行效率。该报表可协助我们监控每日用户动态和产品留存情况。

2、订单交易周报

-- 文件名：weekly_order_report.sql-- 调度：每周一 01:00 执行（统计上周数据）-- 向上周订单报表插入数据INSERT INTO report_weekly_order (week_start, week_end, total_orders, gmv, avg_order_value, top_product)SELECT 
    -- 计算上周起始日期（上周一）    DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 7 DAY) AS week_start,    -- 计算上周结束日期（上周日）    DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 1 DAY) AS week_end,    COUNT(*) AS total_orders,  -- 订单总数    SUM(amount) AS gmv,  -- 销售总额    AVG(amount) AS avg_order_value,  -- 平均订单金额    -- 子查询：找出上周销量最高的商品
    (SELECT p.product_name 
     FROM products p 
     JOIN order_items oi ON p.product_id = oi.product_id 
     JOIN orders o ON oi.order_id = o.order_id 
     WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 7 DAY)       AND o.order_date < DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 1 DAY)     GROUP BY p.product_id 
     ORDER BY SUM(oi.quantity) DESC 
     LIMIT 1
    ) AS top_productFROM orders-- 筛选上周已完成的订单WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 7 DAY)  AND order_date < DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 1 DAY)  AND status = 'completed';

解析：该脚本每周一自动生成上周的订单交易报告，主要指标包括订单总数、销售总额(GMV)、平均订单金额和最畅销商品。通过WEEKDAY(CURDATE())动态计算上周的起止日期（周一至周日），确保无论周几执行都能正确获取上周数据。子查询通过关联订单、订单项和商品表，计算并返回上周销量最高的商品。报表仅统计状态为"completed"的订单，确保数据准确性。该报表可协助我们分析每周业绩和产品销售情况。

3、月度财务报表

-- 文件名：monthly_finance_report.sql-- 调度：每月1日 02:00 执行（统计上月数据）-- 向上月财务报表插入数据INSERT INTO report_monthly_finance (month, revenue, cost, profit, profit_margin)SELECT 
    -- 格式化上月为"年-月"形式    DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') AS month,    -- 子查询：计算上月营收（已完成订单总金额）
    (SELECT SUM(amount) FROM orders WHERE status = 'completed'     AND order_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')     AND order_date < DATE_FORMAT(CURDATE(), '%Y-%m-01')
    ) AS revenue,    -- 子查询：获取上月成本
    (SELECT SUM(cost) FROM product_costs 
     WHERE month = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m')
    ) AS cost,
    revenue - cost AS profit,  -- 计算利润    -- 计算利润率，处理营收为0的特殊情况    CASE WHEN revenue > 0 THEN (revenue - cost) * 100.0 / revenue ELSE 0 END AS profit_marginFROM DUAL;  -- MySQL中使用DUAL表获取单行结果；在其它数据库（如：PostgreSQL、SQL Server）中，可能只需要SELECT ...而无需FROM DUAL

解析：该脚本每月1日自动生成上月财务报表，主要指标包括营收、成本、利润和利润率。通过DATE_FORMAT和DATE_SUB函数动态计算上月的起止日期，确保准确筛选上月数据。营收通过统计上月已完成订单的总金额得出，成本从产品成本表获取，利润为营收减去成本，利润率为利润占营收的百分比。CASE语句处理了营收为0的特殊情况，避免出现错误。该报表为我们提供了月度经营状况数据。

4、错误日志监控日报

-- 文件名：daily_error_monitor.sql-- 调度：每天 00:15 执行（统计昨日数据）-- 向错误监控日报表插入昨日数据INSERT INTO report_error_monitor (report_date, error_count, top_error_url, warning_count)SELECT 
    CURRENT_DATE - INTERVAL 1 DAY,  -- 报表日期为昨天    COUNT(*) AS error_count,  -- 错误总数（5xx状态码）    -- 子查询：找出错误最多的URL
    (SELECT url FROM web_logs 
     WHERE DATE(timestamp) = CURRENT_DATE - INTERVAL 1 DAY 
       AND status >= 500  -- 服务器错误     GROUP BY url 
     ORDER BY COUNT(*) DESC 
     LIMIT 1
    ) AS top_error_url,    -- 统计警告数（4xx状态码）    COUNT(CASE WHEN status BETWEEN 400 AND 499 THEN 1 END) AS warning_countFROM web_logs-- 筛选昨日的错误和警告日志WHERE DATE(timestamp) = CURRENT_DATE - INTERVAL 1 DAY  AND status >= 400;  -- 4xx警告和5xx错误

解析：该脚本每日自动监控系统错误日志，统计错误总数、警告总数和错误最多的URL。HTTP状态码中，4xx表明客户端错误（警告），5xx表明服务器错误（错误）。通过子查询找出错误最多的URL，协助我们快速定位问题。报表数据来自web日志表，筛选条件确保只统计昨天的记录。该报表为我们提供了系统健康状态的每日快照，便于我们及时发现问题并加以解决。

搞定这 20 道题，SQL 窗口函数这块，我们基本就拿下了！从简单的排名到复杂的留存率计算，这些都是实际业务中常用的分析手段。把这些技巧用到日常报表里，不管是用户增长、销售趋势还是系统监控，我们都能轻松搞定。后来再遇到类似的分析需求，我们也能快速写出高效又准确的 SQL 啦！