sqlserver/mysql按天、按小時(shí)、按分鐘統(tǒng)計(jì)連續(xù)時(shí)間段數(shù)據(jù)【推薦】
一，寫在前面的話
最近公司需要按天，按小時(shí)查看數(shù)據(jù)，可以直觀的看到時(shí)間段的數(shù)據(jù)峰值。接到需求，就開始瘋狂百度搜索，但是搜索到的資料有很多都不清楚，需要自己去總結(jié)和挖掘其中的重要信息。現(xiàn)在我把分享出來了呢，希望大家喜歡。
針對(duì)sqlserver, 有幾點(diǎn)需要給大家說清楚（不懂的自行百度）：
•master..spt_values 是什么東西？能用來做什么？
•如何產(chǎn)生連續(xù)的時(shí)間段（年， 月， 天，小時(shí)，分鐘）
二，master..spt_values是什么東西？能用來做什么呢？
相對(duì)固定通用的取數(shù)字的表，主要作用就是取連續(xù)數(shù)字，不過有個(gè)缺陷就是只能取到2047?？梢詧?zhí)行下面語句就知道什么意思了。
select number from master..spt_values where type='p'
三，如何產(chǎn)生連續(xù)的時(shí)間段（年， 月， 天，小時(shí)，分鐘）
在實(shí)際的運(yùn)用中，目前主要是產(chǎn)生連續(xù)的時(shí)間段。我準(zhǔn)備了常用的操作，那下面的語句就分別展示出來。
-- 按年產(chǎn)生連續(xù)的
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')
-- 按月產(chǎn)生連續(xù)的
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')
-- 按天產(chǎn)生連續(xù)的
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')
-- 按小時(shí)產(chǎn)生連續(xù)的
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了
......
四，與業(yè)務(wù)場(chǎng)景進(jìn)行結(jié)合
有了連續(xù)的數(shù)據(jù)過后，當(dāng)然就是以時(shí)間為主，進(jìn)行左連接。就可以查出統(tǒng)計(jì)數(shù)據(jù)了。
下面我就說說我使用的兩個(gè)統(tǒng)計(jì)案例（是采用存儲(chǔ)過程來實(shí)現(xiàn)了，所以有@符號(hào)的是變量），給到大家，至于看不看得懂，就看你的能力了。
-- 按天統(tǒng)計(jì)交易筆數(shù)
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
-- 按小時(shí)統(tǒng)計(jì)交易筆數(shù)
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
五，總結(jié)及展望
掌握的知識(shí)點(diǎn)：
•熟悉了存儲(chǔ)過程的語法和編寫過程
•學(xué)習(xí)到了master..spt_values是什么？以及可以使用的場(chǎng)景？
•針對(duì)按時(shí)間進(jìn)行統(tǒng)計(jì)，比如按天，小時(shí)進(jìn)行統(tǒng)計(jì)的實(shí)現(xiàn)方法。
展望:
•局限性：這種方式目前只針對(duì)sqlserver, 但是目前大部分都是mysql。
ps：MySQL按天，按周，按月，按時(shí)間段統(tǒng)計(jì)
自己做過MySQL按天，按周，按月，按時(shí)間段統(tǒng)計(jì)，但是不怎么滿意，后來找到這位大神的博客，轉(zhuǎn)載一下，謝謝這位博主的分享
知識(shí)點(diǎn)：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)
根據(jù)format字符串格式化date值。下列修飾符可以被用在format字符串中：
•%M 月名字(January……December)
•%W 星
期名字(Sunday……Saturday)
•%D 有英語前綴的月份的日期(1st, 2nd, 3rd, 等等。）
•%Y 年, 數(shù)字, 4 位
•%y 年, 數(shù)字, 2 位
•%a 縮寫的星期名字(Sun……Sat)
•%d 月份中的天數(shù), 數(shù)字(00……31)
•%e 月份中的天數(shù), 數(shù)字(0……31)
•%m 月, 數(shù)字(01……12)
•%c 月, 數(shù)字(1……12)
•%b 縮寫的月份名字(Jan……Dec)
•%j 一年中的天數(shù)(001……366)
•%H 小時(shí)(00……23)
•%k 小時(shí)(0……23)
•%h 小時(shí)(01……12)
•%I 小時(shí)(01……12)
•%l 小時(shí)(1……12)
•%i 分鐘, 數(shù)字(00……59)
•%r 時(shí)間,12 小時(shí)(hh:mm:ss [AP]M)
•%T 時(shí)間,24 小時(shí)(hh:mm:ss)
•%S 秒(00……59)
•%s 秒(00……59)
•%p AM或PM
•%w 一個(gè)星期中的天數(shù)(0=Sunday ……6=Saturday ）
•%U 星期(0……52), 這里星期天是星期的第一天
•%u 星期(0……52), 這里星期一是星期的第一天
•%% 一個(gè)文字“%”。
總結(jié)
以上所述是小編給大家介紹的sqlserver/mysql按天、按小時(shí)、按分鐘統(tǒng)計(jì)連續(xù)時(shí)間段數(shù)據(jù),希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)本站網(wǎng)站的支持！
如果你覺得本文對(duì)你有幫助，歡迎轉(zhuǎn)載，煩請(qǐng)注明出處，謝謝！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信