mysql 前10 后10_MySQL时间段分组排序后取前10的问题？

# 创建表

CREATE TABLE `orders` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`user_id` bigint(20) NOT NULL COMMENT '用户id',

`battery_id` int(11) NOT NULL COMMENT '充电宝id',

`city_id` int(11) NOT NULL COMMENT '城市id',

`start_time` datetime NOT NULL COMMENT '订单起始时间',

`end_time` datetime NOT NULL COMMENT '订单结束时间',

PRIMARY KEY (`id`),

KEY `idx_user_id` (`user_id`),

KEY `idx_battery_id` (`battery_id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=1284 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充电宝订单表';

# 模拟数据

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(1, 500, 15, '2019-10-01 18:18:10', '2019-10-01 18:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(1, 500, 15, '2019-10-01 19:18:10', '2019-10-01 19:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(2, 501, 15, '2019-10-01 18:18:10', '2019-10-01 18:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(3, 502, 16, '2019-10-01 18:18:10', '2019-10-01 18:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(1, 500, 15, '2019-10-02 18:18:10', '2019-10-02 18:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(4, 500, 15, '2019-10-02 19:18:10', '2019-10-02 19:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(5, 501, 15, '2019-10-02 18:18:10', '2019-10-02 18:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(6, 502, 16, '2019-10-02 18:18:10', '2019-10-02 18:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(7, 500, 15, '2019-10-03 18:18:10', '2019-10-03 18:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(1, 500, 15, '2019-10-03 19:18:10', '2019-10-03 19:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(1, 501, 15, '2019-10-03 18:18:10', '2019-10-03 18:18:10');

insert into orders (user_id,battery_id,city_id, start_time, end_time) values(8, 502, 16, '2019-10-03 18:18:10', '2019-10-03 18:18:10');

问题

充电宝被使用一次使用率+1，如果同一天被同一用户多次使用，则该用户使用的第二次开始，每次使用率+0.5，次日0点充电宝使用率重置为0；统计2019-10-01 ~ 2019-10-03 23:59:59时间段内使用率最高的2个充电宝，并显示其使用率。

目前所完成的SQL

SELECT *, sum(eve.xl) AS total FROM

(

SELECT battery_id, date_format(start_time, '%Y%m%d') AS days, count(*)/2+0.5 AS xl, user_id

FROM orders

GROUP BY days, battery_id, user_id

ORDER BY xl DESC

) AS eve

GROUP BY days, battery_id

ORDER BY days DESC, total DESC

上面查询语句确实是算出使用率了，但是应该怎么筛选「每天前2使用率」？

求解答，求优化~