时间戳

首页 > 解决方案 > 如何在 SQL Server 中计算立即访问的每一行经过的时间

sql - 如何在 SQL Server 中计算立即访问的每一行经过的时间

问题描述

我想计算 SQL Server 中每个单击事件之间的即时经过时间。所以基本上，当用户点击Etype同一个中的任何特定内容时source，它应该计算立即经过的时间。计算将从 开始StartTime。

这是表结构

CREATE TABLE [dbo].[EmpLog](
    [EmpId] [int] NOT NULL,        
    [StartTime] [datetime] NULL,
    [Source] [nvarchar](128) NULL,
    [EType] [nvarchar](128) NULL,
    [User] [nvarchar](64) NULL,
 CONSTRAINT [PK_EmpLog] PRIMARY KEY CLUSTERED 
(
    [EmpId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

插入语句

INSERT INTO EmpLog (EmpId, StartTime, Source,EType,User) VALUES (1,'2021-02-20 01:15:44.647', 'PEn', 'In','abc','2050');
INSERT INTO EmpLog (EmpId, StartTime, Source,EType,User) VALUES (2,'2021-02-21 02:15:45.647', 'PEn', 'In','abc','2050');
INSERT INTO EmpLog (EmpId, StartTime, Source,EType,User) VALUES (3,'2021-02-22 01:15:44.647', 'PEn', 'In','abc','2050');
INSERT INTO EmpLog (EmpId, StartTime, Source,EType,User) VALUES (4,'2021-02-23 01:15:44.647', 'PEn', 'In','abc','2050');
INSERT INTO EmpLog (EmpId, StartTime, Source,EType,User) VALUES (5,'2021-02-24 01:15:44.647', 'PEn', 'In','abc','2');
INSERT INTO EmpLog (EmpId, StartTime, Source,EType,User) VALUES (6,'2021-02-24 01:15:46.647', 'PEn', 'OP','abc','0');
INSERT INTO EmpLog (EmpId, StartTime, Source,EType,User) VALUES (7,'2021-02-24 01:15:48.647', 'PEn', 'ED','abcd','0');
INSERT INTO EmpLog (EmpId, StartTime, Source,EType,User) VALUES (8,'2021-02-24 01:15:50.647', 'HM', 'Dash','efg','0');
INSERT INTO EmpLog (EmpId, StartTime, Source,EType,User) VALUES (9,'2021-02-24 01:15:52.647', 'Chk', 'Sup','cde','0');

此查询为我提供了每个用户在 SQL Server 中单击之间经过的时间。但问题是我有user相同source但不同的日期。还在计算经过的时间。在上述情况下，它应该计算0日期是否不同。该条件同样适用于每次点击之间的差异大于 5 分钟的日期。它应该显示0.

  1. 即使用户和来源相同但日期时间不同，它也应该计算0。
  2. 每次点击之间的差异大于 5 分钟然后计算0即使来源和用户相同。

我们只考虑点击差异小于 5 分钟的立即点击。

不确定如何在我的查询中考虑这些场景。

SELECT 
    EmpId
    , [Source]
    , EType
    , [User] 
    , ABS(ISNULL(DATEDIFF(second, previousStartTime, StartTime), 0)) AS ElapsedTime
FROM   
    (
    SELECT  *,
            LEAD(StartTime) OVER ( PArtition by [Source], [User] ORDER BY StartTime) 
            AS  previousStartTime
    FROM    [dbo].[EmpLog]
   ) q

 ORDER BY  EmpId

当前输出为

EmpId 开始时间 资源 E型 用户 经过时间
1 2021-02-20 01:15:44.647 笔 在 美国广播公司 2050
2 2021-02-21 02:15:45.647 笔 在 美国广播公司 2050
3 2021-02-22 01:15:44.647 笔 在 美国广播公司 2050
4 2021-02-23 01:15:44.647 笔 在 美国广播公司 2050
5 2021-02-24 01:15:44.647 笔 在 美国广播公司 2
6 2021-02-24 01:15:46.647 笔 OP 美国广播公司 0
7 2021-02-24 01:15:48.647 笔 ED A B C D 0
8 2021-02-24 01:15:50.647 HM 短跑 电子烟 0
9 2021-02-24 01:15:52.647 校验 苏普 cde 0

预期的输出应该是

EmpId 开始时间 资源 E型 用户 经过时间
1 2021-02-20 01:15:44.647 笔 在 美国广播公司 0
2 2021-02-21 02:15:45.647 笔 在 美国广播公司 0
3 2021-02-22 01:15:44.647 笔 在 美国广播公司 0
4 2021-02-23 01:15:44.647 笔 在 美国广播公司 0
5 2021-02-24 01:15:44.647 笔 在 美国广播公司 2
6 2021-02-24 01:15:46.647 笔 OP 美国广播公司 0
7 2021-02-24 01:15:48.647 笔 ED A B C D 0
8 2021-02-24 01:15:50.647 HM 短跑 电子烟 0
9 2021-02-24 01:15:52.647 校验 苏普 cde 0

标签： sqlsql-servertsql

解决方案

您可以使用以下表达式排除您提到的 2 个条件CASE：

SELECT
    EmpId
    , [Source]
    , EType
    , [User] 
    -- Only return a result when its less then 5 mins
    , CASE WHEN ElapsedTime < 300 then ElapsedTime ELSE 0 END AS ElapsedTime
FROM (
    SELECT *,
        LEAD(StartTime) OVER (PARTITION BY [Source], [User] ORDER BY StartTime) AS PreviousStartTime
    FROM dbo.EmpLog
) q
-- Only return a result when its the same day. The 5min filter will remove any from the same day in a different month
CROSS APPLY (VALUES(CASE WHEN DATEPART(day, PreviousStartTime) = DATEPART(day,StartTime) THEN ABS(ISNULL(DATEDIFF(second, PreviousStartTime, StartTime), 0)) ELSE 0 END)) AS X (ElapsedTime)
ORDER BY EmpId;

这在功能上等同于以下查询，但更紧凑。我把它包括在内是为了解释逻辑。

SELECT
    EmpId
    , [Source]
    , EType
    , [User] 
    -- Only return a result when its less then 5 mins
    , CASE WHEN ElapsedTime < 300 then ElapsedTime ELSE 0 END AS ElapsedTime
FROM (
    SELECT
        EmpId
        , [Source]
        , EType
        , [User]
        -- Only return a result when its the same day. The 5min filter will remove any from the same day in a different month
        , CASE WHEN DATEPART(day, PreviousStartTime) = DATEPART(day,StartTime) THEN ABS(ISNULL(DATEDIFF(second, PreviousStartTime, StartTime), 0)) ELSE 0 END AS ElapsedTime
    FROM (
        SELECT *,
            LEAD(StartTime) OVER (PARTITION BY [Source], [User] ORDER BY StartTime) AS PreviousStartTime
        FROM dbo.EmpLog
    ) q
) v
ORDER BY EmpId;

推荐阅读