Offset on query results

Question

I am trying to compare weekly results. To be more specific, I want to get the difference week after week.

First I would like to show how the result should look like:

CalendarWeek     currently      previous_week      delta
2016-01          200            
2016-02          210            200                 10
2016-03          205            210                 -5
2016-04          230            205                 25
...      

I got a solution but it is pretty slow.

Currently I am doing it like this:

SELECT CalendarWeek, cur_value - prev_value AS delta
FROM  
    (SELECT CalendarWeek, COUNT(Change_ID) AS cur_value
     FROM Changes
     WHERE ...
     GROUP BY CalendarWeek) AS cur_week
LEFT JOIN
    (SELECT CalendarWeek, COUNT(Change_ID) AS prev_value
     FROM Changes
     WHERE ...
     GROUP BY CalendarWeek) AS prev_week
ON cur_week.CalendarWeek = prev_week.CalendarWeek + 1

My problem is, that running the subqueries takes a lot of time (I did simplify them for this showcase). The SELECT with the COUNT() runs 45 sec. each = 1,5 min. I think there should be a more appropriate solution. I just need the prev_value to be 1 row shifted or with an offset of 1 row.

Answer 1

Use variables instead:

SELECT w.*,
       (CASE WHEN (@pcv := @prev_value) = NULL THEN NULL       -- never happens
             WHEN (@prev_value := cur_value) = NULL THEN NULL  -- never happens
             ELSE @pcv
        END) as prev_value
FROM (SELECT CalendarWeek, COUNT(Change_ID) AS cur_value
      FROM Changes
      WHERE ...
      GROUP BY CalendarWeek AS cur_week
     ) w CROSS JOIN
     (SELECT @prev_value := -1) params
ORDER BY CalendarWeek;

You can then use an additional subquery to calculate ratios and differences.

Note: The logic for assigning the previous value is a three step process: (1) cache the previous previous value in a variable, (2) reassign the previous value, and (3) then use the cached value.

MySQL does not guarantee the order of execution of expressions in a SELECT, so this all has to happen in a single expression (hence the = NULL, which is never true). Almost all other databases support lag() for this purpose, which makes the query faster and the logic simpler.