I have a challenge I set out to do that seemed initially trivial. Not so for my developper brain.
Consider the following simple view, used to validate a cron I wrote that queries a subset of 200 000 statements every saturday.
It goes as follows:
mysql> SELECT
-> DATE_FORMAT(s.created, "%Y-%m-%d") as "Date",
-> count(s.id) AS "Accounts credited",
-> sum(s.withdrawal) "Total Credited"
-> -- 100 * (sum(s.withdrawal) - sum(prev.withdrawal))
-- / sum(prev.withdrawal) "Difference in %"
-> FROM statements s
-> -- LEFT JOIN prev
-> -- s.created - interval 7 DAY
-> -- ON prev.created = s.created - interval 7 DAY
-- AND (prev.status_id = 'OPEN'
-- OR prev.status_id = 'PENDING')
-> WHERE (s.status_id = 'OPEN' OR s.status_id = 'PENDING')
-> GROUP BY YEAR(s.created), MONTH(s.created), DAY(s.created)
-> ORDER BY s.created DESC
-> LIMIT 8;
+------------+-------------------+----------------+
| Date | Accounts credited | Total Credited |
+------------+-------------------+----------------+
| 2019-01-19 | 18175 | 3173.68 |
| 2019-01-12 | 18135 | 4768.43 |
| 2019-01-05 | 17588 | 6968.49 |
| 2018-12-29 | 17893 | 5404.18 |
| 2018-12-22 | 17353 | 7048.18 |
| 2018-12-15 | 16893 | 7181.34 |
| 2018-12-08 | 16220 | 9547.09 |
| 2018-12-01 | 15476 | 7699.59 |
+------------+-------------------+----------------+
8 rows in set (0.79 sec)
As is, the query is efficient and practical. I merely would like to add a column, difference in percentage
, from previous week's total, as seen with the — commented out code.
I have tried various approaches, but because of the GROUP BY
, adding an inline column to get the sum(withdrawal)
of previous week makes the query run … forever.
I then tried the LEFT JOIN
approach, but this has the same problem, Obviously. I think the added JOIN
has to fetch the sum of previous week for every row of the outer select.
I then had the (not so smart) idea of querying my view, even but then it seems I would have the same issue.
I assume there are much more optimal approaches out there to this simple task.
Is there an elegant way to calculate a percentage from such a query?
Would a stored procedure or some other 'non-plain-sql' approach be more optimal?
Best Answer
When you use MySQL >= 8.0, you can use CTEs (common table expressions)
If you use an earlier version, just replace the CTE with appropriate subqueries, like
MySQL is smart enough to not execute the same query twice, I think.
Or you create a view and use this.