sql每月选择前5名
sql每月选择前5名
提问于 2013-05-15 16:57:17
我有一个mysql表的格式，让我们称之为product_revenue Product_id，年，月，收入
我需要获取以下列: year、month、revenue_top_5_monthly
其中revenue_top_5_monthly是当月收入最高的产品的收入总和。每月排名前5位的产品各不相同。
我可以通过选择使用子查询单个月，按收入排序并使用limit 5，然后对值进行求和来对单个月执行此操作，但我不知道如何在单个查询中执行此操作
我所拥有的是
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但我每个月都需要它。
product_revenue表在16个月内有超过10m的行，因此最终的查询速度非常重要。对于一个月，目前大约需要80-100秒，我必须在1小时30分钟的时间内运行大约30个这样的查询，每个查询持续16个月。
正如建议的那样，我也尝试了
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返回预期的行，名为。
回答 3
Stack Overflow用户
回答已采纳
发布于 2013-05-15 17:19:22
尝试此查询
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| 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 |Stack Overflow用户
发布于 2013-05-15 17:30:21
也许：
SELECT t1.year,
t1.month,
(SELECT SUM(t2.revenue)
FROM product_revenue t2
WHERE t2.month = t1.month
AND t2.year = t1.year
ORDER BY t2.revenue DESC LIMIT 5 ) AS revenue_top_5
FROM product_revenue t1
GROUP BY t1.year, t1.monthStack Overflow用户
发布于 2013-05-15 17:13:40
试试这个和c。
select top 5 'y' as year,'x' as month,sum(total) as top_5 From
(select sum(revenue) as total from product_revenue
where month=x and year=y
order by revenue desc) as t页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16560770
复制相关文章
相似问题
腾讯云开发者
