SQL server 做类似 Group By 任务

Question

我有一张带有 SQL 服务器的表，如下所示，

Date        Value
---------------------------------------------------
08-01-2016    1
08-02-2016    1
08-03-2016    1
08-04-2016    1
08-05-2016    1
08-06-2016    2
08-07-2016    2
08-08-2016    2
08-09-2016    2.5
08-10-2016    1
08-11-2016    1

由于原始表太大，即使我使用了“Results to file”，它仍然会引发异常“System.OutOfMemoryException”。这就是我想把表格组织成这种的原因。

但我没有很好的逻辑来处理。因此，我想将表格更改为如下所示。

Date_from      Date_to      Value
-------------------------------------------------
08-01-2016     08-05-2016   1
08-06-2016     08-08-2016   2
08-09-2016     08-09-2016   2.5
08-10-2016     08-11-2016   1

我很欣赏你的想法!

Answer 1

通常称为组和岛屿问题。这是做到这一点的一个技巧

;WITH data
    AS (SELECT *,Lag(Value, 1)OVER(ORDER BY Dates) [pVal]
        FROM   (VALUES ('08-01-2016',1 ),
                    ('08-02-2016',1 ),
                    ('08-03-2016',1 ),
                    ('08-04-2016',1 ),
                    ('08-05-2016',1 ),
                    ('08-06-2016',2 ),
                    ('08-07-2016',2 ),
                    ('08-08-2016',2 ),
                    ('08-09-2016',2.5 ),
                    ('08-10-2016',1 ),
                    ('08-11-2016',1 )) tc (Dates, Value)),
     intr
     AS (SELECT Dates,
                Value,
                Sum(Iif(pVal = Value, 0, 1)) OVER(ORDER BY Dates) AS [Counter]
         FROM   data)
SELECT Min(Dates) AS Dates_from,
       Max(Dates) AS Dates_to,
       Value
FROM   intr
GROUP  BY [Counter],
          Value