select
reg_rules,
count(distinct mch_nm) as mch_cnt
from(select mch_nm,
case
when mch_nm like '%按摩保健休闲%' then "按摩保健休闲"
when lower(mch_nm) rlike '.*(按摩|保健|休闲|养生|SPA|会所).*' then "按摩、保健、休闲、养生、SPA、会所"
else "其他"
end as reg_rules
from cmb_usr_trx_rcd
where lower(mch_nm) rlike '.*(按摩|保健|休闲|养生|SPA|会所).*'
or mch_nm like '%按摩保健休闲%')as t
group by reg_rules
order by mch_cnt desc
select
ser_typ,
count(1) as trx_cnt,
min(first_time) as first_date
from(
select
case
when trx_amt = 288 then "1.WithHand"
when trx_amt = 388 then "2.WithMimi"
when trx_amt = 588 then "3.BlowJobbie"
when trx_amt = 888 then "4.Doi"
when trx_amt = 1288 then "5.DoubleFly"
else "6.other"
end as ser_typ,
date(trx_time) as first_time
from cmb_usr_trx_rcd
where usr_id = "5201314520"
and mch_nm = "红玫瑰按摩保健休闲"
)t
group by ser_typ
order by ser_typ
select
usr_id,
mch_nm,
sum(trx_amt) as trx_amt,
count(trx_time) as trx_cnt,
min(trx_time) as first_time
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and trx_amt >= 288
group by mch_nm
order by 4 desc
select
date(trx_time) as trx_date,
max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
from
cmb_usr_trx_rcd
where
year(trx_time) = "2024"
and month(trx_time) = "9"
and mch_nm = "红玫瑰按摩保健休闲"
group by date(trx_time)
order by trx_date
select 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 mch_nm
order by sum_trx_amt desc
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) in(22,23,24,0,1,2,3,4,5)
order by trx_time
select usr_id,
mch_nm,
trx_time,
trx_amt
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) between 1 and 5
order by trx_time
select usr_id,
mch_nm,
trx_time,
trx_amt
from cmb_usr_trx_rcd
where
usr_id = "5201315520" and
date(trx_time) between "2024-09-01" and "2024-09-30" or
hour(trx_time) between 1 and 5
order by trx_time
select usr_id,
mch_nm,
trx_time,
trx_amt
from cmb_usr_trx_rcd
where
usr_id = "5201315520" and
date(trx_time) between "2024-09-01" and "2024-09-30" and
hour(trx_time) between 1 and 5
order by trx_time
select usr_id,
mch_nm,
trx_time,
trx_amt
from cmb_usr_trx_rcd
where
usr_id = 5201315520 and
date(trx_time) between "2024-09-01" and "2024-09-30" and
hour(trx_time) between "01:00:00" and "06:00:00"
order by trx_time