时间戳

首页 > 解决方案 > 查询时态表并合并行

sql-server - 查询时态表并合并行

问题描述

假设我有一个临时表ProductDetails，使用下面的查询返回一些历史数据。

SELECT * FROM ProductDetails
FOR system_time
BETWEEN '1900-01-01 00:00:00' AND '9999-12-31 00:00:00'
WHERE ProductID = 8


ID    ProductID(FK)    Attribute    Value    SysStartTime           SysEndTime
--    -------------    ---------    -----    -------------------    ----------
1     8                Size         S        2020-07-06 05:00:00    9999-12-31 23:59:59
2     8                Color        Blue     2020-07-06 05:00:01    2020-07-09 11:11:11
2     8                Color        Green    2020-07-09 11:11:11    9999-12-31 23:59:59

这意味着在 2020 年 7 月 6 日 05:00:00 创建 ID = 8 的产品时，添加了 2 个属性，然后将其中一个记录编辑为从“蓝色”更改为“绿色”。请注意，保存时第二行的 SysStartTime 有 1 秒的差异。

现在我需要编写一个查询以获得以下结果。基本上，它是发生变化时不同时间快照中的属性值。时间已经到了分钟。

Start Time          End Time            Attributes Values
----------------    ----------------    -----------------
2020-07-06 05:00    2020-07-09 11:11    Size = S, Color = Blue
2020-07-09 11:11    NULL                Size = S, Color = Green

我怎样才能做到这一点？每个产品可能有不同的属性，但查询一次只针对一个产品。

标签： sql-servertsqltemporal-tables

解决方案

下面是一种在一个查询中格式化数据的解决方案。对于 4 行的小型数据集（我在您的示例中添加了一行），性能不是问题，但我猜这对于数百万条记录来说不会很快。

此处提供的解决方案以公用表表达式(CTE)的形式生成不同的数据集，并使用其他 StackOverflow 答案中的一些技术来删除秒数并连接行值。最后加上一个叉号。

该方法可以在与连续 CTE / 连接对应的步骤中进行描述：

  1. 为每个产品创建一组属性。
  2. 为每个产品创建一组周期开始时刻（省略秒数）。
  3. 将每个产品的属性与每个时期相结合，并寻找合适的值。
  4. 使用一些 XML 函数来格式化单行中的属性值。
  5. 用于cross apply获取期末。

完整解决方案：

-- sample data
declare @data table
(
    ID              int,
    ProductId       int,
    Attribute       nvarchar(10),
    Value           nvarchar(10),
    SysStartTime    datetime2(0),
    SysEndTime      datetime2(0)
);

insert into @data (ID, ProductId, Attribute, Value, SysStartTime, SysEndTime) values
(1, 8, 'Size', 'S', '2020-07-06 05:00:00', '9999-12-31 23:59:59'),
(2, 8, 'Color', 'Blue', '2020-07-06 05:00:01', '2020-07-09 11:11:11'),
(2, 8, 'Color', 'Green', '2020-07-09 11:11:11', '9999-12-31 23:59:59'),
(2, 8, 'Weight', 'Light', '2020-07-10 10:11:12', '9999-12-31 23:59:59'); -- additional data to have extra attribute not available from start

-- solution
with prodAttrib as -- attributes per product
(
    select d.ProductId, d.Attribute
    from @data d
    group by d.ProductId, d.Attribute
),
prodPeriod as -- periods per product
(
    select  d.ProductId,
            dateadd(minute, datediff(minute, 0, d.SysStartTime), 0) as 'SysStartTimeNS' -- start time No Seconds
    from @data d
    group by ProductId, dateadd(minute, datediff(minute, 0, d.SysStartTime), 0)
),
prodResult as -- attribute value per period per product
(
    select  pp.ProductId,
            convert(nvarchar(16), pp.SysStartTimeNS, 120) as 'FromDateTime',
            convert(nvarchar(16), coalesce(pe.SysEndTime, '9999-12-31 23:59:59'), 120) as 'ToDateTime',
            pa.Attribute,
            av.Value
    from prodPeriod pp
    join prodAttrib pa
        on  pa.ProductId = pp.ProductId
    outer apply (   select top 1 d.Value
                    from @data d
                    where d.ProductId = pp.ProductId
                      and d.Attribute = pa.Attribute
                      and dateadd(minute, datediff(minute, 0, d.SysStartTime), 0) <= pp.SysStartTimeNS
                    order by d.SysStartTime desc ) av -- attribute values per product
    outer apply (   select top 1 dateadd(second, -1, d.SysStartTime) as 'SysEndTime'
                    from @data d
                    where d.ProductId = pp.ProductId
                      and dateadd(minute, datediff(minute, 0, d.SysStartTime), 0) > pp.SysStartTimeNS
                    order by d.SysStartTime ) pe -- period end
),
prodResultFormat as -- concatenate attribute values per period
(
    select  pp.ProductId,
            convert(nvarchar(16), pp.SysStartTimeNS, 120) as 'FromDateTime',
            (
                select pr.Attribute + ' = ' + coalesce(pr.Value,'') + ', ' as [text()]
                from prodResult pr
                where pr.ProductId = pp.ProductId
                  and pr.FromDateTime = convert(nvarchar(16), pp.SysStartTimeNS, 120)
                order by pr.Attribute
                for xml path('')
            ) as 'Attributes'
    from prodPeriod pp
)
select  prf.ProductId,
        prf.FromDateTime,
        x.ToDateTime,
        left(prf.Attributes, len(prf.Attributes)-1) as 'Attributes'
from prodResultFormat prf
cross apply (   select top 1 pr.ToDateTime
                from prodResult pr
                where pr.ProductId = prf.ProductId
                  and pr.FromDateTime = prf.FromDateTime ) x
order by prf.ProductId, prf.FromDateTime;

扩展示例数据的结果：

ProductId    FromDateTime      ToDateTime        Attributes
-----------  ----------------  ----------------  ----------------------------------------
8            2020-07-06 05:00  2020-07-09 11:11  Color = Blue, Size = S, Weight = 
8            2020-07-09 11:11  2020-07-10 10:11  Color = Green, Size = S, Weight = 
8            2020-07-10 10:11  9999-12-31 23:59  Color = Green, Size = S, Weight = Light

如果您确实需要这些值，请替换x.EndDateTime为PS 。case when x.ToDateTime = '9999-12-31 23:59' then NULL else x.ToDateTime end as 'ToDateTime'NULL

推荐阅读