sql - 合并来自同一数据集的不同时期的总和

Question

我经常遇到需要比较来自同一来源的不同时期的聚合数据的情况。

我一般是这样处理的:

SELECT
    COALESCE(SalesThisYear.StoreId, SalesLastYear.StoreId) StoreId
    , SalesThisYear.Sum_Revenue RevenueThisYear
    , SalesLastYear.Sum_Revenue RevenueLastYear
FROM
    (
        SELECT   StoreId, SUM(Revenue) Sum_Revenue
        FROM     Sales
        WHERE    Date BETWEEN '2017-09-01' AND '2017-09-30'
        GROUP BY StoreId
    ) SalesThisYear
    FULL JOIN (
        SELECT   StoreId, SUM(Revenue) Sum_Revenue
        FROM     Sales
        WHERE    Date BETWEEN '2016-09-01' AND '2016-09-30'
        GROUP BY StoreId
    ) SalesLastYear
    ON (SalesLastYear.StoreId = SalesThisYear.StoreId)

-- execution time 337 ms

在我看来这不是很优雅，因为它访问了表两次，但它有效。

另一种类似的实现方式是:

SELECT
    Sales.StoreId
    , SUM(CASE YEAR(Date) WHEN 2017 THEN Revenue ELSE 0 END) RevenueThisYear
    , SUM(CASE YEAR(Date) WHEN 2016 THEN Revenue ELSE 0 END) RevenueLastYear
FROM
    Sales
WHERE
    Date BETWEEN '2017-09-01' AND '2017-09-30' 
    or Date BETWEEN '2016-09-01' AND '2016-09-30'
GROUP BY
    StoreId

-- execution time 548 ms

两种解决方案在我的数据集上的表现几乎相同(选定时间段内有 1,929,419 行，所有索引都在各自的位置)，第一个在时间上稍好一些。如果我包含更多时间段也没关系，第一个在我的数据集上总是更好。

这只是一个简单的例子，但有时，它涉及两个以上的区间甚至一些逻辑(例如比较 isoweek/weekday 而不是 month/day，比较不同的商店等)。

虽然我已经想出了几种实现相同目标的方法，但我想知道是否有更聪明的方法来实现相同目标。也许是更清洁的解决方案，或者更适合大数据集(超过 TB)。

例如，我认为第二个对于大数据集来说资源密集度较低，因为它对表进行了一次索引扫描。另一方面，第一个需要两次索引扫描和一次合并。如果表太大，内存放不下，会发生什么？还是第一个总是更好？

Answer 1

很少有这种做事方式总是更好，尤其是当他们在做非常相似的事情时。

不过，我要建议的是，您尽可能尝试利用最佳实践，例如尽量减少在查询中使用标量函数，因为这会抑制索引的使用。

例如，通过将您的第二个查询更改为以下内容，我想您至少会看到一些性能方面的改进:

SELECT
    Sales.StoreId
    , SUM(CASE WHEN Date BETWEEN '2017-09-01' AND '2017-09-30' THEN Revenue ELSE 0 END) RevenueThisYear
    , SUM(CASE WHEN Date BETWEEN '2016-09-01' AND '2016-09-30' THEN Revenue ELSE 0 END) RevenueLastYear
FROM
    Sales
WHERE
    Date BETWEEN '2017-09-01' AND '2017-09-30' 
    or Date BETWEEN '2016-09-01' AND '2016-09-30'
GROUP BY
    StoreId