代码之家 › 专栏 › 技术社区 › Philip

使用流标识符密钥更新记录

tsql sql-server sql

Philip · 技术社区 · 6 年前

我正在尝试将一个脚本组合在一起,以便根据每个访问ID的特定区域流更新ActionTypeID列。

有三个区域,访问可以在任何区域之间移动,我需要根据ActionType表中的5个值更新ActionTypeID列。

更新的逻辑都是基于每个VisitID记录的Area列,在这里您可以看到VisitID 100从Area 1移动到2到3。而VisitID记录101从区域1移动到区域2,然后返回到区域1并向前移动到区域2,然后移动到区域3。请参阅“WhatActionTypeShouldBe”列。

样本数据

Create Table #SampleData
(
    VisitID int,
    StartDate datetime,
    EndDate datetime,
    Area tinyint,
    ActionTypeID tinyint,
    WhatActionTypeShouldBe tinyint
)
insert into #SampleData
(
    VisitID,
    StartDate,
    EndDate,
    Area,
    ActionTypeID,
    WhatActionTypeShouldBe
)
select
    100,
    '2020-01-26 00:16:09.800',
    '2020-01-26 00:18:09.800',
    1,
    0,
    1
union 
select
    100,
    '2020-01-26 00:18:09.800',
    '2020-01-26 00:21:09.800',
    2,
    0,
    2
union
select
    100,
    '2020-01-26 00:21:09.800',
    '2020-01-26 00:27:09.800',
    3,
    0,
    3
union
select
    101,
    '2020-01-26 00:16:09.800',
    '2020-01-26 00:18:09.800',
    1,
    0,
    1
union 
select
    101,
    '2020-01-26 00:18:09.800',
    '2020-01-26 00:21:09.800',
    2,
    0,
    2
union
select
    101,
    '2020-01-26 00:21:09.800',
    '2020-01-26 00:24:09.800',
    1,
    0,
    4
union
select
    101,
    '2020-01-26 00:24:09.800',
    '2020-01-26 00:27:09.800',
    2,
    0,
    2
union
select
    101,
    '2020-01-26 00:27:09.800',
    '2020-01-26 00:30:09.800',
    3,
    0,
    3
union
select
    102,
    '2020-01-26 00:24:09.800',
    '2020-01-26 00:27:09.800',
    2,
    0,
    2
union
select
    102,
    '2020-01-26 00:27:09.800',
    '2020-01-26 00:30:09.800',
    3,
    0,
    3
union
select
    103,
    '2020-01-26 00:24:09.800',
    '2020-01-26 00:27:09.800',
    1,
    0,
    1
union
select
    103,
    '2020-01-26 00:27:09.800',
    '2020-01-26 00:30:09.800',
    2,
    0,
    2
union
select
    103,
    '2020-01-26 00:30:09.800',
    '2020-01-26 00:34:09.800',
    3,
    0,
    3
union
select
    103,
    '2020-01-26 00:34:09.800',
    '2020-01-26 00:36:09.800',
    2,
    0,
    5
union
select
    103,
    '2020-01-26 00:36:09.800',
    '2020-01-26 00:37:09.800',
    3,
    0,
    3

Create Table #ActionType
(
    ActionTypeID tinyint,
    ActionTypeName varchar(50)
)

insert into #ActionType
(
    ActionTypeID,
    ActionTypeName
)
select
    1,
    'Visit Started'
union
select
    2,
    'Progress to Area 2'
union
select
    3,
    'Complete Visit in Area 3'
union
select
    4,
    'Return to Area 1'
union
select
    5,
    'Return to Area 2'

select * from #SampleData

select * from #ActionType

drop table #SampleData
drop table #ActionType

2 回复 | 直到 6 年前

Gordon Linoff 6 年前

你想要一些 lag() 以及其他窗口功能。这有点棘手,但有点像这样:

with toupdate as (
      select sd.*,
             row_number() over (partition by visitid, area order by startdate) as seqnum
      from #sampledata sd
     )
update toupdate
    set actiontypeid = (case when area = 1 and seqnum = 1 then 1
                             when area = 2 and seqnum = 1 then 2
                             when area = 3 then 3
                             when area = 1 then 4
                             when area = 2 then 5
                        end);

“5”的逻辑相当不清楚。然而,像这样的事情似乎是你所需要的。

Nick SamSmith1986 6 年前

ActionTypeID :

WITH Actions AS (
  SELECT SampleData.*,
         LAG(AREA, 1, 1) OVER (PARTITION BY VisitID ORDER BY StartDate) AS Last_Area,
         COUNT(CASE WHEN Area = 1 THEN 1 END) OVER (PARTITION BY VisitID ORDER BY StartDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS Area_1_Visits
  FROM SampleData
)
UPDATE Actions
  SET ActionTypeID =
       CASE WHEN Area = 1 AND Area_1_Visits = 0 THEN 1
            WHEN Area = 1 AND Area_1_Visits > 0 THEN 4
            WHEN Area = 2 AND Last_Area = 1     THEN 2
            WHEN Area = 2 AND Last_Area = 3     THEN 5
            WHEN Area = 3 THEN 3 
       END

Demo on SQLFiddle