在T-SQL中有条件地合并和聚合相邻行

Question

我有一个100K行的表，表示特定时间段内的销售额。通常情况下，周期至少有几个小时，但偶尔也会有几分钟的周期。这些小周期搞乱了下游报告，所以我想把它们和前一个周期合并起来。任何30分钟或更短的时段都应该与前一时段合并，并将各时段的销售数据相加。在长周期之间可以有零个、一个或多个后续的短周期。数据中没有时间间隔--一个周期的开始总是与前一个周期的结束相同。

什么是好的基于集合的方法(没有游标！)来执行此合并？

现有数据(简化)如下所示：

UnitsSold     Start              End
---------------------------------------------------
10            06-12-2013 08:03   06-12-2013 12:07
12            06-12-2013 12:07   06-12-2013 16:05
1             06-12-2013 16:05   06-12-2013 16:09 
1             06-12-2013 16:09   06-12-2013 16:13 
7             06-12-2013 16:13   06-12-2013 20:10

所需的输出将如下所示：

UnitsSold     Start              End
---------------------------------------------------
10            06-12-2013 08:03   06-12-2013 12:07
14            06-12-2013 12:07   06-12-2013 16:13
7             06-12-2013 16:13   06-12-2013 20:10

不幸的是，我们仍然使用SQL Server2008 R2，所以我们不能利用SQL Server2012中很酷的新窗口函数，这可能会使这个问题更容易有效地解决。

在Merge adjacent rows in SQL?中有一个关于类似问题的很好的讨论。我特别喜欢PIVOT/UNPIVOT解决方案，但我对如何使其适应我的问题感到困惑。

Answer 1

我的想法是

1. create list only with long period
2. find start of next long period with“sum”

sum units with sum

像这样的东西

declare @t table (UnitsSold int, start datetime, finish datetime)

insert into @t values (10, '20130612 08:03',   '20130612 12:07')
insert into @t values (12, '20130612 12:07',   '20130612 16:05')
insert into @t values (1, '20130612 16:05',   '20130612 16:09')
insert into @t values (1, '20130612 16:09',   '20130612 16:13')
insert into @t values (7, '20130612 16:13',   '20130612 20:10')

select
    (select SUM(UnitsSold) from @t t3 where t3.start>=t1.start and t3.finish<=ISNULL(oa.start, t1.finish)) as UnitsSold,
    t1.start,
    ISNULL(oa.start, t1.finish) as finish
from @t t1
outer apply (
    select top(1) start
    from @t t2
    where datediff(minute,t2.start, t2.finish)>30 
    and t2.start >= t1.finish
    order by t2.start
) oa
where datediff(minute, t1.start, t1.finish)>30 

Answer 2

使用递归CTE：

DECLARE @t TABLE (UnitsSold INT, Start DATETIME, Finish DATETIME)
INSERT INTO @t VALUES
    (10, '06-12-2013 08:03', '06-12-2013 12:07'),
    (12, '06-12-2013 12:07', '06-12-2013 16:05'),
    (1, '06-12-2013 16:05', '06-12-2013 16:09'),
    (1, '06-12-2013 16:09', '06-12-2013 16:13'),
    (7, '06-12-2013 16:13', '06-12-2013 20:10')

;WITH rec AS (
    -- Returns periods > 30 minutes
    SELECT u.UnitsSold, u.Start, u.Finish
    FROM @t u WHERE DATEDIFF(MINUTE, u.Start, u.Finish) > 30
    UNION ALL
    -- Adds on adjoining periods <= 30 minutes
    SELECT
        u.UnitsSold + r.UnitsSold,
        r.Start,
        u.Finish
    FROM rec r
    INNER JOIN @t u ON r.Finish = u.Start
    AND DATEDIFF(MINUTE, u.Start, u.Finish) <= 30)

-- Since the CTE also returns incomplete periods we need
-- to filter out the relevant periods, in this case the
-- last/max values for each start value.
SELECT
    MAX(r.UnitsSold) AS UnitsSold,
    r.Start AS Start,
    MAX(r.Finish) AS Finish
FROM rec r
GROUP BY r.Start

Answer 3

使用CTE和累积和：

DECLARE @t TABLE (UnitsSold INT, Start DATETIME, Finish DATETIME)
INSERT INTO @t VALUES
    (10, '06-12-2013 08:03', '06-12-2013 12:07'),
    (12, '06-12-2013 12:07', '06-12-2013 16:05'),
    (1, '06-12-2013 16:05', '06-12-2013 16:09'),
    (1, '06-12-2013 16:09', '06-12-2013 16:13'),
    (7, '06-12-2013 16:13', '06-12-2013 20:10')

;WITH groups AS (
    SELECT UnitsSold, Start, Finish,
        -- Cumulative sum, IIF returns 1 for each row that
        -- should generate a new row in the final result.
        SUM(IIF(DATEDIFF(MINUTE, Start, Finish) <= 30, 0, 1)) OVER (ORDER BY Start) csum
    FROM @t)

SELECT 
    SUM(UnitsSold) UnitsSold,
    MIN(Start) Start,
    MAX(Finish) Finish
FROM groups
GROUP BY csum