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节日历数据表详解
 
总结语
 
本文仅仅提供了测试数据表的创建以及相关的数据填充,向测试表中增加时间粒度相关的字段列,使用时间粒度相关函数简单了基于日月季年统计汇总的演示。
 
参考清单列表
posted @ 2016-01-19 15:44  剑走江湖  阅读(386)  评论(0编辑  收藏