sqlserver/mysql按天，按小時，按分鐘統計連續時間段數據

-Advertisement-

文 | 子龍有技術，有乾貨，有故事的斜杠青年一，寫在前面的話最近公司需要按天，按小時查看數據，可以直觀的看到時間段的數據峰值。接到需求，就開始瘋狂百度搜索，但是搜索到的資料有很多都不清楚，需要自己去總結和挖掘其中的重要信息。現在我把分享出來了呢，希望大家喜歡。針對sqlserver, 有幾點 ...

文 | 子龍有技術，有乾貨，有故事的斜杠青年

一，寫在前面的話

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

針對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的實現方式，留下聯繫方式，小弟也虛心請教學習一下。

寫得不好，望大家體諒。

您的分享是我們最大的動力!