本文目錄列表：1、準備測試數據2、向測試數據表添加相關時間粒度欄位列3、基於日月季年統計彙總的演示4、總結語5、參考清單列表準備測試數據為了提供不同時間粒度示例的演示，就需要測試數據。為了演示方便，本文提供一個測試數據表（登錄信息數據表----LoginInfo），以及改變插入測試數據。該測試數據表...
本文目錄列表： 1、準備測試數據 2、向測試數據表添加相關時間粒度欄位列 3、基於日月季年統計彙總的演示 4、總結語 5、參考清單列表 準備測試數據 為了提供不同時間粒度示例的演示，就需要測試數據。為了演示方便，本文提供一個測試數據表（登錄信息數據表----LoginInfo），以及改變插入測試數據。該測試數據表就是簡單記錄每個用戶每次的登路時間信息。 LoginInfo創建的腳本的T-SQL代碼如下： IF OBJECT_ID(N'dbo.LoginInfo', 'U') IS NOT NULL BEGIN DROP TABLE dbo.LoginInfo; END GO CREATE TABLE dbo.LoginInfo ( LoginInfoID INT IDENTITY(1, 1) NOT NULL, UserID INT NOT NULL, LoginTime DATETIME NOT NULL ); GO IF OBJECT_ID(N'PK_U_CL_LoginInfo_LoginInfoID', N'PK') IS NULL BEGIN ALTER TABLE [dbo].[LoginInfo] ADD CONSTRAINT [PK_U_CL_LoginInfo_LoginInfoID] PRIMARY KEY CLUSTERED ( [LoginInfoID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]; END GO IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[LoginInfo]', N'U') AND name = N'IX_U_NCL_LoginInfo_LoginTime_UserID') BEGIN CREATE NONCLUSTERED INDEX [IX_U_NCL_LoginInfo_LoginTime_UserID] ON [dbo].[LoginInfo] ( [LoginTime] ASC, [UserID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]; END GO IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[LoginInfo]', N'U') AND name = N'IX_NU_NCL_LoginInfo_UserID') BEGIN CREATE NONCLUSTERED INDEX [IX_NU_NCL_LoginInfo_UserID] ON [dbo].[LoginInfo] ( [UserID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]; END GO 向LoginInfo數據表插入測試數據的T-SQL腳本如下： -- 方法1、 模擬100個用戶在2015年登陸時間的信息記錄 TRUNCATE TABLE dbo.LoginInfo; GO DECLARE @intUserTotal AS INT, @dtmStartDateTime AS DATETIME, @dtmEndDateTime AS DATETIME; SELECT @intUserTotal = 100, @dtmStartDateTime = '2015-01-01', @dtmEndDateTime = '2015-12-31'; -- 插入數據 INSERT INTO dbo.LoginInfo ( UserID ,LoginTime ) SELECT T.Num AS UserID ,T2.LoginTime FROM dbo.ufn_GetNums(1, @intUserTotal) AS T CROSS APPLY ( SELECT CONVERT(DATETIME, CONVERT(VARCHAR(14), DATEADD(HOUR, Num * 4, @dtmStartDateTime), 120) + CAST(dbo.ufn_RandNum(0,59) AS VARCHAR(2)) + ':'+ CAST(dbo.ufn_RandNum(0,59) AS VARCHAR(2)) + '.'+ CAST(dbo.ufn_RandNum(0,997) AS VARCHAR(3)), 120) AS LoginTime FROM dbo.ufn_GetNums(0, DATEDIFF(HOUR, @dtmStartDateTime, @dtmEndDateTime) / 4) ) AS T2 ORDER BY T2.LoginTime ASC, T.Num ASC; GO -- 方法2、 模擬1000個用戶在2015年登陸時間的信息記錄 TRUNCATE TABLE dbo.LoginInfo; GO DECLARE @intUserTotal AS INT, @dtmStartDateTime AS DATETIME, @dtmEndDateTime AS DATETIME; SELECT @intUserTotal = 1000, @dtmStartDateTime = '2015-01-01', @dtmEndDateTime = '2015-12-31'; SELECT T.Num AS UserID ,T2.LoginTime FROM dbo.ufn_GetNums(1, @intUserTotal) AS T CROSS APPLY ( SELECT CONVERT(DATETIME, CONVERT(VARCHAR(14), DATEADD(HOUR, Num * 4, @dtmStartDateTime), 120) + CAST(dbo.ufn_RandNum(0,59) AS VARCHAR(2)) + ':'+ CAST(dbo.ufn_RandNum(0,59) AS VARCHAR(2)) + '.'+ CAST(dbo.ufn_RandNum(0,997) AS VARCHAR(3)), 120) AS LoginTime FROM dbo.ufn_GetNums(0, DATEDIFF(HOUR, @dtmStartDateTime, @dtmEndDateTime) / 4) ) AS T2; GO 註意： 1、以上填充測試數據提供了兩個方法：一個是模擬100個用戶的小數據，另一個是模擬1000個用戶的稍大數據，時間段都是2015年的登錄時間。 2、本文為了演示的方便採用了模擬100個用戶的小數據。 3、填充測試數據使用了函數ufn_GetNums，請參考SQL Server數字輔助表的實現。 查看測試數據表中的數據，如下圖：
註意：
1、以上截圖僅僅顯示很小部分的數據。
向測試數據表添加相關時間粒度欄位列
向測試數據表中增加LoginDays、LoginMonths、LoginQuarters和LoginYears欄位列，T-SQL腳本如下：
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.LoginInfo', 'U') AND name = N'LoginDays')
BEGIN
ALTER TABLE LoginInfo ADD LoginDays INT NOT NULL CONSTRAINT DF_LoginInfo_LoginDays DEFAULT 0;
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.LoginInfo', 'U') AND name = N'LoginMonths')
BEGIN
ALTER TABLE LoginInfo ADD LoginMonths INT NOT NULL CONSTRAINT DF_LoginInfo_LoginMonths DEFAULT 0;
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.LoginInfo', 'U') AND name = N'LoginQuarters')
BEGIN
ALTER TABLE LoginInfo ADD LoginQuarters INT NOT NULL CONSTRAINT DF_LoginInfo_LoginQuarters DEFAULT 0;
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.LoginInfo', 'U') AND name = N'LoginYears')
BEGIN
ALTER TABLE LoginInfo ADD LoginYears SMALLINT NOT NULL CONSTRAINT DF_LoginInfo_LoginYears DEFAULT 0;
END
GO
查詢測試數據表，如下圖：
註意：
1、以上截圖的僅僅顯示部分數據。
修改新增欄位值，相關的腳本如下：
UPDATE dbo.LoginInfo
SET LoginDays = dbo.ufn_Days(LoginTime)
,LoginMonths = dbo.ufn_Months(LoginTime)
,LoginQuarters = dbo.ufn_Quarters(LoginTime)
,LoginYears = dbo.ufn_Years(LoginTime)
WHERE LoginDays = 0
AND LoginMonths = 0
AND LoginQuarters = 0
AND LoginYears = 0;
GO
註意：
1、以上新增的欄位沒有創建相應的索引。
2、以上使用了4個函數：ufn_Days、ufn_Months、ufn_Quarters和ufn_Years，請參考SQL Server時間粒度系列----第7節日曆數據表詳解。
再次查看測試數據，如下圖：
註意：
1、以上截圖僅僅顯示部分數據。
基於日月季年統計彙總的演示
基於日統計彙總，T-SQL如下：
-- 基於日統計彙總
-- 方法1、傳統的使用
SELECT CONVERT(CHAR(10), LoginTime, 120) AS LoginDayDateFormat, COUNT(1) AS DayLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY CONVERT(CHAR(10), LoginTime, 120)
ORDER BY LoginDayDateFormat ASC;
GO
-- 方法2、使用時間粒度轉換函數
SELECT dbo.ufn_Days2Date(dbo.ufn_Days(LoginTime)) AS LoginDayDate, COUNT(1) AS DayLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY dbo.ufn_Days(LoginTime)
ORDER BY LoginDayDate ASC;
GO
-- 方法3、使用時間粒度欄位列和時間粒度轉換函數
SELECT dbo.ufn_Days2Date(LoginDays) AS LoginDayDate, COUNT(1) AS DayLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY LoginDays
ORDER BY LoginDays ASC;
GO
-- 方法4、嵌套查詢與使用時間粒度欄位列和時間粒度轉換函數
SELECT dbo.ufn_Days2Date(T.LoginDays) AS LoginDayDate, T.DayLoginTimesTotal
FROM (
SELECT LoginDays, COUNT(1) AS DayLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY LoginDays
) AS T
ORDER BY LoginDays ASC;
GO
查詢以上四個方法的圖形實際執行計劃，如下圖：
基於月統計彙總，T-SQL如下:
-- 基於月統計彙總
-- 方法1、傳統的使用
SELECT CONVERT(CHAR(7), LoginTime, 120) AS LoginMonthDateFormat, COUNT(1) AS MonthLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY CONVERT(CHAR(7), LoginTime, 120)
ORDER BY LoginMonthDateFormat ASC;
GO
-- 方法2、使用時間粒度轉換函數
SELECT dbo.ufn_Months2Date(dbo.ufn_Months(LoginTime)) AS LoginMonthBasedate, COUNT(1) AS MonthLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY dbo.ufn_Months(LoginTime)
ORDER BY LoginMonthBasedate ASC;
GO
-- 方法3、使用時間粒度欄位列和時間粒度轉換函數
SELECT dbo.ufn_Months2Date(LoginMonths) AS LoginMonthBasedate, COUNT(1) AS MonthLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY LoginMonths
ORDER BY LoginMonths ASC;
GO
-- 方法4、嵌套查詢與使用時間粒度欄位列和時間粒度轉換函數
SELECT dbo.ufn_Months2Date(T.LoginMonths) AS LoginMonthBasedate, T.MonthLoginTimesTotal
FROM (
SELECT LoginMonths, COUNT(1) AS MonthLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY LoginMonths
) AS T
ORDER BY LoginMonths ASC;
GO
查詢以上四個方法的圖形實際執行計劃，如下圖：
基於季統計彙總，T-SQL如下：
-- 基於季統計彙總
-- 方法1、傳統的使用
SELECT CONVERT(CHAR(4), LoginTime, 120) + '0' + CAST(DATEPART(QUARTER, LoginTime) AS CHAR(1)) AS LoginQuarterDateFormat, COUNT(1) AS QuarterLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY CONVERT(CHAR(4), LoginTime, 120) + '0' + CAST(DATEPART(QUARTER, LoginTime) AS CHAR(1))
ORDER BY LoginQuarterDateFormat ASC;
GO
-- 方法2、使用時間粒度轉換函數
SELECT dbo.ufn_Quarters2Date(dbo.ufn_Quarters(LoginTime)) AS LoginQuarterBasedate, COUNT(1) AS QuarterLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY dbo.ufn_Quarters(LoginTime)
ORDER BY LoginQuarterBasedate ASC;
GO
-- 方法3、使用時間粒度欄位列和時間粒度轉換函數
SELECT dbo.ufn_Quarters2Date(LoginQuarters) AS LoginQuarterBasedate, COUNT(1) AS QuarterLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY LoginQuarters
ORDER BY LoginQuarters ASC;
GO
-- 方法4、嵌套查詢與使用時間粒度欄位列和時間粒度轉換函數
SELECT dbo.ufn_Quarters2Date(T.LoginQuarters) AS LoginQuarterBasedate, T.QuarterLoginTimesTotal
FROM (
SELECT LoginQuarters, COUNT(1) AS QuarterLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY LoginQuarters
) AS T
ORDER BY LoginQuarters ASC;
GO
查詢以上四個方法的圖形實際執行計劃，如下圖：
基於年統計彙總，T-SQL如下：
-- 基於年統計彙總
-- 方法1、傳統的使用
SELECT CONVERT(CHAR(4), LoginTime, 120) AS LoginYearDateFormat, COUNT(1) AS YearLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY CONVERT(CHAR(4), LoginTime, 120)
ORDER BY LoginYearDateFormat ASC;
GO
-- 方法2、使用時間粒度轉換函數
SELECT dbo.ufn_Years2Date(dbo.ufn_Years(LoginTime)) AS LoginYearBasedate, COUNT(1) AS YearLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY dbo.ufn_Years(LoginTime)
ORDER BY LoginYearBasedate ASC;
GO
-- 方法3、使用時間粒度欄位列和時間粒度轉換函數
SELECT dbo.ufn_Years2Date(LoginYears) AS LoginYearBasedate, COUNT(1) AS YearLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY LoginYears
ORDER BY LoginYears ASC;
GO
-- 方法4、嵌套查詢與使用時間粒度欄位列和時間粒度轉換函數
SELECT dbo.ufn_Years2Date(T.LoginYears) AS LoginYearBasedate, T.YearLoginTimesTotal
FROM (
SELECT LoginYears, COUNT(1) AS YearLoginTimesTotal
FROM dbo.LoginInfo
GROUP BY LoginYears
) AS T
ORDER BY LoginYears ASC;
GO
查詢以上四個方法的圖形實際執行計劃，如下圖：
註意：
1、以上演示的T-SQL代碼使用了ufn_Days2Date、ufn_Months2Date、ufn_Quarters2Date、ufn_Years2Date，請參考SQL Server時間粒度系列----第7節日曆數據表詳解。
總結語
本文僅僅提供了測試數據表的創建以及相關的數據填充，向測試表中增加時間粒度相關的欄位列，使用時間粒度相關函數簡單了基於日月季年統計彙總的演示。
參考清單列表
1、SQL Server數字輔助表的實現。2、SQL Server時間粒度系列----第7節日曆數據表詳解。
