窗口函数在电商数据分析中的深度解析与实操

第1章 窗口函数在电商数据分析中的不可替代价值

1.1 为什么学完分组聚合还要学窗口函数

我刚学会分组聚合那会儿，觉得自己SQL已经很厉害了。直到有一次，运营总监给我提了一个需求：“把每个店铺销量排名前3的商品列出来，同时保留每个商品的销量和店铺总销量。”

我当时用分组聚合做，发现一个问题：用GROUP BY分组后，只能得到每个店铺的汇总数据，或者每个商品的明细数据，没法在“每个商品的明细”旁边同时显示“该商品所属店铺的总销量”。分组聚合会“压缩”数据，丢失明细。

后来学会窗口函数，才发现这才是解决这类问题的正确工具。窗口函数的核心价值是：在不减少原始行数的情况下，对每一行数据做跨行计算。

电商数据分析里，窗口函数主要解决三类问题：

• 排名类：每个类目销量TOP10、每个店铺爆款筛选

• 累计类：用户累计消费金额、月度GMV滚动求和

• 偏移类：用户复购间隔、环比增长率

1.2 学习前的准备工作

环境确认

假设你已经有了上一章的电商订单表orders，字段包括：

• order_id：订单号

• user_id：用户ID

• shop_name：店铺名称

• category：商品类目

• amount：订单金额

• order_status：订单状态

• create_time：下单时间

表结构梳理

我们还会用到一张订单明细表order_details（用于商品维度的分析）：

• order_id：订单号

• product_id：商品ID

• product_name：商品名称

• category：商品类目

• quantity：购买数量

• price：单价

如果还没有这些表，可以从上一章的orders表扩展，或者在综合实操案例中一起创建。

窗口函数对数据量敏感，在大表上运行时要小心。我第一次在生产库上用窗口函数做全量用户累计消费计算，跑了10分钟没出来，差点把数据库拖垮。建议先在测试环境验证，或者用LIMIT限制数据量测试语法正确性。

第2章 窗口函数核心认知

2.1 窗口函数与分组聚合的本质区别

理解窗口函数，关键是理解它和GROUP BY的不同。

分组聚合：把多行压缩成一行，丢失明细数据。

-- 按店铺分组，每个店铺只有一行
SELECT shop_name, SUM(amount) AS total
FROM orders
GROUP BY shop_name;

窗口函数：保留每一行，同时在每一行旁边附加聚合结果。

-- 保留所有订单行，每行旁边显示该店铺的总金额
SELECT order_id, shop_name, amount,
       SUM(amount) OVER (PARTITION BY shop_name) AS shop_total
FROM orders;

2.2 窗口函数的标准语法

函数() OVER (
    PARTITION BY 字段1, 字段2    -- 分区，相当于分组
    ORDER BY 字段3               -- 排序，决定窗口内行的顺序
    ROWS/RANGE 子句              -- 窗口范围（可选）
)

• PARTITION BY：定义窗口的分区，类似于GROUP BY，不写则整个结果集为一个分区

• ORDER BY：定义分区内的排序顺序，影响排名类函数和累计计算

• ROWS/RANGE：定义窗口大小（后续会讲）

2.3 电商数据分析高频窗口函数场景

函数类别	函数名	电商场景
排名类	ROW_NUMBER, RANK, DENSE_RANK	商品销量排名、店铺GMV排名
聚合类	SUM, COUNT, AVG 开窗	用户累计消费、月度滚动GMV
偏移类	LAG, LEAD	复购间隔、环比增长率

第3章 排名类窗口函数

3.1 ROW_NUMBER、RANK、DENSE_RANK的区别

这三个函数都用于排名，但处理并列值的方式不同。

函数	处理并列的方式	示例（分数：90,90,80）
ROW_NUMBER	相同分数随机排，不并列	1,2,3
RANK	相同分数并列，跳过后续名次	1,1,3
DENSE_RANK	相同分数并列，不跳过名次	1,1,2
基础语法：

ROW_NUMBER() OVER (PARTITION BY 分组字段 ORDER BY 排序字段 DESC) AS rank

3.2 电商场景实操：商品类目销量排名

业务需求：618大促后，需要统计每个商品类目下销量最高的前3个商品，用于运营做爆款复盘。

步骤1：先统计每个商品在类目内的总销量

SELECT 
    category,
    product_id,
    product_name,
    SUM(quantity) AS total_sales
FROM order_details
GROUP BY category, product_id, product_name;

步骤2：用ROW_NUMBER给每个类目内的商品按销量排名

WITH product_sales AS (
    SELECT 
        category,
        product_id,
        product_name,
        SUM(quantity) AS total_sales
    FROM order_details
    GROUP BY category, product_id, product_name
)
SELECT 
    category,
    product_id,
    product_name,
    total_sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank
FROM product_sales;

步骤3：筛选出每个类目的TOP3

WITH product_sales AS (
    SELECT 
        category,
        product_id,
        product_name,
        SUM(quantity) AS total_sales
    FROM order_details
    GROUP BY category, product_id, product_name
),
ranked_products AS (
    SELECT 
        category,
        product_id,
        product_name,
        total_sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank
    FROM product_sales
)
SELECT *
FROM ranked_products
WHERE rank <= 3
ORDER BY category, rank;

我的踩坑经历：第一次做商品排名时，我用了ROW_NUMBER()，没注意到同一个类目下有两个商品销量相同。结果运营按这个排名去备货，并列第一的商品只备了一个的货，差点造成缺货。后来改用RANK()或DENSE_RANK()处理并列情况，再根据业务需求选择保留并列还是额外处理。

3.3 三种排名函数的对比实操

电商场景：同一类目下，商品A销量100，商品B销量100，商品C销量80。

SELECT 
    product_name,
    total_sales,
    ROW_NUMBER() OVER (ORDER BY total_sales DESC) AS row_num_rank,
    RANK() OVER (ORDER BY total_sales DESC) AS rank_rank,
    DENSE_RANK() OVER (ORDER BY total_sales DESC) AS dense_rank_rank
FROM product_sales;

预期结果：

product_name	total_sales	row_num_rank	rank_rank	dense_rank_rank
商品A	100	1	1	1
商品B	100	2	1	1
商品C	80	3	3	2

3.4 实操避坑提醒

问题	错误做法	正确做法
并列处理	用ROW_NUMBER后直接筛选TOP3	根据业务需求选择RANK或DENSE_RANK
分区遗漏	排名时忘记加PARTITION BY	明确排名范围，跨类目排名用不加分区
排序方向	排名升序导致最低值排第一	加DESC让最高值排第一

第4章 聚合类窗口函数

4.1 SUM/COUNT/AVG开窗的核心价值

聚合函数加OVER()后，可以做累计计算和滚动计算，这是分组聚合做不到的。

累计计算：统计每个用户截至当前订单的累计消费金额。

滚动计算：统计过去30天的滚动GMV，观察趋势。

4.2 电商场景实操：用户累计消费金额

业务需求：分析用户生命周期价值（LTV），需要计算每个用户每次下单时的累计消费金额，观察用户从首次下单到第N次下单的消费增长轨迹。

步骤1：按用户和时间排序，计算累计消费

SELECT 
    user_id,
    order_id,
    create_time,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY create_time) AS cumulative_amount
FROM orders
WHERE order_status IN ('paid', 'finished')
ORDER BY user_id, create_time;

分步操作：

1. PARTITION BY user_id：每个用户独立计算累计值

2. ORDER BY create_time：按时间顺序累加

3. SUM(amount) OVER (...)：对每个用户，从第一笔订单开始，逐笔累加金额

预期结果：每个用户的订单按时间排列，cumulative_amount字段显示到当前订单为止的总消费金额。

4.3 电商场景实操：月度GMV滚动求和

业务需求：运营想看最近3个月的滚动GMV，判断增长趋势。

步骤1：先按天统计GMV

SELECT 
    DATE(create_time) AS sale_date,
    SUM(amount) AS daily_gmv
FROM orders
WHERE order_status IN ('paid', 'finished')
GROUP BY DATE(create_time);

步骤2：计算过去30天的滚动GMV

WITH daily_gmv AS (
    SELECT 
        DATE(create_time) AS sale_date,
        SUM(amount) AS daily_gmv
    FROM orders
    WHERE order_status IN ('paid', 'finished')
    GROUP BY DATE(create_time)
)
SELECT 
    sale_date,
    daily_gmv,
    SUM(daily_gmv) OVER (ORDER BY sale_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30d_gmv
FROM daily_gmv
ORDER BY sale_date;

关键语法：ROWS BETWEEN 29 PRECEDING AND CURRENT ROW 定义了窗口范围：当前行往前推29行（共30行）。

4.4 窗口范围（ROWS/RANGE）详解

ROWS和RANGE用于定义窗口大小，常用选项：

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从分区开始到当前行（默认）

• ROWS BETWEEN 29 PRECEDING AND CURRENT ROW：当前行和前29行

• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：当前行到分区结束

电商场景：计算每个商品过去7天的日均销量

SELECT 
    product_id,
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        PARTITION BY product_id 
        ORDER BY sale_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS avg_7d_sales
FROM product_daily_sales;

4.5 实操避坑提醒

问题	错误做法	正确做法
累计范围	忘了加ORDER BY，累计变成全量求和	累计计算必须加ORDER BY定义累加顺序
滚动计算	直接用SUM()不加ROWS	用ROWS BETWEEN定义滚动窗口
数据稀疏	日期不连续导致滚动窗口不是实际30天	考虑用RANGE或先补全日期

第5章 偏移类窗口函数

5.1 LAG/LEAD的核心价值

LAG和LEAD用于获取同一分区内当前行之前或之后某行的值。这在电商数据分析里非常有用：

• 复购间隔：用户上一次购买是什么时候

• 环比计算：跟上个月/去年同期对比

• 漏斗分析：获取用户上一步的行为状态

基础语法：

LAG(字段, 偏移量, 默认值) OVER (PARTITION BY 分组 ORDER BY 排序)
LEAD(字段, 偏移量, 默认值) OVER (PARTITION BY 分组 ORDER BY 排序)

5.2 电商场景实操：用户复购间隔计算

业务需求：计算每个用户两次购买之间的间隔天数，用于判断用户粘性和复购周期。

步骤1：获取每个用户的订单时间序列

SELECT 
    user_id,
    order_id,
    create_time,
    LAG(create_time, 1) OVER (PARTITION BY user_id ORDER BY create_time) AS prev_order_time
FROM orders
WHERE order_status IN ('paid', 'finished');

步骤2：计算间隔天数

WITH user_orders_with_prev AS (
    SELECT 
        user_id,
        order_id,
        create_time,
        LAG(create_time, 1) OVER (PARTITION BY user_id ORDER BY create_time) AS prev_order_time
    FROM orders
    WHERE order_status IN ('paid', 'finished')
)
SELECT 
    user_id,
    order_id,
    create_time,
    prev_order_time,
    DATEDIFF(create_time, prev_order_time) AS days_since_last_order
FROM user_orders_with_prev
WHERE prev_order_time IS NOT NULL  -- 排除首单
ORDER BY user_id, create_time;

预期结果：每个用户的每一笔复购订单，都能看到距离上一单的天数。

5.3 电商场景实操：同比环比数据统计

业务需求：统计每月GMV，并计算环比增长率（相比上个月）和同比增长率（相比去年同月）。

步骤1：按月度统计GMV

SELECT 
    DATE_FORMAT(create_time, '%Y-%m') AS month,
    SUM(amount) AS gmv
FROM orders
WHERE order_status IN ('paid', 'finished')
GROUP BY DATE_FORMAT(create_time, '%Y-%m')
ORDER BY month;

步骤2：用LAG计算环比增长率

WITH monthly_gmv AS (
    SELECT 
        DATE_FORMAT(create_time, '%Y-%m') AS month,
        SUM(amount) AS gmv
    FROM orders
    WHERE order_status IN ('paid', 'finished')
    GROUP BY DATE_FORMAT(create_time, '%Y-%m')
)
SELECT 
    month,
    gmv,
    LAG(gmv, 1) OVER (ORDER BY month) AS prev_month_gmv,
    ROUND((gmv - LAG(gmv, 1) OVER (ORDER BY month)) / LAG(gmv, 1) OVER (ORDER BY month) * 100, 2) AS mom_growth_pct
FROM monthly_gmv
ORDER BY month;

步骤3：计算同比增长率（需要去年同月数据）

WITH monthly_gmv AS (
    SELECT 
        DATE_FORMAT(create_time, '%Y-%m') AS month,
        YEAR(create_time) AS year,
        MONTH(create_time) AS month_num,
        SUM(amount) AS gmv
    FROM orders
    WHERE order_status IN ('paid', 'finished')
    GROUP BY DATE_FORMAT(create_time, '%Y-%m'), YEAR(create_time), MONTH(create_time)
)
SELECT 
    month,
    year,
    month_num,
    gmv,
    LAG(gmv, 12) OVER (ORDER BY month) AS last_year_same_month_gmv,
    ROUND((gmv - LAG(gmv, 12) OVER (ORDER BY month)) / LAG(gmv, 12) OVER (ORDER BY month) * 100, 2) AS yoy_growth_pct
FROM monthly_gmv
ORDER BY month;

5.4 电商场景实操：转化漏斗分析

业务需求：分析用户从“访问商品详情页”到“加入购物车”到“下单”到“支付成功”的转化率。

步骤1：按用户行为序列排列

假设有一张用户行为表user_behavior，字段：user_id、action_type（view/cart/order/pay）、action_time。

SELECT 
    user_id,
    action_type,
    action_time,
    LAG(action_type, 1) OVER (PARTITION BY user_id ORDER BY action_time) AS prev_action
FROM user_behavior;

步骤2：统计各环节转化

这通常需要更复杂的逻辑，但LAG可以帮助判断用户是否完成了上一步。

我的踩坑经历：有一次用LAG计算环比，我忘了ORDER BY月份，结果数据完全错乱。窗口函数里ORDER BY定义了行的顺序，LAG取的是“排序后”的上一条，而不是“逻辑上”的上个月。这个细节让我排查了整整一个下午。

5.5 实操避坑提醒

问题	错误做法	正确做法
顺序错误	忘记ORDER BY，LAG取到随机行	必须加ORDER BY定义时间顺序
首行处理	首行的LAG是NULL，直接计算报错	用IFNULL处理，或者排除首行
间隔计算	直接用日期相减不考虑业务定义	明确“间隔”是按自然日还是按行为顺序

第6章 分区与排序的核心用法

6.1 PARTITION BY的分区逻辑

PARTITION BY定义窗口的分区，相当于GROUP BY的分组概念，但窗口函数不减少行数。

不分区（整个结果集为一个窗口）：

SELECT 
    order_id,
    amount,
    SUM(amount) OVER () AS total_amount  -- 全表总金额
FROM orders;

分区（每个店铺独立计算）：

SELECT 
    order_id,
    shop_name,
    amount,
    SUM(amount) OVER (PARTITION BY shop_name) AS shop_total
FROM orders;

6.2 ORDER BY在窗口函数中的双重作用

在窗口函数中，ORDER BY有两个作用：

作用一：决定排名函数的顺序

ROW_NUMBER() OVER (ORDER BY amount DESC)  -- 按金额排名的顺序

作用二：决定累计计算的累加顺序

SUM(amount) OVER (ORDER BY create_time)  -- 按时间顺序累加

6.3 组合使用：用户分群实操

业务需求：将用户按累计消费金额分为“高价值”“中价值”“低价值”三类，同时保留每笔订单明细。

WITH user_total AS (
    SELECT 
        user_id,
        SUM(amount) AS total_spent
    FROM orders
    WHERE order_status IN ('paid', 'finished')
    GROUP BY user_id
),
user_segments AS (
    SELECT 
        user_id,
        total_spent,
        NTILE(3) OVER (ORDER BY total_spent DESC) AS segment  -- 三等分
    FROM user_total
)
SELECT 
    o.user_id,
    o.order_id,
    o.amount,
    o.create_time,
    s.total_spent,
    CASE s.segment
        WHEN 1 THEN '高价值'
        WHEN 2 THEN '中价值'
        WHEN 3 THEN '低价值'
    END AS user_segment
FROM orders o
JOIN user_segments s ON o.user_id = s.user_id
WHERE o.order_status IN ('paid', 'finished')
ORDER BY o.user_id, o.create_time;

这里用了NTILE(3)函数，将用户按总消费金额分成3组。

用户分群结果属于用户画像数据，使用时要遵守数据最小化原则。只用于运营策略制定和产品优化，不要将带有user_id的分群数据导出给无关人员。如果分群结果需要共享给运营，建议先对user_id做脱敏处理，或者只输出统计层面的数据。

第7章 综合实操案例：618大促服饰类目数据分析

7.1 案例背景

业务场景：618大促结束后，需要对服饰类目的数据进行深度分析，完成以下任务：

1. 计算每个用户的复购率和复购间隔

2. 统计每个商品类目的销量排名（用三种排名函数对比）

3. 对用户按累计消费金额进行价值分层，并计算各层级的贡献占比

涉及表：orders（订单表）、order_details（订单明细表）

7.2 准备工作：创建测试数据

-- 创建订单表（如果已有可跳过）
CREATE TABLE orders (
    order_id VARCHAR(50) PRIMARY KEY,
    user_id VARCHAR(50),
    category VARCHAR(20),
    amount DECIMAL(10,2),
    order_status VARCHAR(10),
    create_time DATETIME
);

-- 创建订单明细表
CREATE TABLE order_details (
    detail_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(50),
    product_id VARCHAR(50),
    product_name VARCHAR(100),
    category VARCHAR(20),
    quantity INT,
    price DECIMAL(10,2)
);

7.3 任务1：用户复购率与复购间隔

步骤1：计算每个用户的订单次数和复购标识

WITH user_order_stats AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS order_cnt,
        MIN(create_time) AS first_order_time,
        MAX(create_time) AS last_order_time
    FROM orders
    WHERE order_status IN ('paid', 'finished')
    GROUP BY user_id
)
SELECT 
    COUNT(DISTINCT CASE WHEN order_cnt >= 2 THEN user_id END) AS repeat_user_cnt,
    COUNT(DISTINCT user_id) AS total_user_cnt,
    ROUND(COUNT(DISTINCT CASE WHEN order_cnt >= 2 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2) AS repeat_rate_pct
FROM user_order_stats;

步骤2：计算复购用户的平均复购间隔

WITH user_orders AS (
    SELECT 
        user_id,
        order_id,
        create_time,
        LAG(create_time, 1) OVER (PARTITION BY user_id ORDER BY create_time) AS prev_order_time
    FROM orders
    WHERE order_status IN ('paid', 'finished')
)
SELECT 
    AVG(DATEDIFF(create_time, prev_order_time)) AS avg_repeat_interval_days
FROM user_orders
WHERE prev_order_time IS NOT NULL;

7.4 任务2：商品类目销量排名

步骤1：统计每个商品的销量

SELECT 
    category,
    product_id,
    product_name,
    SUM(quantity) AS total_sales
FROM order_details
GROUP BY category, product_id, product_name;

步骤2：用三种排名函数对比

WITH product_sales AS (
    SELECT 
        category,
        product_id,
        product_name,
        SUM(quantity) AS total_sales
    FROM order_details
    GROUP BY category, product_id, product_name
)
SELECT 
    category,
    product_name,
    total_sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) AS row_num_rank,
    RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank_rank,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS dense_rank_rank
FROM product_sales
ORDER BY category, total_sales DESC;

步骤3：筛选每个类目的TOP3（用DENSE_RANK保留并列）

WITH product_sales AS (
    SELECT 
        category,
        product_id,
        product_name,
        SUM(quantity) AS total_sales
    FROM order_details
    GROUP BY category, product_id, product_name
),
ranked_products AS (
    SELECT 
        category,
        product_name,
        total_sales,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank
    FROM product_sales
)
SELECT *
FROM ranked_products
WHERE rank <= 3
ORDER BY category, rank;

7.5 任务3：用户价值分层与贡献占比

步骤1：计算每个用户的累计消费金额

WITH user_total AS (
    SELECT 
        user_id,
        SUM(amount) AS total_spent,
        COUNT(DISTINCT order_id) AS order_cnt
    FROM orders
    WHERE order_status IN ('paid', 'finished')
    GROUP BY user_id
)
SELECT * FROM user_total;

步骤2：按累计消费金额进行分层（用NTILE分4组）

WITH user_total AS (
    SELECT 
        user_id,
        SUM(amount) AS total_spent,
        COUNT(DISTINCT order_id) AS order_cnt
    FROM orders
    WHERE order_status IN ('paid', 'finished')
    GROUP BY user_id
),
user_segments AS (
    SELECT 
        user_id,
        total_spent,
        order_cnt,
        NTILE(4) OVER (ORDER BY total_spent DESC) AS segment_num
    FROM user_total
)
SELECT 
    CASE segment_num
        WHEN 1 THEN '高价值'
        WHEN 2 THEN '中高价值'
        WHEN 3 THEN '中低价值'
        WHEN 4 THEN '低价值'
    END AS user_segment,
    COUNT(user_id) AS user_cnt,
    SUM(total_spent) AS total_gmv,
    ROUND(SUM(total_spent) * 100.0 / (SELECT SUM(total_spent) FROM user_total), 2) AS contribution_pct
FROM user_segments
GROUP BY segment_num
ORDER BY segment_num;

7.6 结果验证

执行完所有查询后，建议做验证：

• 各层级GMV之和应等于总GMV

• 各层级用户数之和应等于总用户数

• 排名TOP3商品在各子查询中应一致

第8章 本章踩坑清单与合规总结

8.1 新手常见踩坑

场景	错误操作	正确做法
排名并列	用ROW_NUMBER处理并列值	根据业务选择RANK或DENSE_RANK
累计范围	累计计算时忘了ORDER BY	必须加ORDER BY定义累加顺序
偏移顺序	LAG没加ORDER BY，取到随机行	必须加ORDER BY定义行顺序
性能问题	在大表上不加限制直接跑	先用LIMIT测试，确认语法后再放开
分区遗漏	排名时忘了PARTITION BY	明确排名范围，全量排名不加，分组排名必须加

8.2 电商数据合规提示

用户分群数据的使用边界

窗口函数经常用于用户分群和用户画像计算。这些数据属于敏感信息，使用时要遵守：

• 分群结果用于运营策略制定时，只输出统计层面的数据（如各层级用户数、贡献占比），不输出用户明细

• 如果确实需要用户明细进行个性化触达，应该通过公司内部的CRM系统，而不是导出user_id列表给运营自行处理

• 用户分群规则和数据要定期清理，不要永久保留

敏感信息处理

在窗口函数计算中，如果涉及用户ID、手机号等字段：

• 查询时不需要的敏感字段不要SELECT

• 结果展示时，用户ID可以用CONCAT(LEFT(user_id,3), '***')脱敏

• 导出数据时，确认导出范围和数据用途，避免敏感信息泄露

第9章 结语

窗口函数是SQL进阶的核心技能，也是电商数据分析师和普通数据取数人员的分水岭。掌握排名、累计、偏移这三类窗口函数，你就能独立完成用户复购分析、商品排名、用户分层、漏斗分析等复杂业务需求。
有问题的评论区留言，我看到会回复。