select
Substr(trx_time,1,7),
last_day(max(trx_time))
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time) in (2023,2024)
group by Substr(trx_time,1,7)
select mch_typ,a.mch_nm,count(trx_amt),sum(trx_amt)
from
cmb_usr_trx_rcda
leftjoin cmb_mch_typb
on a.mch_nm=b.mch_nm
where
usr_id = 5201314520
and year(trx_time) = 2024
andmch_typ is null
group by mch_typ,a.mch_nm
select mch_typ,a.mch_nm,count(trx_amt),sum(trx_amt)
from
cmb_usr_trx_rcda
rightjoin cmb_mch_typb
on a.mch_nm=b.mch_nm
where
usr_id = 5201314520
and year(trx_time) = 2024
group by mch_typ,a.mch_nm
select mch_typ,count(trx_amt),sum(trx_amt)
from
(select
*
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time) = 2024) a
left join (
select * from cmb_mch_typ ) b
on a.mch_nm=b.mch_nm
group by mch_typ
select a.mch_nm
from
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id=5201314520
and year(trx_time)=2024)a
inner join(select distinct mch_nm from
cmb_usr_trx_rcd
whereusr_id= 5211314521
and year(trx_time)=2024 ) b
on a.mch_nm= b.mch_nm
select a.mch_nm
from
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id=5201314520) a
inner join(select distinct mch_nm from
cmb_usr_trx_rcd
whereusr_id= 5211314521 )b
on a.mch_nm= b.mch_nm
select
*
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time) = 2024
and trx_amt =
(select max(trx_amt)
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time) = 2024 )
select
substr(trx_time,1,7) trx_mon,
count(1) trx_cnt,
sum(trx_amt) trx_amt
from
cmb_usr_trx_rcd
where
usr_id = "5201314520"
and (
((truncate(trx_amt,0) like "%88" or truncate(trx_amt,0) like "%98" )
and
trx_amt>=200
and
hour(trx_time) in (23,0,1,2) )
or
(mch_nm rlike "足疗|保健|按摩|养生|SPA"))
and
date(trx_time) between "2022-11-01" and "2024-12-31"
group by trx_mon
order by trx_mon
select
substr(trx_time,1,7) trx_mon,
count(1) trx_cnt,
sum(trx_amt) trx_amt
from
cmb_usr_trx_rcd
where
usr_id = "5201314520"
and (
((truncate(trx_amt,0) like "%88$" or truncate(trx_amt,0) like "%98$" )
and
trx_amt>=200
and
hour(trx_time) in (23,0,1,2) )
or
(mch_nm rlike "足疗|保健|按摩|养生|SPA"))
and
date(trx_time) between "2022-11-01" and "2024-12-31"
group by trx_mon
order by trx_mon
select
substr(trx_time,1,7) trx_mon,
count(1) trx_cnt,
sum(trx_amt) trx_amt
from
cmb_usr_trx_rcd
where
usr_id = "5201314520"
and (
((truncate(trx_amt,0) like "88" or truncate(trx_amt,0) like "98" )
and
trx_amt>=200
and
hour(trx_time) in (23,0,1,2) )
or
(mch_nm rlike "足疗|保健|按摩|养生|SPA"))
and
date(trx_time) between "2022-11-01" and "2024-12-31"
group by trx_mon
order by trx_mon
select
substr(trx_time,1,7) trx_mon,
count(1) trx_cnt,
sum(trx_amt) trx_amt
from
cmb_usr_trx_rcd
where
usr_id = "5201314520"
and (
((truncate(trx_amt,0) like "88$" or truncate(trx_amt,0) like "98$" )
and
trx_amt>=200
and
hour(trx_time) in (23,0,1,2) )
or
(mch_nm rlike "足疗|保健|按摩|养生|SPA"))
and
date(trx_time) between "2022-11-01" and "2024-12-31"
group by trx_mon
order by trx_mon
select
substr(trx_time,1,7) trx_mon,
count(1) trx_cnt,
sum(trx_amt) trx_amt
from
cmb_usr_trx_rcd
where
usr_id = "5201314520"
and (
((truncate(trx_amt,0) rlike "88$" or truncate(trx_amt,0) like "98$" )
and
trx_amt>=200
and
hour(trx_time) in (23,0,1,2) )
or
(mch_nm rlike "足疗|保健|按摩|养生|SPA"))
and
date(trx_time) between "2022-11-01" and "2024-12-31"
group by trx_mon
order by trx_mon
select
substr(trx_time,1,7) trx_mon,
count(1) trx_cnt,
sum(trx_amt) trx_amt
from
cmb_usr_trx_rcd
where
usr_id = "5201314520"
and (
((truncate(trx_amt,0) like "%88" or truncate(trx_amt,0) like "%98" )
and
trx_amt>=200
and
hour(trx_time) in (23,0,1,2,3) )
or
(mch_nm rlike "足疗|保健|按摩|养生|SPA"))
and
date(trx_time) between "2022-11-01" and "2024-12-31"
group by
trx_mon
order by
trx_mon
select
role,
count(1) total_count,
count( if (banned = 1 ,1,null) ) banned_count,
concat(round(count( if (banned = 1 ,1,null) )/count(1),2)*100,"%") banned_rate
from
hll_t2
group by
role
order by
role
select
role,
count(1) total_count,
count( if (banned = 1 ,1,null) ) banned_count,
concat(round(count( if (banned = 1 ,1,null) )/count(1)*100,2),"%") banned_rate
from
hll_t2
group by
role
order by
role
select
role,
count(1) total_count,
count( if (banned = 1 ,1,null) ) banned_count,
concat(round(count( if (banned = 1 ,1,null) ),2)/count(1)*100,"%") banned_rate
from
hll_t2
group by
role
order by
role