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

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

SELECT 
    u.user_id,
    DAYNAME(lr.start_time) AS day_of_week,
    COUNT(*) AS listens_per_day
FROM 
    qqmusic_user_info u
JOIN 
    listen_rcd lr 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 singer_info limit 5;

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'))

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')

select count(distinct usr_id) active_user 
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 year(dt) as Y ,
cast(avg(case when city <=> 'beijing' then substr(tmp_h,1,2) else null end) as decimal(4,2)) as '北京',
cast(avg(case when city <=> 'shanghai' then substr(tmp_h,1,2) else null end) as decimal(4,2)) as 上海, 
cast(avg(case when city <=> 'shenzhen' then substr(tmp_h,1,2) else null end) as decimal(4,2)) as 深圳,
cast(avg(case when city <=> 'guangzhou' then substr(tmp_h,1,2) else null end) as decimal(4,2)) as 广州
from weather_rcd_china where year(dt) 
between 2011 and 2022 group by year(dt)

select year(dt) as Y ,
cast(avg(case when city <=> 'beijing' then substr(tmp_h,1,2) end) as decimal(4,2)) as '北京',
cast(avg(case when city <=> 'shanghai' then substr(tmp_h,1,2) end) as decimal(4,2)) as 上海, 
cast(avg(case when city <=> 'shenzhen' then substr(tmp_h,1,2) end) as decimal(4,2)) as 深圳,
cast(avg(case when city <=> 'guangzhou' then substr(tmp_h,1,2) end) as decimal(4,2)) as 广州
from weather_rcd_china where year(dt) 
between 2011 and 2022 group by year(dt)

select year(dt) as Y
    ,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京'
    ,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
    ,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
    ,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
from
    weather_rcd_china
where 
    year(dt) between 2011 and 2022
group by 
    year(dt)

select year(dt) Y ,
cast(avg(case when city <=> 'beijing' then substr(tmp_h,1,2) end) as decimal(4,2)) as '北京',
cast(avg(case when city <=> 'shanghai' then substr(tmp_h,1,2) end) as decimal(4,2)) as 上海, 
cast(avg(case when city <=> 'shenzhen' then substr(tmp_h,1,2) end) as decimal(4,2)) as 深圳,
cast(avg(case when city <=> 'guangzhou' then substr(tmp_h,1,2) end) as decimal(4,2)) as 广州
from weather_rcd_china where year(dt) 
between 2011 and 2022 group by year(dt)

select year(dt) V ,
cast(avg(case when city <=> 'beijing' then substr(tmp_h,1,2) end) as decimal(4,2)) as '北京',
cast(avg(case when city <=> 'shanghai' then substr(tmp_h,1,2) end) as decimal(4,2)) as 上海, 
cast(avg(case when city <=> 'shenzhen' then substr(tmp_h,1,2) end) as decimal(4,2)) as 深圳,
cast(avg(case when city <=> 'guangzhou' then substr(tmp_h,1,2) end) as decimal(4,2)) as 广州
from weather_rcd_china where year(dt) 
between 2011 and 2022 group by year(dt)

select year(dt) V ,
cast(avg(case when city <=> 'beijing' then substr(tmp_h,1,2) end) as decimal(4,2)) as '北京',
cast(avg(case when city <=> 'shanghai' then substr(tmp_h,1,2) end) as decimal(4,2)) as '上海', 
cast(avg(case when city <=> 'shenzhen' then substr(tmp_h,1,2) end) as decimal(4,2)) as '深圳',
cast(avg(case when city <=> 'guangzhou' then substr(tmp_h,1,2) end) as decimal(4,2)) as '广州'
from weather_rcd_china where year(dt) 
between 2011 and 2022 group by year(dt)

select year(dt) V ,avg(substr(tmp_h,1,2) && city <=> 'beijing') '北京',
avg(substr(tmp_h,1,2) && city <=> 'shanghai') '上海',
avg(substr(tmp_h,1,2) && city <=> 'shenzhen') '深圳',
avg(substr(tmp_h,1,2) && city <=> 'guangzhou') '广州'
from weather_rcd_china where year(dt) 
between '2011' and '2022' group by year(dt)

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

SELECT 
    sum(TIMESTAMPDIFF(SECOND, call_time, grab_time))/count(1) AS avg_response_time_seconds
FROM 
    didi_order_rcd
WHERE 
    grab_time != '1970-01-01 00:00:00';

select * from cmb_usr_trx_rcd where usr_id <=> '5201314520' and date(trx_time) between '2024-09-01'
and '2024-09-30' and(hour(trx_time) >= 22 or (hour(trx_time) between 0 and 5)) order by trx_time

select * from cmb_usr_trx_rcd where usr_id <=> '5201314520' and date(trx_time) between '2024-09-01'
and '2024-09-30' and(hour(trx_time) >= 22) or (hour(trx_time) between 0 and 5) order by trx_time

select * from cmb_usr_trx_rcd where usr_id <=> '5201314520' and date(trx_time) between '2024-09-01'
and '2024-09-30' andhour(trx_time) >= 22 or (hour(trx_time) between 0 and 5) order by trx_time

select * from cmb_usr_trx_rcd where usr_id <=> '5201314520' and date(trx_time) between '2024-09-01'
and '2024-09-30' andhour(trx_time) >= 22 or hour(trx_time) between 0 and 5 order by trx_time