电商数据分析 SQL 速查：20 个高频查询模板（GMV、复购、漏斗）

下面是 20 个可直接套用的 SQL 模板。字段名请按你的表结构替换。

第一节：销售类（5 个）

1. 日 GMV 趋势

SELECT DATE(order_time) AS d, SUM(paid_amount) AS gmv
FROM orders
WHERE order_time >= '2026-01-01'
GROUP BY d
ORDER BY d;

2. 商品 GMV 排名

SELECT sku_id, SUM(paid_amount) AS gmv, COUNT(*) AS orders
FROM order_items
GROUP BY sku_id
ORDER BY gmv DESC
LIMIT 20;

3. 客单价

SELECT SUM(paid_amount) / COUNT(DISTINCT order_id) AS aov
FROM orders
WHERE DATE(order_time) BETWEEN '2026-03-01' AND '2026-03-31';

4. 月环比 GMV

SELECT DATE_FORMAT(order_time, '%Y-%m') AS m, SUM(paid_amount) AS gmv
FROM orders
GROUP BY m
ORDER BY m;

5. 退款后净 GMV

SELECT SUM(paid_amount) - COALESCE(SUM(refund_amount), 0) AS net_gmv
FROM orders LEFT JOIN refunds USING(order_id)
WHERE DATE(order_time) = '2026-04-10';

第二节：商品类（4 个）

6. 上新爬坡

SELECT DATE(order_time) AS d, SUM(paid_amount) AS gmv
FROM order_items
WHERE sku_id = 'SKU001'
GROUP BY d;

7. 高退货 SKU

SELECT sku_id, SUM(CASE WHEN is_refund=1 THEN 1 ELSE 0 END) / COUNT(*) AS refund_rate
FROM order_items
GROUP BY sku_id
HAVING refund_rate > 0.2
ORDER BY refund_rate DESC;

8. 商品连带率

SELECT order_id, COUNT(DISTINCT sku_id) AS sku_count
FROM order_items
GROUP BY order_id
HAVING sku_count > 1;

9. 断货 SKU

SELECT sku_id, stock
FROM inventory
WHERE stock < safety_stock;

第三节：用户类（5 个）

10. 新老客分布

SELECT
  SUM(CASE WHEN is_new=1 THEN 1 ELSE 0 END) AS new_users,
  SUM(CASE WHEN is_new=0 THEN 1 ELSE 0 END) AS old_users
FROM orders
WHERE DATE(order_time) = CURDATE();

11. 30 日复购率

SELECT COUNT(DISTINCT CASE WHEN repurchase_cnt >= 2 THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS repurchase_rate
FROM (
  SELECT user_id, COUNT(*) AS repurchase_cnt
  FROM orders
  WHERE order_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  GROUP BY user_id
) t;

12. RFM 分层（简化）

SELECT user_id,
  DATEDIFF(CURDATE(), MAX(order_time)) AS recency,
  COUNT(*) AS frequency,
  SUM(paid_amount) AS monetary
FROM orders
GROUP BY user_id;

13. 高价值客户

SELECT user_id, SUM(paid_amount) AS total
FROM orders
GROUP BY user_id
ORDER BY total DESC
LIMIT 100;

14. 流失用户

SELECT user_id
FROM orders
GROUP BY user_id
HAVING MAX(order_time) < DATE_SUB(CURDATE(), INTERVAL 90 DAY);

第四节：投放类（3 个）

15. 渠道 ROI

SELECT channel, SUM(revenue) / SUM(cost) AS roi
FROM ad_stats
GROUP BY channel;

16. 素材表现

SELECT creative_id, SUM(click) / SUM(impression) AS ctr, SUM(convert) / SUM(click) AS cvr
FROM ad_creative_stats
GROUP BY creative_id
ORDER BY ctr DESC;

17. 关键词转化

SELECT keyword, SUM(click) AS clicks, SUM(order_cnt) AS orders
FROM search_ads_stats
GROUP BY keyword
ORDER BY orders DESC;

第五节：漏斗类（3 个）

18. 商品页漏斗

SELECT
  COUNT(DISTINCT CASE WHEN event='view' THEN user_id END) AS viewers,
  COUNT(DISTINCT CASE WHEN event='cart' THEN user_id END) AS cart_users,
  COUNT(DISTINCT CASE WHEN event='order' THEN user_id END) AS buyers
FROM events
WHERE DATE(event_time) = CURDATE();

19. 直播间漏斗

SELECT
  MAX(CASE WHEN metric='watch' THEN value END) AS watch,
  MAX(CASE WHEN metric='interact' THEN value END) AS interact,
  MAX(CASE WHEN metric='click_product' THEN value END) AS clicks,
  MAX(CASE WHEN metric='order' THEN value END) AS orders
FROM live_stats
WHERE live_id = 123;

20. 搜索转化漏斗

SELECT
  SUM(search_cnt) AS searches,
  SUM(click_cnt) AS clicks,
  SUM(add_cart_cnt) AS add_carts,
  SUM(order_cnt) AS orders
FROM search_funnel
WHERE DATE(d) BETWEEN '2026-03-01' AND '2026-03-31';

第六节：优化建议

• 用 LIMIT 先看一部分，不要直接全表；
• 时间范围必加 WHERE；
• 大表 JOIN 用索引字段；
• 复杂查询拆分成 CTE；
• 结果落盘到临时表便于二次分析。

第七节：常见误区

• 不加时间范围，把集群打挂；
• JOIN 条件错，结果翻倍；
• 口径与 BI 不一致；
• 查询跑慢不优化，团队都在等。

掌握这 20 个模板，配合 Excel 或 BI，可以让运营从「求人要数据」变成「自己动手分析」。