sql - 重叠日期范围(SQL Server 2008)按天分组的最有效方法是什么？

Question

考虑如下的简化表 T1:

CREATE TABLE dbo.T1 (
    id        INTEGER       NOT NULL
    ,measure  NUMERIC(15,2) NOT NULL
    ,begin_dt DATE          NOT NULL
    ,end_dt   DATE          NOT NULL
);

假设约束/业务逻辑确保虽然每个 id 可以有多个记录，但单个 id 没有重叠的日期范围，单个 id 没有日期范围间隙。例如。，

id   | measure |  begin_dt  |   end_dt
-----------------------------------------
1    |  100.00 | 2012-05-07 | 2012-05-30
1    |  200.00 | 2012-05-31 | 2013-10-11
1    |   50.00 | 2013-10-12 | 2013-10-13
1    |    0.00 | 2013-10-14 | 9999-12-31
2    | 1234.56 | 2002-02-25 | 9999-12-31
3    |    9.87 | 2014-01-31 | 2014-02-15
3    |   50.00 | 2014-02-16 | 2015-01-04
3    |    0.00 | 2015-01-05 | 9999-12-31
...

现在，我的目标是生成一个结果集，该结果集显示 T1 中每个唯一 begin_dt 的一条记录，以及具有正度量值的 id 计数以及该日期介于 begin_dt 和 end_dt 之间的所有 id 的度量字段的总和.因此，类似于以下内容:

    dt     | count_of_ids | sum_of_measure 
-------------------------------------------
2002-02-25 |      1       |   1234.56 
2012-05-07 |      2       |   1334.56 
2012-05-31 |      2       |   1434.56 
2013-10-12 |      2       |   1284.56 
2013-10-14 |      1       |   1234.56 
2014-01-31 |      2       |   1244.43 
2014-02-16 |      2       |   1284.56
2015-01-05 |      1       |   1234.56
... 

我目前的解决方案基本上如下:

SELECT *
FROM (
    SELECT DISTINCT t1.begin_dt AS dt
    FROM dbo.T1 AS t1
) AS dt_s
CROSS APPLY (
    SELECT COUNT(t1.id)     AS count_of_ids
           ,SUM(t1.measure) AS sum_of_measure
    FROM dbo.T1 AS t1
    WHERE t1.measure > 0
          AND dt_s.dt BETWEEN t1.begin_dt AND t1.end_dt
) AS t1_x
ORDER BY dt_s.dt DESC;

这大约需要 3.5 分钟来执行(在具有 ~10MM 记录、~2,500 个唯一日期和更多要处理的字段、度量和聚合的实际数据集上) - 我希望有一种方法可以让 <10 秒左右.

我尝试过其他方法(使用 UDF/CTE/等)，但它们似乎都遵循相同的执行计划。我对事物的优化方面还没有太多经验，所以我很想听听其他人的最佳通用方法是什么。提前致谢!

Answer 1

尝试使用以下代码:

SELECT  t1.begin_dt AS dt,COUNT(t2.id) AS count_of_ids,SUM(t1.measure) AS sum_of_measure
    FROM dbo.T1 AS t1
    JOIN dbo.T1 AS t2 ON t1.begin_dt BETWEEN t2.begin_dt AND t2.end_dt
    GROUP BY t1.begin_dt;

通过在 begin_dt、end_dt 上使用索引并转换字段 ID 和度量，肯定可以提高性能。
希望这可以帮助!