改了一下,还是提交不对,麻烦看看了
with b as (/*对数据打标签*/
select usr_id,live_id,enter_time as event_time,1 as flag
from ks_live_t1
union all
select usr_id,live_id,leave_time as event_time,-1 as flag
from ks_live_t1
),
b1 as (/*窗口函数累积求和得到每个直播间每个时刻的在线人数*/
select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
from b
),
b2 as (/*利用窗口函数得到在线人数峰值*/
select live_id,event_time,online_users,max(online_users)over(partition by live_id) as max_online_users
from b1
),
b3 as (/*筛选峰值时点,GROUP BY之后使用max min函数得到第一次和最后一次峰值*/
select live_id,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
from b2
WHERE online_users = max_online_users
group by live_id,max_online_users
),
b4 as (/*连接得直播间名字*/
select b3.live_id,t2.live_nm,max_online_users,first_peak_time,last_peak_time
from b3
left join ks_live_t2 t2 on b3.live_id = t2.live_id
order by max_online_users desc
)
select * from b4;
输出示例没放对
with b as (/*对数据打标签*/
select usr_id,live_id,enter_time as event_time,1 as flag
from ks_live_t1
union all
select usr_id,live_id,leave_time as event_time,-1 as flag
from ks_live_t1
),
b1 as (/*窗口函数累积求和得到每个直播间每个时刻的在线人数*/
select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
from b
),
b2 as (/*利用窗口函数得到在线人数峰值及其时点*/
select live_id,event_time,max(online_users)over(partition by live_id) as max_online_users
from b1
),
b3 as (/*GROUP BY之后使用max min函数得到第一次和最后一次峰值*/
select b2.live_id,t2.live_nm,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
from b2
left join ks_live_t2 t2 on b2.live_id = t2.live_id
group by live_id,live_nm,max_online_users
order by max_online_users desc
)
select * from b3;
运行没问题,但就是提交判定不正确,不知道是哪里出问题了
select sum(timestampdiff(second, call_time, grab_time))/count(1) AS avg_response_time_seconds
from didi_order_rcd
where cancel_time = '1970-01-01 00:00:00'
这样跑出来的答案还是错的是为啥
with t as(
select t1.live_id,t2.live_nm,t2.live_type,
sum(timestampdiff(second,t1.enter_time,t1.leave_time)) as total_duration,
count(distinct t1.usr_id) as total_users
from ks_live_t1 t1
left join ks_live_t2 t2 on t1.live_id = t2.live_id
where enter_time >= '2021-9-12 23:00:00' and enter_time<='2021-9-13 3:59:59'
group by live_id,live_nm,live_type
),
t1 as (
select live_id,live_nm,live_type,total_duration,total_users,total_duration/total_users as avg_duration
from t
),
t2 as (
select live_id,live_nm,live_type,total_duration,total_users,avg_duration,row_number()over(partition by live_type order by avg_duration) as rnk
from t1
)
select live_id,live_nm,live_type,total_duration,total_users,avg_duration
from t2
where rnk=1
order by live_id
实在不知道错哪了,就是得不出正确答案
select city,
sum(if(con like '%多云%',1,0)) as cloudy_days,
concat(round(sum(case when con like '%多云%' then 1 else 0 end)*100/count(1),2),'%') as p
from weather_rcd_china
where year(dt) = '2021'
group by city
order by p desc
with b as (
select usr_id,live_id,enter_time as event_time,1 as flag
from ks_live_t1
union all
select usr_id,live_id,leave_time as event_time,-1 as flag
from ks_live_t1
),
b1 as (
select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
from b
),
b2 as (
select live_id,event_time,online_users,max(online_users)over(partition by live_id) asmax_online_users
from b1
),
b3 as (
select live_id,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
from b2
WHERE online_users = max_online_users
group by live_id,max_online_users
),
b4 as (
select b3.live_id,t2.live_nm,max_online_users,first_peak_time,last_peak_time
from b3
left join ks_live_t2 t2 on b3.live_id = t2.live_id
order by max_online_users desc,live_id desc
)
select * from b4;
with b as (
select usr_id,live_id,enter_time as event_time,1 as flag
from ks_live_t1
union all
select usr_id,live_id,leave_time as event_time,-1 as flag
from ks_live_t1
),
b1 as (
select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
from b
),
b2 as (
select live_id,event_time,online_users,max(online_users)over(partition by live_id) asmax_online_users
from b1
),
b3 as (
select live_id,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
from b2
WHERE online_users = max_online_users
group by live_id,max_online_users
),
b4 as (
select b3.live_id,t2.live_nm,max_online_users,first_peak_time,last_peak_time
from b3
left join ks_live_t2 t2 on b3.live_id = t2.live_id
order by max_online_users desc,live_id
)
select * from b4;
with b as (
select usr_id,live_id,enter_time as event_time,1 as flag
from ks_live_t1
union all
select usr_id,live_id,leave_time as event_time,-1 as flag
from ks_live_t1
),
b1 as (
select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
from b
),
b2 as (
select live_id,event_time,online_users,max(online_users)over(partition by live_id) asmax_online_users
from b1
),
b3 as (
select live_id,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
from b2
WHERE online_users = max_online_users
group by live_id,max_online_users
),
b4 as (
select b3.live_id,t2.live_nm,max_online_users,first_peak_time,last_peak_time
from b3
left join ks_live_t2 t2 on b3.live_id = t2.live_id
order by max_online_users desc
)
select * from b4;
with b as (
select usr_id,live_id,enter_time as event_time,1 as flag
from ks_live_t1
union all
select usr_id,live_id,leave_time as event_time,-1 as flag
from ks_live_t1
),
b1 as (
select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
from b
),
b2 as (
select live_id,event_time,online_users,max(online_users)over(partition by live_id) asmax_online_users
from b1
),
b3 as (
select b2.live_id,t2.live_nm,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
from b2
left join ks_live_t2 t2 on b2.live_id = t2.live_id
WHERE online_users = max_online_users
group by live_id,live_nm,max_online_users
order by max_online_users desc
)
select * from b3;
with b as (
select usr_id,live_id,enter_time as event_time,1 as flag
from ks_live_t1
union all
select usr_id,live_id,leave_time as event_time,-1 as flag
from ks_live_t1
),
b1 as (
select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
from b
),
b2 as (
select live_id,event_time,max(online_users)over(partition by live_id) asmax_online_users
from b1
),
b3 as (
select b2.live_id,t2.live_nm,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
from b2
left join ks_live_t2 t2 on b2.live_id = t2.live_id
group by live_id,live_nm,max_online_users
order by max_online_users desc,live_id desc
)
select * from b3;
with b as (
select usr_id,live_id,enter_time as event_time,1 as flag
from ks_live_t1
union all
select usr_id,live_id,leave_time as event_time,-1 as flag
from ks_live_t1
),
b1 as (
select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
from b
),
b2 as (
select live_id,event_time,max(online_users)over(partition by live_id) asmax_online_users
from b1
),
b3 as (
select b2.live_id,t2.live_nm,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
from b2
left join ks_live_t2 t2 on b2.live_id = t2.live_id
group by live_id,live_nm,max_online_users
order by live_id
)
select * from b3;
with b as (
select usr_id,live_id,enter_time as event_time,1 as flag
from ks_live_t1
union all
select usr_id,live_id,leave_time as event_time,-1 as flag
from ks_live_t1
),
b1 as (
select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
from b
),
b2 as (
select live_id,event_time,max(online_users)over(partition by live_id) asmax_online_users
from b1
),
b3 as (
select b2.live_id,t2.live_nm,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
from b2
left join ks_live_t2 t2 on b2.live_id = t2.live_id
group by live_id,live_nm,max_online_users
order by max_online_users desc
)
select * from b3;
with t as (
select live_id,enter_time as event_time ,1 as flag
from ks_live_t1
union all
select live_id,leave_time as event_time ,-1 as flag
from ks_live_t1
),
t1 as (
select live_id , sum(flag)over(partition by live_id order by event_time) as online_users
from t
),
t2 as (
select t1.live_id ,live_nm,max(online_users) as max_online_users
from t1
left join ks_live_t2 a on t1.live_id = a.live_id
group by live_id,live_nm
order by max_online_users desc
)
select * from t2
with t as (
select live_id,enter_time ,1 as flag
from ks_live_t1
union all
select live_id,leave_time,-1 as flag
from ks_live_t1
),
t1 as (
select live_id , sum(flag)over(partition by live_id order by enter_time) as online_users
from t
order by online_users desc
),
t2 as (
select t1.live_id ,live_nm,max(online_users) as max_online_users
from t1
left join ks_live_t2 a on t1.live_id = a.live_id
group by live_id,live_nm
order by max_online_users desc
)
select * from t2
with t as (
select live_id,enter_time ,1 as flag
from ks_live_t1
union all
select live_id,leave_time,-1 as flag
from ks_live_t1
),
t1 as (
select live_id , sum(flag)over(partition by live_id order by enter_time) as online_users
from t
order by online_users desc
),
t2 as (
select t1.live_id ,live_nm,max(online_users) as max_online_users
from t1
left join ks_live_t2 a on t1.live_id = a.live_id
group by live_id,live_nm
)
select * from t2
select hour(local_call_time) as local_hour ,count(1)cnt
from (
select order_id,
cust_uid,
date_add(call_time,interval -3 hour) as local_call_time,
grab_time,
cancel_time,
finish_time
from didi_order_rcd
) as t
group by hour(local_call_time)
order by cnt desc
select hour(local_call_time) as local_hour ,count(1)cnt
from (
select order_id,
cust_uid,
date_add(call_time,interval -4 hour) as local_call_time,
grab_time,
cancel_time,
finish_time
from didi_order_rcd
) as t
group by hour(local_call_time)
order by cnt desc
select order_id, cust_uid ,call_time ,grab_time ,cancel_time , finish_time
from didi_order_rcd
where cust_uid = 'kjhd30' and cancel_time != '1970-01-01 00:00:00'
order by order_id
limit 1