sql - 间隙和岛屿 : Splitting Islands Based On External Table

Question

我的场景一开始类似于孤岛和差距问题，我需要找到连续的工作天数。我当前的 SQL 查询回答“ProductA 是在 LocationA 从 DateA 到 DateB 生产的，总共 X 数量”。

但是，当我需要将价格纳入其中时，这还不够。价格位于单独的表中，并在事后用 C# 进行处理。价格变化本质上是一个记录列表，上面写着“LocationA 的 ProductA 现在是在 DateC 生效的每单位 Y 值”。

最终结果是，只要岛屿不与价格更改日期重叠，它就可以工作，但如果确实重叠，我会得到“接近”答案，但并不精确。

C# 代码可以有效地处理应用价格，但我需要做的是根据价格变化分割岛屿。我的目标是让 SQL 的分区考虑到其他表中的天数排名，但我在应用我想要做的事情时遇到了困难。

<小时/>

当前生成我的岛屿的SQL如下

SELECT MIN(ScheduledDate) as StartDate, MAX(ScheduledDate) as 
EndDate, ProductId, DestinationId, SUM(Quantity) as TotalQuantity
FROM (
    SELECT ScheduledDate, DestinationId, ProductId, PartitionGroup = DATEADD(DAY ,-1 * DENSE_RANK() OVER (ORDER BY ScheduledDate), ScheduledDate), Quantity
    FROM History
) tmp
GROUP BY PartitionGroup, DestinationId, ProductId;

当前从 PriceChange 表获取并对日期进行排名的 SQL 如下

DECLARE @PriceChangeDates TABLE(Rank int, SplitDate Date);
INSERT INTO @PriceChangeDates
SELECT DENSE_RANK() over (ORDER BY EffectiveDate) as Rank, EffectiveDate as SplitDate
FROM ProductPriceChange
GROUP BY EffectiveDate;

<小时/>

我的想法是以某种方式更新第一个查询内部 SELECT 语句，以某种方式利用第二个查询创建的 @PriceChangeDates 表。我认为我们可以将 DATEADD 的增量参数乘以声明表中的排名，但我很难编写它。

如果我要以某种方式用循环来做到这一点，我的思考过程将是确定 ScheduledDate 在 @PriceChangeDates 表中的排名，其中它的排名是它能找到的比它本身小的最接近日期的排名。然后采用给出的任何排名，我认为将其乘以传入的增量参数(或一些数学，例如对现有参数执行 *@PriceChangeDates.Count() ，然后添加新的排名以避免碰撞)。然而，这是“循环”逻辑而不是“集合”逻辑，在 SQL 中我需要用集合来思考。

<小时/>

非常感谢任何和所有的帮助/建议。谢谢:)

<小时/>

更新:

SQLFiddle 上的示例数据和示例:http://www.sqlfiddle.com/#!18/af568/1

数据在哪里:

CREATE TABLE History
(
ProductId int,
DestinationId int,
ScheduledDate date,
Quantity float
);

INSERT INTO History (ProductId, DestinationId, ScheduledDate, Quantity)
VALUES
  (0, 1000, '20180401', 5),
  (0, 1000, '20180402', 10),
  (0, 1000, '20180403', 7),
  (3, 5000, '20180507', 15),
  (3, 5000, '20180508', 23),
  (3, 5000, '20180509', 52),
  (3, 5000, '20180510', 12),
  (3, 5000, '20180511', 14);

CREATE TABLE PriceChange
(
  ProductId int,
  DestinationId int,
  EffectiveDate date,
  Price float
);

INSERT INTO PriceChange (ProductId, DestinationId, EffectiveDate, Price)
VALUES
  (0, 1000, '20180201', 1),
  (0, 1000, '20180402', 2),
  (3, 5000, '20180101', 5),
  (3, 5000, '20180510', 20);

期望的结果是有一个生成结果的 SQL 语句:

StartDate   EndDate     ProductId   DestinationId   TotalQuantity
2018-04-01  2018-04-01  0           1000            5
2018-04-02  2018-04-03  0           1000            17
2018-05-07  2018-05-09  3           5000            90
2018-05-10  2018-05-11  3           5000            26

需要澄清的是，最终结果确实需要每个拆分金额的 TotalQuantity，因此操纵结果并应用定价的程序代码知道价格变化的每一侧每种产品的数量，以准确确定值.

Answer 1

这是另一个可能比我的第一个答案表现更好的变体。我决定将其作为第二个答案，因为方法相当不同，而且答案太长。您应该将所有变体的性能与硬件上的真实数据进行比较，并且不要忘记索引。

在第一个变体中，我使用 APPLY 为 History 表中的每一行选择相关价格。对于 History 表中的每一行，引擎都会从 PriceChange 表中搜索相关行。即使在 PriceChange 表上有适当的索引，当通过单次查找完成此操作时，它仍然意味着循环连接中有 370 万次查找。

我们可以简单地将 History 和 PriceChange 表连接在一起，并在两个表上使用适当的索引，这将是一个有效的合并连接。

在这里，我还使用扩展的示例数据集来说明差距。我将这些行添加到问题的示例数据中。

INSERT INTO History (ProductId, DestinationId, ScheduledDate, Quantity)
VALUES
  (0, 1000, '20180601', 5),
  (0, 1000, '20180602', 10),
  (0, 1000, '20180603', 7),
  (3, 5000, '20180607', 15),
  (3, 5000, '20180608', 23),
  (3, 5000, '20180609', 52),
  (3, 5000, '20180610', 12),
  (3, 5000, '20180611', 14);

中间查询

我们在这里执行FULL JOIN，而不是LEFT JOIN，因为价格更改的日期可能不会出现在历史记录中 根本没有表。

WITH
CTE_Join
AS
(
    SELECT
        ISNULL(History.ProductId, PriceChange.ProductID) AS ProductID
        ,ISNULL(History.DestinationId, PriceChange.DestinationId) AS DestinationId
        ,ISNULL(History.ScheduledDate, PriceChange.EffectiveDate) AS ScheduledDate
        ,History.Quantity
        ,PriceChange.Price
    FROM
        History
        FULL JOIN PriceChange
            ON  PriceChange.ProductID = History.ProductID
            AND PriceChange.DestinationId = History.DestinationId
            AND PriceChange.EffectiveDate = History.ScheduledDate
)
,CTE2
AS
(
    SELECT
        ProductID
        ,DestinationId
        ,ScheduledDate
        ,Quantity
        ,Price
        ,MAX(CASE WHEN Price IS NOT NULL THEN ScheduledDate END)
            OVER (PARTITION BY ProductID, DestinationId ORDER BY ScheduledDate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
    FROM CTE_Join
)
SELECT *
FROM CTE2
ORDER BY
    ProductID
    ,DestinationId
    ,ScheduledDate

创建以下索引

CREATE UNIQUE NONCLUSTERED INDEX [IX_History] ON [dbo].[History]
(
    [ProductId] ASC,
    [DestinationId] ASC,
    [ScheduledDate] ASC
)
INCLUDE ([Quantity])

CREATE UNIQUE NONCLUSTERED INDEX [IX_Price] ON [dbo].[PriceChange]
(
    [ProductId] ASC,
    [DestinationId] ASC,
    [EffectiveDate] ASC
)
INCLUDE ([Price])

并且该连接将是执行计划中高效的MERGE连接(而不是LOOP连接)

中间结果

+-----------+---------------+---------------+----------+-------+------------+
| ProductID | DestinationId | ScheduledDate | Quantity | Price |    grp     |
+-----------+---------------+---------------+----------+-------+------------+
|         0 |          1000 | 2018-02-01    | NULL     | 1     | 2018-02-01 |
|         0 |          1000 | 2018-04-01    | 5        | NULL  | 2018-02-01 |
|         0 |          1000 | 2018-04-02    | 10       | 2     | 2018-04-02 |
|         0 |          1000 | 2018-04-03    | 7        | NULL  | 2018-04-02 |
|         0 |          1000 | 2018-06-01    | 5        | NULL  | 2018-04-02 |
|         0 |          1000 | 2018-06-02    | 10       | NULL  | 2018-04-02 |
|         0 |          1000 | 2018-06-03    | 7        | NULL  | 2018-04-02 |
|         3 |          5000 | 2018-01-01    | NULL     | 5     | 2018-01-01 |
|         3 |          5000 | 2018-05-07    | 15       | NULL  | 2018-01-01 |
|         3 |          5000 | 2018-05-08    | 23       | NULL  | 2018-01-01 |
|         3 |          5000 | 2018-05-09    | 52       | NULL  | 2018-01-01 |
|         3 |          5000 | 2018-05-10    | 12       | 20    | 2018-05-10 |
|         3 |          5000 | 2018-05-11    | 14       | NULL  | 2018-05-10 |
|         3 |          5000 | 2018-06-07    | 15       | NULL  | 2018-05-10 |
|         3 |          5000 | 2018-06-08    | 23       | NULL  | 2018-05-10 |
|         3 |          5000 | 2018-06-09    | 52       | NULL  | 2018-05-10 |
|         3 |          5000 | 2018-06-10    | 12       | NULL  | 2018-05-10 |
|         3 |          5000 | 2018-06-11    | 14       | NULL  | 2018-05-10 |
+-----------+---------------+---------------+----------+-------+------------+

您可以看到 Price 列有很多 NULL 值。我们需要用前面的非 NULL 值“填充”这些 NULL 值。

Itzik Ben-Gan 写了一篇很好的文章，展示了如何有效地解决这个问题 The Last non NULL Puzzle 。另请参阅Best way to replace NULL with most recent non-null value .

这是在 CTE2 中使用 MAX 窗口函数完成的，您可以看到它如何填充 grp 列。这需要 SQL Server 2012+。确定组后，我们应该删除 Quantity 为 NULL 的行，因为这些行不是来自 History 表。

现在我们可以使用 grp 列作为附加分区来执行相同的间隙和岛屿步骤。

查询的其余部分与第一个变体几乎相同。

最终查询

WITH
CTE_Join
AS
(
    SELECT
        ISNULL(History.ProductId, PriceChange.ProductID) AS ProductID
        ,ISNULL(History.DestinationId, PriceChange.DestinationId) AS DestinationId
        ,ISNULL(History.ScheduledDate, PriceChange.EffectiveDate) AS ScheduledDate
        ,History.Quantity
        ,PriceChange.Price
    FROM
        History
        FULL JOIN PriceChange
            ON  PriceChange.ProductID = History.ProductID
            AND PriceChange.DestinationId = History.DestinationId
            AND PriceChange.EffectiveDate = History.ScheduledDate
)
,CTE2
AS
(
    SELECT
        ProductID
        ,DestinationId
        ,ScheduledDate
        ,Quantity
        ,Price
        ,MAX(CASE WHEN Price IS NOT NULL THEN ScheduledDate END)
            OVER (PARTITION BY ProductID, DestinationId ORDER BY ScheduledDate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
    FROM CTE_Join
)
,CTE_RN
AS
(
    SELECT
        ProductID
        ,DestinationId
        ,ScheduledDate
        ,grp
        ,Quantity
        ,ROW_NUMBER() OVER (PARTITION BY ProductId, DestinationId, grp ORDER BY ScheduledDate) AS rn1
        ,DATEDIFF(day, '20000101', ScheduledDate) AS rn2
    FROM CTE2
    WHERE Quantity IS NOT NULL
)
SELECT
    ProductId
    ,DestinationId
    ,MIN(ScheduledDate) AS StartDate
    ,MAX(ScheduledDate) AS EndDate
    ,SUM(Quantity) AS TotalQuantity
FROM
    CTE_RN
GROUP BY
    ProductId
    ,DestinationId
    ,grp
    ,rn2-rn1
ORDER BY
    ProductID
    ,DestinationId
    ,StartDate
;

最终结果

+-----------+---------------+------------+------------+---------------+
| ProductId | DestinationId | StartDate  |  EndDate   | TotalQuantity |
+-----------+---------------+------------+------------+---------------+
|         0 |          1000 | 2018-04-01 | 2018-04-01 |             5 |
|         0 |          1000 | 2018-04-02 | 2018-04-03 |            17 |
|         0 |          1000 | 2018-06-01 | 2018-06-03 |            22 |
|         3 |          5000 | 2018-05-07 | 2018-05-09 |            90 |
|         3 |          5000 | 2018-05-10 | 2018-05-11 |            26 |
|         3 |          5000 | 2018-06-07 | 2018-06-11 |           116 |
+-----------+---------------+------------+------------+---------------+

此变体不会输出相关价格(作为第一个变体)，因为我简化了“最后一个非空”查询。问题中没有要求。无论如何，如果需要的话，添加价格是相当容易的。