select
*
from cmb_usr_trx_rcd
where year(trx_time) in(2022,2023,2024)
andday(trx_time) in(28,29,30,31,1)
and usr_id=5201314520
and trx_amt>300
order by trx_time asc;
select * from cmb_usr_trx_rcd
where
date(trx_time) between '2024-06-08' and'2024-06-10'
and hour(trx_time) in(11,12,18,19)
and mch_nm='红玫瑰按摩保健休闲'
order by
trx_time asc;
select * from cmb_usr_trx_rcd
where
(date(trx_time) between '2024-06-08' and '2024-06-10'
or date(trx_time) between '2024-09-15' and '2024-09-17')
and usr_id=5201314520
order by
trx_time asc;
select * from cmb_usr_trx_rcd
where
(date(trx_time) between '2024-06-08' and '2024-06-10'
or date(trx_time) between '2024-09-15' and '2024-09-17')
and usr_id=5201314520;
with a as
(select
uid
,kl.video_id
,timestampdiff(second,start_time,end_time) dt
,duration
,if_AI_talking
,if_hint
,screen_type
,case whenif_AI_talking=1 andif_hint=1then'AI_with_hint'
when if_AI_talking=1 andif_hint=0 then 'AI_no_hint'
when if_AI_talking=0 andif_hint=1 then 'no_AI_with_hint'
when if_AI_talking=0 andif_hint=0 then 'no_AI_no_hint'
end lable
from
ks_video_wat_log kl
left join
ks_video_inf ki
on
kl.video_id=ki.video_id
)
select
screen_type
,round(count(distinct case whenlable= 'AI_with_hint' anddt>duration then uid else null end)/count(distinct case whenlable= 'AI_with_hint' then uid else null end)*100,2)AI_with_hint
,round(count(distinct case whenlable= 'AI_no_hint' anddt>duration then uid else null end)/count(distinct case whenlable= 'AI_no_hint' then uid else null end)*100,2)AI_no_hint
,round(count(distinct case whenlable= 'no_AI_with_hint' anddt>duration then uid else null end)/count(distinct case whenlable= 'no_AI_with_hint' then uid else null end)*100,2)no_AI_with_hint
,round(count(distinct case whenlable= 'no_AI_no_hint' anddt>duration then uid else null end)/count(distinct case whenlable= 'no_AI_no_hint' then uid else null end)*100,2)no_AI_no_hint
from
a
group by
screen_type;
with p as
(select
*
,1+a.i+b.i1*10 as n
from
(
select
0 i
union
select
1
union
select
2
union
select
3
union
select
4
union
select
5
union
select
6
union
select
7
union
select
8
union
select
9
) as a
,
(
select
0 i1
union
select
1
union
select
2
union
select
3
union
select
4
union
select
5
union
select
6
union
select
7
union
select
8
union
select
9
) as b
where
1+a.i+b.i1*10<=(
select
max(length(tag)-length(replace(tag,' ',''))+1)
from
ks_video_inf
)
),
w as
(
select
video_id
,substring_index(substring_index(tag,' ',p.n),' ',-1) tag
,substring_index(tag,' ',p.n)
from
ks_video_inf ks
left join
p
on
length(tag)-length(replace(tag,' ',''))+1>=p.n
)
select
tag
,count(1)
from
ks_video_wat_log lg
left join
w
on
lg.video_id=w.video_id
where
date(start_time)>=date_sub(current_date,interval 1 month)
group by
tag
order by
count(1) desc
limit 1;
select
count(distinct uid) total_users
from
(select
uid
,count(1)
from
ks_video_wat_log a
left join ks_video_inf b
on a.video_id=b.video_id
where
date(start_time)>=date_sub(current_date,interval 1 month)
and duration>=180
and timestampdiff(second,start_time,end_time)>=duration
group by
uid
having count(1)>=2
) p;
select
count(distinct uid)
from
(select
uid
,sum(case whenduration/60>=3 and(timestampdiff(second,start_time,end_time)/60)>=(duration/60) then 1 else 0 end)type
from
ks_video_wat_log a
left join ks_video_inf b
on a.video_id=b.video_id
where
date(start_time)>=date_sub(current_date,interval 1 month)
group by
uid
having sum(case whenduration/60>=3 and(timestampdiff(second,start_time,end_time)/60)>=(duration/60) then 1 else 0 end)>=2
) p;
select
count(distinct uid)
from
(select
uid
,sum(case whenduration/60>3 and(timestampdiff(second,start_time,end_time)/60)>(duration/60) then 1 else 0 end)type
from
ks_video_wat_log a
left join ks_video_inf b
on a.video_id=b.video_id
where
date(start_time)>=date_sub(current_date,interval 1 month)
group by
uid
having sum(case whenduration/60>3 and(timestampdiff(second,start_time,end_time)/60)>(duration/60) then 1 else 0 end)>=2
) p
select
a.video_id
,title
,round(count(distinct case whentimestampdiff(second,start_time,end_time)>=duration then uid else null end)/count(distinct uid)*100,4) rate
from
ks_video_wat_log a
left join
ks_video_inf b
on
a.video_id=b.video_id
where
date(start_time)>=date_sub(current_date,interval 1 month)
group by
a.video_id
,title
order by
rate desc
limit 5;
select
a.video_id
,title
,round(count(distinct case whentimestampdiff(second,start_time,end_time)>=duration then uid else null end)/count(distinct uid)*100,2) rate
from
ks_video_wat_log a
left join
ks_video_inf b
on
a.video_id=b.video_id
where
date(start_time)>=date_sub(current_date,interval 1 month)
group by
a.video_id
,title
order by
rate desc
limit 5;
select
a.video_id
,title
,round(sum(case whentimestampdiff(second,start_time,end_time)>=duration then 1 else 0 end)/count(1)*100,2) rate
from ks_video_wat_log a
left joinks_video_inf b
on a.video_id=b.video_id
where
date(start_time)>=date_sub(current_date,interval 1 month)
group by
a.video_id
,title
order by
rate desc
limit 5;
select
a.video_id
,title
,round(sum(timestampdiff(second,start_time,end_time))/3600,2) total_play_duration_hours
from
ks_video_wat_log a
left joinks_video_inf b
on a.video_id=b.video_id
where
date(start_time)>=date_sub(current_date,interval 1 month)
group by
a.video_id
,title
order by
total_play_duration_hours desc
limit 5;
select
a.video_id
,title
,round(sum(timestampdiff(minute,start_time,end_time))/60,2)
from
ks_video_wat_log a
left joinks_video_inf b
on a.video_id=b.video_id
where
date(start_time)>=date_sub(current_date,interval 1 month)
group by
a.video_id
,title
order by
round(sum(timestampdiff(minute,start_time,end_time))/60,2) desc
limit 5
select
a.video_id
,title
,round(sum(timestampdiff(minute,start_time,end_time))/60,2)
from
ks_video_wat_log a
left joinks_video_inf b
on a.video_id=b.video_id
where
date(start_time) >=date_sub(start_time,interval 1 month)
group by
a.video_id
,title
order by
round(sum(timestampdiff(minute,start_time,end_time))/60,2) desc
limit 5
select
a.video_id
,title
,round(sum(timestampdiff(minute,start_time,end_time))/60,2)
from
ks_video_wat_log a
left joinks_video_inf b
on a.video_id=b.video_id
where
date(start_time) >=date_sub(start_time,interval 1 day)
group by
a.video_id
,title
order by
round(sum(timestampdiff(minute,start_time,end_time))/60,2) desc
limit 5