的背景：
我正在尝试确定客户在特定房间里停留的时间。每个客户都可以用标识CustomerID，并且在他们访问时会分配一个VisitNumber。例如，如果客户今天拜访了他们，他们将得到111111。VisitNumber然后他们离开并在下周回来VisitNumber，得到111112。
当客户第一次访问时，最初没有为他们分配房间，而当最终为他们分配了指定的房间时，会将条目写入数据库。CurrentRoom因为他们还没有房间，所以将为空白，这NewRoom就是他们已经搬进来的房间。
该条目将被记录为事件1（客户从无房间移到另一房间），时间是交易发生的时间。如果客户随后在其现有逗留期间被转移，则将其记录为事件9（客户从一个房间移至另一个房间），并且CurrentRoom＆*NewRoom值也将被记录。
问题
我已经使用LAG和LEAD设法获得了上一行的时间和下一行的时间，然后计算出这两次之间的差值，这使我得到了客户在该特定房间花费的时间长度。
使用LAG时，问题在于它正在获得先前的价值，在某些情况下，这可能是来自完全不同的客户的价值。我想仅获取特定电流和当前值的滞后和滞后值，然后计算出这些值之间的差值，以找出该客户在一个房间里呆了多长时间。CustomerIDVisitNumber
演示数据：
CREATE TABLE #beds
(
[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[User] [nvarchar](50) NULL,
[CustomerID] [nvarchar](50) NULL,
[Area] [nchar](10) NULL,
[Event] [nvarchar](50) NULL,
[VisitNumber] [nvarchar](50) NULL,
[Time] [datetime] NULL,
[CurrentRoom] [nvarchar](50) NULL,
[NewRoom] [nvarchar](50) NULL
)
GO
INSERT INTO #beds ([User],[CustomerID],[Area],[Event],[VisitNumber],[Time],[CurrentRoom],[NewRoom])
VALUES ('00001','C11111111','Area1',2,111111111,'2017-03-22 11:05:44.360','B22','B44'),
('00001','C11111111','Area1',1,111111111,'2017-03-22 11:05:15.517','','B22'),
('00001','C22222222','Area2',1,222222222,'2017-03-22 07:38:16.117','','POD3'),
('00001','C22222222','Area2',3,222222222,'2017-03-22 07:41:24.787','POD3','POD3'),
('00001','C22222222','Area2',9,222222222,'2017-03-22 09:10:49.697','POD3',''),
('00001','C22222222','Area2',1,222222222,'2017-03-22 10:05:19.130','','POD15'),
('00001','C22222222','Area2',2,222222222,'2017-03-22 10:13:43.057','POD15','A'),
('00001','C22222222','Area2',3,222222222,'2017-03-22 10:25:01.527','A','A'),
('00001','C22222222','Area2',3,222222222,'2017-03-22 10:46:03.960','A','A'),
('00001','C22222222','Area2',3,222222222,'2017-03-22 10:46:17.030','A','A'),
('00002','C33333333','Area3',1,333333333,'2017-03-22 09:20:23.660','','B46'),
('00001','C33333333','Area2',9,333333333,'2017-03-22 08:53:32.860','POD8','POD1'),
('00001','C33333333','Area2',1,333333333,'2017-03-22 07:34:58.810','POD7','POD8'),
('00001','C33333333','Area2',1,333333333,'2017-03-22 11:49:55.203','','BB4'),
('00001','C33333333','Area2',3,333333333,'2017-03-22 11:50:11.943','BB4','BB4'),
('00001','C33333333','Area2',3,333333333,'2017-03-22 08:42:56.157','POD8','POD8'),
('00001','C33333333','Area2',3,333333333,'2017-03-22 08:22:59.157','POD8','POD8'),
('00003','C33333333','Area3',1,333333333,'2017-03-23 06:41:12.753','','B46')
GO
这是我到目前为止的查询；这将为我提供上一行的值和下一行的值，但是我认为这并没有考虑到客户。
SELECT
T1.[User], T1.[CustomerID],
T1.[Area], T1.[Event],
T1.[VisitNumber],
T1.[CurrentRoom], T1.[NewRoom],
T1.[Time],
LAG(T1.TIME) OVER (ORDER BY T1.VisitNumber) PreviousTime,
LEAD(T1.TIME) OVER (ORDER BY T1.VisitNumber) NextTime
FROM
#beds t1
WHERE
T1.[Area] = 'Area2'
AND T1.[CurrentRoom] IS NOT NULL
AND T1.[NewRoom] IS NOT NULL
AND T1.[CustomerID] IS NOT NULL
AND T1.[CustomerID] <> ' '
AND T1.Event IN (1,9)
ORDER BY
VisitNumber DESC
预期的输出：这是我期望的输出。我只想要TimeInRoom（时间中不包括日期字段）：
+------------+-------+-------------+-------------+---------+------------+
| CustomerID | Area | VisitNumber | CurrentRoom | NewRoom | TimeInRoom |
+------------+-------+-------------+-------------+---------+------------+
|C33333333 |Area2 | 333333333 | | BB4 | 00:10 |
|C33333333 |Area2 | 333333333 | | POD8 | 00:20 |
|C33333333 |Area2 | 333333333 | POD8 | | 00:30 |
+------------+-------+-------------+-------------+---------+------------+
我希望这有帮助：
;WITH cte_Result AS
(
SELECT
[CustomerID],
[Area],
[VisitNumber],
[CurrentRoom],
[NewRoom],
[Time],
LAG([TIME]) OVER (partition by [CustomerID],[VisitNumber] ORDER BY ID DESC) PreviousTime,
LEAD([TIME]) OVER (partition by [CustomerID],[VisitNumber] ORDER BY ID DESC) NextTime
FROM #beds
WHERE [Area] = 'Area2'
AND [CurrentRoom] IS NOT NULL
AND [NewRoom] IS NOT NULL
AND [CustomerID] IS NOT NULL
AND [CustomerID] <> ' '
AND [Event] IN (1,9)
--AND [CustomerID] = 'C33333333'
),
cte_BuildStayPeriod
AS (
SELECT CustomerID,
Area,
VisitNumber,
CurrentRoom,
NewRoom,
DATEDIFF(SECOND, COALESCE([NextTime], PreviousTime), COALESCE(PreviousTime, [time])) AS StayDuration
FROM cte_Result
)
SELECT CustomerID,
Area,
VisitNumber,
CurrentRoom,
NewRoom,
StayDuration,
CAST(DATEADD(SECOND, StayDuration, '1900-01-01') AS TIME) AS StayDuration
FROM cte_BuildStayPeriod
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句