select
a.live_id,
b.live_nm,
count(distinct usr_id) as cnt
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where '2021-09-12 23:48:38' between a.enter_time and a.leave_time
group bya.live_id,
b.live_nm
order by count(*) desc

select
a.live_id,
b.live_nm,
count(*) as cnt
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where '2021-09-12 23:48:39' between a.enter_time and a.leave_time
group bya.live_id,
b.live_nm
order by count(*) desc

select
a.live_id,
b.live_nm,
count(*) as cnt
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where '2021-09-12 23:48:39' between a.enter_time and a.leave_time
group bya.live_id,
b.live_nm

with t1 as(
select 
a.live_id,
b.live_nm,
b.live_type,
sum(timestampdiff(second,a.enter_time,a.leave_time)) as td,
count(distinct a.usr_id) as tu
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where a.enter_time between '2021-09-12 23:00:00' and '2021-09-13 03:59:59'
group by a.live_id,
b.live_nm,
b.live_type
),
t2 as(
select
live_id,
live_nm,
live_type,
td,
tu,
td/tu as aut,
row_number()over(partition by live_type order by td/tu desc) as rnk
from t1
)
select
live_id,
live_nm,
live_type,
td,
tu,
aut
from t2
where rnk=1

with t1 as(
select 
a.live_id,
b.live_nm,
b.live_type,
count(*) cnt,
row_number()over(partition by b.live_type order by count(*) desc) as rnk
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where date_format(a.enter_time,'%Y-%m-%d %H')='2021-09-12 23'
group by a.live_id,
b.live_nm,
b.live_type)
select 
live_id,
live_nm,
live_type,
cnt
from t1
where rnk=1

select
live_id,
live_nm,
live_type,
cnt
from
(select 
a.live_id,
b.live_nm,
b.live_type,
count(*) cnt,
row_number()over(partition by b.live_type order by count(*) desc) as rnk
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where date_format(a.enter_time,'%Y-%m-%d %H')='2021-09-12 23'
group by a.live_id,
b.live_nm,
b.live_type)t1

select
live_id,
live_nm,
live_type,
cnt
from
(select 
a.live_id,
b.live_nm,
b.live_type,
count(*) cnt,
row_number()over(partition by b.live_type order by count(*) desc) as rnk
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where date_format(a.enter_time,'%Y-%m-%d %H'='2021-09-12 23')
group by a.live_id,
b.live_nm,
b.live_type)t1
where rnk=1

SELECT 
    t2.live_id,
    t2.live_nm,
    COUNT(*) AS cnt
FROM 
    ks_live_t1 t1
JOIN 
    ks_live_t2 t2
ON 
    t1.live_id = t2.live_id
WHERE 
    DATE_FORMAT(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
GROUP BY 
    t1.live_id, t2.live_nm
ORDER BY 
    cnt DESC
LIMIT 5;

select 
a.live_id,
b.live_nm,
count(*)as cnt
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where date_format(a.enter_time,'%Y-%m-%d %H') ='2021-09-12 23'
group by a.live_id, b.live_nm

select 
a.live_id,
b.live_nm,
count(*) cnt
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where date_format(a.enter_time,'%Y-%m-%d %H') ='2021-09-12 23'
group by a.live_id, b.live_nm
order by cnt desc
limit 5

select 
a.live_id,
b.live_nm,
count(*)
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where date_format(a.enter_time,'%Y-%m-%d %H') ='2021-09-12 23'
group by a.live_id, b.live_nm

select 
a.live_id,
b.live_nm,
count(*)
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where date_format(a.enter_time,'%y-%m-%d %h') ='2021-09-12 23'
group by a.live_id, b.live_nm

select 
a.live_id,
b.live_nm,
count(distinct a.usr_id)
from ks_live_t1 a
left join ks_live_t2 b on a.live_id=b.live_id
where date_format(a.enter_time,'%y-%m-%d %h') ='2021-09-12 23'
group by a.live_id, b.live_nm

select
lpad(hor,2,'0'),
cnt
from
(select 
hour(enter_time) hor,
count(distinct usr_id) cnt
from ks_live_t1 
group by hour(enter_time))t1

select 
hour(enter_time),
count(distinct usr_id)
from ks_live_t1 
group by hour(enter_time)

select 
count(*),
sum(case when grab_time!='1970-01-01 00:00:00' then 1 end),
concat(round(sum(case when grab_time!='1970-01-01 00:00:00' then 1 end)/count(*)*100,2),'%')
from didi_order_rcd 
where date(call_time)='2021-05-03'

select 
* 
from didi_order_rcd 
where date(call_time)='2021-05-03' and cancel_time!='1970-01-01 00:00:00'

select 
* 
from didi_order_rcd 
where date(call_time)='2021-05-04' and cancel_time!='1970-01-01 00:00:00'

select
tm,
sum(case when car_cls='A' then 1 else 0 end),
count(*) as cnt_t,
round(sum(case when car_cls='A' then 1 else 0 end)/count(*)*100,2)
from
(select
cust_uid,
substr(start_tm,1,2) as tm,
car_cls
from didi_sht_rcd 
)t1
where tm between 18 and 24
group by tm

select
start_loc,
end_loc,
start_ctg,
end_ctg,
cnt
from
(select
start_loc,
end_loc,
count(*) cnt,
start_ctg,
end_ctg,
dense_rank()over(partition by case when end_ctg='酒店' then start_ctg else end_ctg end order by count(*) desc) as rnk
from
(select 
a.start_loc,
a.end_loc,
b.loc_ctg as start_ctg,
c.loc_ctg as end_ctg
from didi_sht_rcd a
left join loc_nm_ctg b on a.start_loc=b.loc_nm
left join loc_nm_ctg c on a.end_loc=c.loc_nm
where (b.loc_ctg='酒店' or c.loc_ctg='酒店')and c.loc_ctg is not null and b.loc_ctg is not null)t1
group by start_loc,end_loc,start_ctg,end_ctg
order by cnt desc
)t2
where rnk=1
order by cnt desc