sql每月选择前5名 [英] sql select top 5 every month

问题描述

我有一个格式的mysql表，我们称它为product_revenue Product_id，年，月，收入

I have a mysql table of the format, let's call it product_revenue Product_id, year, month, revenue

，我需要获取以下各列: 年，月，income_top_5_monthly

and i need to get the following columns: year, month, revenue_top_5_monthly

其中Revenue_top_5_monthly是该月收入最高的产品的收入总和.排名前5位的产品每个月都不一样.

where revenue_top_5_monthly is the sum of revenue of the products that had the highest revenue in that month. The top 5 products vary from month to month.

我可以在一个月中执行此操作，方法是选择一个月的子查询，然后按收入排序并使用限制5，然后对其值求和，但是我不知道该如何在每个月执行此操作单个查询

I can do this for a single month by selecting with a sub-query a single month, sorting by revenue and using limit 5, then summing up the value, but I wouldn't know how to do this for every month in a single query

我拥有的是

select 'y' as year, 'x' as month, sum(revenue) as revenue_top_5 from
(select revenue from product_revenue
where month=x and year=y
order by revenue desc
limit 5) as top5

但我每月需要一次注射.

but I need it for every month in one shot.

product_revenue表在16个月内有超过10m的行，因此最终查询速度具有很大的相关性.目前，一个月的时间大约为80-100秒，我必须在1小时30分钟的时间内运行大约30个这样的查询，每个查询在整个16个月内进行.

The product_revenue table has over 10m rows for 16 months, so final query speed is of large relevance. For one month it currently takes about 80-100 sec, and I have to run about 30 such queries, each for the whole 16 months, in a 1h 30min slot.

根据建议，我也尝试过

select * from
(
select dd.year, dd.monthnumber,
u.product_id, sum(revenue) as revenue
from source
group by 1,2,3
)a
where 
(select count(*) from
                            (select dd.year, dd.monthnumber,
                            u.product_id, sum(revenue) as revenue
                            from source
                            group by 1,2,3)b
where b.year=a.year and b.monthnumber=a.monthnumber and b.revenue<=a.revenue
)<=5

，但不返回任何行.单个子查询a和b返回预期的行，如命名的.

but returns no rows. The individual subqueries a and b return the expected rows as named.

推荐答案

尝试此查询

select * from
(select 
@rn:=if(@prv=product_id, @rn+1, 1) as rId,
@prv:=product_id as product_id,
year, 
month,
revenue
from tbl
join
(select @prv:=0, @rn:=0)tmp
order by 
product_id, revenue desc) a
where rid<=5

SQL FIDDLE :

| RID | PRODUCT_ID | YEAR | MONTH | REVENUE |
---------------------------------------------
|   1 |          1 | 2013 |     1 |     100 |
|   2 |          1 | 2013 |     1 |      90 |
|   3 |          1 | 2013 |     1 |      70 |
|   4 |          1 | 2013 |     1 |      60 |
|   5 |          1 | 2013 |     1 |      50 |
|   1 |          2 | 2013 |     1 |    5550 |
|   2 |          2 | 2013 |     1 |     550 |
|   3 |          2 | 2013 |     1 |     520 |
|   4 |          2 | 2013 |     1 |     510 |
|   5 |          2 | 2013 |     1 |     150 |

这篇关于sql每月选择前5名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！