当前位置：首页 > news >正文

掌握 CTE 技巧，实现连续日期和月份的 SQL 报表统计

news 2025/6/29 12:19:10

在 SQL 查询中，报表统计往往涉及到特定时间段内的数据汇总，如每日、每月的销售数据等。然而，面对缺少数据的日期或月份，传统 SQL 查询可能会直接跳过这些日期，使得输出的报表在视觉上并不连续。本文将展示如何利用 CTE（通用表表达式）生成完整的时间范围，并确保报表统计结果中，数据缺失的日期或月份以 0 填充。通过这种方法，可以大大提高数据报表的完整性，使结果更加连贯清晰。

需求背景

假设我们在电商系统中有一个订单表 orders，其结构如下：

order_id：订单编号
order_date：订单日期
amount：订单金额

我们将基于该表，构建以下几种常见的报表统计需求：

最近 30 天的每日销售统计，包含数据缺失的日期。
最近 6 个月的月度销售统计，包含没有数据的月份。

在这两个场景中，我们希望输出的结果表中，不论是否有数据，特定时间段内的每一天或每一月都应该显示，并且缺失数据的日期或月份的销售额显示为 0。

示例 1：最近 30 天的每日销售统计

为了显示最近 30 天的每日销售数据，并确保每一天都显示出来，我们首先可以利用递归 CTE 生成一个完整的 30 天日期范围，然后通过 LEFT JOIN 将订单数据连接上去。

SQL 查询示例

-- 递归 CTE 生成最近 30 天的日期范围
WITH RECURSIVE DateRange AS (SELECT CURDATE() - INTERVAL 29 DAY AS dateUNION ALLSELECT date + INTERVAL 1 DAYFROM DateRangeWHERE date + INTERVAL 1 DAY <= CURDATE()
),
Last30Days AS (SELECT order_date, amountFROM ordersWHERE order_date >= CURDATE() - INTERVAL 30 DAY
)
SELECT d.date AS order_date,COALESCE(SUM(l.amount), 0) AS daily_sales
FROM DateRange d
LEFT JOIN Last30Days l ON d.date = l.order_date
GROUP BY d.date
ORDER BY d.date;

查询解析

DateRange CTE：生成最近 30 天的完整日期范围。
Last30Days CTE：筛选出订单表中最近 30 天的数据。
主查询：通过 LEFT JOIN 将 DateRange 和 Last30Days 连接在一起，确保每一天都出现在结果中。使用 COALESCE 函数将没有数据的日期销售额填充为 0。

通过该查询，我们可以得到一个包含最近 30 天每日销售额的表格，其中没有订单数据的日期也会显示为 0。

示例 2：最近 6 个月的月度销售统计

同样地，为了展示最近 6 个月的月度销售数据，并包含没有订单的月份，我们可以生成一个完整的 6 个月月份范围，再将订单数据连接上去。

SQL 查询示例

-- 递归 CTE 生成最近 6 个月的月份范围
WITH RECURSIVE MonthRange AS (SELECT DATE_FORMAT(CURDATE() - INTERVAL 5 MONTH, '%Y-%m') AS monthUNION ALLSELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE(month, '%Y-%m'), INTERVAL 1 MONTH), '%Y-%m')FROM MonthRangeWHERE DATE_ADD(STR_TO_DATE(month, '%Y-%m'), INTERVAL 1 MONTH) <= CURDATE()
),
Last6Months AS (SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, amountFROM ordersWHERE order_date >= CURDATE() - INTERVAL 6 MONTH
)
SELECT m.month,COALESCE(SUM(l.amount), 0) AS monthly_sales
FROM MonthRange m
LEFT JOIN Last6Months l ON m.month = l.month
GROUP BY m.month
ORDER BY m.month;

查询解析

MonthRange CTE：通过递归 CTE 生成最近 6 个月的完整月份范围。
Last6Months CTE：提取订单表中最近 6 个月的订单数据，并格式化日期为 YYYY-MM 月份格式。
主查询：利用 LEFT JOIN 将 MonthRange 和 Last6Months 连接在一起，确保每个月都出现在最终结果中。使用 COALESCE 确保无数据的月份销售额为 0。

这样，我们可以得到包含最近 6 个月每月销售额的表格，其中没有订单数据的月份会显示为 0，保证了数据的连续性。