我有一個表格，運動，顯示用戶對他們的卡的使用。 它是這樣的：
| 移動日期 | 費用 | 存款 | 余額。||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| 2021-07-09 19:23:49 | 0 | 2 | 2 | 2 | 2021-07-09 20:40:13 | 0 | 6 | 6 | 8 | |
| 2021-07-09 13:50:01 | 0 | 2 | 2 | 10 | 2021-07-10 11:50:31 | 5 | ||||
| 5 | 0 | 0 | 5 | 2021-07-10 12:59:15 | 0 | 5 | 5 | 10 | 2021-07-10 18:01:39 | 10 |
| 10 | 0 | 0 | 0 |
它顯示了他們的存款和支出，也顯示了他們的最終余額。 我有這樣一個查詢，可以得到每天的存款和支出的總額；
select EXTRACT(day FROM m． movementdate） as d, EXTRACT(month FROM m.movementdate） as m,
EXTRACT(year FROM m. movementdate) as y, sum(m.expense) as TotalExpense,
sum (m.deposit) as TotalDeposit
from movements m
group by 3, 2, 1
但我也想得到他們每天的最后一次余額記錄。
像這樣：
| 日期 | >。TotalExpense | TotalDeposit余額。||||||
|---|---|---|---|---|---|---|---|
| 2021-07-09 | 0 | 10 | 10 | 10 | 2021-07-10 | 2021年7月10日 | 15 |
| 15 | 5 | 5 | 0 |
我怎樣才能得到這最后一部分？我已經找到了一些方法來獲得當天的最后一筆余額，但我不知道如何在同一個查詢中包含它。
uj5u.com熱心網友回復：
另一個想要達到結果的方法是使用視窗函式row_number()
with cte as
(
select[/span
movementdate::date as dt。
EXTRACT(day FROM movementdate) as d。
EXTRACT(month FROM movementdate) as m。
EXTRACT(year FROM movementdate) as y。
sum(expense) over (partition by movementdate::date) as expense,
sum(存款) over (partition by movementdate::date) as deposit,
余額。
row_number() over (partition by movementdate: :date order by movementdate desc) as rn
from movements
)
select 選擇
dt,
d,
m,
y,
支出。
存款。
余額
from cte
where rn = 1
order by
1, 2, 3.
輸出：
*-------------------------------------------------------*
| dt d m y expense deposit balance|
*-------------------------------------------------------*
| 20210709 9 7 2021 0 10 8 |
| 202107-10 10 7 2021 15 5 0 |
*-------------------------------------------------------*
uj5u.com熱心網友回復：
一種方法使用陣列：
select EXTRACT(day FROM m． movementdate) as d,
EXTRACT(month FROM m.movementdate) as m,
EXTRACT(year FROM m.movementdate) as y。
sum(m.expense) as TotalExpense,
sum (m.deposit) as TotalDeposit,
(array_agg(m.balance order bym. movementdate desc））[1] as balance
from movements m
group by 3,2,1;
我還建議你使用m.movementdate::date而不是定義前三列。
uj5u.com熱心網友回復：
另一個選擇是使用PostgreSQL LATERAL Subquery
類似于：
WITH movements_per_day AS (
select m.movementdate::date as dt
, EXTRACT(day FROM m.movementdate) asd
, EXTRACT(month FROM m.movementdate) as m
, EXTRACT(year FROM m.movementdate) as y
, sum(m.expense) as TotalExpense
, sum (m.deposit) as TotalDeposit
from movements m
group by 1,2,3,4)
)
SELECT movements_per_day.*, last_movement.balance
FROM movements_per_day.
CROSS JOIN LATERAL (
SELECT movements.balance
FROM movements.
WHERE movements.movement_date::date = movements_per_day.dt
ORDER BY movements.movementdate DESC
LIMIT 1
) AS last_movement
正如其他答案所建議的，使用m.movementdate::date而不是前三列會更簡單一點。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/310917.html
標籤：