sql—如何跨多个列和行“汇总”数据

6qftjkof 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(316)

我有一个审计表，我们在其中记录对数据库中字段的更改。我有一个查询，在那里我能够从审计中获得关于几个列的数据子集，它们记录的更改，以及何时与适用的id相关联。下面是输出的示例：

ID      ada       IsHD  HDF   DTStamp
-----------------------------------------------------
68      NULL      0     0     2020-04-28 21:12:21.287
68      NULL      NULL  NULL  2020-04-17 14:59:49.700
68      No/Unsure NULL  NULL  2020-04-17 14:03:46.160
68      NULL      0     0     2020-04-17 13:49:49.720
102     NULL      NULL  NULL  2020-04-30 13:11:15.273
102     No/Unsure NULL  NULL  2020-04-20 16:00:35.410
102     NULL      1     1     2020-04-20 15:59:55.750
105     No/Unsure 1     1     2020-04-17 12:06:10.833
105     NULL      NULL  NULL  2020-04-13 07:51:30.180
126     NULL      NULL  NULL  2020-05-01 17:59:24.460
126     NULL      0     0     2020-04-28 21:12:21.287

我想弄清楚的是，最有效的方法是“汇总”给定id的多行，以便保留最新的非空值，只为该id保留一行。
也就是说，转动这个：

68      NULL      0     0     2020-04-28 21:12:21.287
68      NULL      NULL  NULL  2020-04-17 14:59:49.700
68      No/Unsure NULL  NULL  2020-04-17 14:03:46.160
68      NULL      0     0     2020-04-17 13:49:49.720
102     NULL      NULL  NULL  2020-04-30 13:11:15.273
102     No/Unsure NULL  NULL  2020-04-20 16:00:35.410
102     NULL      1     1     2020-04-20 15:59:55.750

对此：

68      No/Unsure 0     0     2020-04-28 21:12:21.287
102     No/Unsure 1     1     2020-04-30 13:11:15.273

……等等。就像你要把结果的顶端往下压，然后挤出所有的空值一样。
将上述结果转储到temp表中 @audit 然后运行以下查询：

SELECT DISTINCT a.[ID]
     , (SELECT TOP 1 [ADA]
        FROM @audit
        WHERE [ID] = a.[ID]
          AND [ADA] IS NOT NULL
        ORDER BY [DTStamp] DESC) AS 'ADA'
     , (SELECT TOP 1 [IsHD]
        FROM @audit
        WHERE [ID] = a.[ID]
          AND [IsHD] IS NOT NULL
        ORDER BY [DTStamp] DESC) AS 'IsHD'
     , (SELECT TOP 1 [HDF]
        FROM @audit
        WHERE [ID] = a.[ID]
          AND [HDF] IS NOT NULL
        ORDER BY [DTStamp] DESC) AS 'HDF'
     , (SELECT Max([DTStamp])
        FROM @audit
        WHERE [ID] = a.[ID]) AS 'DTStamp'
FROM @audit a
ORDER BY [ID]

这就是我所想到的，它确实起作用，但感觉非常糟糕和低效。有没有更好的方法来实现最终目标？

sql sql-server greatest-n-per-group tsql sql-server-2012

来源：https://stackoverflow.com/questions/61942074/how-to-roll-up-data-across-multiple-columns-and-rows

2条答案

按热度按时间

hfyxw5xn1#

如果希望每个id有一行，请使用聚合：

select id, max(ada), max(IsHD), max(HDF), max(DTStamp)
from @audit a
group by id;

这适用于您提供的数据，并且似乎符合您想要的规则。

赞(0）回复(0）举报 2021-08-13

6fe3ivhb2#

我知道您需要每个 id 对于每列，使用列 DTStamp 为了订购。
使用多个子查询的方法可以实现所需的功能。另一种方法是使用多个 row_number() 和条件聚合。这实际上可能更有效，因为它避免了对表的多次扫描。

select
    id,
    max(case when rn_ada  = 1 then ada  end) ada,
    max(case when rn_isHd = 1 then isHd end) isHd,
    max(case when rn_hdf  = 1 then hdf  end) hdf,
    max(DTStamp) DTStamp
from (
    select 
        a.*,
        row_number() over(
            partition by id
            order by case when ada is not null then DTStamp end desc
        ) rn_ada,
        row_number() over(
            partition by id
            order by case when isHd is not null then DTStamp end desc
        ) rn_isHd,
        row_number() over(
            partition by id
            order by case when hdf is not null then DTStamp end desc
        ) rn_hdf
    from @audit a
) t
group by id
order by id

db小提琴演示：

id | ada       | isHd | hdf | DTStamp                
--: | :-------- | ---: | --: | :----------------------
 68 | No/Unsure |    0 |   0 | 2020-04-28 21:12:21.287
102 | No/Unsure |    1 |   1 | 2020-04-30 13:11:15.273

赞(0）回复(0）举报 2021-08-13

我来回答

sql—如何跨多个列和行“汇总”数据

2条答案

相关问题

热门标签

最新问答