企业官网建设流程全解析

简介：面向使用SQL Server与MySQL的数据库开发、运维及数据分析人员，这份PDF资料系统讲解了按天、按小时、按分钟统计连续时间段数据的完整方法。内容首先介绍系统表提供连续数字序列的用途，接着演示如何利用日期加减和差值函数生成连续的年份、月份、日期、小时序列，并延伸到分钟级统计思路；同时结合实际交易统计场景给出存储过程封装案例，覆盖左连接补零、日期字段索引优化以及MySQL没有对应系统表时的替代方案等关键细节。资料为单个PDF文件，大小约68KB，便于快速下载和随时查阅。目前已有六千三百余人浏览学习。通过阅读可以快速掌握时间段补全与分组统计的核心套路，包含可直接套用的SQL语句，避免因某时间段无数据而漏报，无论是按天汇总交易笔数，还是按小时观察请求峰值，都能找到对应的写法，适合需要做趋势分析、峰值监控或生成报表统计的数据库从业者参考。

1. 按天按小时统计的真相：先把时间轴补齐，再谈 LEFT JOIN

业务方丢过来一句话：把交易量按天、按小时统计出来，缺的日期补 0，我要看连续时间段的峰值。这句话听着简单，实际上写过 SQL 的人十有八九翻过车：直接GROUP BY日期，某天没交易，这行就没了，折线图硬生生断一截。真正的解法不是查完再补，而是查询前先造一条连续时间轴，再让业务表LEFT JOIN上去。

这份资料把两边都覆盖了：SQL Server 用master..spt_values生成连续年、月、天、小时、分钟，MySQL 用DATE_FORMAT做分组，再配合数字表或递归 CTE 补全时间轴。适合正在写经营报表、做数据看板的后台开发，也适合面试前突击聚合查询的人。下面按我的使用顺序展开：先讲时间轴怎么造，再讲怎么挂业务表，最后落到踩坑点。

2. SQL Server 连续时间段：master..spt_values 是怎么帮你造数的

2.1 spt_values 是什么：一张系统表为什么能当数字生成器

SQL Server 不像 PostgreSQL 自带generate_series，但系统库里藏着一张master..spt_values，很多老 DBA 拿它当免费数字生成器。它真正的用途不是业务表，而是支持 SQL Server 内部一些组件取枚举值，其中type = 'P'这一组数据最有用：返回从 0 开始的连续整数。

先跑一条最基础的语句，看看它到底能取到什么：

SELECT number FROM master..spt_values WHERE type = 'P' AND number <= 2047;

参数说明：type = 'P'限定的是主序列，number是返回的整数列，<= 2047是人为截断。如果不加number条件，你会看到一堆负数和其他 type，那些是系统内部用的，不适合拿来做时间轴。这个序列的上限是 2047，许多教程不提醒这一点，导致后面按小时、按分钟统计时莫名其妙丢数据，这点后面避坑章节专门讲。

2.2 按年、按月、按天、按小时生成连续序列

先声明开始时间和结束时间，然后让number从 0 递增到两个日期之间的差值，再用DATEADD把起始时间往后推，就能得到完整的连续时间点。下面四段是这份资料里最核心的模板：

-- 按年：取 2016 到 2019 的连续年份 SELECT CONVERT(NVARCHAR(4), DATEADD(YEAR, number, '2016-01-01'), 120) AS GroupYear FROM master..spt_values WHERE type = 'P' AND number <= DATEDIFF(YEAR, '2016-01-01', '2019-01-01'); -- 按月：取 2018 到 2019 的连续月份 SELECT CONVERT(NVARCHAR(7), DATEADD(MONTH, number, '2019-01-01'), 120) AS GroupMonth FROM master..spt_values WHERE type = 'P' AND number <= DATEDIFF(MONTH, '2018-01-01', '2019-01-01'); -- 按天：取 2019-01-01 到 2019-01-18 的连续日期 SELECT CONVERT(NVARCHAR(10), DATEADD(DAY, number, '2019-01-01'), 120) AS GroupDay FROM master..spt_values WHERE type = 'P' AND number <= DATEDIFF(DAY, '2019-01-01', '2019-01-18'); -- 按小时：取 2019-01-18 00:00 到 23:00 的连续小时 SELECT CONVERT(NVARCHAR(16), DATEADD(HOUR, number, '2019-01-18 00:00'), 120) AS GroupHour FROM master..spt_values WHERE type = 'P' AND DATEDIFF(HOUR, DATEADD(HOUR, number, '2019-01-18 00:00'), '2019-01-18 23:00') >= 0;

这里先解释最关键的两个函数：DATEADD(part, number, date)是把一个时间点往前推 number 个时间单位，number 为 0 时返回起始时间；DATEDIFF(part, start, end)算的是两个时间之间有多少个完整单位，这个差值决定了序列的长度。CONVERT(NVARCHAR(长度), ..., 120)里的 120 是 ODBC 规范的yyyy-mm-dd hh:mi:ss格式，截取前 10 位得到日期，前 16 位得到yyyy-mm-dd hh:mi，正好用作小时粒度的轴。

小时这段原资料的写法是DATEDIFF(HH, ...) >= 0，实际效果是从起始小时一直生成到结束小时。如果你对边界敏感，也可以改成number <= DATEDIFF(HOUR, @StartDT, @EndDT)，语义更一致，后面业务查询也更好对齐。

2.3 分钟粒度怎么做，以及 2047 上限怎么绕

分钟粒度就是照搬小时写法，把DATEADD和DATEDIFF的单位从HOUR换成MINUTE：

SELECT CONVERT(NVARCHAR(16), DATEADD(MINUTE, number, '2019-01-18 00:00'), 120) AS GroupMinute FROM master..spt_values WHERE type = 'P' AND number <= DATEDIFF(MINUTE, '2019-01-18 00:00', '2019-01-18 23:59');

但这条语句有个明显天花板：分钟跨度超过 2047 分钟，也就是大约 1.42 天，spt_values就造不出连续序列了。小时跨度超 85 天同样会断。我一般遇到跨月、跨年报表，直接放弃 spt_values，改用自建数字表，一劳永逸：

CREATE TABLE dbo.Nums(n INT PRIMARY KEY); INSERT INTO dbo.Nums SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 FROM sys.columns a CROSS JOIN sys.columns b;

数字表只有一列 n，专门存从 0 开始的大整数。后面造时间轴时把它替换掉master..spt_values，就不会再有 2047 的限制，性能反而更稳定。

3. 把时间轴接上业务表：按天、按小时统计交易笔数的完整 SQL

3.1 按天统计：时间轴做主表，业务子查询做左连接

有了连续时间轴，最忌讳的做法是直接拿业务表GROUP BY后再去拼时间轴，因为业务表里缺失的日期根本不会出现在聚合结果里。正确姿势是让时间轴当主表，业务统计结果作为子查询，再LEFT JOIN过去。

下面这段是使用存储过程变量时的标准写法，@paySdate和@payEdate表示统计起止日期：

DECLARE @paySdate DATETIME = '2024-01-01'; DECLARE @payEdate DATETIME = '2024-01-31'; SELECT a.GroupDay , ISNULL(b.e, 0) AS feeCount FROM ( SELECT CONVERT(NVARCHAR(10), DATEADD(DAY, number, @paySdate), 120) AS GroupDay FROM master..spt_values WHERE type = 'P' AND number <= DATEDIFF(DAY, @paySdate, @payEdate) ) a LEFT JOIN ( SELECT CONVERT(NVARCHAR(10), create_time, 120) AS d , COUNT(*) AS e FROM trade_log WHERE create_time >= @paySdate AND create_time < DATEADD(DAY, 1, @payEdate) GROUP BY CONVERT(NVARCHAR(10), create_time, 120) ) b ON b.d = a.GroupDay ORDER BY a.GroupDay;

参数说明：a子查询生成从@paySdate到@payEdate的日期序列，b子查询按天统计交易笔数，LEFT JOIN保证没有交易的日期也能出现在结果里，ISNULL(b.e, 0)把空值补成 0。注意业务子查询的过滤条件写的是create_time < DATEADD(DAY, 1, @payEdate)，不是<= @payEdate。因为@payEdate如果是零点，就漏掉当天一整天；如果是 23:59:59，又可能漏掉那条 23:59:59.999 的记录。用左闭右开区间最稳妥。

3.2 按小时统计：日期和时间两段拼接要统一格式

按小时统计比按天麻烦，因为业务表的create_time是完整 datetime，要把它对齐到整点，同时时间轴也要生成同样的整点字符串。最容易翻车的是两边格式化方式不一致，比如一边用23格式取日期，一边用120格式取小时，结果字符串差个空格或前导零，连接条件永远匹配不上。

DECLARE @paySdate DATETIME = '2024-01-18'; DECLARE @payEdate DATETIME = '2024-01-18'; DECLARE @paySTime VARCHAR(8) = '00:00:00'; DECLARE @payETime VARCHAR(8) = '23:00:00'; SELECT a.GroupHour , ISNULL(b.e, 0) AS feeCount FROM ( SELECT CONVERT(NVARCHAR(16), DATEADD(HOUR, number, CONVERT(NVARCHAR(16), @paySdate, 120)), 120) AS GroupHour FROM master..spt_values WHERE type = 'P' AND DATEDIFF(HOUR, DATEADD(HOUR, number, CONVERT(NVARCHAR(16), @paySdate, 120)), @payEdate) >= 0 ) a LEFT JOIN ( SELECT CONVERT(NVARCHAR(16), DATEADD(HOUR, DATEPART(HOUR, create_time), CONVERT(NVARCHAR(16), create_time, 120)), 120) AS st , COUNT(*) AS e FROM trade_log WHERE create_time >= CONVERT(NVARCHAR(16), @paySdate, 120) AND create_time < DATEADD(DAY, 1, @payEdate) AND CONVERT(VARCHAR(8), create_time, 108) >= @paySTime AND CONVERT(VARCHAR(8), create_time, 108) <= @payETime GROUP BY CONVERT(NVARCHAR(16), DATEADD(HOUR, DATEPART(HOUR, create_time), CONVERT(NVARCHAR(16), create_time, 120)), 120) ) b ON b.st = a.GroupHour ORDER BY a.GroupHour;

这个查询看起来绕，本质做了两件事。轴子查询里，先用CONVERT(NVARCHAR(16), @paySdate, 120)把日期截成2024-01-18 00:00，再按小时推进，得到 24 个整点字符串。业务子查询里，DATEADD(HOUR, DATEPART(HOUR, create_time), ...)是把每条记录的时间部分归零到整点，再把整个 datetime 转成同样 16 位的字符串，两边才能在b.st = a.GroupHour上精确匹配。

这里有个血泪经验：不要把create_time放在GROUP BY的完整时间上，否则每小时会产生多个明细时间行，统计数会按秒散开。必须先用DATEADD把分钟和秒抹掉，再分组。

3.3 聚合结果为空时，ISNULL 补零不是可选项

很多同学写LEFT JOIN时忘了ISNULL，结果发现没数据的时间段显示 NULL，前端图表直接不渲染。这个场景下ISNULL(b.e, 0)必须写全。同理，如果统计的是金额，就写ISNULL(SUM(b.amount), 0)；如果统计的是订单数，就写ISNULL(COUNT(b.id), 0)。

4. MySQL 的 DATE_FORMAT 方案：没有 spt_values 也能按时段聚合

4.1 DATE_FORMAT 占位符：先决定粒度再写查询

MySQL 不像 SQL Server 有master..spt_values，统计连续时间段时更多靠DATE_FORMAT直接格式化时间列。这份资料里给了一张很全的格式化对照表，实际使用频率最高的是下面这几个：

注意%u和%v的差异：%u以周一为一周第一天，%v也是周一，但和%x搭配使用；只有%U和%V才是以周日为一周第一天。周报统计最容易在这两个占位符上出问题，我一般会先跑一次SELECT DATE_FORMAT('2024-01-07', '%Y-%m-%d %U %u')确认当前版本行为，再写进正式脚本。

4.2 按天、按周、按月统计的标准写法

资料里那段经典的三段式统计，直接分组即可：

SELECT DATE_FORMAT(create_time, '%Y%m%d') AS days , COUNT(caseid) AS count FROM tc_case GROUP BY DATE_FORMAT(create_time, '%Y%m%d'); SELECT DATE_FORMAT(create_time, '%Y%u') AS weeks , COUNT(caseid) AS count FROM tc_case GROUP BY DATE_FORMAT(create_time, '%Y%u'); SELECT DATE_FORMAT(create_time, '%Y%m') AS months , COUNT(caseid) AS count FROM tc_case GROUP BY DATE_FORMAT(create_time, '%Y%m');

参数说明：%Y是四位年份，%m是两位月份，%d是两位日期，%u是周数。这三条语句逻辑一致，改一下 format 就能切换粒度。需要提醒的是，在 MySQL 5.7 及以上版本，GROUP BY days这种别名写法在ONLY_FULL_GROUP_BY开启时会直接报错，最保险的做法是GROUP BY后面写完整的DATE_FORMAT表达式，别名只用于显示和排序。

4.3 MySQL 8.0 造连续时间轴：递归 CTE 和数字表

MySQL 没有 spt_values，造连续时间轴要用递归 CTE。MySQL 8.0 支持WITH RECURSIVE，一条语句就能生成一段时间序列：

WITH RECURSIVE seq (n) AS ( SELECT 0 UNION ALL SELECT n + 1 FROM seq WHERE n < DATEDIFF('2024-01-31', '2024-01-01') ) SELECT DATE_FORMAT(DATE_ADD('2024-01-01', INTERVAL n DAY), '%Y-%m-%d') AS day FROM seq;

逻辑说明：递归的第一段SELECT 0是起点，第二段不断n + 1，直到n等于两个日期之间的天数差。外层用DATE_ADD把从 0 开始的天数偏移量加回起始日期，再用DATE_FORMAT输出统一格式。如果统计粒度是小时，把INTERVAL n DAY换成INTERVAL n HOUR，DATEDIFF换成TIMESTAMPDIFF(HOUR, start, end)即可。

需要注意，递归 CTE 默认深度有限制。超过 1000 层时，MySQL 会报recursive query aborted，此时要么调高cte_max_recursion_depth，要么直接建一张数字表。数字表方案和 SQL Server 类似，一张只有从 0 开始的连续整数的表，可以跨版本复用，我反而更推荐生产环境用这个。

5. 避坑与排查：连续时间段统计最容易翻车的五个细节

5.1 统计结果少了没数据的那几天

现象：按天统计只返回有交易的日子，某天没有订单，折线图断掉。

原因：直接对业务表GROUP BY日期，或者用了INNER JOIN连接时间轴和业务表，缺失日期被过滤掉了。

解决：先造出完整时间轴，再让业务统计子查询LEFT JOIN到时间轴上，最后ISNULL补 0。这条必须刻在脑子里，时间轴永远是主表。

5.2 小时边界对不齐，最后一段小时统计落空

现象：按小时统计后，23:00 之后的数据要么没有，要么被并进第二天 00:00，小时图最右侧异常。

原因：时间轴生成时用的是DATEDIFF(HH, ...) >= 0，如果结束时间是23:59:59，会产生多出的一段；而业务子查询把create_time转成 23 位字符串截前 10 位，和轴上的 16 位整点字符串根本对不上。

解决：时间轴和业务子查询统一用CONVERT(NVARCHAR(16), ..., 120)截取到分钟，小时段过滤用>= '2024-01-18 08:00:00' AND < '2024-01-18 09:00:00'，左闭右开。

5.3 master..spt_values 只有 2047 条，长周期统计静默截断

现象：按小时统计 90 天以上数据，结果只出 2047 个小时，后面全没有。

原因：spt_values的type='P'序列上限就是 2047，dateadd 偏移超过这个值后没有数据，也不会报错，属于静默丢失。

解决：跨长周期时改用自建数字表dbo.Nums，或者递归 CTE。数字表只存连续整数，不依赖系统表，语义清楚，排查也方便。

5.4 过滤条件对时间字段用了函数，查询走不了索引

现象：数据量到百万级后，同样一条统计 SQL 越跑越慢，执行计划出现全表扫描。

原因：WHERE CONVERT(CHAR(10), create_time, 120) = @paySdate这类写法让索引列参与函数计算，SQL Server 和 MySQL 都很难再用到索引。

解决：把过滤条件改成范围条件create_time >= @paySdate AND create_time < DATEADD(DAY, 1, @payEdate)，让查询能用上 create_time 上的索引。天数人为偏移一格没关系，统计结果不会变。

5.5 MySQL 里 GROUP BY 别名报错或结果不准

现象：MySQL 5.7 开启ONLY_FULL_GROUP_BY后，GROUP BY days直接报错；有些老版本不报错，但语义含糊。

原因：SQL 标准要求在分组时使用表达式本身，而不是 select 列表里的别名。

解决：GROUP BY写完整DATE_FORMAT(create_time, '%Y%m%d')，别名days只放在ORDER BY里用。真要在分组里复用别名，可以用子查询先格式化再分组，但性能上没优势。

6. 进阶用法：把统计封装成存储过程，并用计数脚本验证时间轴没有缺口

6.1 做一个接受开始时间、结束时间、粒度的统计过程

上面的查询都是即写即用，但同一套逻辑如果每周跑一次，或者要给不同项目复用，就应该封成存储过程。下面这个是我常用的模板，传入起止时间和粒度，输出对应的时间轴：

CREATE PROCEDURE usp_TimeLine @StartDT DATETIME, @EndDT DATETIME, @Granularity VARCHAR(10) -- DAY / HOUR / MINUTE AS BEGIN SET NOCOUNT ON; IF @Granularity = 'DAY' BEGIN SELECT CONVERT(NVARCHAR(10), DATEADD(DAY, number, CONVERT(NVARCHAR(10), @StartDT, 120)), 120) AS TimeLine FROM master..spt_values WHERE type = 'P' AND number <= DATEDIFF(DAY, @StartDT, @EndDT); END ELSE IF @Granularity = 'HOUR' BEGIN SELECT CONVERT(NVARCHAR(16), DATEADD(HOUR, number, CONVERT(NVARCHAR(16), @StartDT, 120)), 120) AS TimeLine FROM master..spt_values WHERE type = 'P' AND number <= DATEDIFF(HOUR, @StartDT, @EndDT); END ELSE IF @Granularity = 'MINUTE' BEGIN SELECT CONVERT(NVARCHAR(16), DATEADD(MINUTE, number, CONVERT(NVARCHAR(16), @StartDT, 120)), 120) AS TimeLine FROM master..spt_values WHERE type = 'P' AND number <= DATEDIFF(MINUTE, @StartDT, @EndDT); END END

逻辑说明：存储过程把粒度判断放到外层，避免业务 SQL 里到处复制DATEADD片段。调用时直接EXEC usp_TimeLine '2024-01-01', '2024-01-18', 'DAY'拿到时间轴，再和你自己的统计子查询做LEFT JOIN。如果跨度超过 2047 个时间点，把内部master..spt_values换成自建数字表dbo.Nums，其余不用动。

6.2 每次上线前先验证时间轴没有缺口

光有过程还不够，统计结果能不能直接上图，取决于时间轴是不是连续。我现在的习惯是不管报表多急，第一个版本跑完，一定先跑一条核对脚本：

DECLARE @StartDT DATETIME = '2024-01-01'; DECLARE @EndDT DATETIME = '2024-01-18'; DECLARE @Granularity VARCHAR(10) = 'DAY'; DECLARE @ExpectCount INT = DATEDIFF(DAY, @StartDT, @EndDT) + 1; DECLARE @ActualCount INT; IF @Granularity = 'DAY' SET @ActualCount = ( SELECT COUNT(*) FROM master..spt_values WHERE type = 'P' AND number <= DATEDIFF(DAY, @StartDT, @EndDT) ); SELECT CASE WHEN @ActualCount = @ExpectCount THEN 'OK' ELSE 'HAS GAP' END AS AxisCheck;

这段脚本把预期时间点数和实际生成的时间点数做对比，两边一致说明时间轴完整，不一致说明要么使用了 spt_values 超过 2047，要么结束时间计算有偏差。注意@ExpectCount里的+1，因为时间轴包含起始点和结束点，DATEDIFF只算两者之间的间隔数。

从那以后，我每次接到按时间段统计的需求，都会强行走一遍这个流程：先确认跨度是否在 2047 以内，再生成时间轴，然后验证时间轴点数，最后才去接业务表。这样虽然多花五到十分钟，但能省掉报表上线后被业务方追着问“为什么这里断了一截”的麻烦。希望这篇整理对你也有同样的帮助。

本文还有配套的精品资源，点击获取