将今天的值与昨天的值进行比较，如果差值> 10，则返回数据

Question

我有一个Products表，其中每天更新产品价格。

价格<2.9>2022-11-26年<13.92>

eff_date	product_id
2022-11-25	P1	150
2022-11-25	P2	75.8
2022-11-25	P3
2022-11-26	P1	180.5
	P2	77
2022-11-26	P4

根据当前日期调整日期，你可以照拉蒙说的做。

CTE为您提供所需的所有数据

CREATE TABLE products
("eff_date" date, "product_id" varchar(2), "price" numeric)
;

INSERT INTO products
("eff_date", "product_id", "price")
VALUES
('2022-11-27', 'P1', 150),
('2022-11-27', 'P2', 75.8),
('2022-11-27', 'P3', 2.9),
('2022-11-28', 'P1', 180.5),
('2022-11-28', 'P2', 77),
('2022-11-28', 'P4', 13.92),
('2022-11-28', 'P5', 150),
('2022-11-27', 'P5', 180.5)
;

CREATE TABLE

INSERT 0 8

WITH CTE as (
SELECT
"eff_date", "product_id", "price"
,LAG("price") OVER(PARTITION BY "product_id" ORDER BY "eff_date") last_price
, extract(day from "eff_date"::timestamp - LAG("eff_date") OVER(PARTITION BY "product_id" ORDER BY "eff_date")::timestamp ) diffdays
FROM 
products)
SELECT "eff_date", "product_id" , "price" as "today's price" , last_price as "yesterday's price"
FROm CTE
WHERE diffdays = 1 AND "eff_date" = current_date AND  ABS( ( 1 - "price"/ last_price)) > 0.1 

eff_date
2022-11-28

您可以使用CTE，它根据product_id对数据进行分组并按日期排序，然后检查当前价格>=之前的价格*1.1为了满足您的条件，价格必须至少上涨了10%。

WITH CTE AS (
SELECT
eff_date, 
product_id,
price,
LAG(eff_date,1) OVER (PARTITION BY product_id
ORDER BY eff_date
) previous_date,
LAG(price,1) OVER (PARTITION BY product_id
ORDER BY eff_date
) previous_price
FROM products)
SELECT eff_date, product_id, 
price AS todays_price, 
previous_price AS yesterdays_price
FROM cte
WHERE 
eff_date = previous_date + 1 AND 
price >= previous_price * 1.1;

试用：db<gt；小提琴

试试这个：

create temporary table temp1 as
select 
eff_date,
product_id,
price as todays_price
lag(price) over(partition by product_id order by eff_date) as yesterdays_price,
from products

select * from temp1
where 
(todays_price-yesterdays_price)/yesterdays_price >= 0.10

请注意，我们只包括价格上涨10%的产品

我提出了以下查询。有人能确认一下它是否正确吗？

select a.eff_date as todays_date,b.eff_date as yesterdays_date, a.product_id, a.price as todays_price, b.price as yesterdays_price,
(((a.price - b.price) / b.price) * 100) as perc_change
from (select * from products where eff_date=cast(CURRENT_DATE as date)) a 
inner join 
(select * from products where eff_date=cast(CURRENT_DATE -1 as date)) b
on a.product_id=b.product_id 
where (((a.price - b.price) / b.price) * 100) > 10