postgresql从同一个表中选择行两次

Question

我想比较表中每个人的deposit。并返回存款字段减少的所有行。

这是我到目前为止所做的;

客户表为;

person_id  employee_id  deposit  ts 
101        201         44        2021-09-30 10:12:19+00
100        200         45        2021-09-30 10:12:19+00
101        201         47        2021-09-30 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 
101        201         44        2021-09-30 10:12:19+00
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 
100        200         45        2021-09-30 10:12:19+00    21          2021-09-29 10:12:19+00
101        201         47        2021-09-30 09:12:19+00    null        null 
100        200         21        2021-09-29 10:12:19+00    null        null
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)
WHERE pre_deposit > deposit //this returns column not found for pre_deposit
ORDER BY person_id , ts

到目前为止，我需要再次选择相同的表才能应用这个条件;

where pre_deposit > deposit 

这里有什么意义?联盟吗?外连接?左连接?右连接?

Answer 1

使用您的查询作为子查询并过滤结果:

SELECT person_id, employee_id, deposit, ts
FROM (
SELECT *, lag(deposit) over client_window as pre_deposit
FROM customer 
WINDOW client_window as (partition by person_id order by ts)
) t
WHERE deposit < pre_deposit 
ORDER BY person_id, ts;