分组查询前十名
表结构
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 t1WHERE created_at = '2017-11-07 00:00:00'AND rank_type = 'grossing'AND change_rank = 1000AND ( 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 t1left 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 = 1000AND t1.country = t2.countryAND t1.category = t2.categoryAND t1.rank > t2.rankwhere t1.created_at = '2017-11-07 00:00:00'AND t1.rank_type = 'grossing'AND t1.change_rank = 1000group by t1.country, t1.category, t1.product_idhaving count(t1.product_id)<3order 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 tmpWHERE created_at = '2017-11-07 00:00:00'AND rank_type = 'grossing'AND change_rank = 1000ORDER BY country, category, rank) t2 where t2.rownum <=3
消耗时间: 0.113s
由上得出结论，使用游标而不是连表或嵌套子查询，速度快翻几千倍