如何以不同的条件从另一个表格的列更新表格单元格？

Question

假设我的数据库中有两个表，如下所示：

表1(current_prices(：其中包含一些东西及其价格，每天更新一次：

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

表2(stat_history(：在价格范围内划分商品，并在所有日子内保持每个范围的元素数量如下：

# 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

stat_history表的内容是在一天结束时由current_price内容生成的。

目前，我使用多个更新插入(Upsert(查询来更新我的stat_history表，如下所示：

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)

问题是：

有没有更短、更简单或更高效的方法可以做到这一点(例如，在单个SQL查询中(

Answer 1

您可以使用Postgres标准filter子句进行条件计数：

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

注：

• 我调整了在间隔中放置行的逻辑，使它们连续(例如，在您的原始查询中，等于VA1的价格永远不会计入(

• 有了这个逻辑，您甚至可能不需要on conflict子句