SQL实战：按月统计订单与顾客数的电商数据分析

1. 力扣1565题解析按月统计订单数与顾客数的SQL实战这道题来自力扣数据库题库要求我们按月统计每个月的订单数量和顾客数量。作为电商数据分析的基础操作这类统计能直观反映业务增长趋势和用户活跃度。实际工作中市场部门每月都会要求技术团队提供类似报表用于评估营销活动效果和用户留存情况。题目给出orders表结构包含order_id、customer_id和order_date三个关键字段。我们需要从这三个字段中提取出月份维度然后进行聚合统计。这种按月统计的需求在真实业务场景中非常普遍比如统计每月新增用户数分析月度复购率监控GMV月度变化2. 核心解题思路与SQL方案设计2.1 日期处理函数选择处理日期字段是本题的第一个关键点。SQL中常用的日期函数有DATE_FORMAT(order_date, %Y-%m)EXTRACT(YEAR_MONTH FROM order_date)CONCAT(YEAR(order_date), -, LPAD(MONTH(order_date), 2, 0))提示不同数据库系统的日期函数略有差异。MySQL推荐使用DATE_FORMATSQL Server则常用FORMAT函数Oracle则使用TO_CHAR。我最终选择DATE_FORMAT方案因为输出格式统一为YYYY-MM形式代码可读性更好在MySQL中性能最优2.2 去重统计的实现方式统计顾客数时需要去重常见方案有COUNT(DISTINCT customer_id)先GROUP BY customer_id再COUNT测试表明在数据量小于100万时COUNT(DISTINCT)效率更高。当customer_id有索引时性能差异会更明显。3. 完整SQL实现与优化3.1 基础实现方案SELECT DATE_FORMAT(order_date, %Y-%m) AS month, COUNT(order_id) AS order_count, COUNT(DISTINCT customer_id) AS customer_count FROM orders GROUP BY DATE_FORMAT(order_date, %Y-%m) ORDER BY month;这个方案清晰易懂但在大数据量时可能存在性能问题。3.2 性能优化版本SELECT DATE_FORMAT(order_date, %Y-%m) AS month, COUNT(*) AS order_count, COUNT(DISTINCT customer_id) AS customer_count FROM orders WHERE order_date BETWEEN 2020-01-01 AND 2022-12-31 -- 添加时间范围过滤 GROUP BY month ORDER BY month;优化点用COUNT(*)替代COUNT(order_id)避免非空检查添加时间范围条件减少扫描数据量确保order_date和customer_id上有索引4. 进阶分析与业务应用4.1 同比环比计算在实际业务中常需要计算环比增长率WITH monthly_stats AS ( SELECT DATE_FORMAT(order_date, %Y-%m) AS month, COUNT(*) AS order_count, COUNT(DISTINCT customer_id) AS customer_count FROM orders GROUP BY month ) SELECT curr.month, curr.order_count, prev.order_count AS prev_month_order, ROUND((curr.order_count - prev.order_count) / prev.order_count * 100, 2) AS mom_growth FROM monthly_stats curr LEFT JOIN monthly_stats prev ON curr.month DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(prev.month, -01), %Y-%m-%d), INTERVAL 1 MONTH), %Y-%m) ORDER BY curr.month;4.2 新老客户分析区分新老客户能提供更多业务洞察WITH first_orders AS ( SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id ) SELECT DATE_FORMAT(o.order_date, %Y-%m) AS month, COUNT(DISTINCT CASE WHEN DATE_FORMAT(o.order_date, %Y-%m) DATE_FORMAT(f.first_order_date, %Y-%m) THEN o.customer_id END) AS new_customers, COUNT(DISTINCT CASE WHEN DATE_FORMAT(o.order_date, %Y-%m) DATE_FORMAT(f.first_order_date, %Y-%m) THEN o.customer_id END) AS returning_customers FROM orders o JOIN first_orders f ON o.customer_id f.customer_id GROUP BY month ORDER BY month;5. 实战经验与避坑指南5.1 时区问题处理跨国业务中时区是常见坑点SELECT DATE_FORMAT(CONVERT_TZ(order_date, 00:00, 08:00), %Y-%m) AS month, COUNT(*) AS order_count FROM orders GROUP BY month;5.2 NULL值处理当order_date可能为NULL时SELECT DATE_FORMAT(COALESCE(order_date, 1970-01-01), %Y-%m) AS month, COUNT(*) AS order_count FROM orders GROUP BY month HAVING month ! 1970-01; -- 过滤掉NULL值5.3 性能优化技巧为order_date和customer_id创建复合索引大数据量时考虑按月分区表使用EXPLAIN分析执行计划考虑使用物化视图预计算结果6. 不同数据库方言实现6.1 PostgreSQL版本SELECT TO_CHAR(order_date, YYYY-MM) AS month, COUNT(*) AS order_count, COUNT(DISTINCT customer_id) AS customer_count FROM orders GROUP BY TO_CHAR(order_date, YYYY-MM) ORDER BY month;6.2 SQL Server版本SELECT FORMAT(order_date, yyyy-MM) AS month, COUNT(*) AS order_count, COUNT(DISTINCT customer_id) AS customer_count FROM orders GROUP BY FORMAT(order_date, yyyy-MM) ORDER BY month;6.3 Oracle版本SELECT TO_CHAR(order_date, YYYY-MM) AS month, COUNT(*) AS order_count, COUNT(DISTINCT customer_id) AS customer_count FROM orders GROUP BY TO_CHAR(order_date, YYYY-MM) ORDER BY month;7. 真实业务场景扩展7.1 结合用户画像分析SELECT DATE_FORMAT(o.order_date, %Y-%m) AS month, COUNT(DISTINCT o.customer_id) AS total_customers, COUNT(DISTINCT CASE WHEN u.vip_level 1 THEN o.customer_id END) AS vip_customers, COUNT(DISTINCT CASE WHEN u.age BETWEEN 18 AND 25 THEN o.customer_id END) AS young_customers FROM orders o LEFT JOIN users u ON o.customer_id u.user_id GROUP BY month ORDER BY month;7.2 商品类别维度分析SELECT DATE_FORMAT(o.order_date, %Y-%m) AS month, p.category, COUNT(*) AS order_count, COUNT(DISTINCT o.customer_id) AS customer_count FROM orders o JOIN order_items oi ON o.order_id oi.order_id JOIN products p ON oi.product_id p.product_id GROUP BY month, p.category ORDER BY month, p.category;在实际项目中这类SQL通常会被封装成存储过程或视图供BI工具直接调用。我通常会创建一个名为monthly_order_stats的视图然后基于这个视图开发各种分析报表。