排名

用户解题统计

过去一年提交了

勋章 ①金银铜:在竞赛中获得第一二三名;②好习惯:自然月10天提交;③里程碑:解决1/2/5/10/20/50/100/200题;④每周打卡挑战:完成每周5题,每年1月1日清零。

收藏

收藏日期 题目名称 解决状态
2024-12-01 小结(2)越花越多是死罪,按月统计Substr  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-18 窗口函数(3)越来越喜欢召妓,窗口函数用累计(1) 
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,  这句后面的逗号是中文还是英文?
2024-12-01 字符串与通配符(2)好多关键词做规则,可以使用rlike 
好的谢谢~
啥也没说
2024-11-30 字符串与通配符(2)好多关键词做规则,可以使用rlike 
lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*' 这个语句是什么呀,lower不是字母变小写吗,但mch_nm不是汉字吗;然后rlike和后面的'.*(按摩|保健|休闲|spa|养生|会所).*'都是什么写法呀,没见过
mch_nm里面有字母呀。SPA、ZARA之类的,得把SPA转成小写,再去匹配SPA;
如果你要匹配100个词,写100次:mch_nm like '%养生%' or mch_nm like '%会所%'…… 人都累傻了
rlike可以实现所有关键词写在一个语句里,用|隔开即可。

语句更简单易读

提交记录

提交日期 题目名称 提交代码
2025-01-14 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
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
2025-01-14 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
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
2025-01-14 时间日期(3)按月统计日花费,一天都不要浪费 
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
2025-01-14 时间日期(2)按月统计日花费,一天都不要浪费 
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
2025-01-14 时间日期(1)按月统计日花费,一天都不要浪费 
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
2025-01-14 时间日期(1)按月统计日花费,一天都不要浪费 
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
2025-01-10 大结局(😊)渣男9月爽翻天,罪证送他去西天 
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
2025-01-10 大结局(😊)渣男9月爽翻天,罪证送他去西天 
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
2025-01-09 窗口函数(7)三天吃四餐,你特么是不是乔杉? 
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
2025-01-09 窗口函数(6)隔三差五去召妓,统计间隔用偏移 
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
2024-12-27 窗口函数(5)越来越喜欢召妓,窗口函数用累计(3) 
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
2024-12-27 窗口函数(5)越来越喜欢召妓,窗口函数用累计(3) 
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
2024-12-27 窗口函数(5)越来越喜欢召妓,窗口函数用累计(3) 
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
2024-12-26 窗口函数(4)越来越喜欢召妓,窗口函数用累计(2) 
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
2024-12-24 窗口函数(4)越来越喜欢召妓,窗口函数用累计(2) 
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
2024-12-18 窗口函数(3)越来越喜欢召妓,窗口函数用累计(1) 
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
2024-12-16 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数 
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
2024-12-16 窗口函数(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
2024-12-14 时间日期(6)爽完来根事后烟,不羡鸳鸯不羡仙 
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
2024-12-13 时间日期(5)三腿爱往会所走,全当良心喂了狗 
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 = '红玫瑰按摩保健休闲'