分 享

【凤凰社】sqlserver/mysql按天,按小时,按分钟统计连续时间段数据

image.png


文 | 子龙 有技术,有干货,有故事的斜杠青年


一,写在前面的话


最近公司需要按天,按小时查看数据,可以直观的看到时间段的数据峰值。接到需求,就开始疯狂百度搜索,但是搜索到的资料有很多都不清楚,需要自己去总结和挖掘其中的重要信息。现在我把分享出来了呢,希望大家喜欢。


针对sqlserver, 有几点需要给大家说清楚(不懂的自行百度):


  • master..spt_values 是什么东西?能用来做什么?
  • 如何产生连续的时间段(年, 月, 天,小时,分钟)

二,master..spt_values是什么东西?能用来做什么呢?


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


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

三,如何产生连续的时间段(年, 月, 天,小时,分钟)


在实际的运用中,目前主要是产生连续的时间段。我准备了常用的操作,那下面的语句就分别展示出来。


-- 按年产生连续的
SELECT 
  substring(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', '2019-01-01')  

-- 按月产生连续的
SELECT 
  substring(CONVERT(NVARCHAR(10), DateAdd(MONTH, number, '2019-01-01'),120),1,7) AS GroupDay,type  
FROM 
  master..spt_values  
WHERE type = 'p' AND number <= DateDiff(MONTH, '2018-01-01', '2019-01-01')  

-- 按天产生连续的
SELECT 
  CONVERT(NVARCHAR(10), DateAdd(day, number, '2019-01-01'),120) AS GroupDay,type  
FROM 
  master..spt_values  
WHERE type = 'p' AND number <= DateDiff(day, '2019-01-01', '2019-01-18')  

-- 按小时产生连续的
SELECT 
  substring(convert(char(32),DATEADD(HH,number,CONCAT('2019-01-18',' ', '00:00')),120),1,16) AS GroupDay,type  
FROM
  master..spt_values  
WHERE type = 'p' AND DATEDIFF(HH,DATEADD(HH,number,CONCAT('2019-01-18',' ', '00:00')),CONCAT('2019-01-18',' ', '23:00'))>=0

-- 按分钟的就自己可以YY了
......

四,与业务场景进行结合


有了连续的数据过后,当然就是以时间为主,进行左连接。就可以查出统计数据了。


下面我就说说我使用的两个统计案例(是采用存储过程来实现了,所以有@符号的是变量),给到大家,至于看不看得懂,就看你的能力了。


-- 按天统计交易笔数
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)  
            ) a 
            left join 
                (select 
                    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  
                ) a 
              left join (
               select 
                  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 
              where create_time >= @paySdate and create_time<=@payEdate 
                  and convert(char(8),create_time,108)>=@paySTime and 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

五,总结及展望


掌握的知识点:


  • 熟悉了存储过程的语法和编写过程
  • 学习到了master..spt_values是什么?以及可以使用的场景?
  • 针对按时间进行统计,比如按天,小时进行统计的实现方法。

展望:


  • 局限性:这种方式目前只针对sqlserver, 但是目前大部分都是mysql。

希望大神如果有mysql的实现方式,留下联系方式,小弟也虚心请教学习一下。


写得不好,望大家体谅。


, '\n'

原文地址:https://www.cnblogs.com/werewolfBoy/p/11076072.html

0 评论

回复