WITH T1 AS (
select
CASE WHEN mch_typ IS NOT NULL then 'all' ELSE 'all' END as mch_typ,
ur.mch_nm,
COUNT(trx_amt) as trx_cnt,
row_number()over(ORDER BY COUNT(trx_amt) DESC) as rnk
from cmb_usr_trx_rcd AS ur
LEFT JOIN cmb_mch_typ AS typ
ON ur.mch_nm =typ.mch_nm
where usr_id = '5201314520'
GROUP BY mch_typ, ur.mch_nm
order by trx_cnt DESC
LIMIT 1
),
T2 AS (
select * from (
select
mch_typ, ur.mch_nm,
COUNT(trx_amt) as trx_cnt,
row_number()over(PARTITION BY mch_typ ORDER BY COUNT(trx_amt) DESC) as rnk_sort
from cmb_usr_trx_rcd AS ur
LEFT JOIN cmb_mch_typ AS typ
ON ur.mch_nm =typ.mch_nm
where usr_id = '5201314520'
GROUP BY mch_typ, ur.mch_nm
order by trx_cnt DESC
) as sorting
wheremch_typ IN ('交通出行', '休闲娱乐', '咖啡奶茶') AND rnk_sort =1
order by mch_typ
)
select * from T1
UNION ALL
select * from T2;
select
mch_typ AS 'all',
ur.mch_nm,
COUNT(trx_amt) as trx_cnt,
row_number()over(ORDER BY COUNT(trx_amt) DESC) as rnk
from cmb_usr_trx_rcd AS ur
LEFT JOIN cmb_mch_typ AS typ
ON ur.mch_nm =typ.mch_nm
where usr_id = '5201314520'
GROUP BY mch_typ, ur.mch_nm
order by trx_cnt DESC
select trx_mon, mch_nm, sum_trx_amt from (
select
YEAR(trx_time) AS trx_mon,
mch_nm,
SUM(trx_amt) AS sum_trx_amt,
row_number()over(ORDER BY SUM(trx_amt) DESC) as ranking
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = 2024
GROUP BY trx_mon,mch_nm
limit 3
) AS T1
UNION
SELECT trx_mon, mch_nm, sum_trx_amt
FROM (
select
SUBSTR(trx_time,1,7) as trx_mon,
mch_nm,
SUM(trx_amt) AS sum_trx_amt,
row_number()over(PARTITION BY SUBSTR(trx_time,1,7) ORDER BY SUM(trx_amt) DESC) as ranking_monthly
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = 2024
GROUP BY trx_mon,mch_nm
order by trx_mon,ranking_monthly
) AS TV
WHERE ranking_monthly <= 3
select trx_mon, mch_nm, sum_trx_amt from (
select
YEAR(trx_time) AS trx_mon,
mch_nm,
SUM(trx_amt) AS sum_trx_amt,
row_number()over(ORDER BY SUM(trx_amt) DESC) as ranking
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = 2024
GROUP BY trx_mon,mch_nm
limit 3
) AS T1
UNION
select trx_mon, mch_nm, sum_trx_amt from (
select
SUBSTR(trx_time,1,7) as trx_mon,
mch_nm,
SUM(trx_amt) AS sum_trx_amt,
row_number()over(ORDER BY SUM(trx_amt) DESC) as ranking
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = 2024
GROUP BY trx_mon,mch_nm
order by trx_mon,ranking
) AS T2
select trx_mon, mch_nm, sum_trx_amt from (
select
YEAR(trx_time) AS trx_mon,
mch_nm,
SUM(trx_amt) AS sum_trx_amt,
row_number()over(ORDER BY SUM(trx_amt) DESC) as ranking
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = 2024
GROUP BY trx_mon,mch_nm
limit 3
) AS T1
UNION
select trx_mon, mch_nm, sum_trx_amt from (
select
SUBSTR(trx_time,1,7) as trx_mon,
mch_nm,
SUM(trx_amt) AS sum_trx_amt,
row_number()over(ORDER BY SUM(trx_amt) DESC) as ranking
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = 2024
GROUP BY trx_mon,mch_nm
order by trx_mon,ranking
) AS T2
WHERE T2.ranking <= 3
select
YEAR(trx_time) AS trx_year,
mch_nm,
SUM(trx_amt) AS sum_trx_amt,
row_number()over(PARTITION BY mch_nm ORDER BY SUM(trx_amt) DESC)
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = 2024
GROUP BY trx_year,mch_nm
select
SUBSTR(trx_time,1,7) as trx_mon,
COUNT(trx_amt) as trx_cnt,
SUM(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and trx_time between '2022-11-01' and '2024-12-31'
AND
(
(RIGHT(TRUNCATE(trx_amt,0),2) IN ('88', '98') and trx_amt > 200 and HOUR(trx_time) IN (23,24,0,1,2)) OR
(mch_nm rlike '足疗|保健|按摩|养生|SPA')
)
GROUP BY 1
ORDER BY 1 ASC;
select
SUBSTR(trx_time,1,7) as trx_mon,
COUNT(trx_amt) as trx_cnt,
SUM(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and trx_time between '2022-11-01' and '2024-12-31'
AND
(
(RIGHT(TRUNCATE(trx_amt,0),2) IN ('88', '98') and trx_amt > 200 and HOUR(trx_time) IN (23,24,0,1,2)) OR
(upper(mch_nm rlike '足疗|保健|按摩|养生|SPA'))
)
GROUP BY 1
ORDER BY 1 ASC;
select
SUBSTR(trx_time,1,7) as trx_mon,
COUNT(trx_amt) as trx_cnt,
SUM(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and trx_time between '2022-11-01' and '2024-12-31'
AND
(
(RIGHT(TRUNCATE(trx_amt,0),2) IN ('88', '98') and trx_amt > 200 and HOUR(trx_time) IN (23,24,0,1,2,3)) OR
(upper(mch_nm rlike '足疗|保健|按摩|养生|SPA'))
)
GROUP BY 1
ORDER BY 1 ASC;
select
SUBSTR(trx_time,1,7) as trx_mon,
COUNT(trx_amt) as trx_cnt,
SUM(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and trx_time between '2022-11-01' and '2024-12-31'
AND
(
(RIGHT(TRUNCATE(trx_amt,0),2) IN ('88', '98') and trx_amt > 200) OR
(upper(mch_nm rlike '足疗|保健|按摩|养生|SPA'))
)
GROUP BY 1
ORDER BY 1 ASC;
select
SUBSTR(trx_time,1,7) as trx_mon,
COUNT(trx_amt) as trx_cnt,
SUM(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and trx_time between '2022-11-01' and '2024-12-31'
AND
(
(RIGHT(TRUNCATE(trx_amt,0),2) IN ('88', '98') and trx_amt > 200) OR
(mch_nm rlike '足疗|保健|按摩|养生|SPA')
)
GROUP BY 1
ORDER BY 1 ASC;
WITH M as (
SELECT MIN(trx_time) AS MARK
FROM cmb_usr_trx_rcd WHERE usr_id = '5201314520'
AND mch_nm = '红玫瑰按摩保健休闲'
)
select * from cmb_usr_trx_rcd
where usr_id = 5201314520
and trx_time BETWEEN (select MARK from M)
AND (select DATE_ADD(MARK, INTERVAL 2 hour) from M)
order by trx_time ASC;
WITH M as (
SELECT MIN(trx_time) AS MARK
FROM cmb_usr_trx_rcd WHERE usr_id = '5201314520'
AND mch_nm = '红玫瑰按摩保健休闲'
),
T as (
SELECT *
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
AND trx_time >=
(SELECT MIN(trx_time) FROM cmb_usr_trx_rcd WHERE usr_id = '5201314520'
AND mch_nm = '红玫瑰按摩保健休闲' )
ORDER BY trx_time ASC
)
select T.*
from T,M
where T.trx_time <= DATE_ADD(M.MARK, interval 2 HOUR );
WITH M as (
SELECT MIN(trx_time) AS MARK
FROM cmb_usr_trx_rcd WHERE usr_id = '5201314520'
AND mch_nm = '红玫瑰按摩保健休闲'
),
T as (
SELECT *
FROM cmb_usr_trx_rcd
WHERE usr_id = '5201314520'
AND trx_time >=
(SELECT MIN(trx_time) FROM cmb_usr_trx_rcd WHERE usr_id = '5201314520'
AND mch_nm = '红玫瑰按摩保健休闲' )
ORDER BY trx_time ASC
)
select * from T,M
where T.trx_time <= DATE_ADD(M.MARK, interval 2 HOUR );
select
'2022-10-03 17:20:20' AS time_he_love_me,
DATEDIFF(current_date, '2022-10-03 17:20:20') AS days_we_falling_love,
timestampdiff(hour, '2022-10-03 17:20:20', now()) AS hours_we_falling_love ,
DATEDIFF(date(MIN(trx_time)), '2022-10-03') as days_he_fvck_else
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and mch_nm = '红玫瑰按摩保健休闲'
select
'2022-10-3 17:20:20' AS time_he_love_me,
DATEDIFF(current_date, '2022-10-3 17:20:20') AS days_we_falling_love,
timestampdiff(hour, '2022-10-3 17:20:20', now()) AS hours_we_falling_love ,
DATEDIFF(date(MIN(trx_time)), '2022-10-03') as days_he_fvck_else
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and mch_nm = '红玫瑰按摩保健休闲';
select
MIN(trx_time) AS first_else_time,
STR_TO_DATE('2022-10-3 17:20:20', '%Y%m%d%H%i%s') AS formatted_date,
DATEDIFF(date(MIN(trx_time)), '2022-10-03') as days_he_fvck_else
from cmb_usr_trx_rcd
where usr_id = '5201314520'
and mch_nm = '红玫瑰按摩保健休闲'
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,
sum(trx_amt) as trx_amt,
COUNT(*) AS trx_cnt,
sum(trx_amt) / DAY(last_day(MAX(trx_time)) ) AS avg_day_amt,
COUNT(*) / DAY(last_day(MAX(trx_time)) ) AS avg_day_cnt
from cmb_usr_trx_rcd AS ur
LEFT JOIN cmb_mch_typ AS typ
ON ur.mch_nm = typ.mch_nm
where usr_id = '5201314520' AND year(trx_time) in (2023,2024) AND mch_typ = '休闲娱乐'
GROUP BY 1
ORDER BY 1 ASC;