在不使用游标的情况下在行中填充缺失的数据

Question

我有一个这种形式的表格(简化(：

CREATE TABLE [dbo].[test](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Extension] [varchar](30) NULL DEFAULT (''),
[StartTimestamp] [datetime] NULL,
[UserId] [varchar](30) NULL DEFAULT (''),
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

请考虑以下数据：

Id | Extension | StartTimestamp      | UserId
------------------------------------------------
1  |  100      | 2018-09-24 16:00:00 | A 
2  |  101      | 2018-09-24 16:01:15 | B
3  |  100      | 2018-09-24 16:01:14 | 
4  |  102      | 2018-09-24 16:02:24 | C
5  |  100      | 2018-09-24 16:05:00 | A
6  |  101      | 2018-09-24 16:07:00 | B 
7  |  101      | 2018-09-24 16:30:00 |
8  |  100      | 2018-09-24 17:00:00 | D

现在我想按照以下规则填充 UserId 列的空白： 某个扩展名的记录，且不超过 10 分钟 因此，对于 ID 为 3 的行，这意味着 UserId A(因为它会找到 ID 为 1 的记录(。对于第 7 行，UserId 将保持为空，因为它无法在 10 分钟内找到此扩展的记录。 目前，我正在使用此查询来识别具有间隙的扩展(与游标一起(：

SELECT 
DISTINCT b.Extension
FROM test b
INNER JOIN (
SELECT DISTINCT Extension
FROM test
WHERE
UserId = ''
) a ON a.Extension=b.Extension
WHERE
b.UserId <> ''
ORDER BY b.Extension

是否可以在不使用游标(即使用单个更新语句(的情况下实现此目的？

Answer 1

我认为你可以只使用lag()：

select t.*,
(case when userid is not null then userid
when (lag(StartTimestamp) over (partition by Extension 
order by StartTimestamp) > 
dateadd(minute, -10, StartTimestamp)
)
then lag(userid) over (partition by Extension 
order by StartTimestamp)
end) as imputed_userid
from test t;

Answer 2

我使用 ROW_NUMBER(( 来实现这一点：

DECLARE @temp TABLE (
[Id] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Extension] [varchar](30) NULL DEFAULT (''),
[StartTimestamp] [datetime] NULL,
[UserId] [varchar](30) NULL DEFAULT ('')
)
INSERT INTO @temp (Extension, StartTimestamp, UserId)
SELECT 100, {ts'2018-09-24 16:00:00.000'}, 'A' UNION ALL
SELECT 101, {ts'2018-09-24 16:01:15.000'}, 'B' UNION ALL
SELECT 100, {ts'2018-09-24 16:01:14.000'}, ''  UNION ALL
SELECT 102, {ts'2018-09-24 16:02:24.000'}, 'C' UNION ALL
SELECT 100, {ts'2018-09-24 16:05:00.000'}, 'A' UNION ALL
SELECT 101, {ts'2018-09-24 16:07:00.000'}, 'B' UNION ALL
SELECT 101, {ts'2018-09-24 16:30:00.000'}, ''
SELECT
a.Id, a.Extension, a.StartTimestamp, a.UserId, b.StartTimestamp, b.UserId
FROM @temp a
JOIN (
SELECT
ROW_NUMBER() OVER(PARTITION BY a.Extension ORDER BY b.StartTimestamp) RowNo,
a.Id, a.Extension, b.StartTimestamp, b.UserId
FROM @temp a
JOIN @temp b ON b.Extension = a.Extension -- edited to add this
AND LEN(b.UserId) > 0
AND b.StartTimestamp >= DATEADD(mi, -10, a.StartTimestamp)
AND b.StartTimestamp < DATEADD(mi, 10, a.StartTimestamp)
WHERE a.UserId = ''
) b ON b.Id = a.Id AND b.RowNo = 1

Answer 3

with Changes as (SELECT t1.id FROM test t1 
CROSS APPLY 
(SELECT TOP 1 t2.UserId from test  t2 where
t2.Extension = t1.extension 
and  t1.id != t2.id 
and t2.starttimestamp 
between dateadd(minute,-10,t1.starttimestamp) 
AND t2.starttimestamp 
ORDER BY t2.starttimestamp DESC) DQ
WHERE COALESCE(t1.userid,'') = '' 
)
Update Test SET UserId = 
(SELECT UserId from changes WHERE Changes.Id = test.Id 
AND EXISTS(SELECT 0 FROm Changes c2 WHERE c2.Id = test.Id)
)