如何按不同日期价格之间的百分比差异对表格进行排序 [英] How to sort a table by the difference in percent between prices on different date

问题描述

我有这个查询:

SELECT * FROM (
select t.article_number, t.date, t.company, t.size, t.price , count(*) as rnk
from price t
INNER JOIN price s
ON(t.article_number = s.article_number and t.company = s.company and t.date <= s.date)
    GROUP BY t.article_number,t.company,t.date,t.size,t.price
)

    WHERE rnk <= 2
    order by article_number, company, date

适用于这些数据:

('50240-20', '2016-03-08 13:06:12.872955', '2B', '20', '645,75')
('50240-20', '2016-03-08 13:13:55.311955', '2B', '20', '645,75')
('50240-20', '2016-03-08 13:47:13.737155', '2B', '20', '635,75')
('50240-20', '2016-03-08 13:51:32.677155', '2B', '20', '645,75')
('50240-20', '2016-03-08 13:06:12.872955', 'Bio', '20', '423,20')
('50240-20', '2016-03-08 13:13:55.311955', 'Bio', '20', '423,20')
('50240-20', '2016-03-08 13:47:13.737155', 'Bio', '20', '403,20')
('50240-20', '2016-03-08 13:51:32.677155', 'Bio', '20', '423,20') ...

它产生这样的输出:

('50240-20', '2016-03-08 13:47:13.737155', '2B', '20', '635,75', 2)
('50240-20', '2016-03-08 13:51:32.677155', '2B', '20', '645,75', 1)

('50240-20', '2016-03-08 13:47:13.737155', 'Bio', '20', '403,20', 2)
('50240-20', '2016-03-08 13:51:32.677155', 'Bio', '20', '423,20', 1) ...

但我想按这两个日期之间的最大价格差异对这个输出进行排序.所以它应该是这样的:

But I want to sort this output by the biggest difference in price between these two dates. So it should look like this:

('50240-20', '2016-03-08 13:47:13.737155', 'Bio', '20', '403,20', 2)
('50240-20', '2016-03-08 13:51:32.677155', 'Bio', '20', '423,20', 1)

('50240-20', '2016-03-08 13:47:13.737155', '2B', '20', '645,75', 2)
('50240-20', '2016-03-08 13:51:32.677155', '2B', '20', '635,75', 1) ...

因为顶级产品的价格变化了 4.7%，第二个产品的价格变化了 1.6%

Because the price of the top product changed 4,7% and the second changed 1,6%

我希望你明白我的意思，也许可以提出一个解决方案，因为我不太擅长 sql.我在 python 中使用 sqlite3.

I hope you understand what I mean and maybe can suggest a solution because I'm not that good in sql. I'm using sqlite3 with python.

非常感谢！

推荐答案

• 假设 sqlite3 使用 cte，你可以使用这样的东西.
• 我使用 date1 作为 date 和 size1 作为 size 因为我的数据库不允许我使用这些关键字.
• price 格式应该改为decimal 而不是string.
• Assuming that sqlite3 uses cte, you can use something like this.
• I used date1 as date and size1 as size as my DB doesn't let me use these keywords.
• The price format should be changed to decimal rather than string.

这是我在 Oracle 中使用通用类型表达式尝试的查询.如果 sqlite 不支持，则必须将每个派生表的查询替换为主查询.(就像我使用 tbl1 的任何地方一样，您必须使用用于派生 tbl1 的查询等等.

This is query I tried in Oracle using Common Type Expression. If sqlite doesn't support it, you have to replace the query of each derived table with main query. (like whereever I used tbl1, you have to use the query which is used to derive tbl1 and so on.

with tbl1 as
(select p1.article_number,p1.company,count(p1.date1) as rno,max(p1.date1) as date1,max(p1.size1) as size1,max(p1.price) as price
from price p1 inner join price p2
on p1.article_number=p2.article_number and p1.company=p2.company and p1.date1>=p2.date1
group by p1.article_number,p1.company,p1.date1
)
,tbl2 as
(select p1.article_number,p1.company,count(p1.date1)+1 as rno,max(p1.date1) as date1,max(p1.size1) as size1,max(p1.price) as price
from price p1 inner join price p2
on p1.article_number=p2.article_number and p1.company=p2.company and p1.date1>=p2.date1
group by p1.article_number,p1.company,p1.date1
)
,tbl3 as
(
select tbl1.*,
case when tbl1.price>tbl2.price then ((tbl1.price-tbl2.price)/tbl1.price)*100
else ((tbl2.price-tbl1.price)/tbl1.price)*100 end as diff_percent
from tbl1 inner join tbl2 
on tbl1.article_number=tbl2.article_number and tbl1.company=tbl2.company
and tbl1.rno=tbl2.rno
order by tbl1.article_number,tbl1.company,tbl1.rno
)
,tbl4 as(
select t1.article_number,t1.company,count(t1.diff_percent) as rnk,max(t1.date1) as date1,max(t1.price) as price,max(t1.diff_percent) as diff_percent
from tbl3 t1 inner join tbl3 t2
on t1.article_number=t2.article_number and t1.company=t2.company
and t1.diff_percent <=t2.diff_percent
group by t1.article_number,t1.company,t1.diff_percent
)
select * from tbl4 
where rnk <=2
order by article_number,company,date1

这篇关于如何按不同日期价格之间的百分比差异对表格进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！