-- rollup方法(mysql8.0+)
with t1 as (
select date_format(trx_time,'%Y-%m') as trx_mon,mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where trx_time>='2024-01-01' and trx_time<'2025-01-01' and usr_id=5201314520
group by mch_nm,trx_mon
with rollup
having trx_mon is not null or mch_nm is not null
),
t2 as (
select coalesce(trx_mon,2024) as trx_mon,mch_nm,sum_trx_amt,row_number() over(partition by coalesce(trx_mon,2024) order by sum_trx_amt desc) as rk
from t1
)
select trx_mon,mch_nm,sum_trx_amt
from t2
where rk<=3
order by trx_mon asc,sum_trx_amt desc
select
'2022-10-03 17:20:20' as time_he_love_me,
datediff(now(),'2022-10-03 17:20:20') as days_we_falling_love,
timestampdiff(hour,'2022-10-03 17:20:20',now()) as hours_we_falling_love,
datediff((select min(trx_time) from cmb_usr_trx_rcd where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'),'2022-10-03 17:20:20')
as days_he_fvck_else
with dates as (
select distinct date_format(date_value,'%Y-%m') as trx_mon
from date_table
where date_value between '2023-01-01' and '2024-06-30'
),
costs as (
select
date_format(trx_time,'%Y-%m') as trx_mon,
last_day(trx_time) as last_day,
max(day(last_day(trx_time))) as day_of_mon,
sum(trx_amt) as trx_amt,
count(*) as trx_cnt,
round(sum(trx_amt)/max(day(last_day(trx_time))),2) as avg_day_amt,
round(count(*)/max(day(last_day(trx_time))),2)as avg_day_cnt
from cmb_usr_trx_rcd c1 left join cmb_mch_typ c2 using (mch_nm)
where usr_id=5201314520
and trx_time>='2023-01-01' and trx_time<'2024-07-01'
and (mch_typ='休闲娱乐' or mch_typ is null)
and trx_amt>288
and hour(trx_time) in (23,0,1,2)
group by trx_mon,last_day
)
select
d.trx_mon,
coalesce(last_day,'1900-01-01') as last_day,
coalesce(day_of_mon,0) as day_of_mon,
coalesce(trx_amt,0.00) as trx_amt,
coalesce(trx_cnt,0) as trx_cnt,
coalesce(avg_day_amt,0.00) as avg_day_amt,
coalesce(avg_day_cnt,0.00) as avg_day_cnt
from dates d left join costs c using (trx_mon)
order by d.trx_mon
SELECT
DATE_FORMAT(trx_time, '%Y-%m') AS trx_mon,
LAST_DAY(trx_time) AS last_day,
DAY(LAST_DAY(trx_time)) AS days_of_mon,
SUM(trx_amt) AS trx_amt,
COUNT(*) AS trx_cnt,
SUM(trx_amt) / MAX(DAY(LAST_DAY(trx_time))) AS avg_day_amt,
COUNT(*) / MAX(DAY(LAST_DAY(trx_time))) AS avg_day_cnt
FROM cmb_usr_trx_rcd c1
JOIN cmb_mch_typ c2 USING (mch_nm)
WHERE usr_id = 5201314520
AND YEAR(trx_time) IN (2023, 2024)
AND mch_typ = '休闲娱乐'
GROUP BY
DATE_FORMAT(trx_time, '%Y-%m'),
LAST_DAY(trx_time),
DAY(LAST_DAY(trx_time))
ORDER BY trx_mon;
select
null as mch_typ,
mch_nm,
count(*) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd c1
where usr_id=5201314520 and year(trx_time)=2024
and not exists (select 1 from cmb_mch_typ c2 where c1.mch_nm=c2.mch_nm)
group by mch_nm
order by trx_cnt desc
select
mch_typ,
count(*) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd c1 left join cmb_mch_typ c2 using (mch_nm)
where usr_id=5201314520 and year(trx_time)=2024
group by mch_typ
order by trx_cnt desc
select
date_format(trx_time,'%Y-%m') as trx_mon,
count(*) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where usr_id=5201314520 and (floor(trx_amt)%100 in (88,98) and trx_amt>200 and hour(trx_time) in (23,0,1,2)
or mch_nm regexp '足疗|保健|按摩|养生|SPA') and trx_time>='2022-11-01' and trx_time<'2025-01-01'
group by trx_mon
order by trx_mon
select
case
when floor(trx_amt)%100 in (88,98) and trx_amt>200 and hour(trx_time) in (0,1,2,3,23)
then 'illegal'
else 'other'
end as trx_typ,
count(*) as trx_cnt,
sum(trx_amt) as trx_amt,
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where usr_id=5201314520
group by trx_typ
order by mch_cnt desc
with t1 as (
select trx_time,trx_amt,
case
when trx_amt=1288 and lag(trx_amt,1) over (partition by date(trx_time) order by trx_time)=888
then 1
else null
end as mark
from cmb_usr_trx_rcd
where usr_id=5201314520 and trx_time>='2024-09-01' and trx_time<'2024-10-01' and mch_nm regexp '按摩|保健|休闲|会所'
),
t2 as (
select date(trx_time) as date_value,
count(*) as FvckCnt,
count(case when trx_amt=288 then 1 end) as WithHand,
count(case when trx_amt=388 then 1 end) as WithBalls,
count(case when trx_amt=588 then 1 end) as BlowJobbie,
count(case when trx_amt=888 then 1 end) as Doi,
count(case when trx_amt=1288 then 1 end) as DoubleFly,
count(distinct mark) as Ohya
from t1
group by date(trx_time)
),
t3 as (
select date_value
from date_table
where date_value between '2024-09-01' and '2024-09-30'
)
select
t3.date_value,
coalesce(FvckCnt,0) as FvckCnt,
coalesce(WithHand,0) as WithHand,
coalesce(WithBalls,0) as WithBalls,
coalesce(BlowJobbie,0) as BlowJobbie,
coalesce(Doi,0) as Doi,
coalesce(DoubleFly,0) as DoubleFly,
coalesce(Ohya,0) as Ohya
from t3 left join t2 using (date_value)
order by t3.date_value
with t1 as (
select trx_time,trx_amt,
case
when trx_amt=888 and lag(trx_amt,1) over (partition by date(trx_time) order by trx_time)=1288
then 1
else null
end as mark
from cmb_usr_trx_rcd
where usr_id=5201314520 and trx_time>='2024-09-01' and trx_time<'2024-10-01' and mch_nm regexp '按摩|保健|休闲|会所'
),
t2 as (
select date(trx_time) as date_value,
count(*) as FvckCnt,
count(case when trx_amt=288 then 1 end) as WithHand,
count(case when trx_amt=388 then 1 end) as WithBalls,
count(case when trx_amt=588 then 1 end) as BlowJobbie,
count(case when trx_amt=888 then 1 end) as Doi,
count(case when trx_amt=1288 then 1 end) as DoubleFly,
count(distinct mark) as Ohya
from t1
group by date(trx_time)
),
t3 as (
select date_value
from date_table
where date_value between '2024-09-01' and '2024-09-30'
)
select
t3.date_value,
coalesce(FvckCnt,0) as FvckCnt,
coalesce(WithHand,0) as WithHand,
coalesce(WithBalls,0) as WithBalls,
coalesce(BlowJobbie,0) as BlowJobbie,
coalesce(Doi,0) as Doi,
coalesce(DoubleFly,0) as DoubleFly,
coalesce(Ohya,0) as Ohya
from t3 left join t2 using (date_value)
order by t3.date_value
select distinct usr_id
from (
select usr_id,datediff(trx_time,lag(trx_time,3) over (partition by usr_id order by trx_time)) as dt
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲'
) as tmp
where dt<=3
order by usr_id
select usr_id,trx_time,trx_amt,mch_nm,
lag(trx_time,1) over (order by trx_time) as prev_trx_time,
datediff(trx_time,lag(trx_time,1) over (order by trx_time)) as days_since_last_fvck
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'
order by trx_time
select concat(year(trx_time),'-Q',quarter(trx_time)) as trx_quarter,
sum(count(case when trx_amt=288 then 1 end)) over (order by concat(year(trx_time),'-Q',quarter(trx_time))) as withhand,
sum(count(case when trx_amt=888 then 1 end)) over (order by concat(year(trx_time),'-Q',quarter(trx_time))) as doi
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲' and year(trx_time) in (2023,2024)
group by trx_quarter
with recursive t1 as (
select '2023-01-01' as dt,1 as month
union all
select date_add(dt,interval 1 month) as dt,month+1 as month
from t1
where month<12
),
t2 as (
select date_format(dt,'%Y-%m') as trx_mon
from t1
),
t3 as (
select date_format(trx_time,'%Y-%m') as trx_mon,sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd c1 join cmb_mch_typ using (mch_nm)
where usr_id=5201314520 and year(trx_time)=2023 and mch_typ='休闲娱乐'
group by trx_mon
)
select t2.trx_mon,
sum(coalesce(trx_amt,0)) over (order by t2.trx_mon) as trx_amt
from t2 left join t3 using (trx_mon)
select date_format(trx_time,'%Y-%m') as trx_mon,
sum(sum(trx_amt)) over (order by date_format(trx_time,'%Y-%m')) as trx_amt
from cmb_usr_trx_rcd c1 join cmb_mch_typ c2 using (mch_nm)
where usr_id=5201314520 and year(trx_time) in (2023,2024) and mch_typ='休闲娱乐'
group by trx_mon
select *
from (
select 'all' as mch_typ,mch_nm,count(*) as trx_cnt,1 as rnk
from cmb_usr_trx_rcd
where usr_id='5201314520'
group by mch_nm
order by trx_cnt desc
limit 1
) as t1
union all
select *
from (
select mch_typ,mch_nm,count(*) as trx_cnt,
dense_rank() over(partition by mch_typ order by count(*) desc) as rnk
from cmb_usr_trx_rcd c1 join cmb_mch_typ c2 using (mch_nm)
where usr_id='5201314520' and mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
group by mch_typ,mch_nm
) as t2
where rnk=1
select trx_mon,mch_nm,sum_trx_amt
from (
select '2024' as trx_mon,mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time)=2024
group by trx_mon,mch_nm
order by sum_trx_amt desc
limit 3
) as t1
union all
select trx_mon,mch_nm,sum_trx_amt
from (
select date_format(trx_time,'%Y-%m') as trx_mon,mch_nm,sum(trx_amt) as sum_trx_amt,
row_number() over(partition by date_format(trx_time,'%Y-%m') order by sum(trx_amt) desc) as rk
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time)=2024
group by trx_mon,mch_nm
) as t2
where rk<=3
select singer_id,singer_name,album_id,album_name,0 as play_count
from singer_info s join album_info a using (singer_id)
where not exists (select 1 from song_info s1 join listen_rcd l using (song_id) where s1.origin_singer_id=s.singer_id)
select singer_id,singer_name,album_id,album_name,play_count
from (
select singer_id,singer_name,coalesce(sum(cnt),0) as play_count
from singer_info s1 left join song_info s2 on s1.singer_id=s2.origin_singer_id left join (select song_id,count(*) as cnt from listen_rcd group by song_id) s3 using (song_id)
group by singer_id,singer_name
having coalesce(sum(cnt),0)=0
) t1 join album_info t2 using (singer_id)
select user_id,dayname(start_time) as day_of_week,count(*) as listens_per_day
from listen_rcd
group by user_id,dayname(start_time)
order by user_id,day_of_week
select
case
when score>=110 then '[110, 120]'
when score>=90 then '[90, 110)'
when score>=60 then '[60, 90)'
else '[0, 60)'
end as score_range,
count(*) as num_students
from scores
where exam_date='2024-06-30' and subject='数学' and score between 0 and 120
group by score_range