mysql - sql select top 5 every month -
i have mysql table of format, let's call product_revenue product_id, year, month, revenue
and need following columns: year, month, revenue_top_5_monthly
where revenue_top_5_monthly sum of revenue of products had highest revenue in month. top 5 products vary month month.
i can single month selecting sub-query single month, sorting revenue , using limit 5, summing value, wouldn't know how every month in single query
what have is
select 'y' year, 'x' month, sum(revenue) revenue_top_5 (select revenue product_revenue month=x , year=y order revenue desc limit 5) top5
but need every month in 1 shot.
the product_revenue table has on 10m rows 16 months, final query speed of large relevance. 1 month takes 80-100 sec, , have run 30 such queries, each whole 16 months, in 1h 30min slot.
as suggested, tried
select * ( select dd.year, dd.monthnumber, u.product_id, sum(revenue) revenue source group 1,2,3 )a (select count(*) (select dd.year, dd.monthnumber, u.product_id, sum(revenue) revenue source group 1,2,3)b b.year=a.year , b.monthnumber=a.monthnumber , b.revenue<=a.revenue )<=5
but returns no rows. individual subqueries , b return expected rows named.
try query
select * (select @rn:=if(@prv=product_id, @rn+1, 1) rid, @prv:=product_id product_id, year, month, revenue tbl join (select @prv:=0, @rn:=0)tmp order product_id, revenue desc) 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 |
Comments
Post a Comment