从每日表到日期范围表转换的SQL

Question

我需要将以下输入表转换为输出表，其中输出表将具有范围，而不是每天的数据。

输入：

Asin day is_instock
--------------------    
 A1   1      0
 A1   2      0
 A1   3      1
 A1   4      1
 A1   5      0
 A2   3      0
 A2   4      0

输出：

asin start_day end_day is_instock
---------------------------------
 A1      1        2       0
 A1      3        4       1
 A1      5        5       0
 A2      3        4       0

Answer 1

这就是所谓的“差距和岛屿”问题。如果你使用这个搜索词，你可以找到大量的文章和参考资料。

解决办法如下：

/*Data setup*/
DROP TABLE IF EXISTS #Stock
CREATE TABLE #Stock ([Asin] Char(2),[day] int,is_instock bit)

INSERT INTO #Stock
VALUES
 ('A1',1,0)
,('A1',2,0)
,('A1',3,1)
,('A1',4,1)
,('A1',5,0)
,('A2',3,0)
,('A2',4,0);

/*Solution*/
WITH cte_Prev AS (
    SELECT *
        /*Compare previous day's stock status with current row's status. Every time it changes, return 1*/
        ,StockStatusChange = CASE WHEN is_instock = LAG(is_instock) OVER (PARTITION BY [Asin] ORDER BY [day]) THEN 0 ELSE 1 END
    FROM #Stock
)
,cte_Groups AS (
    /*Cumulative sum so everytime stock status changes, add 1 from StockStatusChange to begin the next group*/
    SELECT GroupID = SUM(StockStatusChange) OVER (PARTITION BY [Asin] ORDER BY [day])
        ,*
    FROM cte_Prev
)

SELECT [Asin]
    ,start_day = MIN([day])
    ,end_day = MAX([day])
    ,is_instock
FROM cte_Groups
GROUP BY [Asin],GroupID,is_instock

Answer 2

您正在寻找在时态数据文献中描述的运算符，以及“最著名的”PACK。

这个操作符不是SQL标准( SQL :2011)的一部分，该标准将文献的时间特性引入到语言中，所以您很难在任何SQL产品/方言中找到支持您的任何东西。

归结为:您必须写出算法才能自己完成PACKing。