CODEWITHSUNDEEP
sqlpostgresql
Ebrahim Ghasemi
Ebrahim Ghasemi

Reputation: 6116

How to update a table cells from another table's columns with different conditions?

Let assume that I have two tables in my database as below:

Table 1 (current_prices): Which contains some stuffs and their prices and it updates one time per day:

# current_prices
| commodity  | price |
____________________
|  stuff1    | price1
|  stuff2    | price2
|  stuff3    | price3
|.           |
|.           |
|.           |
|  stuffN    | priceN

Table 2 (stat_history): Which divide stuffs in price ranges and keep the number of elements of each range for all days as below:

# stat_history
|   date    | range1_count | range2_count | range3_count
________________________________________________________
|  20200411 |       12     |      5       |      9
|  20200412 |       10     |      5       |      11
|  20200413 |       13     |      4       |      9
|  20200414 |       15     |      3       |      8

The content of stat_history table are generated from current_price contents at the end of the day.

Currently I use multiple Update-Insert (Upsert) queries to update my stat_history table as below:

insert into stat_history (date, range1_count)
    select now()::date , count(stuff) as range1_count from current_prices
        where 0 < price and price < VAL1
    on conflict(day)
        update set
            range1_count = excluded.range1_count

insert into stat_history (date, range2_count)
    select now()::date , count(stuff) as range2_count from current_prices
        where VAL1 < price and price < VAL2
    on conflict(day)
        update set
            range2_count = excluded.range2_count

..... (blah blah)

The question is:

Is there any shorter, simpler or more efficient way to do this (In a single SQL query for example)?

Upvotes: 0

Views: 24

Answers (1)

GMB
GMB

Reputation: 222462

You could do conditional counts, using Postgres standard filter clause:

insert into stat_history (date, range1_count)
select 
    now()::date, 
    count(stuff) filter(where price >= 0 and price < VAL1) as range1_count,
    count(stuff) filter(where price >= VAL1 and price < VAL2) as range2_count
from current_prices
where price >= 0 and price < VAL2
on conflict(day) 
update set 
    range1_count = excluded.range1_count
    range2_count = excluded.range2_count

Notes:

  • I adapted the logic that puts rows in the intervals to make them contiguous (in your original query for example, a price that is equal to VA1 would never be counted in)

  • with this logic at hand, you might not even need the on conflict clause

Upvotes: 1

Related Questions