☰

创建一个视图以每 10 分钟显示一次备份状态

tag-icon • tag-icon sql-server sql
创建一个视图以每 10 分钟显示一次备份状态

我有个问题，

我的表有以下数据：

userID, startTime, EndTime
—————————————
101, 04/11/2013 11:00:00, 04/11/2013 11:55:00
102, 04/11/2013 11:00:00, 04/11/2013 11:24:00
103, 04/11/2013 11:20:00, 04/11/2013 11:45:00
104, 04/11/2013 11:30:00, 04/11/2013 11:35:00
105, 04/11/2013 11:40:00, 04/11/2013 11:55:00

我可以使用该视图每 10 分钟显示一次备份状态吗？

我想知道结果如下：

time, count
——————————
04/11/2013 11:00:00, 2
04/11/2013 11:10:00, 2
04/11/2013 11:20:00, 3
04/11/2013 11:30:00, 3
04/11/2013 11:40:00, 3
04/11/2013 11:50:00, 2
04/11/2013 12:00:00, 0



04/11/2013 11:00:00 – 04/11/2013 11:09:59 have 2 jobs, 101 & 102
04/11/2013 11:10:00 – 04/11/2013 11:19:59 have 2 jobs, 101 & 102
04/11/2013 11:20:00 – 04/11/2013 11:29:59 have 3 jobs, 101 & 102 & 103
…
04/11/2013 11:50:00 – 04/11/2013 11:59:59 have 2 jobs, 101 & 105
04/11/2013 12:00:00 – 04/11/2013 12:09:59 have 0 job

我想知道你能不能帮我一下……非常感谢

答案1

这里实际上并不是基于代码的解决方案的地方（请参阅 Stack Overflow），但我会坚持：

首先，我假设有一个名为 logins 的表，其中包含以下数据......

IF OBJECT_ID('tempdb..logins') IS NOT NULL DROP TABLE logins;
GO
CREATE TABLE logins (userID INT, startTime DATETIME, endTime DATETIME)
GO
INSERT INTO logins (userID, startTime, endTime)
VALUES (101, '2013-11-04T11:00:00', '2013-11-04T11:00:00')
      ,(102, '2013-11-04T11:00:00', '2013-11-04T11:24:00')
      ,(103, '2013-11-04T11:20:00', '2013-11-04T11:45:00')
      ,(104, '2013-11-04T11:30:00', '2013-11-04T11:35:00')
      ,(105, '2013-11-04T11:40:00', '2013-11-04T11:55:00');

GO

如果您只是想知道登录属于哪 10 分钟时间段，那么一个简单（尽管有点神奇）的方法是使用零日期......

SELECT
    period_start = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, l.startTime) / 10 * 10, 0),
    l.*
FROM logins l
GO

...因此，对于简单的计数，基于时期...

WITH starts AS (
    SELECT
        period_start = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, l.startTime) / 10 * 10, 0),
        l.*
    FROM logins l
)
SELECT
    period_start,
    login_count = COUNT(1)
FROM starts
GROUP BY period_start
GO

但是，如果您还想显示 0 计数间隙，则需要一个函数来获取所有日期，然后对其进行左连接......

-- periods: a table-function to get a series of date/times intervals between two dates
-- (tally approach taken from Itzik Ben-Gan's article http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers)
IF OBJECT_ID('tempdb..periods') IS NOT NULL DROP FUNCTION periods;
GO
CREATE FUNCTION periods(
    @start_date DATETIME2,
    @end_date    DATETIME2 = GETDATE,
    @incr_mins    INT = 10
)
RETURNS TABLE
AS RETURN (
    WITH 
        E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
        ),                                      -- 10^1     or 10 rows
        E2(N) AS (SELECT 1 FROM E1 a, E1 b),    -- 10^2     or 100 rows
        E4(N) AS (SELECT 1 FROM E2 a, E2 b),    -- 10^4     or 10,000 rows
        E8(N) AS (SELECT 1 FROM E4 a, E4 b),    -- 10^8     or 100,000,000 rows
        E16(N) AS (SELECT 1 FROM E8 a, E8 b),   -- 10^16 or 10,000,000,000,000,000 rows
        E32(N) AS (SELECT 1 FROM E16 a, E16 b), -- 10^32 or 100,000,000,000,000,000,000,000,000,000,000 rows
        E64(N) AS (SELECT 1 FROM E32 a, E32 b), -- 10^64 or 10,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 rows

        starts AS (
            SELECT TOP (CEILING(ABS(DATEDIFF(MINUTE, @start_date, @end_date)) / @incr_mins))
                period_start = DATEADD(MINUTE, 
                                       @incr_mins * (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1), 
                                       @start_date)
            FROM E64
        ),
        ends AS (
            SELECT
                period_start,
                period_end = DATEADD(MINUTE, @incr_mins, period_start)
            FROM starts
        )
    SELECT period_start, period_end
    FROM ends
)
GO

现在您可以使用该功能来统计您在各个时期的登录次数......

SELECT
    p.period_start,
    login_count = SUM(CASE WHEN l.userID IS NULL THEN 0 ELSE 1 END)
FROM periods('2013-11-04T00:00:00', '2013-11-05T00:00:00', 10) p
LEFT JOIN logins l ON 
    l.startTime >= p.period_start AND 
    l.startTime < p.period_end
GROUP BY p.period_start
GO

请注意，调用该函数的开销有点大，因此最好填充一个索引良好的“日历”表，预先计算所有周期，然后加入该表。

希望这可以帮助。

J

相关内容