mysql - SQL:按时间范围内的百分比变化查询

Question

我有一个表格，其中记录了不规则时间间隔的产品价格:

prod_id   | price      | timestamp
p1        | 10         | 01-01-2018
p1        | 25         | 03-01-2018
p1        | 15         | 05-01-2018
p2        | 40         | 03-01-2018
p2        | 80         | 04-01-2018
p2        | 90         | 08-01-2018
p3        | 150        | 03-01-2018
p3        | 200        | 05-01-2018

给定一个时间范围和一个阈值，我想查询表并获取在该时间段内增长百分比大于输入阈值的产品。

例如，如果时间范围为 01-01-2018 至 06-01-2018，则 p1、p2 和 p3 的增长百分比为:

p1 -> 50%
p2 -> 100%
p3 -> 33.33%

因此，如果阈值是 40%，则响应应该是

prod_id   |
p1
p2

是否可以使用 SQL 来完成此操作？

Answer 1

试试这个:

示例数据:

create table tbl (prod_id char(2), price int, `timestamp` date);
insert into tbl values
('p1', 10, '2018-01-01'),
('p1', 25, '2018-01-03'),
('p1', 15, '2018-01-05'),
('p2', 40, '2018-01-03'),
('p2', 80, '2018-01-04'),
('p2', 90, '2018-01-08'),
('p3', 150, '2018-01-03'),
('p3', 200, '2018-01-05');

T-SQL:

set @start = '2018-01-01', @end = '2018-01-06', @threshold = 0.4;

select a.prod_id, (c.price - b.price)/b.price from (
    select prod_id, min(`timestamp`) `min`, max(`timestamp`) `max` from tbl
    where `timestamp` between @start and @end
    group by prod_id
) a join tbl b on a.prod_id = b.prod_id and a.`min` = b.`timestamp`
    join tbl c on a.prod_id = c.prod_id and a.`max` = c.`timestamp`
where (c.price - b.price)/b.price > @threshold;