CODEWITHSUNDEEP
postgresql
Olivia Lundy
Olivia Lundy

Reputation: 127

PostgreSQL Lag Function between of Two Tables

I am new to Postgresql/Python, so please bear with me! Assuming we have two tables:

  1. item table having a itemid, price, time.
  2. user table having colums userid, itemid, timecreated, quantity, firstprice, lastprice, difference. Table examples like :

item table:

itemid  price   time
RBK      92 1546408800
LBV      51 1546408800
ZBT      49 1546408800
GLS      22 1546408800
DBC      17 1546408800
RBK      91 1546495200
LBV      55 1546495200
ZBT      51 1546495200
GLS      24 1546495200
DBC      28 1546581600
RBK     108 1546581600
LBV      46 1546581600
ZBT      49 1546581600
GLS      21 1546581600
DBC     107 1546581600

In item table all those values comes up with api. and user table:

   userid   itemid  timecreated quantitty   firstprice  currentprice difference
    1       RBK       1546408800    20      
    2       RBK       1546408800    15      
    3       RBK       1546408800    35      
    3       GLS       1546408800    101     
    3       DBC       1546495200    140     
    1       RBV       1546495200    141     
    2       RBK       1546495200    25      
    2       RBV       1546581600    31

User table is djangobased table which is user can register\add new items to follow prices.

My struggle access the item table to fetch first price which is having a same timestamp. In that example userid 1 RBK First price (1546408800) must be filling with 92

I did some trick with postgresql with (lag) But this does not seems to be working:

update user
    set firstprice = tt.prev_price
    from (select item.*,
                 lag(price) over (partition by itemid order by time) as prev_price
          from item
         ) tt
    where tt.id = item.id and
          tt.prev_close is distinct from item.price;

I can call current price from the api but didnt find out the way to filling firstprice from the item table. I will be making for a trigger for this query. I searched on google and on stackoverflow but I couldn't find anything that could help me. Thanks in advance.

Upvotes: 1

Views: 149

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

I can advice next approach (may be not fastest):

update "user" set firstprice = (
  select price from "item" i 
  where i.itemid = "user".itemid and i.time >= "user".timecreated order by i.time limit 1
);

It calculate firstprice using sub-query. Test this SQL here

Upvotes: 1

Related Questions