select k1.live_id,live_nm,count(*) enter_cnt
from ks_live_t1 k1
join ks_live_t2 k2
on k1.live_id=k2.live_id
where enter_time between '2021-09-12 23:00:00' and '2021-09-12 23:59:59'
group by k1.live_id,live_nm
order by enter_cnt desc
limit 5

select
    case 
        when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
        when lower(mch_nm) regexp '按摩|保健|休闲|spa|养生|会所' then '按摩、保健、休闲、养生、SPA、会所'
    end as reg_rules,
    count(distinct mch_nm) as mch_cnt
from
    cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
   or lower(mch_nm) regexp '按摩|保健|休闲|spa|养生|会所'
group by reg_rules
order by mch_cnt desc;

select length(singer_name),count(singer_name)
from singer_info
group by length(singer_name)

select singer_name,char_length(singer_name) len
from singer_info

select upper(singer_name) from singer_info

select dt,tmp_h,tmp_l,con from weather_rcd_china
where con like '%雪%'
and city ='beijing'

select dt,tmp_h,tmp_l,con,wnd from weather_rcd_china
where con like '%雪%'
and city ='beijing'

select dt,tmp_h,tmp_l,con,wnd from weather_rcd_china
where con like '雪'
and city ='beijing'

select left(name,1) as surname,count(*) as cnt from students
group by surname
order by cnt desc
limit 5

select city,count(dt) as cloudy_days,concat(round(count(dt)/365*100,2),'%') as p from weather_rcd_china
where year(dt)=2021
and con like '%多云%'
group by city
order by p desc

select * from hand_permutations
where left(card1,1)=left(card2,1)

select * from hand_permutations
where right(card1,1)=right(card2,1)
and
(card1 like 'A%' or card2 like 'A%')
order by id

select * from hand_permutations
where concat(card1,card2) like'%A%K%'
or concat(card1,card2) like'%A%A%'
or concat(card1,card2) like'%K%A%'
or concat(card1,card2) like'%K%K%'
order by id

select
    case 
        when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
        when lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
    end as reg_rules,
    count(distinct mch_nm) as mch_cnt
from
    cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
   or lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;

select
    case 
        when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
        when lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
    end as reg_rules,
    count(distinct mch_nm) as mch_cnt
from
    cmb_usr_trx_rcd
where lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;

select '按摩、保健、休闲、养生、SPA、会所' as reg_rules,count(distinct mch_nm)
from cmb_usr_trx_rcd
where mch_nm like '%按摩%'
or mch_nm like '%保健%'
or mch_nm like '%休闲%'
or mch_nm like '%养生%'
or mch_nm like '%SPA%'
or mch_nm like '%会所%'
union 
select '按摩保健休闲'as reg_rules,count(distinct mch_nm)
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'

select count(distinct mch_nm) as mch_cnt from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'

select user_id,
dayname(start_time)
as day_of_week,
count(*) as listens_per_day
from listen_rcd
group by user_id,day_of_week
order by user_id,day_of_week

select user_id,
case when dayofweek(start_time) =1 then 'Sunday'
when dayofweek(start_time) =2 then 'Monday'
when dayofweek(start_time) =3 then 'Tuesday'
when dayofweek(start_time) =4 then 'Wednesday'
when dayofweek(start_time) =5 then 'Thursday'
when dayofweek(start_time) =6 then 'Friday'
when dayofweek(start_time) =7 then 'Saturday'
end
as day_of_week,
count(*) as listens_per_day
from listen_rcd
group by user_id,day_of_week
order by user_id,day_of_week

select * from cmb_usr_trx_rcd
where usr_id =5201314520
and
(date(trx_time) between '2024-06-08' and '2024-06-10'
or
date(trx_time) between '2024-09-15' and '2024-09-17')
order by trx_time asc