select
case when trx_amt > 0 AND trx_amt <= 288 then '1.WithHand'
when trx_amt BETWEEN 289 AND 388 then '2.WithMimi'
when trx_amt BETWEEN 389 AND 588 then '3.BlowJobbie '
when trx_amt BETWEEN 589 AND 888 then '4.Doi'
when trx_amt BETWEEN 889 AND 1288 then '5.DoubleFly'
when trx_amt>=1288 then '6.other'
end
as set_typ,
count(case when trx_amt > 0 AND trx_amt <= 288 then '1.WithHand'
when trx_amt BETWEEN 289 AND 388 then '2.WithMimi'
when trx_amt BETWEEN 389 AND 588 then '3.BlowJobbie '
when trx_amt BETWEEN 589 AND 888 then '4.Doi'
when trx_amt BETWEEN 889 AND 1288 then '5.DoubleFly'
when trx_amt>=1288 then '6.other'
end ) as trx_cnt,
min(date(trx_time)) first_date
from cmb_usr_trx_rcd
where usr_id = 5201314520
and mch_nm = '红玫瑰按摩保健休闲'
group by set_typ
order by set_typ;
select usr_id,
mch_nm,
sum(trx_amt),
count(trx_amt) trx_cnt,
min(trx_time) first_time
from cmb_usr_trx_rcd
where usr_id =5201314520
and trx_amt >= 288
group by usr_id, mch_nm
order by trx_cnt desc;
select usr_id,
mch_nm,
trx_amt,
count(trx_amt) trx_cnt,
min(trx_time) first_time
from cmb_usr_trx_rcd
where usr_id =5201314520
and trx_amt >= 288
group by usr_id, mch_nm, trx_amt
order by trx_cnt desc;
select trx_amt,count(trx_amt) total_trx_cnt,count(distinct(usr_id))unique_usr_cnt,COUNT(trx_amt) / COUNT(DISTINCT usr_id) AS avg_trx_per_user from cmb_usr_trx_rcd where mch_nm ='红玫瑰按摩保健休闲' and date(trx_time) BETWEEN '2023-1-1'AND '2024-6-30' group by trx_amt order by avg_trx_per_user desc limit 5;
select trx_amt,count(trx_amt) total_trx_cnt,count(distinct(usr_id))unique_usr_cnt,COUNT(trx_amt) / COUNT(DISTINCT usr_id) AS avg_trx_per_user from cmb_usr_trx_rcd where mch_nm ='红玫瑰按摩保健休闲' and date(trx_time) BETWEEN '2023-1-1'AND '2024-6-30' group by trx_amt order by avg_trx_per_user desc;
SELECT trx_amt,
count(trx_amt) trx_cnt
FROM cmb_usr_trx_rcd
WHERE mch_nm = '红玫瑰按摩保健休闲'
and year(trx_time) = 2024
and month(trx_time) in (1, 2, 3, 4, 5, 6, 7)
group by trx_amt
ORDER BY trx_cnt desc limit 5;
SELECT
DATE(trx_time) trx_date,
MAX(trx_amt) max_trx_amt,
MIN(trx_amt) min_trx_amt,
AVG(trx_amt) avg_trx_amt,
SUM(trx_amt) total_trx_amt
FROM cmb_usr_trx_rcd
WHEREYEAR(trx_time) = 2024
AND MONTH(trx_time) = 9
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY trx_date
ORDER BY trx_date;
SELECT
DATE(trx_time) trx_date,
MAX(trx_amt) max_trx_amt,
MIN(trx_amt) min_trx_amt,
AVG(trx_amt) avg_trx_amt,
SUM(trx_amt) total_trx_amt
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND YEAR(trx_time) = 2024
AND MONTH(trx_time) = 9
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY trx_date
ORDER BY trx_date;
SELECT
DATE(trx_time),
MAX(trx_amt),
MIN(trx_amt),
AVG(trx_amt),
SUM(trx_amt)
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND YEAR(trx_time) = 2024
AND MONTH(trx_time) = 9
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY DATE(trx_time)
ORDER BY DATE(trx_time);
select
*
from
cmb_usr_trx_rcd
where
date(trx_time)
between '2024-09-01' and '2024-09-30'
and (hour(trx_time) between 22 and 23 or time(trx_time) between '00:00:00' and '06:00:00')
and usr_id = '5201314520'
order by trx_time
select
*
from
cmb_usr_trx_rcd
where
date(trx_time)
between '2024-09-01' and '2024-09-30'
and (hour(trx_time) between 22 and 23 or time(trx_time) between '00:00:00' and '05:00:00')
and usr_id = '5201314520'
order by trx_time
SELECT *
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
AND trx_time >= '2024-09-01 22:00:00'
AND trx_time < '2024-09-30 05:00:01'
ORDER BY trx_time;
select
*
from
cmb_usr_trx_rcd
where
date(trx_time)
between '2024-09-01' and '2024-09-30'
and (hour(trx_time) between 22 and 23 or hour(trx_time) between 0 and 5)
and usr_id = '5201314520'
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) BETWEEN 22 AND 23
OR hour(trx_time) BETWEEN 0 AND 4)
ORDER BY trx_time;
select
*
from
cmb_usr_trx_rcd
where
date(trx_time)
between '2024-09-01' and '2024-09-30'
and (hour(trx_time) between 22 and 23 or hour(trx_time) between 0 and 4)
and usr_id = '5201314520'
order by trx_time