sql 分组查询前N名
分组查询前十名
表结构
CREATE TABLE `tb_api_app_income_rank` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` varchar(64) DEFAULT '',
`product_name` varchar(255) DEFAULT NULL,
`publisher_id` varchar(64) DEFAULT NULL,
`publisher_name` varchar(512) DEFAULT NULL,
`company_id` varchar(64) DEFAULT NULL,
`company_name` varchar(512) DEFAULT NULL,
`category` varchar(64) DEFAULT NULL COMMENT '分类',
`rank_type` varchar(32) DEFAULT NULL COMMENT '排行榜类型，0：免费，1：付费，2：畅销',
`country` varchar(32) DEFAULT NULL,
`rank` int(11) DEFAULT NULL COMMENT '在收入下载量排行榜的排名',
`change_rank` int(11) DEFAULT NULL,
`change_percent` decimal(11,6) DEFAULT NULL,
`change_value` int(11) DEFAULT NULL,
`price` decimal(11,2) DEFAULT NULL,
`estimate` int(11) DEFAULT NULL,
`downloads` int(11) DEFAULT NULL COMMENT '下载量',
`income` int(11) DEFAULT NULL COMMENT '收入',
`release_date` varchar(32) DEFAULT NULL,
`is_new` int(11) DEFAULT '0' COMMENT '是否新品，0：不是，1：是。新品：距离当前时间往前推7天（昨日为第7日），release date在这7天之内，且该产品的appid是首次出现，才算做新品',
`created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`,`created_at`),
KEY `idx_apir_update_time` (`updated_at`) USING BTREE,
KEY `idx_apir_ctime_country` (`country`,`created_at`) USING BTREE,
KEY `idx_apir_aid` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=52188 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='app 收入下载量的排行榜，分免费、付费、畅销排行榜'
- 第一种方案
SELECT
t1.*
FROM
tb_api_app_income_rank t1
WHERE
created_at = '2017-11-07 00:00:00'
AND rank_type = 'grossing'
AND change_rank = 1000
AND (
SELECT
count(1)
FROM
tb_api_app_income_rank
WHERE
rank_type = 'grossing'
AND change_rank = 1000
AND created_at = '2017-11-07 00:00:00'
AND country = t1.country
AND category = t1.category
AND rank < t1.rank
) < 3;
消耗时间: 7.260s
- 第二种方案
SELECT
t1.*
FROM
tb_api_app_income_rank t1
left join tb_api_app_income_rank t2 ON t2.created_at = '2017-11-07 00:00:00'
AND t2.rank_type = 'grossing'
AND t2.change_rank = 1000
AND t1.country = t2.country
AND t1.category = t2.category
AND t1.rank > t2.rank
where t1.created_at = '2017-11-07 00:00:00'
AND t1.rank_type = 'grossing'
AND t1.change_rank = 1000
group by t1.country, t1.category, t1.product_id
having count(t1.product_id)<3
order by t1.country, t1.category,rank;
消耗时间: 8.076s
- 第三种
select * from
(SELECT
(
@i := CASE
WHEN @pre_country = country && @pre_category = category THEN
@i + 1
ELSE
1
END
) rownum,
t1.*, (@pre_country := country), (@pre_category := category)
FROM
tb_api_app_income_rank t1,
(
SELECT
@i := 0,
@pre_country := '',
@pre_category := ''
) AS tmp
WHERE
created_at = '2017-11-07 00:00:00'
AND rank_type = 'grossing'
AND change_rank = 1000
ORDER BY
country,
category,
rank) t2 where t2.rownum <=3
消耗时间: 0.113s
由上得出结论，使用游标而不是连表或嵌套子查询，速度快翻几千倍
