select 
LENGTH(singer_name) AS LEN,
count(*) AS CNT_LEN
from singer_info
group by LEN
order by LEN ASC;

select singer_name,CHAR_LENGTH(singer_name) AS LEN
from singer_info

select UPPER(singer_name) 
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 surname,
count(*) AS cnt
from students
group by left(name,1)
order by cnt desc
limit 5;

select 
city, 
sum(IF(con LIKE '%多云%',1,0)) AS cloudy_days,
CONCAT(ROUND(sum(IF(con LIKE '%多云%',1,0))/count(*)*100,2),'%') AS P
from weather_rcd_china
where YEAR(dt)=2021
group by city
order by P desc;

select * from hand_permutations
where LEFT(card1,1)=LEFT(card2,1)
order by id ASC;

select *
from hand_permutations
where
right(card1,1)=right(card2,1)
AND CONCAT(card1,card2) LIKE ('%A%')
ORDER BY id ASC;

select * 
from hand_permutations
where card1 LIKE 'A_' AND card2 LIKE 'A_'
OR card1 LIKE 'A_' AND card2 LIKE 'K_'
OR card1 LIKE 'K_' AND card2 LIKE 'K_'
OR card1 LIKE 'K_' AND card2 LIKE 'A_'
order by id ASC;

select 
case when mch_nm LIKE '%按摩保健休闲%' THEN '按摩保健休闲'
when 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 regexp '按摩|保健|休闲|养生|SPA|会所'
OR mch_nm LIKE '%按摩保健休闲%'
group by reg_rules
order by mch_cnt DESC;

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 *
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;

select * 
from cmb_usr_trx_rcd
where usr_id=5201314520
 and (trx_time>='2024-06-08' and trx_time<'2024-06-11'
or trx_time>='2024-09-15' and trx_time<'2024-09-18')
order by trx_time asc;

select * 
from cmb_usr_trx_rcd
where usr_id=5201314520
and trx_time>='2024-06-08' and trx_time<'2024-06-11'
or trx_time>='2024-09-15' and trx_time<'2024-09-18'
order by trx_time asc;

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) 通勤,
count(distinct case when TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' then usr_id END) 午休,
count(distinct case when TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' 
OR TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' then usr_id END) 临睡
from user_login_log
where login_time>=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 MONTH),'%Y-%m-01 00:00:00')
AND login_time<DATE_FORMAT(CURDATE(),'%Y-%m-01');

select count(distinct usr_id) AS ACTIVE
from user_login_log
where login_time>=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 MONTH),'%Y-%m-01 00:00:00')
AND login_time<DATE_FORMAT(current_date(),'%Y-%m-01');

select count(distinct usr_id) AS ACTIVE
from user_login_log
where login_time>=DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 MONTH),'%Y-%m-01 00:00:00')
AND login_time<DATE_FORMAT(current_date(),'%Y-%m-01')
order by login_time DESC;

select 
year(dt) AS Y,
round(avg(case when city='beijing' then CAST(SUBSTRING_INDEX(tmp_h,'℃',1) AS DECIMAL(4)) END),2) AS 北京,
round(avg(case when city='shanghai' then CAST(SUBSTRING_INDEX(tmp_h,'℃',1) AS DECIMAL(4)) END),2) AS 上海,
round(avg(case when city='shenzhen' then CAST(SUBSTRING_INDEX(tmp_h,'℃',1) AS DECIMAL(4)) END),2) AS 深圳,
round(avg(case when city='guangzhou' then CAST(SUBSTRING_INDEX(tmp_h,'℃',1) AS DECIMAL(4)) END),2) AS 广州
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
order by year(dt);

select 
city,
sum(if(con like '%雪%',1,0)) as snowy_days
from weather_rcd_china 
where month(dt) in (12,1,2)
group by city
order by snowy_days desc;