查找 SQL Server 2008 中用户的日期范围之间的差距

阿布顿·阿拉亚

我需要知道用户记录了多少时间（天）以及未记录时间之间的差距。在这个表中，我只存储 id 及其开始和结束日期（分别为 ID、INI、FIN）。我已经设法通过将用户与行号分组，然后将最新日志与以下日志等进行比较，根据条件来检测三个记录的差距。

问题是我过去有 n 个日志的人，我不能写 n 个左连接和 n 个条件。我希望使我当前的代码更具可扩展性，可以更递归地检测这些差距，并且让用户更“可理解”。

    CREATE TABLE [dbo].[baseRecurrentes](
    [ID] [nvarchar](8) NULL,
    [INI] [datetime] NULL,
    [FIN] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA1', CAST(0x0000A9C800000000 AS DateTime), CAST(0x0000A9E600000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA1', CAST(0x0000A9E700000000 AS DateTime), CAST(0x0000AA0200000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA1', CAST(0x0000AA0300000000 AS DateTime), CAST(0x0000AA2100000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA1', CAST(0x0000AA2200000000 AS DateTime), CAST(0x0000AA3F00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA2', CAST(0x0000A9D600000000 AS DateTime), CAST(0x0000A9D900000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA2', CAST(0x0000A9EB00000000 AS DateTime), CAST(0x0000A9ED00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA2', CAST(0x0000A9F000000000 AS DateTime), CAST(0x0000A9F100000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA3', CAST(0x0000AA1A00000000 AS DateTime), CAST(0x0000AA5A00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA4', CAST(0x0000A9CA00000000 AS DateTime), CAST(0x0000A9CB00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000A8DC00000000 AS DateTime), CAST(0x0000A8F100000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000A8F200000000 AS DateTime), CAST(0x0000A90F00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000A91000000000 AS DateTime), CAST(0x0000A92E00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000A92F00000000 AS DateTime), CAST(0x0000A94D00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000A94E00000000 AS DateTime), CAST(0x0000A96B00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000A96C00000000 AS DateTime), CAST(0x0000A98A00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000A98B00000000 AS DateTime), CAST(0x0000A9A800000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000A9A900000000 AS DateTime), CAST(0x0000A9C700000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000A9C800000000 AS DateTime), CAST(0x0000A87900000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000A9E700000000 AS DateTime), CAST(0x0000AA0200000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000AA0300000000 AS DateTime), CAST(0x0000AA2100000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000AA2200000000 AS DateTime), CAST(0x0000AA3F00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA5', CAST(0x0000AA4000000000 AS DateTime), CAST(0x0000AA5000000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA6', CAST(0x0000AA0900000000 AS DateTime), CAST(0x0000AA2900000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA7', CAST(0x0000A96C00000000 AS DateTime), CAST(0x0000A98A00000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA7', CAST(0x0000A98B00000000 AS DateTime), CAST(0x0000A9A800000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA7', CAST(0x0000A9A900000000 AS DateTime), CAST(0x0000A9C700000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA7', CAST(0x0000A85B00000000 AS DateTime), CAST(0x0000A87900000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA7', CAST(0x0000A9E700000000 AS DateTime), CAST(0x0000AA0200000000 AS DateTime))
INSERT [dbo].[baseRecurrentes] ([ID], [INI], [FIN]) VALUES (N'PERSONA7', CAST(0x0000AA0300000000 AS DateTime), CAST(0x0000AA2100000000 AS DateTime))

;WITH CTE AS (
    SELECT *, RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY INI DESC)
    FROM BASERECURRENTES
), CTE2 AS (
    --
    SELECT DISTINCT T.ID,
    -- 
    CAST(T2.INI AS DATETIME) AS INI1, CAST(T2.FIN AS DATETIME) AS FIN1, 
    -- 
    CAST(T3.INI AS DATETIME) AS INI2, CAST(T3.FIN AS DATETIME) AS FIN2, 
    --
    CAST(T4.INI AS DATETIME) AS INI3, CAST(T4.FIN AS DATETIME) AS FIN3 
    --
    FROM CTE T
    LEFT JOIN CTE T2 ON T.ID = T2.ID AND T2.RN = 1 
    LEFT JOIN CTE T3 ON T.ID = T3.ID AND T3.RN = 2
    LEFT JOIN CTE T4 ON T.ID = T4.ID AND T4.RN = 3

), CTE3 AS (
    SELECT *, MSG = (CASE 
                                --NO GAPS ON 3 LOGS
                                WHEN (INI1 - 1 BETWEEN INI2 AND FIN2) AND (INI2 - 1 BETWEEN INI3 AND FIN3) THEN 'SEC2' 
                                --NO GAPS ON 2 LOGS
                                WHEN (INI1 - 1 BETWEEN INI2 AND FIN2) THEN 'SEC1' 
                                --NO GAP AT ALL
                                ELSE 'NO SEC'
                            END)

    FROM CTE2
)
SELECT * FROM CTE3
ORDER BY ID ASC

我希望有一个表格显示用户 ID、“间隔天数”（未登录时间的总和）和一条显示间隔位置的消息。

ID       GD  MSG
-------------------
PERSONA2 5   GAP ON X-Y

妮可L。

我认为通过将每个用户的跨度与用户以前的跨度进行比较，这将为您提供您正在寻找的“递归”结果。这会将所有结果汇总给用户；从 CTE2 中选择可以返回有间隙或 Ini/Fin 反转的跨度。

;WITH CTE AS (
    SELECT *,
        Person_RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY INI, FIN),
        Person_Count = COUNT(*) OVER (PARTITION BY ID)
    FROM BASERECURRENTES
), CTE2 AS (
    SELECT T.*,
        N_Days_Logged_On = 
        CASE 
            WHEN DATEDIFF(DAY, T.Ini, T.Fin) < 0 THEN NULL -- Reversed span
            ELSE DATEDIFF(DAY, T.Ini, T.Fin) 
        END,
        N_Days_Gap_From_Prev = 
        CASE
            WHEN T.Person_RN = 1 THEN NULL -- First span
            WHEN DATEADD(DAY, 1, tPrev.Fin) = t.Ini THEN 0 -- OP allowed a one-day gap
            ELSE DATEDIFF(DAY, tPrev.Fin, T.Ini) 
        END,
        tPrev.Ini AS Ini_Prev, tPrev.Fin AS Fin_Prev
    FROM CTE T
    LEFT JOIN CTE TPrev ON T.ID = TPrev.ID AND TPrev.Person_RN = (T.Person_RN - 1)
    -- LEFT JOIN CTE TNext ON T.ID = TNext.ID AND TNext.Person_RN = (T.Person_RN + 1) 
), CTE3 AS (
    SELECT ID,
        GD = SUM(N_Days_Gap_From_Prev),
        N_Days_Logged_On = SUM(N_Days_Logged_On),
        N_Logs = MAX(Person_Count),
        N_Spans_WO_Gaps = COUNT(CASE WHEN N_Days_Gap_From_Prev = 0 THEN ID ELSE NULL END),
        N_Spans_W_Gaps = COUNT(NULLIF(N_Days_Gap_From_Prev, 0)),
        -- Captures reversed/invalid(?) spans, plus incomplete spans (null Ini or Fin)
        N_Spans_Suspect = COUNT(CASE WHEN N_Days_Logged_On IS NULL THEN ID ELSE NULL END)
    FROM CTE2
    GROUP BY ID
)
SELECT *
FROM CTE3
ORDER BY ID

我对此做了一些假设。从您的示例来看，登录/注销似乎是存储为日期时间的日期，但时间部分不是问题（例如，在 00:00 注销与 23:59 相同）。正如您在 CTE2 中看到的，DATEDIFF 结果不一定直观（例如，2019-01-01 到 2019-01-31 的 DATEDIFF 是 30 天）。

从样本数据中，这将返回：

PERSONA1：4 个日志，0 个间隔天数，116 个登录天数，0 个间隔时间间隔
PERSONA2：3 个日志，21 天间隔天，登录 6 天，所有跨度都有间隔。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于 2021-07-17

我来说两句

0 条评论

登录后参与评论

上一篇：tfidf 向量和 tfidf 向量数组之间的 Sklearn cosine_similarity

TOP 榜单

文章

查找 SQL Server 2008 中用户的日期范围之间的差距

查找 SQL Server 2008 中用户的日期范围之间的差距

UITableView的项目向下滚动后更改颜色，然后快速备份

Linux的官方Adobe Flash存储库是否已过时？

用日期数据透视表和日期顺序查询

应用发明者仅从列表中选择一个随机项一次

Mac OS X更新后的GRUB 2问题

验证REST API参数

Java Eclipse中的错误13，如何解决？

带有错误“ where”条件的查询如何返回结果？

ggplot：对齐多个分面图-所有大小不同的分面

尝试反复更改屏幕上按钮的位置 - kotlin android studio

如何从视图一次更新多行（ASP.NET - Core）

计算数据帧中每行的NA

蓝屏死机没有修复解决方案

在 Python 2.7 中。如何从文件中读取特定文本并分配给变量

离子动态工具栏背景色

VB.net将2条特定行导出到DataGridView

通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException

在Windows 7中无法删除文件（2）

python中的boto3文件上传

当我尝试下载 StanfordNLP en 模型时，出现错误

Node.js中未捕获的异常错误，发生调用