我有两张桌子。在一个表（order_produt）中按日期有多条记录，而其他表（Transfer_product）也按日期有多条记录。order_product 表有正确的记录。我想按日期范围用 order_product 表更新我的 transfer_product 表。
order_product_table
-------------------------
id | date | Product_id | value
-------------------------------------------
1 | 2017-07-01 | 2 | 53
2 | 2017-08-05 | 2 | 67
3 | 2017-10-02 | 2 | 83
4 | 2018-01-20 | 5 | 32
5 | 2018-05-01 | 5 | 53
6 | 2008-08-05 | 6 | 67
Transfer_product_table
----------------------------
id | date | Product_id | value
--------------------------------------------
1 | 2017-08-01 | 2 | 10
2 | 2017-10-06 | 2 | 20
3 | 2017-12-12 | 2 | 31
4 | 2018-06-25 | 5 | 5
Result(Transfer_product_table)
--------------------------------
id | date | Product_id | value
--------------------------------------------
1 | 2017-08-01 | 2 | 53
2 | 2017-10-06 | 2 | 83
3 | 2017-12-12 | 2 | 83
4 | 2018-06-25 | 5 | 53
我想按日期值更新，就像你可以看到结果表一样。
我使用查询分区，但这不是我想要的。
UPDATE Transfer_product_table imp
SET value = sub.value
FROM (SELECT product_id,value
,ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY orderdate DESC)AS Rno
FROM order_product_table
where orderdate between '2017-07-01' and '2019-10-31') sub
WHERE imp.product_id = sub.product_id
and sub.Rno=1
and imp.date between '2017-07-01' and '2019-10-31'
使用 postgres 的超棒日期范围类型非常简单。
with order_product_table as (
select * from (
VALUES (1, '2017-07-01'::date, 2, 53),
(2, '2017-08-05', 2, 67),
(3, '2017-10-02', 2, 83),
(4, '2018-01-20', 5, 32),
(5, '2018-05-01', 5, 53),
(6, '2008-08-05', 6, 67)
) v(id, date, product_id, value)
), transfer_product_table as (
select * from (
VALUES (1, '2017-08-01'::date, 2, 10),
(2, '2017-10-06', 2, 20),
(3, '2017-12-12', 2, 31),
(4, '2018-06-25', 5, 5)
) v(id, date, product_id, value)
), price_ranges AS (
select product_id,
daterange(date, lead(date) OVER (PARTITION BY product_id order by date), '[)') as pricerange,
value
FROM order_product_table
)
SELECT id,
date,
transfer_product_table.product_id,
price_ranges.value
FROM transfer_product_table
JOIN price_ranges ON price_ranges.product_id = transfer_product_table.product_id
AND date <@ pricerange
ORDER BY id
;
id | date | product_id | value
----+------------+------------+-------
1 | 2017-08-01 | 2 | 53
2 | 2017-10-06 | 2 | 83
3 | 2017-12-12 | 2 | 83
4 | 2018-06-25 | 5 | 53
(4 rows)
基本上，我们通过使用 order_product_table 计算出任何给定日期的价格。我们通过以下方式获得当前日期（含）和下一个日期（不含）之间的价格：
daterange(date, lead(date) OVER (PARTITION BY product_id order by date), '[)') as pricerange,
然后我们简单地加入这个条件，即 product_ids 匹配并且 transfer_product_table 中的日期包含在 pricerange 中。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句