select
usr_id,mch_nm,trx_time,max(trx_amt)
from cmb_usr_trx_rcd
where usr_id ='5201314520'
and substring(trx_time,1,4) = '2024'
group by 1,2,3
order by 4 desc
limit 1
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
order by mch_cnt desc;
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' endser_typ
,sum(1) as trx_cnt,min(date(trx_time)) as first_date
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and mch_nm = '红玫瑰按摩保健休闲'
group byser_typ
order byser_typ
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 'other' endser_typ
,sum(1) as trx_cnt,min(date(trx_time)) as first_date
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and mch_nm = '红玫瑰按摩保健休闲'
group byser_typ
order byser_typ
select
usr_id
,mch_nm
,sum(trx_amt) as trx_amt
,sum(1) as trx_cnt
,min(trx_time) as first_time
from
cmb_usr_trx_rcd
where
usr_id='5201314520'
and trx_amt>=288
group by usr_id, mch_nm
order by 4 desc
select
usr_id,mch_nm,sum(trx_amt) as trx_amt ,sum(1) as trx_cnt, min(trx_time) as first_time
from cmb_usr_trx_rcd
where usr_id ='5201314520'
and trx_amt >=288
group by 1,2
order by 4 desc
select
usr_id,mch_nm,sum(trx_amt) as trx_amt ,sum(1) as trx_cnt, min(trx_time) as first_time
from cmb_usr_trx_rcd
where usr_id ='5201314520'
and trx_amt >=288
group by 1,2
order by 4
select
trx_amt 服务
,sum(1) 总消费笔数
,count(distinct usr_id) 服务客户数
,sum(1)/count(distinct usr_id) avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm ='红玫瑰按摩保健休闲'
and substring(trx_time,1,7)between '2023-01' and '2024-06'
group by 服务
order by avg_trx_per_user desc
limit 5
select
trx_amt 服务
,sum(1) 总消费笔数
,count(distinct usr_id) 服务客户数
,sum(1)/count(distinct usr_id) avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm ='红玫瑰按摩保健休闲'
and date(trx_time) between '2023-01-01' and '2024-06-30'
group by 服务
order by avg_trx_per_user desc
limit 5
select
trx_amt 服务
,sum(1) 总消费笔数
,count(distinct usr_id) 服务客户数
,sum(1)/count(distinct usr_id) avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm ='红玫瑰按摩保健休闲'
and date(trx_time) between '2023-01-01' and '2023-12-31'
group by 服务
order by avg_trx_per_user desc
limit 5
select
trx_amt 服务
,sum(1) 总消费笔数
,count(distinct usr_id) 服务客户数
,sum(1)/count(distinct usr_id) avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm ='红玫瑰按摩保健休闲'
and date(trx_time) between '2023-01-01' and '2024-06=31'
group by 服务
order by avg_trx_per_user desc
limit 5
select
trx_amt 服务
,sum(1) 总消费笔数
,count(distinct usr_id) 服务客户数
,sum(1)/count(distinct usr_id) avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm ='红玫瑰按摩保健休闲'
and ((year(trx_time) =' 2023' and month(trx_time) between '1' and '12')
or (year(trx_time) = '2024 'and month(trx_time) between '1' and '6') )
group by 服务
order by avg_trx_per_user desc
limit 5