sqlserver/mysql如何按天、小时、分钟统计连续时间段数据？

首先是我之前写的

最近公司需要按天按小时查看数据，可以直观的看到期间的数据峰值时间段。接到需求后，我开始在百度上疯狂搜索，但查到的很多信息都不清楚，需要自己总结和挖掘出重要的信息。

对于sqlserver，有几件事需要给大家解释清楚（不懂的自行百度）：

•什么是master。 .spt_值？它可以用来做什么？
•如何生成连续的时间段（年、月、日、小时、分钟）

2. master..spt_values是什么？它可以用来做什么？

它是一个相对固定且通用的取数表。它的主要功能是取连续的数字。不过有一个缺陷就是只能取2047。你可以执行下面的语句就知道什么意思了。

select number from master..spt_values where type='p'

三、如何生成连续时间周期（年、月、日、时、分）

实际应用中，目前主要生成连续时间段。常用的操作我都准备好了，下面分别展示。

-- 按年份子字符串生成连续 SELECT(CONVERT(NVARCHAR(10), DateAdd(YEAR, number, '2016-01-01') , 120),1,4) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND number <= DateDiff(YEAR, '2016-01-01','2023-01-01') --生成连续的 SELECT 子字符串(CONVERT(NVARCHAR(10), DateAdd(MONTH, number, '2023-01-01'),120),1,7) AS GroupDay, type FROM master..spt_values WHERE type = 'p' AND number <= DateDiff(MONTH, '2023-01-01', '2023-01-01') -- 生成连续 SELECT CONVERT(NVARCHAR(10) ) 按天 , DateAdd(day, number, '2023-01-01'),120) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND number <= DateDiff(day, '2023-01 -01', ' 2023-01-18') -- 按小时生成连续 SELECT substring(convert(char(32),DATEADD(HH,number,CONCAT('2023-01-18',' ', '00: 00')) ,120),1,16) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND DATEDIFF(HH,DATEADD(HH,number,CONCAT('2023-01-18', ' ', '00:00')),CONCAT('2023-01-18',' ', '23:00 '))>=0--分分钟自己搞定...
四、与业务场景结合
有了连续的数据之后，当然我们会根据时间进行左连接。您可以查看统计数据。 
现在说一下我使用的两个统计案例（使用存储过程实现，所以带@符号的都是variables）适合所有人。至于你能不能理解，就看吧。由你决定。 
-- 按天统计交易数量 select a.GroupDay, ISNULL(b.e, 0) 'feeCount' from ( SELECT CONVERT(NVARCHAR(10) ) , DateAdd(day, number, @paySdate),120) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND number <= DateDiff(day, @paySdate, @payEdate) ) 左连接 (从trade_log中选择convert(char(32),create_time,23) as d, count(*) as e from trade_log where create_time >= @paySdate and create_time<=@payEdate group by Convert(char(32),create_time,23)) b on b.d=a.GroupDay--按小时统计交易数量 select a.GroupDay, ISNULL(b.e,0) 'feeCount' from ( SELECT substring(convert(char(32),DATEADD(HH,number,CONCAT(@ paySdate,' ', @paySTime)),120),1,16) AS GroupDay,type FROM master..spt_values WHERE type = 'p' AND DATEDIFF(HH,DATEADD(HH,number,CONCAT(@paySdate, ' ' , @paySTime)),CONCAT(@payEdate,' ', @payETime))>=0 ) 左连接 ( se选择convert(char(32),create_time,23) as d, datepart(hh,create_time) as h, substring(convert(char(32),DATEADD (HH,datepart(hh,create_time)),convert(char(32) ,create_time,23)),120),1,16) as st, count(*) as e from trade_log 其中 create_time >= @paySdate 和 create_time< =@payEdate 并转换(char(8),create_time,108)> =@paySTime 和 Convert(char(8),create_time,108)<=@payETime group by Convert(char(32),create_time,23),datepart (hh,create_time)) b on b.st=a.GroupDay order by GroupDay
V.总结与展望
掌握的知识点：
•熟悉存储过程的语法和编写过程
•学习掌握..什么是spt_values？又可以用在哪些场景呢？ 
•实现基于时间的统计方法，如按天、小时等统计。 
Outlook：
•局限性：该方法目前仅针对sqlserver，但目前大部分都是mysql。 
ps：MySQL按日、周、月、日统计nd时间段
我做过MySQL按天、按周、按月、按时间段的统计，但不是很满意。后来我找到了这位大师的博客并转载了。感谢这位博主的分享
知识点：DATE_FORMAT 
使用示例
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) 从 tc_case 分组的天数； select DATE_FORMAT(create_time,'%Y%u') 周,count(caseid) 按周从 tc_case 分组计数； select DATE_FORMAT(create_time,'%Y%m')months,count(caseid) count from tc_case group bymonths;
DATE_FORMAT(日期,格式)
< p>根据格式字符串格式化日期值。格式字符串中可以使用以下修饰符：
•%M 月份名称（January......December）
•%W 星期几
名称星期（星期日...星期六）
•%D 月份中的第几天，带英文前缀（第一、第二、第三等）
• %Y 年份、数字、4 位
 •%yy耳朵，数字，2 位
•%a 周的缩写名称（Sun...Sat）
•%d 该月的天数，数字（00……31）
 •%e 月份天数，数字 (0……31)
•%m 月份，数字 (01……12)
•%c 月份，数字 (1...12)< br/>•%b 月份名称缩写（Jan...Dec）
•%j 一年中的天数 (001...366)
•%H 小时 (00...23)< br/>•%k 小时 (0…23)
•%h 小时 (01…12)
•%I 小时 (01…12)
•%l 小时 (1.. .12)
•%i 分钟，数字 (00...59)
•%r 时间，12 小时 (hh:mm:ss [AP]M )
•%T 时间, 24 小时 (hh:mm:ss)
•%S 秒 (00……59)
•%s 秒 (00……59)
•%p AM 或 PM
 />•%w 一周中的天数 (0=星期日...6=星期六)
•%U 一周中的某一天 (0...52)，其中星期日是星期几week Day
•%u 周 (0...52)，其中星期一是一周的第一天
•%% 文字“%”。 
这篇关于《sqlserver/mysql如何按天、小时、分钟统计连续时间段数据》的文章就分享到这里。希望阿宝这些内容可能很有用。对大家有帮助，让你学到更多的知识。如果您觉得文章不错，请转发出去，让更多的人看到。