组多个日期范围上的行

Question

我正在尝试解决我认为(至少)两个问题，因此可能已经存在部分回答这个问题的答案，但我不从根本上理解我试图用数据做什么以将其转化为我想要的最终形式的概念。

问题是:我有三组数据(姑且称它们为气体、液体和固体)，它们发生在更长的一段时间内，姑且称其为观察期。请求是显示观察期的GasPeriod数据(如果存在)，然后是Liquid，然后是Solid。对于给定的状态，只会有0或1个活动记录。

Gas  X----X       X----X    X---------X 1
Liq  X-------X    X--------XX---------X 2
Sol  X--------------XX----------------X 3
Need 1----12-23--31----12--21---------1

我需要的是将这8个范围(3个GasPeriod, 3个LiquidPeriod, 2个SolidPeriod)减少到6行，包含6个日期范围和来自"获胜"的数据。

保存给定重叠时间段的PeriodTemp和Description。任何解决方案都会很有帮助，但我也非常感谢这里的实际问题的分解，这样我就可以自学我正在做的事情。我怀疑所涉及的问题步骤如下:

• 返回8行观测周期(必须包含GasPeriod, LiquidPeriod和SolidPeriod?)
• 按日期范围分组并对它们进行排名(我已经看到涉及LAG和DENSE_RANK的解决方案，但我不熟悉后者，我只使用LAG来解决不同的问题，所以它让我有点困惑它是如何工作的。
• 选择给定日期范围的获奖记录(我假设这是使用子查询解决的)

编辑清楚

create table ObservationPeriod (
ObservationPeriodId    BIGINT    IDENTITY (1,1) NOT NULL,
BusinessKey            BIGINT    NOT NULL,
Effective              DATETIME2 NOT NULL,
Expiry                 DATETIME2 NOT NULL
)
create table GasPeriod (
GasPeriodId            BIGINT    IDENTITY (1,1) NOT NULL,
BusinessKey            BIGINT    NOT NULL,
PeriodTemp             DECIMAL (11, 5) NOT NULL,
Description            NVARCHAR(100) NOT NULL,
Effective              DATETIME2 NOT NULL,
Expiry                 DATETIME2 NOT NULL
)
create table LiquidPeriod (
LiquidPeriodId         BIGINT    IDENTITY (1,1) NOT NULL,
BusinessKey            BIGINT    NOT NULL,
PeriodTemp             DECIMAL (11, 5) NOT NULL,
Description            NVARCHAR(100) NOT NULL,
Effective              DATETIME2 NOT NULL,
Expiry                 DATETIME2 NOT NULL
)
create table SolidPeriod (
SolidPeriodId          BIGINT    IDENTITY (1,1) NOT NULL,
BusinessKey            BIGINT    NOT NULL,
PeriodTemp             DECIMAL (11, 5) NOT NULL,
Description            NVARCHAR(100) NOT NULL,
Effective              DATETIME2 NOT NULL,
Expiry                 DATETIME2 NOT NULL
)
create table ObservationPeriodObserved (
ObservationPeriodObservedId BIGINT IDENTITY(1,1) NOT NULL,
BusinessKey            BIGINT    NOT NULL,
PeriodTemp             DECIMAL (11, 5) NOT NULL,
Description            NVARCHAR(100) NOT NULL,
Effective              DATETIME2 NOT NULL,
Expiry                 DATETIME2 NOT NULL
)

ObservationPeriod数据

2021-12-31

下面的方法首先创建三个数据集Gas、Liquid和Solid的并集。在这个联合中创建了一个附加列PeriodPriority，它将帮助选择获胜行。我将获胜行解释为在观测期内发生的周期条目，是最近的，尚未过期，并将根据Gas-1,Liquid-2和Solid-3的排名进行选择。这构成了DENSE_RANK窗口函数的基础，因为它是按最近过期日期和PeriodPriority排序的。由于这条获奖记录的日期可能超过观察期，因此我使用了case表达式来确保插入的值在观察期内。

虽然只有一个观察期，但我仍然包括where子句WHERE op.ObservationPeriodId=1，您可以根据需要更新/删除。我也加入了BusinessKey，因为我不确定这是否会在你的整个设置中改变。如果BusinessKey永远不会改变，那么可以从连接表达式中省略它。

结果查询当前看起来像这样

SELECT 
ROW_NUMBER() OVER (ORDER BY c.Effective, c.Expiry ) as ObservationPeriodObservedId,
c.BusinessKey,
c.PeriodTemp, 
c.Description,
c.Effective,
CASE 
WHEN c.Expiry >= op.Expiry THEN op.Expiry
ELSE c.Expiry
END as Expiry
FROM (
SELECT 
*,
DENSE_RANK() OVER (ORDER BY t.Expiry DESC, t.PeriodPriority ) rk
FROM ( 
SELECT 
BusinessKey,PeriodTemp, Description,Effective,Expiry, 1 as PeriodPriority 
FROM GasPeriod
UNION ALL
SELECT 
BusinessKey,PeriodTemp, Description,Effective,Expiry, 2 
FROM LiquidPeriod
UNION ALL
SELECT 
BusinessKey,PeriodTemp, Description,Effective,Expiry, 3 
FROM SolidPeriod
) t
) c
INNER JOIN ObservationPeriod op ON c.BusinessKey=op.BusinessKey AND
op.Effective <= c.Effective AND
(op.Expiry >= c.Expiry OR rk=1)
WHERE op.ObservationPeriodId=1
ORDER BY c.Effective, c.Expiry

和insert语句

INSERT INTO ObservationPeriodObserved (BusinessKey, PeriodTemp, Description, Effective, Expiry)
SELECT 
c.BusinessKey,
c.PeriodTemp, 
c.Description,
c.Effective,
CASE 
WHEN c.Expiry >= op.Expiry THEN op.Expiry
ELSE c.Expiry
END as Expiry
FROM (
SELECT 
*,
DENSE_RANK() OVER (ORDER BY t.Expiry DESC, t.PeriodPriority ) rk
FROM ( 
SELECT 
BusinessKey,PeriodTemp, Description,Effective,Expiry, 1 as PeriodPriority 
FROM GasPeriod
UNION ALL
SELECT 
BusinessKey,PeriodTemp, Description,Effective,Expiry, 2 
FROM LiquidPeriod
UNION ALL
SELECT 
BusinessKey,PeriodTemp, Description,Effective,Expiry, 3 
FROM SolidPeriod
) t
) c
INNER JOIN ObservationPeriod op ON c.BusinessKey=op.BusinessKey AND
op.Effective <= c.Effective AND
(op.Expiry >= c.Expiry OR rk=1)
WHERE op.ObservationPeriodId=1
ORDER BY c.Effective, c.Expiry

生成期望的结果。

查看工作状态

编辑1 -连续有效，到期日期

根据更新的问题和评论，我已经修改了上面的LAG和DATE_ADD来提供连续的日期。下面包括插入查询(后一部分是SELECT)以及提供所需结果的更新数据库。这里的例外是SolidPeriod的record 1，它的结束日期为2021-06-30。当将该日期更改为2021-05-31(如您期望的结果)时，查询更正了与预期不同的1个日期。如果这里有额外的考虑，或者样本数据有错误，请告诉我。我对样本数据进行了调整，而不是临时计算，因为我不能假设一些逻辑来做出这样的改变(即在任意记录上减去1个月)。让我知道这是否适用于你，并进一步建议。

INSERT INTO ObservationPeriodObserved (BusinessKey, PeriodTemp, Description, Effective, Expiry)
SELECT 
c.BusinessKey,
c.PeriodTemp, 
c.Description,
CASE
WHEN LAG(c.Expiry) OVER (ORDER BY c.Effective, c.Expiry) IS NULL THEN op.Effective
ELSE DATEADD(DAY,1,LAG(c.Expiry) OVER (ORDER BY c.Effective, c.Expiry))
END as Effective,
CASE 
WHEN c.Expiry >= op.Expiry THEN op.Expiry
ELSE c.Expiry
END as Expiry

FROM (
SELECT 
*,
DENSE_RANK() OVER (ORDER BY t.Expiry DESC, t.PeriodPriority ) rk
FROM ( 
SELECT 
BusinessKey,PeriodTemp, Description,Effective,Expiry, 1 as PeriodPriority 
FROM GasPeriod
UNION ALL
SELECT 
BusinessKey,PeriodTemp, Description,Effective,Expiry, 2 
FROM LiquidPeriod
UNION ALL
SELECT 
BusinessKey,PeriodTemp, Description,Effective,Expiry, 3 
FROM SolidPeriod
) t
) c
INNER JOIN ObservationPeriod op ON c.BusinessKey=op.BusinessKey AND
op.Effective <= c.Effective AND
(op.Expiry >= c.Expiry OR rk=1)
WHERE op.ObservationPeriodId=1
ORDER BY c.Effective, c.Expiry 

查看Demo DB Fiddle

让我知道这是否适合你。