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

  • Z时代
  • 2024-01-10
  • 分类：IT

# 创建表
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使用率」？

求解答，求优化~

回答：

这行代码后面没有逗号，执行有错误。

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

执行你完成的SQL，感觉你基本都完成了。
把你的sql简单改了下，不知道是否是你需要的结果。
キャプチャ.JPG

你好，这个是我改了以后的结果。
1.开始你写好的统计数，500最大 1.5
キャプチャ.JPG
2.最后结果
キャプチャ1.JPG

以上是 MySQL时间段分组排序后取前10的问题？ 的全部内容， 来源链接： utcz.com/p/171511.html

回到顶部