Postgresql Using Limit with Order by without select and where case-CodePudding

So I want to get the latest 2 rows of the person_id and check if the deposit field is decreased.

Here is what I have done so far;

The customer table is;

   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

and as a result I want is;

   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

I don't want to get deposit:65 row because I just want to check the last 2 rows. 47 > 44 so. I need to compare only the last 2 rows. if the deposit decreased in any other rows, I simply don't care.

    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

so it returns a table with the following results;

   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

but if I do the following;

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)

this query doesn't work, because it throws an error as;

ERROR:  syntax error at or near "limit"
LINE 11:    limit 2

so how can I limit to compare the last 2 rows? where pre_deposit > deposit

CodePudding user response：

You can do:

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

Result:

 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

See running example at DB Fiddle.

CodePudding user response：

You are pretty close. Use this brilliant distinct on on your first query slightly modified and you are there.

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 Fiddle here.