jl0427

分类

/ 注册

全部文章（共50篇）

题解 | #各城市最大同时等车人数#

WITH t1 AS( SELECT city,SUM(uv)OVER(PARTITION BY city ORDER BY uv_time,uv DESC) AS uv_cnt FROM ( SELECT city,event_time uv_time,1 AS uv FRO...

2023-07-20

0 261

题解 | #每个城市中评分最高的司机信息#

select city,driver_id,round(avg_grade,1) avg_grade , round(avg_order_num,1) avg_order_num, round(avg_mileage,3) avg_mileage from (select *, rank() ove...

2023-07-18

2 382

题解 | #有取消订单记录的司机平均评分#

select ifnull(driver_id,'总体') as driver_id, round(avg(grade),1) as avg_grade from tb_get_car_order a left join tb_get_car_record b on a.order_id = b....

2023-07-18

0 306

题解 | #2021年国庆#

SELECT "北京" as city, ROUND(AVG(order_num), 3) as avg_order_num, ROUND(AVG(income), 3) as avg_income FROM ( SELECT driver_id, COUNT(o...

2023-07-18

2 304

题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

select user_id,count(1) as day_count from ( select distinct sales_date,user_id,dense_rank()over(partition by user_id order by sales_date)rk from s...

2023-07-17

0 318

题解 | #近一个月发布的视频中热度最高的top3视频#

SELECT video_id, round((100*finish_rate+5*like_index+3*comment_index+2*retweet_index)/(fresh_index+1),0) hot_index FROM( SELECT a.video_id, ...

2023-07-01

0 235

题解 | #各个视频的平均完播率#

select a.video_id,round(sum(IF(a.end_time-a.start_time >= b.duration,1,0))/count(a.start_time),3) as avg_comp_play_rate from tb_user_video_log a le...

2023-06-27

0 205

题解 | #平均活跃天数和月活人数#

select date_format(submit_time, '%Y%m') as month, round((count(distinct uid, date_format(submit_time, '%y%m%d'))) / count(distinct uid), 2) as avg_act...

2023-06-27

2 260

题解 | #SQL类别高难度试卷得分的截断平均值#

select b.tag, b.difficulty, round((sum(a.score)-max(a.score)-min(a.score))/(count(a.score)-2),1) as clip_avg_score from exam_record a inner join exam...

2023-06-27

1 219

题解 | #返回产品名称和每一项产品的总订单数#

SELECT prod_name,IF(orders IS NULL,0,orders) AS orders FROM Products AS p LEFT JOIN (SELECT prod_id,COUNT(*) AS orders FROM OrderItems GROUP BY prod_...

2023-06-27

1 237