SQL Server时间粒度系列----第9节时间粒度示例演示
为了提供不同时间粒度示例的演示，就需要测试数据。为了演示方便，本文提供一个测试数据表（登录信息数据表----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节日历数据表详解。
本文仅仅提供了测试数据表的创建以及相关的数据填充，向测试表中增加时间粒度相关的字段列，使用时间粒度相关函数简单了基于日月季年统计汇总的演示。
