主页 / dba / 问题 / 282604
Accepted
Michael Green
Asked: 2021-01-06 05:40:40 +0800 CST

分割区间

  • 772

我有两张桌子。每个都包含业务实体的一些属性以及这些属性有效的日期范围。我想将这些表合并为一个，匹配通用业务键上的行并拆分时间范围。

真实世界的示例是两个源时态表，为数据仓库中的类型 2 维表提供数据。

该实体在任何时间点都可以不存在于任何一个源系统中，也可以存在于一个源系统中，也可以同时存在于两个源系统中。一旦实体被记录在源系统中，间隔就会表现良好 - 没有间隙、重复或其他猴子业务。来源中的成员资格可以在不同的日期结束。

业务规则规定我们只想返回实体同时出现在两个源中的时间间隔。

什么查询会给出这个结果？

这说明了这种情况：

Month          J     F     M     A     M     J     J
Source A:  <--><----------><----------><---->
Source B:            <----><----><----------------><-->
               
Result:              <----><----><----><---->

样本数据

为简单起见，我使用了封闭的日期间隔；很可能任何解决方案都可以通过少量输入扩展到半开区间。

drop table if exists dbo.SourceA;
drop table if exists dbo.SourceB;
go

create table dbo.SourceA
(
    BusinessKey int,
    StartDate   date,
    EndDate     date,
    Attribute   char(9)
);

create table dbo.SourceB
(
    BusinessKey int,
    StartDate   date,
    EndDate     date,
    Attribute   char(9)
);
GO


insert dbo.SourceA(BusinessKey, StartDate, EndDate, Attribute)
values
    (1, '19990101', '19990113', 'black'),
    (1, '19990114', '19990313', 'red'),
    (1, '19990314', '19990513', 'blue'),
    (1, '19990514', '19990613', 'green'),
    (2, '20110714', '20110913', 'pink'),
    (2, '20110914', '20111113', 'white'),
    (2, '20111114', '20111213', 'gray');

insert dbo.SourceB(BusinessKey, StartDate, EndDate, Attribute)
values
    (1, '19990214', '19990313', 'left'),
    (1, '19990314', '19990413', 'right'),
    (1, '19990414', '19990713', 'centre'),
    (1, '19990714', '19990730', 'back'),
    (2, '20110814', '20110913', 'top'),
    (2, '20110914', '20111013', 'middle'),
    (2, '20111014', '20120113', 'bottom');

期望的输出

BusinessKey StartDate   EndDate     a_Colour  b_Placement
----------- ----------  ----------  --------- -----------
1           1999-02-14  1999-03-13  red       left     
1           1999-03-14  1999-04-13  blue      right    
1           1999-04-14  1999-05-13  blue      centre   
1           1999-05-14  1999-06-13  green     centre   
2           2011-08-14  2011-09-13  pink      top      
2           2011-09-14  2011-10-13  white     middle   
2           2011-10-14  2011-11-13  white     bottom   
2           2011-11-14  2011-12-13  gray      bottom
sql-server interval

3 个回答

  1. Best Answer

    我可能误解了你的问题，但结果似乎是根据你的问题：

    select a.businesskey
     -- greatest(a.startdate, b.startdate)
     , case when a.startdate > b.startdate 
            then a.startdate 
            else b.startdate 
       end as startdate
     -- least(a.enddate, b.enddate)
     , case when a.enddate < b.enddate 
            then a.enddate 
            else b.enddate 
       end as enddate
     , a.attribute as a_color
     , b.attribute as b_placement
from dbo.SourceA a 
join dbo.SourceB b 
        on a.businesskey = b.businesskey
       and (a.startdate between b.startdate and b.enddate 
          or b.startdate between a.startdate and a.enddate)
order by 1,2

    由于间隔需要重叠，因此大部分工作可以通过将其作为谓词的连接来完成。然后只需选择区间的交集即可。

    LEAST 和 GREATEST 似乎缺少函数，所以我改用了 case 表达式。

    小提琴

    • 2

  2. 此解决方案将源间隔解构为它们的开始日期。通过组合这两个列表，可以获得一组输出间隔开始日期。根据这些，相应的输出结束日期由窗口函数计算。由于最终输出间隔必须在两个输入间隔中的任何一个结束时结束，因此需要进行特殊处理来确定该值。

    ;with Dates as
(
    select BusinessKey, StartDate
    from dbo.SourceA

    union

    select BusinessKey, StartDate
    from dbo.SourceB

    union

    select x.BusinessKey, DATEADD(DAY, 1, MIN(x.EndDate))
    from
    (
        select BusinessKey, EndDate = MAX(EndDate) 
        from dbo.SourceA
        group by BusinessKey

        union all

        select BusinessKey, EndDate = MAX(EndDate) 
        from dbo.SourceB
        group by BusinessKey
    ) as x
    group by x.BusinessKey
),
Intervals as
(
    select
        dt.BusinessKey,
        dt.StartDate,
        EndDate = lead (DATEADD(DAY, -1, dt.StartDate), 1)
                  over (partition by dt.BusinessKey order by dt.StartDate)
    from Dates as dt
)
select
    i.BusinessKey,
    i.StartDate,
    i.EndDate, 
    a_Colour = a.Attribute,
    b_Placement = b.Attribute
from Intervals as i
inner join dbo.SourceA as a
    on i.BusinessKey = a.BusinessKey
    and i.StartDate between a.StartDate and a.EndDate
inner join dbo.SourceB as b
    on i.BusinessKey = b.BusinessKey
    and i.StartDate between b.StartDate and b.EndDate
where i.EndDate is not NULL
order by
    i.BusinessKey,
    i.StartDate;

    “日期”CTE 使用 UNION 而不是 UNION ALL 来消除重复项。如果两个来源在同一日期发生变化，我们只需要一个对应的输出行。

    因为我们想在任一源关闭“日期”中的第三个查询时关闭输出，所以会添加最早的结束日期，即 EndDates 的 MAX 的 MIN。由于它是伪装成 StartDate 的 EndDate，因此必须添加另一天。它的目的是允许窗口函数计算前一个间隔的结束。它将在最终谓词中被消除。

    对最终查询使用内部连接会消除在其他源中没有对应值的源区间。

    • 0

  3. 这个问题有很多有趣的解决方案（用不同的术语表述）here及其前几页。在那里，它在拍卖中表现为供需匹配。供应/需求的单位直接类似于这个问题的间隔天数，因此解决方案可以转化。不过，我已将其保留在链接站点中使用的术语中。

    样本数据。

    DROP TABLE IF EXISTS dbo.Auctions;
 
CREATE TABLE dbo.Auctions
(
  ID INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT pk_Auctions PRIMARY KEY CLUSTERED,
  Code CHAR(1) NOT NULL
    CONSTRAINT ck_Auctions_Code CHECK (Code = 'D' OR Code = 'S'),
  Quantity DECIMAL(19, 6) NOT NULL
    CONSTRAINT ck_Auctions_Quantity CHECK (Quantity > 0)
);
 
SET NOCOUNT ON;
 
DELETE FROM dbo.Auctions;
 
SET IDENTITY_INSERT dbo.Auctions ON;
 
INSERT INTO dbo.Auctions(ID, Code, Quantity) VALUES
  (1, 'D', 5.0),
  (2, 'D', 3.0),
  (3, 'D', 8.0),
  (5, 'D', 2.0),
  (6, 'D', 8.0),
  (7, 'D', 4.0),
  (8, 'D', 2.0),
  (1000, 'S', 8.0),
  (2000, 'S', 6.0),
  (3000, 'S', 2.0),
  (4000, 'S', 2.0),
  (5000, 'S', 4.0),
  (6000, 'S', 3.0),
  (7000, 'S', 2.0);

    所阐述的解决方案将他的 400k 行样本数据的经过时间从天真的 11 秒减少到 0.4 秒。最快的是 Paul White（这个教区的），如图所示。

    DROP TABLE IF EXISTS #MyPairings;
 
CREATE TABLE #MyPairings
(
  DemandID integer NOT NULL,
  SupplyID integer NOT NULL,
  TradeQuantity decimal(19, 6) NOT NULL
);
GO
 
INSERT #MyPairings 
    WITH (TABLOCK)
(
    DemandID,
    SupplyID,
    TradeQuantity
)
SELECT 
    Q3.DemandID,
    Q3.SupplyID,
    Q3.TradeQuantity
FROM 
(
    SELECT
        Q2.DemandID,
        Q2.SupplyID,
        TradeQuantity =
            -- Interval overlap
            CASE
                WHEN Q2.Code = 'S' THEN
                    CASE
                        WHEN Q2.CumDemand >= Q2.IntEnd THEN Q2.IntLength
                        WHEN Q2.CumDemand > Q2.IntStart THEN Q2.CumDemand - Q2.IntStart
                        ELSE 0.0
                    END
                WHEN Q2.Code = 'D' THEN
                    CASE
                        WHEN Q2.CumSupply >= Q2.IntEnd THEN Q2.IntLength
                        WHEN Q2.CumSupply > Q2.IntStart THEN Q2.CumSupply - Q2.IntStart
                        ELSE 0.0
                    END
            END
    FROM
    (
        SELECT 
            Q1.Code, 
            Q1.IntStart, 
            Q1.IntEnd, 
            Q1.IntLength, 
            DemandID = MAX(IIF(Q1.Code = 'D', Q1.ID, 0)) OVER (
                    ORDER BY Q1.IntStart, Q1.ID 
                    ROWS UNBOUNDED PRECEDING),
            SupplyID = MAX(IIF(Q1.Code = 'S', Q1.ID, 0)) OVER (
                    ORDER BY Q1.IntStart, Q1.ID 
                    ROWS UNBOUNDED PRECEDING),
            CumSupply = SUM(IIF(Q1.Code = 'S', Q1.IntLength, 0)) OVER (
                    ORDER BY Q1.IntStart, Q1.ID 
                    ROWS UNBOUNDED PRECEDING),
            CumDemand = SUM(IIF(Q1.Code = 'D', Q1.IntLength, 0)) OVER (
                    ORDER BY Q1.IntStart, Q1.ID 
                    ROWS UNBOUNDED PRECEDING)
        FROM 
        (
            -- Demand intervals
            SELECT 
                A.ID, 
                A.Code, 
                IntStart = SUM(A.Quantity) OVER (
                    ORDER BY A.ID 
                    ROWS UNBOUNDED PRECEDING) - A.Quantity,
                IntEnd = SUM(A.Quantity) OVER (
                    ORDER BY A.ID 
                    ROWS UNBOUNDED PRECEDING),
                IntLength = A.Quantity
            FROM dbo.Auctions AS A
            WHERE 
                A.Code = 'D'
 
            UNION ALL 
 
            -- Supply intervals
            SELECT 
                A.ID, 
                A.Code, 
                IntStart = SUM(A.Quantity) OVER (
                    ORDER BY A.ID 
                    ROWS UNBOUNDED PRECEDING) - A.Quantity,
                IntEnd = SUM(A.Quantity) OVER (
                    ORDER BY A.ID 
                    ROWS UNBOUNDED PRECEDING),
                IntLength = A.Quantity
            FROM dbo.Auctions AS A
            WHERE 
                A.Code = 'S'
        ) AS Q1
    ) AS Q2
) AS Q3
WHERE
    Q3.TradeQuantity > 0;
    • 0

相关问题