當前位置:源碼庫-建站教程-數據庫教程-MSSQL教程-sqlserver/mysql按天、按小時、按分鍾統計連續時間段數據【推薦】

sqlserver/mysql按天、按小時、按分鍾統計連續時間段數據【推薦】

時間:2019-09-17來源/作者:方子龍 編輯:源碼庫 文章熱度:0

一,寫在前麵的話

最近公司需要按天,按小時查看數據,可以直觀的看到時間段的數據峰值。接到需求,就開始瘋狂百度搜索,但是搜索到的資料有很多都不清楚,需要自己去總結和挖掘其中的重要信息。現在我把分享出來了呢,希望大家喜歡。

針對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按天、按小時、按分鍾統計連續時間段數據,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回複大家的。在此也非常感謝大家對ASPKU源碼庫網站的支持!
如果你覺得本文對你有幫助,歡迎轉載,煩請注明出處,謝謝!


注:相關教程知識閱讀請移步到MSSQL教程頻道。
相關MSSQL教程
熱門標簽

MSSQL教程Rss訂閱MSSQL教程搜索