力扣题目跳转(3166. 计算停车费与时长 - 力扣(LeetCode))
表:
ParkingTransactions
+--------------+-----------+ | Column Name | Type | +--------------+-----------+ | lot_id | int | | car_id | int | | entry_time | datetime | | exit_time | datetime | | fee_paid | decimal | +--------------+-----------+ (lot_id, car_id, entry_time) 是这张表的主键(有不同值的列的组合)。 这张表的每一行包含停车场的 ID,车的 ID,入场和出场时间,以及停车时长的支付费用
题目要求:
编写一个解决方案来找到 所有停车场 中每辆车支付的 总停车费,以及 每 辆车支付的 每小时平均费用(舍入到 2
位小数)。同时,找到每辆车 总花费时间 最多的 停车场。
返回结果表以 car_id
升序 排序。
注意:测试用例的生成方式使得单辆汽车不能同时位于多个停车场。
结果格式如下所示。
示例:
输入:
ParkingTransactions 表:
+--------+--------+---------------------+---------------------+----------+ | lot_id | car_id | entry_time | exit_time | fee_paid | +--------+--------+---------------------+---------------------+----------+ | 1 | 1001 | 2023-06-01 08:00:00 | 2023-06-01 10:30:00 | 5.00 | | 1 | 1001 | 2023-06-02 11:00:00 | 2023-06-02 12:45:00 | 3.00 | | 2 | 1001 | 2023-06-01 10:45:00 | 2023-06-01 12:00:00 | 6.00 | | 2 | 1002 | 2023-06-01 09:00:00 | 2023-06-01 11:30:00 | 4.00 | | 3 | 1001 | 2023-06-03 07:00:00 | 2023-06-03 09:00:00 | 4.00 | | 3 | 1002 | 2023-06-02 12:00:00 | 2023-06-02 14:00:00 | 2.00 | +--------+--------+---------------------+---------------------+----------+输出:
+--------+----------------+----------------+---------------+ | car_id | total_fee_paid | avg_hourly_fee | most_time_lot | +--------+----------------+----------------+---------------+ | 1001 | 18.00 | 2.40 | 1 | | 1002 | 6.00 | 1.33 | 2 | +--------+----------------+----------------+---------------+解释:
- 对于汽车 ID 1001:
总共支付费用:18.00,总小时:7.5,每小时平均费用:2.40,停车场 1 总花费时间最长:4.25 小时。
- 从 2023-06-01 08:00:00 到 2023-06-01 10:30:00 在停车场 1:2.5 小时,费用 5.00
- 从 2023-06-02 11:00:00 到 2023-06-02 12:45:00 在停车场 1:1.75 小时,费用 3.00
- 从 2023-06-01 10:45:00 到 2023-06-01 12:00:00 在停车场 2:1.25 小时,费用 6.00
- 从 2023-06-03 07:00:00 到 2023-06-03 09:00:00 在停车场 3:2 小时,费用 4.00
- 对于汽车 ID 1002:
总共支付费用:6.00,总小时:4.5,每小时平均费用:1.33,停车场 2 总花费时间最长:2.5 小时。
- 从 2023-06-01 09:00:00 到 2023-06-01 11:30:00 在停车场 2:2.5 小时,费用 4.00
- 从 2023-06-02 12:00:00 到 2023-06-02 14:00:00 在停车场 3:2 小时,费用 2.00
注意: 输出表以 car_id 升序排序。
case 1 的建表语句。
CREATE TABLE If not exists ParkingTransactions (
lot_id INT,
car_id INT,
entry_time DATETIME,
exit_time DATETIME,
fee_paid DECIMAL(10, 2)
)Truncate table ParkingTransactions
insert into ParkingTransactions (lot_id, car_id, entry_time, exit_time, fee_paid) values ('1', '1001', '2023-06-01 08:00:00', '2023-06-01 10:30:00', '5.0')
insert into ParkingTransactions (lot_id, car_id, entry_time, exit_time, fee_paid) values ('1', '1001', '2023-06-02 11:00:00', '2023-06-02 12:45:00', '3.0')
insert into ParkingTransactions (lot_id, car_id, entry_time, exit_time, fee_paid) values ('2', '1001', '2023-06-01 10:45:00', '2023-06-01 12:00:00', '6.0')
insert into ParkingTransactions (lot_id, car_id, entry_time, exit_time, fee_paid) values ('2', '1002', '2023-06-01 09:00:00', '2023-06-01 11:30:00', '4.0')
insert into ParkingTransactions (lot_id, car_id, entry_time, exit_time, fee_paid) values ('3', '1001', '2023-06-03 07:00:00', '2023-06-03 09:00:00', '4.0')
insert into ParkingTransactions (lot_id, car_id, entry_time, exit_time, fee_paid) values ('3', '1002', '2023-06-02 12:00:00', '2023-06-02 14:00:00', '2.0')
一 我们先使用 timestampdiff 函数获取两个时间段的分钟差值,再除以 60 换算成小时。
select *, timestampdiff(minute ,entry_time,exit_time)/60 as ht from parkingtransactions;
输出如下
二 使用 cte 然后进行分组,列出停车时长的排名。
with tmp as (select *, timestampdiff(minute ,entry_time,exit_time)/60 as ht from parkingtransactions) select car_id, lot_id, sum(fee_paid) as fp, sum(ht) as ht, rank() over (partition by car_id order by sum(ht) desc) as rn from tmp group by car_id,lot_id;
输出如下
然后再使用分组聚合,对数据进行微操即可。
with tmp as (select *, timestampdiff(minute ,entry_time,exit_time)/60 as ht from parkingtransactions), tmp1 as (select car_id, lot_id, sum(fee_paid) as fp, sum(ht) as ht, rank() over (partition by car_id order by sum(ht) desc) as rn from tmp group by car_id,lot_id) select car_id, sum(fp) as total_fee_paid, round(sum(fp) / sum(ht),2) as avg_hourly_fee, sum(if(rn = 1,lot_id,0)) as most_time_lot from tmp1 group by car_id order by car_id;
这里我使用了 if 判断来获取 停车场号
以上就是全部答案,如果对你有帮助请点个赞,谢谢。
来源:力扣(leecode)
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
转载请注明出处:
我会尽快把力扣上的所有数据库题目发出来。感兴趣的可以点个赞与关注。每天不定时跟新。
标签:00,06,car,time,停车费,2023,3166,id,时长 From: https://blog.csdn.net/CYJ1844/article/details/144084510