with monthly_trx as (select date_format(a.trx_time,'%Y-%m') as trx_mon,
sum(a.trx_amt) as monthly_trx_amt
from cmb_usr_trx_rcd a
join cmb_mch_typ b
on a.mch_nm = b.mch_nm
where a.usr_id = 5201314520
and b.mch_typ = '休闲娱乐'
and year(a.trx_time) between 2023 and 2024
group by
trx_mon
order by
trx_mon),
cummulative_trx as (
select trx_mon,monthly_trx_amt,
sum(monthly_trx_amt) over(order by trx_mon) as cummulative_trx_amt
from monthly_trx)
select trx_mon,cummulative_trx_amt as trx_amt
from cummulative_trx
order by trx_mon
select *
from cmb_usr_trx_rcd
where trx_time between
(select min(trx_time)
from cmb_usr_trx_rcd
where usr_id = '5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by mch_nm
)
and
(select date_add(min(trx_time),interval 2 hour)
from cmb_usr_trx_rcd
where usr_id = '5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by mch_nm)
and usr_id='5201314520'
order by trx_time
select *
from cmb_usr_trx_rcd
where trx_time between
(select min(trx_time)
from cmb_usr_trx_rcd
where usr_id = '5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by mch_nm
)
and
(select date_add(min(trx_time),interval 2 hour)
from cmb_usr_trx_rcd
where usr_id = '5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by mch_nm)
and usr_id='5201314520'
SELECT
'2022-10-03 17:20:20' AS time_he_love_me,
DATEDIFF(CURRENT_DATE, '2022-10-03') AS days_we_falling_love,
TIMESTAMPDIFF(HOUR, '2022-10-03 17:20:20' , NOW()) AS hours_we_falling_love,
DATEDIFF( MIN(trx_time),'2022-10-03') AS days_he_fvck_else
FROM
cmb_usr_trx_rcd
WHERE
usr_id = '5201314520'
AND mch_nm ='红玫瑰按摩保健休闲'
GROUP BY
mch_nm;
select datediff(now(),'2022-10-03'),
timestampdiff(hour,'2022-10-03 05:20:20',now()),
datediff('2022-10-03',(select trx_time
from cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
order by trx_time asc
limit 1))
select substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as days_of_mon,
sum(trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(trx_amt)/day(last_day(max(trx_time))) as avg_day_amt,
count(1)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as t
on a.mch_nm=t.mch_nm
where usr_id = '5201314520' and (year(trx_time)=2023 or year(trx_time)=2024) and t.mch_typ='休闲娱乐'
group by trx_mon
order by trx_mon
select substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as days_of_mon
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as t
on a.mch_nm=t.mch_nm
where usr_id = '5201314520' and (year(trx_time)=2023 or year(trx_time)=2024) and t.mch_typ='休闲娱乐'
group by trx_mon
order by trx_mon
select substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as days_of_mon
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as t
on a.mch_nm=t.mch_nm
group by trx_mon
select substr(trx_time,1,7) as trx_mon ,last_day(max(trx_time)) as last_day, day(last_day(max(trx_time)) ) as day_of_mon
from cmb_usr_trx_rcd a
left join cmb_mch_typ m
on a.mch_nm = m.mch_nm
where a.usr_id=5201314520 and year(a.trx_time) in (2023, 2024) and m.mch_typ='休闲娱乐'
group by substr(a.trx_time,1,7)
order by 1
select substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as days_of_mon
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm=b.mch_nm
where (year(trx_time)=2023 or year(trx_time)=2024) and usr_id ='5201314520' and b.mch_nm='休闲娱乐'
group by trx_mon
order by trx_mon
select substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as days_of_mon
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm=b.mch_nm
where (year(trx_time)=2023 or year(trx_time)=2024) and usr_id ='5201314520'
group by trx_mon
order by trx_mon
select substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as days_of_mon
from cmb_usr_trx_rcd
where (year(trx_time)=2023 or year(trx_time)=2024) and usr_id ='5201314520'
group by trx_mon
order by trx_mon
select substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time) in (2023, 2024)
group by trx_mon
order by 1
select b.mch_typ,
a.mch_nm,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where b.mch_typ is null and year(trx_time)=2024 and a.usr_id = '5201314520'
group by a.mch_nm,b.mch_typ
order by 3 desc
SELECT
m.mch_typ, u.mch_nm,
COUNT(u.trx_amt) AS trx_cnt,
SUM(u.trx_amt) AS trx_amt
FROM
cmb_usr_trx_rcd u
LEFT JOIN
cmb_mch_typ m ON u.mch_nm = m.mch_nm
WHERE
u.usr_id = '5201314520' and year(trx_time)=2024 and m.mch_typ is null
GROUP BY
m.mch_typ,u.mch_nm
ORDER BY
3 DESC
select b.mch_typ,
a.mch_nm,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where b.mch_typ is null and year(trx_time)=2024
group by a.mch_nm,b.mch_typ
order by 3 desc
select b.mch_typ,
a.mch_nm,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where b.mch_typ is null and year(trx_time)=2024
group by a.mch_nm,b.mch_typ
select
a.mch_nm,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
where mch_typ is null and year(trx_time)=2024
group by a.mch_nm
SELECT
m.mch_typ,
COUNT(u.trx_amt) AS trx_cnt,
SUM(u.trx_amt) AS trx_amt
FROM
cmb_usr_trx_rcd u
LEFT JOIN
cmb_mch_typ m ON u.mch_nm = m.mch_nm
WHERE
u.usr_id = '5201314520' and left(trx_time,4)=2024
GROUP BY
m.mch_typ
ORDER BY
2 DESC
select a.mch_typ,count(1) as trx_cnt,sum(b.trx_amt) as trx_amt
from cmb_mch_typ as a
left join cmb_usr_trx_rcdas b
on a.mch_nm = b.mch_nm
where usr_id = '5201314520' and year(trx_time)=2024
group by a.mch_typ
order by trx_cnt desc