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

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

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

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

select city, 
		sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
		concat(cast(sum(case when con like '%多云%' then 1 else 0 end) / count(con) * 100 as decimal(4,2)), '%') as p
from weather_rcd_china
where year(dt) = '2011'
group by city
order by p desc

select city, 
		sum(case when con like '多云%' then 1 else 0 end) as cloudy_days,
		concat(cast(sum(case when con like '多云%' then 1 else 0 end) / count(con) * 100 as decimal(4,2)), '%') as p
from weather_rcd_china
where year(dt) = '2011'
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) rlike 'A*'
order by id

select * 
from hand_permutations
where concat(card1, card2) rlike '^A'
order by id

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

select id, card1, card2
from hand_permutations
where concat(card1,card2) rlike 'A.A.|\A.K.|\K.K.|\K.A.'
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

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

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

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

select 	lr.user_id,
		dayname(lr.start_time) as day_of_week,
count(1) as listens_per_day
from 
	listen_rcd lr
group by
	lr.user_id, day_of_week
order by
	lr.user_id asc, day_of_week asc

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

select 	u.user_id,
		dayname(lr.start_time) as day_of_week,
count(*) as listens_per_day
from 
	listen_rcd lr
join
	qqmusic_user_info u on u.user_id = lr.user_id
group by
	u.user_id, day_of_week
order by
	u.user_id asc, day_of_week asc

select *
from 
	cmb_usr_trx_rcd
where 
	(date_format(trx_time, '%Y-%m-%d') between '2024-06-08' and '2024-06-10'
or date_format(trx_time, '%Y-%m-%d') between '2024-09-15' and '2024-09-17')
and usr_id = 5201314520
order by trx_time