所以我想獲取 person_id 的最新 2 行并檢查存款欄位是否減少。
這是我到目前為止所做的；
客戶表是;
person_id employee_id deposit ts
101 201 44 2021-09-30 10:12:19 00
101 201 47 2021-09-30 09:12:19 00
101 201 65 2021-09-29 09:12:19 00
100 200 21 2021-09-29 10:12:19 00
104 203 54 2021-09-27 10:12:19 00
結果我想要的是；
person_id employee_id deposit ts pre_deposit pre_ts
101 201 44 2021-09-30 10:12:19 00 47 2021-09-30 09:12:19 00
我不想得到deposit:65行，因為我只想檢查最后 2 行。47 > 44 所以。我只需要比較最后兩行。如果存款在任何其他行中減少，我根本不在乎。
SELECT person_id,
employee_id,
deposit,
ts,
lag(deposit) over client_window as pre_deposit,
lag(ts) over client_window as pre_ts
FROM customer
WINDOW client_window as (partition by person_id order by ts)
ORDER BY person_id , ts
所以它回傳一個具有以下結果的表；
person_id employee_id deposit ts pre_deposit pre_ts
101 201 44 2021-09-30 10:12:19 00 47 2021-09-30 09:12:19 00
101 201 47 2021-09-30 09:12:19 00 65 null
100 200 21 2021-09-29 10:12:19 00 null 2021-09-29 09:12:19 00
104 203 54 2021-09-27 10:12:19 00 null null
但如果我執行以下操作；
SELECT person_id,
employee_id,
deposit,
ts,
lag(deposit) over client_window as pre_deposit,
lag(ts) over client_window as pre_ts
FROM customer
WINDOW client_window as (partition by person_id order by ts limit 2)
這個查詢不起作用，因為它拋出一個錯誤；
ERROR: syntax error at or near "limit"
LINE 11: limit 2
那么如何限制比較最后兩行呢？其中 pre_deposit > 存款
uj5u.com熱心網友回復：
你可以做：
with
data as (
select person_id, employee_id, deposit, ts,
row_number() over(partition by person_id order by ts desc) as rn
from customer
)
select a.*,
b.deposit as pre_deposit,
b.ts as pre_ts
from data a
left join data b on a.person_id = b.person_id and b.rn = 2
where a.rn = 1
結果：
person_id employee_id deposit ts rn pre_deposit pre_ts
---------- ------------ -------- ------------------------- --- ------------ ------------------------
100 200 21 2021-09-29T10:12:19.000Z 1 null null
101 201 44 2021-09-30T10:12:19.000Z 1 47 2021-09-30T09:12:19.000Z
104 203 54 2021-09-27T10:12:19.000Z 1 null null
請參閱DB Fiddle 上的運行示例。
uj5u.com熱心網友回復：
你很接近。distinct on在您的第一個查詢中使用這個輝煌的稍微修改一下，你就在那里。
select distinct on (person_id) *
from
(
select person_id, employee_id, deposit, ts,
lead(deposit) over w as pre_deposit,
lead(ts) over w as pre_ts
from customer
window w as (partition by person_id order by ts desc)
) t
where pre_deposit > deposit
order by person_id, ts desc;
SQL 小提琴在這里。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/313928.html
標籤：sql PostgreSQL