在多個日期范圍內對行進行分組

我想通過這個問題同時解決我認為的（至少）兩個問題，所以可能已經存在部分回答這個問題的答案，但是我并沒有從根本上理解我想對資料進行處理以使其成為我所期望的最終形式的概念。

問題：我有三粒資料（我們姑且稱它們為氣體、液體、固體），它們發生在一個較大的時間段內，我們姑且稱其為觀察期。我的要求是，在觀察期內，如果有GasPeriod資料，就顯示它，然后是液體，再是固體。對于一個給定的狀態，只會有0或1條有效記錄。

Gas X----X X----X X_901X 1
Liq X-------X X_901XX---------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和描述。

任何解決方案都將是有幫助的，但我也非常感謝對實際問題的任何分解，這樣我就可以了解我在做什么。我懷疑涉及的問題步驟是：

• 回傳8行
• 回傳觀察期的8行（必須涉及GasPeriod、LiquidPeriod和SolidPeriod的聯合？
• 按日期范圍對記錄進行分組，并對它們進行排序（我看到過涉及LAG和DENSE_RANK的解決方案，但我對后者并不熟悉，我只用過LAG來解決一個不同的問題，所以它的作用讓我有點困惑。
• 在給定的日期范圍內挑選獲勝記錄（我想這是用子查詢來解決的）

為清晰起見，進行了編輯

create table ObservationPeriod (
    ObservationPeriodId BIGINT IDENTITY ( 1, 1) NOT NULL,
    BusinessKey BIGINT NOT NULL,
    有效期DATETIME2 NOT NULL。
    有效期DATETIME2 NOT NULL.
)

創建 表 GasPeriod (
    GasPeriodId BIGINT IDENTITY ( 1, 1) NOT NULL,
    BusinessKey BIGINT NOT NULL,
    PeriodTemp DECIMAL (11, 5) Not NULL,
    描述 NVARCHAR(100) NOT NULL。
    有效日期DATETIME2 NOT NULL,
    有效期DATETIME2 NOT NULL.
)

創建 表 LiquidPeriod (
    LiquidPeriodId BIGINT IDENTITY ( 1, 1) NOT NULL,
    BusinessKey BIGINT NOT NULL,
    PeriodTemp DECIMAL (11, 5) Not NULL,
    描述 NVARCHAR(100) NOT NULL。
    有效日期DATETIME2 NOT NULL,
    有效期DATETIME2 NOT NULL.
)

創建 表 SolidPeriod (
    SolidPeriodId BIGINT IDENTITY ( 1, 1) NOT NULL,
    BusinessKey BIGINT NOT NULL,
    PeriodTemp DECIMAL (11, 5) Not NULL,
    描述 NVARCHAR(100) NOT NULL。
    有效日期DATETIME2 NOT NULL,
    有效期DATETIME2 NOT NULL.
)

創建 表 ObservationPeriodObserved (
    ObservationPeriodObservedId BIGINT IDENTITY（1, 1) NOT NULL,
    BusinessKey BIGINT NOT NULL,
    PeriodTemp DECIMAL (11, 5) Not NULL,
    描述 NVARCHAR(100) NOT NULL。
    有效日期DATETIME2 NOT NULL,
    有效期DATETIME2 NOT NULL.
)

觀測期資料

氣體周期資料

氣體周期資料 PeriodTemp描述有效的。

GasPeriod	BusinessKey	到期
1	24	101.328	101.328?	第一個g	第一個g?	2020-09-30	2021-03-31[/td
2	24	102.456?	秒g	秒g?	2021-06-01	2021-07-31
3	24	100.011	100.011	第三克	第三克?	2021-09-01	9999-12-31

LiquidPeriod資料

實期資料

ObvservationPeriodObserved資料

我們的想法是，對于ObservationPeriod中的某一行，在上述三個顆粒之間有許多相關的時間段，但在一個特定的時間段中，應該只有一個被記錄為ObservationPeriod的子集。

請同時假設這里必須有單獨的粒度，并且這個問題不能通過將這些資料放入同一個表中來解決--不能。我不能在這里使用實際的業務模型，所以我試圖在概念上盡可能地接近。

uj5u.com熱心網友回復：

下面的方法從創建三個資料集Gas、Liquid和Solid的聯盟開始。在這個聯盟中，創建了一個附加列PeriodPriority，這將有助于選擇獲勝行。我將獲勝的行解釋為發生在觀察期內的、最近的、未過期的時期條目，并將根據Gas-1、Liquid-2和Solid的等級來選擇。這構成了DENSE_RANK視窗函式的基礎，因為它是按照最近的過期日期和PeriodPriority排序的。由于這個獲勝記錄的日期可能超過觀察期，我使用了一個case運算式來確保插入的值在觀察期內。

盡管只有一個觀察期，我仍然包含了where子句WHERE op.ObservationPeriodId=1，你可以根據需要更新/洗掉。我還加入了BusinessKey，因為我不確定這是否會在你的整個集合中改變。如果BusinessKey從未改變，那么可以從連接運算式中省略。

結果查詢目前看起來像這樣

和插入陳述句

INSERT INTO ObservationPeriodObserved (BusinessKey, PeriodTemp, Description, Effective, Expiry)
SELECT 
    c.BusinessKey,
    c.PeriodTemp, 
    c.描述。
    c.Effective。
    CASE> 
        WHEN c.Expiry >= op.Expiry THEN op.Expiry
        ELSE c.expiry
    END as Expiry
FROM (
    SELECT[/span 
        *,
        DENSE_RANK() OVER (ORDER BY t.expiry DESC, t.PeriodPriority ) RK
    FROM ( 
        SELECT[/span 
            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.EffectiveAND
                                   (op.Expiry >= c.Expiry OR rk= 1)
WHERE op.ObservationPeriodId=1。
ORDER BY c.Effective, c.Expiry

產生預期的結果。

在此查看作業的db fiddle

編輯1--連續的有效、失效日期

根據更新的問題和評論，我修改了上述內容，利用LAG和DATE_ADD來提供連續日期。插入查詢（后半部分是一個SELECT）包括在下面，同時還包括一個更新的db fiddle，它提供了期望的結果。這里的例外是SolidPeriod的記錄1，其結束日期為2021-06-30。當這個日期被改變為2021-05-31時，正如你所期望的結果，查詢糾正了1個不符合預期的日期。如果這里有額外的考慮，或者在樣本資料中存在錯誤，請讓我知道。我對樣本資料進行了調整，而不是臨時計算，因為我無法假設一些邏輯來做這樣的改變（即在一個任意的記錄上減去1個月）。讓我知道這是否對你有用，并進一步提出建議。

INSERT INTO ObservationPeriodObserved （BusinessKey, PeriodTemp, Description, Effective, Expiry)

    SELECT 
        c.BusinessKey,
        c.PeriodTemp, 
        c.描述。
        CASE[/span
            WHEN LAG(c.Expiry) OVER (ORDER BYc. 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 CASE 
            WHEN c.expiry >= op.expiry THEN op.expiry
            ELSE c.expiry
        END as Expiry
        
    FROM (
        SELECT[/span 
            *,
            DENSE_RANK() OVER (ORDER BY t.expiry DESC, t.PeriodPriority ) RK
        FROM ( 
            SELECT[/span 
                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.EffectiveAND
                                       (op.Expiry >= c.Expiry OR rk= 1)
    WHERE op.ObservationPeriodId=1。
    ORDER BY c.Effective, c.Expiry 

查看演示DB Fiddle

讓我知道這是否對你有用。

標籤：

上一篇：在SQLServer存盤程序中替代WhileLoop的方法

下一篇：沒有游標的TSQL幫助