select length(singer_name) as l,count(*)
from singer_info
group by length(singer_name)

select singer_name, length(singer_name) from singer_info

select singer_name,char_length(singer_name) as l
from singer_info

select upper(singer_name) as a
from singer_info

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

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

select 
	city,
sum(case when con like '%多云%' then 1 else 0 end) as cnt,
concat(cast( sum(case when con like '%多云%' then 1 else 0 end)/count(1)*100 as decimal(10,2)),'%') as a
from weather_rcd_china
where 
	year(dt)=2021 
group by 
	city

select 
	city,
sum(case when con like '%多云%' then 1 else 0 end) as cnt,
cast( sum(case when con like '%多云%' then 1 else 0 end)/count(1) as decimal(10,2)) as a
from weather_rcd_china
where 
	year(dt)=2021 
group by 
	city

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

select * 
from hand_permutations
where right(card1,1)=right(card2,1) and 
	(left(card1,1)='A' or left(card2,1)='A' )

select * 
from hand_permutations 
where left(card1,1) in ('A','K') and left(card2,1) in ('A','K')

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 count(distinct mch_nm) as a
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'

select count(1) as x
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
group by mch_nm

select user_id,dayname(start_time) as s,count(1)
from listen_rcd
group by 1,2
order by 1,2

select * from cmb_usr_trx_rcd
where 
	usr_id=5201314520 and 
	(date(trx_time) between '2024-6-8' and '2024-6-10' 
 or date(trx_time) between '2024-9-15' and '2024-9-17' )

select 
	count(distinct case when time(login_time) between '07:30:00' and '09:30:00' 
or time(login_time) between '18:30:00' and '20:30:00' then usr_id end) as '通勤',
count(distinct case when time(login_time) between '11:30:00' and '14:00:00' 
then usr_id end) as '午休',
count(distinct case when time(login_time) between '22:30:00' and '24:00:00' 
or time(login_time) between '00:00:00' and '01:00:00' then usr_id end) as '临睡'
from user_login_log
WHERE 
login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') 
AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');

select 
	count(distinct case when time(login_time) between '07:30:00' and '09:30:00' 
or time(login_time) between '18:30:00' and '20:30:00' then usr_id end) as '通勤',
count(distinct case when time(login_time) between '11:30:00' and '14:00:00' 
then usr_id end) as '午休',
count(distinct case when time(login_time) between '22:30:00' and '01:00:00' 
then usr_id end) as '临睡'
from user_login_log
WHERE 
login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') 
AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');

SELECT 
    COUNT(DISTINCT usr_id) AS active_users
FROM 
    user_login_log
WHERE 
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');

select count(distinct usr_id)
from user_login_log
wherelogin_time>= DATE_SUB(NOW(), INTERVAL 1 MONTH) and login_time<now()