sql-server - 如何为我的数据池正确使用 Row_Number()(分区)

Question

我们有下表(输出已经排序并分开以便理解):

| PK | FK1 | FK2 |   ActionCode |         CreationTS  | SomeAttributeValue |
+----+-----+-----+--------------+---------------------+--------------------+
|  6 | 100 | 500 |       Create | 2011-01-02 00:00:00 |                  H |
----------------------------------------------------------------------------
|  3 | 100 | 500 |       Change | 2011-01-01 02:00:00 |                  Z |
|  2 | 100 | 500 |       Change | 2011-01-01 01:00:00 |                  X |
|  1 | 100 | 500 |       Create | 2011-01-01 00:00:00 |                  Y |
----------------------------------------------------------------------------
|  4 | 100 | 510 |       Create | 2011-01-01 00:30:00 |                  T |
----------------------------------------------------------------------------
|  5 | 100 | 520 | CreateSystem | 2011-01-01 00:30:00 |                  A |
----------------------------------------------------------------------------

什么是ActionCode？我们在 c# 中使用它，它代表一个枚举值

我想达到什么目的？

好吧，我需要以下输出:

| FK1 | FK2 |   ActionCode | SomeAttributeValue |
+-----+-----+--------------+--------------------+
| 100 | 500 |       Create |                  H |
| 100 | 500 |       Create |                  Z |
| 100 | 510 |       Create |                  T |
| 100 | 520 | CreateSystem |                  A |
-------------------------------------------------

那么，实际的逻辑是什么？我们有一些复合键(FK1 + FK2)的逻辑组。这些组中的每一个都可以分成多个分区，这些分区以 Create 或 CreateSystem 开头。每个分区都以 Create、CreateSystem 或 Change 结尾。每个分区的 SomeAttributeValue 的实际值应该是分区最后一行的值。

不可能有以下数据池:

| PK | FK1 | FK2 |   ActionCode |         CreationTS  | SomeAttributeValue |
+----+-----+-----+--------------+---------------------+--------------------+
|  7 | 100 | 500 |       Change | 2011-01-02 02:00:00 |                  Z |
|  6 | 100 | 500 |       Create | 2011-01-02 00:00:00 |                  H |
|  2 | 100 | 500 |       Change | 2011-01-01 01:00:00 |                  X |
|  1 | 100 | 500 |       Create | 2011-01-01 00:00:00 |                  Y |
----------------------------------------------------------------------------

然后期望 PK 7 影响 PK 2 或 PK 6 影响 PK 1。

我什至不知道如何/从哪里开始……我怎样才能做到这一点？我们在 mssql 2005+ 上运行

编辑:
有 a dump可用:

• instanceId:我的PK
• 租户编号:FK 1
• campaignId:FK 2
• callId:FK 3
• 充值柜台:FK 4
• ticketType:ActionCode(1&4&6是Create，5是Change，3必须忽略)
• ticketType, profileId, contactPersonId, ownerId, handlingStartTime, handlingEndTime, memo, callWasPreselected, creatorId, creationTS, changerId, changeTS 应该取自 Create(分组分区的第一行)
• callingState、reasonId、followUpDate、callingAttempts 和 callingAttemptsConsecutivelyNotReached 应该取自最后一个 Create(这将是一个“one-line-partition-in-group”/与上一个相同) 或 Change (组中分区的最后一行)

Answer 1

我假设每个分区只能包含一个单个 Create 或CreateSystem，否则您的要求定义不明确。以下是未经测试的，因为我没有示例表，也没有易于使用的格式的示例数据:

;With Partitions as (
     Select
         t1.FK1,
         t1.FK2,
         t1.CreationTS as StartTS,
         t2.CreationTS as EndTS
     From
         Table t1
             left join
         Table t2
             on
                  t1.FK1 = t2.FK1 and
                  t1.FK2 = t2.FK2 and
                  t1.CreationTS < t2.CreationTS and
                  t2.ActionCode in ('Create','CreateSystem')
             left join
         Table t3
             on
                  t1.FK1 = t3.FK1 and
                  t1.FK2 = t3.FK2 and
                  t1.CreationTS < t3.CreationTS and
                  t3.CreationTS < t2.CreationTS and
                  t3.ActionCode in ('Create','CreateSystem')
       where
           t1.ActionCode in ('Create','CreateSystem') and
           t3.FK1 is null
), PartitionRows as (
     SELECT
         t1.FK1,
         t1.FK2,
         t1.ActionCode,
         t2.SomeAttributeValue,
         ROW_NUMBER() OVER (PARTITION_FRAGMENT_ID BY t1.FK1,T1.FK2,t1.StartTS ORDER BY t2.CreationTS desc) as rn
     from
         Partitions t1
             inner join
         Table t2
             on
                t1.FK1 = t2.FK1 and
                t1.FK2 = t2.FK2 and
                t1.StartTS <= t2.CreationTS and
                (t2.CreationTS < t1.EndTS or t1.EndTS is null)
)
select * from PartitionRows where rn = 1

(请注意，我在这里使用了各种保留名称)

基本逻辑是:Partitions CTE用于根据FK1、FK2、包含开始时间戳和排他结束时间戳来定义每个分区。它通过对基表的三重连接来实现这一点。来自 t2 的行被选择出现在来自 t1 的行之后，然后是 t3 中的行被选择出现在来自 t1 的匹配行之间和 t2 .然后，在 WHERE 子句中，我们从结果集中排除来自 t3 的匹配项的任何行。 - 结果是来自 t1 的行和来自 t2 的行代表两个相邻分区的开始。

然后第二个 CTE 从 Table 中检索所有行对于每个分区，但分配一个 ROW_NUMBER()每个分区内的分数，基于 CreationTS , 降序排列，结果为 ROW_NUMBER()每个分区中的 1 是最后一行。

最后，在选择中，我们选择在各自分区中最后出现的那些行。

这都假设 CreationTS每个分区中的值都是不同的。如果该假设不成立，我也可以使用 PK 对其进行重新处理。