Joining sums of different periods from the same data set

Question

I often face the situation where I need to compare aggregated data of different periods from the same source.

I usually deal with it this way:

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

It is not very elegant in my opinion, because it visits the table twice, but it works.

Another similar way to achieve the same is:

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

Both solutions performs almost the same on my data set (1,929,419 rows in the selected period, all indexes on their places), the first one a little better in time. And it doesn't matter if I include more periods, the first one is always better on my data set.

This is only a simple example but, sometimes, it involves more than two intervals and even some logic (e.g. compare isoweek/weekday instead of month/day, compare different stores, etc).

Although I already have figured out several ways to achieve the same, I was wondering if there is a clever way to achieve the same. Maybe a more cleaner solution, or a more suitable for big data sets (over a TB).

For example, I suppose the second one is less resource intensive for a big data set, since it does a single Index Scan over the table. The first one, on the other hand, requires two Index Scans and a Merge. If the table is too big to fit in memory, what will happen? Or the first one is always better?

Answer 1

There is very rarely a This way of doing things is always better, especially when they are doing very similar things.

What I will suggest however is that you try to utilise best practise wherever you can, such as minimising the use of scalar functions in your queries as this inhibits index usage.

For example, by changing your second query to the following I would imagine you will see at least some improvement performance wise:

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

Answer 2

The second looks better. But I guess the year part is slowing the query. Lets take out the year and put this. 2017-01-01 will be greater for this year range('2017-09-01' AND '2017-09-30') and less for last year range ('2016-09-01' AND '2016-09-30') .

 SELECT
        Sales.StoreId
        , SUM(CASE WHEN date > 2017-01-01 THEN Revenue ELSE 0 END) RevenueThisYear
        , SUM(CASE WHEN date < 2017-01-01 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

IF FULL join is working great, lets try this.

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

Edit *

  SELECT  Sales.StoreId
        , SUM(CASE WHEN date > '2017-01-01' THEN Revenue ELSE 0 END) RevenueThisYear
        , SUM(CASE WHEN date < '2017-01-01' THEN Revenue ELSE 0 END) RevenueLastYear
    FROM
        (Select store_id, date, revenue 
           from Sales
           WHERE Date BETWEEN '2017-09-01' AND '2017-09-30' 
              or Date BETWEEN '2016-09-01' AND '2016-09-30') q

GROUP BY StoreId