select t.trx_mon,sum(t.trx_amt)over(order by t.trx_mon) as trx_amt
from(
select date_format(a.trx_time,'%Y-%m') as trx_mon, sum(a.trx_amt) as trx_amt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id = 5201314520
and (date_format(trx_time,'%Y-%m') between '2023-01' and '2024-12')
and mch_typ = '休闲娱乐'
group by trx_mon)t
请问这个哪里错了呀
select date_format(a.trx_time,'%Y-%m') as trx_mon, 这句后面的逗号是中文还是英文?
with date_range as(
select distinct date_format(date_value,'%Y-%m') as trx_mon
from date_table
where date_format(date_value,'%Y-%m') between '2023-01' and '2024-06')
select a.trx_mon,
coalesce(m.last_day,'1900-01-01') as last_day,
coalesce(m.day_of_mon,0) as day_of_mon,
coalesce(m.trx_amt,0) as trx_amt,
coalesce(m.trx_cnt,0) as trx_cnt,
coalesce(round(m.avg_day_amt,2),0) as avg_day_amt,
coalesce(round(m.avg_day_cnt,2),0) as avg_day_cnt
from date_range a left join
(select date_format(trx_time,'%Y-%m') 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(trx_amt) as trx_cnt, sum(trx_amt)/day(last_day(max(trx_time))) as avg_day_amt, count(trx_amt)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd a left join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id = 5201314520
and (trx_time between '2023-01-01' and '2024-06-30')
and (mch_typ = '休闲娱乐' or mch_typ is null) and a.trx_amt >= 288 and hour(trx_time) in (23,0,1,2)
group by trx_mon
order by trx_mon) m on a.trx_mon = m.trx_mon
order by a.trx_mon
with date_range as(
select distinct(date_format(date_value,'%Y-%m')) as trx_mon
from date_table
where year(date_value) = 2023 or (year(date_value)=2024 and month(date_value) between 1 and 6))
select a.trx_mon,
coalesce(b.last_day,'1900-01-01') as last_day,
coalesce(b.day_of_mon,0) as day_of_mon,
coalesce(b.trx_amt,0) as trx_amt,
coalesce(b.trx_cnt,0) as trx_cnt,
coalesce(round(b.avg_day_amt,2),0) as avg_day_amt,
coalesce(round(b.avg_day_cnt,2),0) as avg_day_cnt
from date_range a left join
(select date_format(trx_time,'%Y-%m') 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(trx_amt) as trx_cnt, sum(trx_amt)/ day(last_day(max(trx_time))) as avg_day_amt, count(trx_amt)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd cutr join cmb_mch_typ cmt on cutr.mch_nm = cmt.mch_nm
where usr_id = 5201314520 and (mch_typ = '休闲娱乐' or mch_typ is null) and cutr.trx_amt>=288 and (cutr.trx_time between '2023-01-01' and '2024-06-30') and hour(trx_time) in (23,0,1,2)
group by trx_mon
order by trx_mon)b on a.trx_mon=b.trx_mon
select date_format(trx_time,'%Y-%m') 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(trx_amt) as trx_cnt, sum(trx_amt)/day(last_day(max(trx_time))) as avg_day_amt, count(trx_amt)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id = 5201314520 and year(trx_time) in (2023,2024) and mch_typ = '休闲娱乐'
group by trx_mon
order by trx_mon
select date_format(trx_time,'%Y-%m') 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 join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id = 5201314520 and year(trx_time) in (2023,2024) and mch_typ = '休闲娱乐'
group by trx_mon
order by trx_mon
select date_format(trx_time,'%Y-%m') 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 trx_mon
select date_format(trx_time,'%Y-%m') 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
with date_range as(
select date_value
from date_table
where date_value between '2024-09-01' and '2024-09-30'),
transaction_summary as(
select date(trx_time) as dt,
count(1) as cnt,
sum(case when trx_amt=288 then 1 else 0 end) as WithHand,
sum(case when trx_amt=388 then 1 else 0 end) as WithBalls,
sum(case when trx_amt=588 then 1 else 0 end) as BlowJobbie,
sum(case when trx_amt=888 then 1 else 0 end) as Doi,
sum(case when trx_amt=1288 then 1 else 0 end) as DoubleFly
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm rlike '按摩|保健|休闲|会所' and date(trx_time) between '2024-09-01' and '2024-09-30'
group by date(trx_time)),
Ohya_group as(
select distinct(date(trx_time)) as dt
from(select *,
lag(trx_amt,1)over(partition by usr_id order by trx_time) as last_amt,
lag(trx_time,1)over(partition by usr_id order by trx_time) as last_time
from cmb_usr_trx_rcd
where usr_id = 5201314520 and mch_nm rlike '按摩|保健|休闲|会所' and date(trx_time) between '2024-09-01' and '2024-09-30')t
where trx_amt=1288 and last_amt = 888 and date(trx_time) = date(last_time))
select a.date_value as date_value,
coalesce(b.cnt,0) as FvckCnt,
coalesce(b.WithHand,0) as WithHand,
coalesce(b.WithBalls,0) as WithBalls,
coalesce(b.BlowJobbie,0) as BlowJobbie,
coalesce(b.Doi,0) as Doi,
coalesce(b.DoubleFly,0) as DoubleFly,
case when c.dt is not null then 1 else 0 end as Ohya
from date_range a left join transaction_summary b
on a.date_value = b.dt
left join Ohya_group c
on a.date_value = c.dt
order by a.date_value
with date_range as(
select date_value
from date_table
where date_value between '2024-09-01' and '2024-09-30'),
transaction_summary as(
select date(trx_time) as dt,
count(1) as cnt,
sum(case when trx_amt=288 then 1 else 0 end) as WithHand,
sum(case when trx_amt=388 then 1 else 0 end) as WithBalls,
sum(case when trx_amt=588 then 1 else 0 end) as BlowJobbie,
sum(case when trx_amt=888 then 1 else 0 end) as Doi,
sum(case when trx_amt=1288 then 1 else 0 end) as DoubleFly
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm rlike '按摩|保健|休闲|会所' and date(trx_time) between '2024-09-01' and '2024-09-30'
group by date(trx_time)),
Ohya_group as(
select distinct(date(trx_time)) as dt
from(select *,
lag(trx_amt,1)over(partition by usr_id order by trx_time) as last_amt,
lag(trx_time,1)over(partition by usr_id order by trx_time) as last_time
from cmb_usr_trx_rcd
where usr_id = 5201314520 and mch_nm rlike '按摩|保健|休闲|会所' and date(trx_time) between '2024-09-01' and '2024-09-30')t
where trx_amt=1288 and last_amt = 888 and date(trx_time) = date(last_time))
select a.date_value as date_value,
coalesce(b.cnt,0) as FvckCnt,
coalesce(b.WithHand,0) as WithHand,
coalesce(b.WithBalls,0) as WithBalls,
coalesce(b.BlowJobbie,0) as BlowJobbie,
coalesce(b.Doi,0) as Doi,
coalesce(b.DoubleFly,0) as DoubleFly,
coalesce(c.dt,0) as Ohya
from date_range a left join transaction_summary b
on a.date_value = b.dt
left join Ohya_group c
on a.date_value = c.dt
order by a.date_value
with user_transactions as (
select usr_id, trx_time, mch_nm, lag(trx_time,3)over(partition by usr_id order by trx_time) as prev_3_trx_time
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲'),
datediff_transactions as(
select usr_id, trx_time, mch_nm, prev_3_trx_time,datediff(trx_time,prev_3_trx_time) as days_since_3rd_purchase
from user_transactions)
select distinct usr_id
from datediff_transactions
where days_since_3rd_purchase <= 3
order by usr_id
with user_transactions as (
select usr_id, trx_time, trx_amt, mch_nm, lag(trx_time)over(partition by usr_id order by trx_time) as prev_trx_time
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲')
select usr_id, trx_time, trx_amt, mch_nm, prev_trx_time, datediff(trx_time,prev_trx_time) as days_since_last_fvck
from user_transactions
order by trx_time
with quarterly_transaction as(
select
concat(year(trx_time),'-','Q',quarter(trx_time)) as trx_quarter,
count(case when trx_amt=288 then trx_amt else null end)over(order by concat(year(trx_time),'-','Q',quarter(trx_time))) as withhand,
count(case when trx_amt=888 then trx_amt else null end)over(order by concat(year(trx_time),'-','Q',quarter(trx_time))) as doi
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲' and year(trx_time) in (2023,2024)
)
select trx_quarter, withhand, doi
from quarterly_transaction
group by trx_quarter,withhand,doi
order by trx_quarter
with quarterly_transaction as(
select
concat(year(trx_time),'-','Q',quarter(trx_time)) as trx_quarter,
count(case when trx_amt=288 then trx_amt else null end)over(order by concat(year(trx_time),'-','Q',quarter(trx_time))) as withhand,
count(case when trx_amt=888 then trx_amt else null end)over(order by concat(year(trx_time),'-','Q',quarter(trx_time))) as doi
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲' and year(trx_time) in (2023,2024)
)
select trx_quarter, Withhand, Doi
from quarterly_transaction
order by trx_quarter
with quarterly_transaction as(
select
concat(year(trx_time),'-','Q',quarter(trx_time)) as trx_quarter,
count(case when trx_amt=288 then trx_amt else null end)over(order by concat(year(trx_time),'-','Q',quarter(trx_time))) as withhand,
count(case when trx_amt=888 then trx_amt else null end)over(order by concat(year(trx_time),'-','Q',quarter(trx_time))) as doi
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id=5201314520 and mch_typ='休闲娱乐' and year(trx_time) in (2023,2024)
)
select trx_quarter, Withhand, Doi
from quarterly_transaction
order by trx_quarter
select u.date_value as trx_mon, sum(t.trx_amt)over(order by u.date_value) as trx_amt
from(select distinct date_format(date_value,'%Y-%m') as date_value
from date_table
where year(date_value) = 2023) u
left join(
select date_format(trx_time,'%Y-%m') as trx_mon, sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id=5201314520 and year(trx_time) = 2023 and mch_typ='休闲娱乐'
group by trx_mon) t
on u.date_value = t.trx_mon
order by trx_mon
select u.date_value as trx_mon, sum(t.trx_amt)over(order by u.date_value) as trx_amt
from(select distinct date_format(date_value,'%Y-%m') as date_value
from date_table
where year(date_value) = 2023) u
left join(
select date_format(trx_time,'%Y-%m') as trx_mon, sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id=5201314520 and year(trx_time) = 2023 and mch_typ='休闲娱乐'
group by trx_mon) t
on u.date_value = t.trx_mon
select t.trx_mon,sum(t.trx_amt)over(order by t.trx_mon) as trx_amt
from(select date_format(a.trx_time,'%Y-%m') as trx_mon,sum(a.trx_amt) as trx_amt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id = 5201314520
and (date_format(trx_time,'%Y-%m') between '2023-01' and '2024-12')
and mch_typ = '休闲娱乐'
group by trx_mon)t
select u.mch_typ, u.mch_nm, u.trx_cnt,u.rnk
from(select t.*, dense_rank()over(partition by t.mch_typ order by t.trx_cnt desc) as rnk
from (
select 'all' as mch_typ, mch_nm, count(trx_amt) as trx_cnt
from cmb_usr_trx_rcd
where usr_id = 5201314520
group by mch_typ,mch_nm
union all
select mch_typ, a.mch_nm, count(trx_amt) as trx_cnt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm=b.mch_nm
where usr_id = 5201314520 and mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
group by b.mch_typ,a.mch_nm)t)u
where rnk <= 1
select trx_mon, mch_nm, sum_trx_amt
from(select a.*, row_number()over(partition by trx_mon order by sum_trx_amt desc) as rn
from (select year(trx_time) as trx_mon, mch_nm, sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and year(trx_time) = 2024
group by trx_mon, mch_nm
union all
select substr(trx_time,1,7) as trx_mon, mch_nm, sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and year(trx_time) = 2024
group by trx_mon, mch_nm) a
)b
where rn<=3
with first_time as(
select min(trx_time) as first_trx_time
from cmb_usr_trx_rcd
where usr_id = 5201314520 and mch_nm='红玫瑰按摩保健休闲')
select usr_id, mch_nm, trx_time, trx_amt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and trx_time between (select first_trx_time from first_time) and (select date_add(first_trx_time,interval 2 hour) from first_time)
order by trx_time
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_faliing_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 = '红玫瑰按摩保健休闲'