我需要撰寫一個查詢來獲取給定日期串列中每個類別的值的總和。如果某個類別的值不存在，我們應該從上一個日期獲取值。基本上類似于“每個日期每個類別的最大值”。最終目標是趨勢圖。如果某個類別的先前值不存在，則將該值設定為即可0。
見下表和結果：
類別
| ID | 名稱 |
|---|---|
| 1 | 儲蓄 |
| 2 | 現金 |
| 3 | 股票 |
物品
| ID | 類別 ID | 價值 | 創建時間 |
|---|---|---|---|
| 1 | 1 | 100 | 2022-01-01 |
| 2 | 2 | 20 | 2022-01-01 |
| 3 | 3 | 500 | 2022-01-01 |
| 4 | 2 | 0 | 2022-01-02 |
| 5 | 3 | 1000 | 2022-01-03 |
結果
| 創建時間 | 全部的 |
|---|---|
| 2022-01-01 | 620 |
| 2022-02-02 | 600 |
| 2022-02-03 | 1100 |
要獲得單個日期的結果，我可以執行以下操作：
SELECT SUM(value) as total
FROM Category
LEFT JOIN (
SELECT id, categoryId, value
FROM Item
WHERE id IN (
SELECT MAX(id) FROM Item WHERE createdAt <= '2022-01-10' GROUP BY categoryId)
) items ON Category.id = items.categoryId;
我完全不知道如何在多個日期執行此操作，例如。如果我的輸入是 2022 年 1 月的每一天。我在 MySQL 8.0.23 上運行。此外，如果這對單個查詢不可行，我會提出想法。你有什么建議嗎？
uj5u.com熱心網友回復：
試試這個：
with u as
(select id as categoryId from Category),
v as
(select distinct createdAt from Item),
w as
(select * from u cross join v),
x as
(select createdAt,
categoryId,
(select value
from Item
where categoryId = w.categoryId and createdAt <= w.createdAt
order by createdAt desc
limit 1) as value
from w)
select createdAt, sum(value) as total
from x
group by createdAt
基本上是獲取創建日期與 categoryIds 的所有組合，然后使用子查詢獲取每個 categoryId 的最接近或相等日期的值。
小提琴。_
uj5u.com熱心網友回復：
一種選擇使用視窗函式SUM() OVER ()，LAG()例如
WITH i AS
(
SELECT SUM(`value`) OVER (PARTITION BY `createdAt`,`categoryId` ORDER BY `createdAt`) AS total_sofar,
LAG(`value`,1,0) OVER (PARTITION BY `categoryId` ORDER BY `createdAt`) AS lg,
`createdAt`
FROM Item
)
SELECT DISTINCT `createdAt`,
SUM(total_sofar) OVER (ORDER BY `createdAt`)-SUM(lg) OVER (ORDER BY `createdAt`) AS total
FROM i
ORDER BY `createdAt`
因為你有 8.0 版的 MySQL DBMS。訣竅是分組（在第一個查詢中進行磁區）categoryIdLAG
Demo
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/419731.html
標籤：