您的位置:首页 > 数据库 > > 正文

mysqlsql按日期统计(sqlserver/mysql按天、按小时、按分钟统计连续时间段数据推荐)

更多 时间:2021-10-18 11:34:34 类别:数据库 浏览量:1818

mysqlsql按日期统计

sqlserver/mysql按天、按小时、按分钟统计连续时间段数据推荐

一,写在前面的话

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

针对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。

    ps:MySQL按天,按周,按月,按时间段统计

    自己做过MySQL按天,按周,按月,按时间段统计,但是不怎么满意,后来找到这位大神的博客,转载一下,谢谢这位博主的分享

    知识点:DATE_FORMAT

    使用示例

  • select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;
     select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;
     select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;
    
  • DATE_FORMAT(date,format)

    根据format字符串格式化date值。下列修饰符可以被用在format字符串中:

    •%M 月名字(January……December)
    •%W 星
    期名字(Sunday……Saturday)
    •%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
    •%Y 年, 数字, 4 位
    •%y 年, 数字, 2 位
    •%a 缩写的星期名字(Sun……Sat)
    •%d 月份中的天数, 数字(00……31)
    •%e 月份中的天数, 数字(0……31)
    •%m 月, 数字(01……12)
    •%c 月, 数字(1……12)
    •%b 缩写的月份名字(Jan……Dec)
    •%j 一年中的天数(001……366)
    •%H 小时(00……23)
    •%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=Sunday ……6=Saturday )
    •%U 星期(0……52), 这里星期天是星期的第一天
    •%u 星期(0……52), 这里星期一是星期的第一天
    •%% 一个文字“%”。

    总结

    以上所述是小编给大家介绍的sqlserver/mysql按天、按小时、按分钟统计连续时间段数据,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对开心学习网网站的支持!
    如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!

    您可能感兴趣