mysql - SQL 季度总计和增长百分比前十名

Question

我有一个表，需要按产品的季度创建总销售额，其中 account_name 和 deal_id = X。

然后，我想要一份上一季度涨幅最大的 10 名企业的名单。

我一直通过创建一个临时表来做到这一点，但该表的大小约为1G，性能达不到我们想要的水平。我们可以创建另一个汇总表，但在这样做之前，我想看看是否有人对首先使用该表有建议。

account_name    product_title   type    period  deal_id total_amount
Account1    product1    Type A  2002_Q4 9100    146.54
Account1    product1    Type B  2002_Q4 9100    34.32
Account1    product1    Type C  2002_Q4 9100    0.02
Account1    product2    Type A  2002_Q4 9100    14.45
Account1    product2    Type B  2002_Q4 9100    3.58
Account1    product1    Type A  2002_Q3 9100    68.23
Account1    product1    Type B  2002_Q3 9100    12.56
Account1    product1    Type C  2002_Q3 9100    75.21
Account1    product2    Type A  2002_Q3 9100    5.68
Account1    product2    Type B  2002_Q3 9100    3.2

product1    180.88  2002_Q4 16%     
product2    18.03   2002_Q4 103%        
product1    156     2002_Q3         
product2    8.88    2002_Q3         

好的，我添加了新数据，并包括了与上一季度相比增长情况的结果。

Answer 1

这是使用固定四分位数的一种方法:

select d.product_title, ((
        select sum(d1.total_amount)
        from deals d1
        where d1.account_name = 'Account1'
          and d1.deal_id = d.deal_id
          and d1.product_title = d.product_title
          and d1.period  = '2002_Q4'
    ) - sum(d.total_amount)) / sum(d.total_amount) * 100
    as diff
from deals d
where d.account_name = 'Account1'
  and d.deal_id = 9100
  and d.period = '2002_Q3'
group by d.product_title
order by diff desc
limit 10

http://sqlfiddle.com/#!9/65bd95/26

这是另一个 - 连接两个子查询:

select
  q3.product_title, 
  100 * (q4.total - q3.total) / q3.total as diff
from (
  select d.product_title, sum(d.total_amount) as total
  from deals d
  where d.account_name = 'Account1'
    and d.deal_id = 9100
    and d.period = '2002_Q3'
  group by d.product_title
) q3
join  (
  select d.product_title, sum(d.total_amount) as total
  from deals d
  where d.account_name = 'Account1'
    and d.deal_id = 9100
    and d.period = '2002_Q4'
  group by d.product_title
) q4 using (product_title)
order by diff desc
limit 10

http://sqlfiddle.com/#!9/65bd95/9

一个有用的索引是(account_name，period，deal_id，product_title，total_amount)。前三列可以按任何顺序排列。最后一个是可选的，但使其成为“覆盖”索引。