如何在聚合查詢中計算前一周的百分比？

我有一個開始做的挑戰，最初看起來微不足道。對於我的開發者大腦來說並非如此。

考慮以下簡單視圖，用於驗證我編寫的每週六查詢 200 000 條語句子集的 cron。

它是這樣的：

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)

照原樣，查詢是有效和實用的。我只想 difference in percentage從前一周的總數中添加一列 ，如 – 註釋掉的程式碼所示。

我嘗試了各種方法，但由於GROUP BY添加了一個內聯列來獲取sum(withdrawal)前一周的內容，使得查詢執行……永遠。

然後我嘗試了這種LEFT JOIN方法，但這顯然有同樣的問題。我認為添加的JOIN必須為外部選擇的每一行獲取前一周的總和。

然後我有了查詢我的觀點的（不太聰明）的想法，即使這樣，我似乎也會遇到同樣的問題。

我認為對於這個簡單的任務有更優化的方法。

有沒有一種優雅的方法可以從這樣的查詢中計算百分比？

儲存過程或其他一些“非普通 sql”方法會更優化嗎？

當您使用 MySQL >= 8.0 時，您可以使用CTE（通用表表達式）

;
WITH 
   (
       SELECT  DATE(s.created) as "Date",
               COUNT(s.id) AS "Accounts credited",
               SUM(s.withdrawal) "Total Credited"
           FROM  statements s
           WHERE  s.status_id IN ('OPEN', 'PENDING')
           GROUP BY  DATE(s.created)
           ORDER BY  s.created DESC 
   ) AS cte 
SELECT  cte.*,
       100 * (cte.`Total Credited` - prev.`Total Credited`) /
             prev.`Total Credited`  AS "Difference in %"
   FROM  cte
   LEFT JOIN  cte prev  ON cte.`Date` = prev.`Date` - INTERVAL 7 DAY;

如果您使用早期版本，只需將 CTE 替換為適當的子查詢，例如

SELECT <your_fancy_percentage_calculation> 
FROM (<cte from above>) a 
LEFT JOIN (<cte from above>) b ON a.Date = b.Date - INTERVAL 7 DAY;

我認為 MySQL 足夠聰明，不會兩次執行相同的查詢。

或者您創建一個視圖並使用它。

引用自：https://dba.stackexchange.com/questions/227547