排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
没有收藏的题目。

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-11-26 小结(2)越花越多是死罪,按月统计Substr 
select 
substr(trx_time,1,7) as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from
cmb_usr_trx_rcd
where
usr_id = 5201314520 and
date(trx_time) > '2022-11-01' and
((truncate(trx_amt,0) rlike "88$|98$" and trx_amt>200) and
hour(trx_time) in (23,1,2)
or
upper(mch_nm) rlike "足疗|保健|按摩|养生|SPA")
group by trx_mon
order by trx_mon

这个代码为什么会显示回答错误呢。。。求解
hour(trx_time) in (23,1,2) .0点呢 ,丢了吗?
2024-11-25 时间日期(2)按月统计日花费,一天都不要浪费 
抱歉,是我自己读题误解的关系,已经没问题了
啥也没说
2024-11-25 时间日期(3)按月统计日花费,一天都不要浪费 
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(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
year(trx_time) in (2023,2024)
and
mch_typ = "休闲娱乐"
group by substr(trx_time,1,7)
order by 1

这个为什么会显示回答错误呢。。。
看正确答案,day(last_day(max(trx_time))) as day_of_mon,没有days,只有day
2024-11-25 时间日期(2)按月统计日花费,一天都不要浪费 
这边输出示例里没给出mch_typ列,望更正
你的意思是输出4列,第一列是mch_typ,这列的值是“休闲娱乐”?

提交记录

提交日期 题目名称 提交代码
2024-11-27 时间日期(6)爽完来根事后烟,不羡鸳鸯不羡仙 
select usr_id,
mch_nm,
trx_time,
trx_amt 
from
cmb_usr_trx_rcd a
inner join
(select
 min(trx_time) as first_time
 from cmb_usr_trx_rcd
 where
 usr_id = '5201314520'
 and
 mch_nm like '%红玫瑰%'
) b
on
a.trx_time between b.first_time and date_add(b.first_time, interval 2 hour)
where
usr_id = '5201314520'
order by trx_time
2024-11-27 时间日期(6)爽完来根事后烟,不羡鸳鸯不羡仙 
select * from
cmb_usr_trx_rcd a
inner join
(select
 min(trx_time) as first_time
 from cmb_usr_trx_rcd
 where
 usr_id = '5201314520'
 and
 mch_nm like '%红玫瑰%'
) b
on
a.trx_time between b.first_time and date_add(b.first_time, interval 2 hour)
where
usr_id = '5201314520'
order by trx_time
2024-11-27 时间日期(6)爽完来根事后烟,不羡鸳鸯不羡仙 
with first_time as (
select min(trx_time) as fst
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
)
select *
from cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
trx_time between (select fst from first_time) and date_add((select fst from first_time), interval 2 hour)
order by trx_time
2024-11-27 时间日期(5)三腿爱往会所走,全当良心喂了狗 
select 
'2022-10-03 17:20:20' time_he_love_me,
datediff(current_date,'2022-10-03') days_we_falling_love,
timestampdiff(hour,'2022-10-03 17:20:20',now()) hours_we_falling_love,
datediff(date(min(trx_time)),'2022-10-03') days_he_fvck_else
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
2024-11-27 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
select
distinct a.trx_mon trx_mon,
coalesce(b.last_day,'1900-01-01') last_day,
coalesce(b.day_of_mon,0) day_of_mon,
round(coalesce(b.trx_amt,0),2) trx_amt,
coalesce(b.trx_cnt,0) trx_cnt,
round(coalesce(b.avg_day_amt,0),2) avg_day_amt,
round(coalesce(b.avg_day_cnt,0),2) avg_day_cnt
from
(select
substr(date_value,1,7) as trx_mon
 from
 date_table
 where
 date_value between '2023-01-01' and '2024-06-30'
) a
left join
(select
 substr(aa.trx_time,1,7) trx_mon,
 last_day(max(aa.trx_time)) last_day,
 day(last_day(max(aa.trx_time))) day_of_mon,
 sum(aa.trx_amt) trx_amt,
 count(1) trx_cnt,
 sum(aa.trx_amt)/day(last_day(max(aa.trx_time))) avg_day_amt,
 count(1)/day(last_day(max(aa.trx_time))) avg_day_cnt
 from cmb_usr_trx_rcd aa
 left join
 cmb_mch_typ ab
 on
 aa.mch_nm=ab.mch_nm
 where
 aa.usr_id = '5201314520'
 and
 date(aa.trx_time) between '2023-01-01' and '2024-06-30'
 and
 (ab.mch_typ = '休闲娱乐' or ab.mch_typ is NULL
)
 and
 aa.trx_amt >288
 and
 hour(aa.trx_time) in (23,0,1,2)
 group by trx_mon
) b
on
a.trx_mon = b.trx_mon
order by 1
2024-11-27 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
select 
distinct 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
(select substr(date_value,1,7) as trx_mon
 from date_table
 where substr(date_value,1,7) between '2023-01' and '2024-06'
) a
left join
(select 
 substr(a.trx_time,1,7) as trx_mon,
 last_day(max(a.trx_time)) as last_day,
 day(last_day(max(a.trx_time))) as day_of_mon,
 sum(a.trx_amt) as trx_amt,
 count(1) as trx_cnt,
 sum(a.trx_amt)/day(last_day(max(a.trx_time))) as avg_day_amt,
 count(1)/day(last_day(max(a.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
 a.usr_id = '5201314520'
 and
 substr(a.trx_time,1,7) between '2023-01' and '2024-06'
 and (b.mch_typ = '休闲娱乐' or b.mch_typ is NULL)
 and hour(a.trx_time) in (23,0,1,2)
 and a.trx_amt >=288
 group by substr(a.trx_time,1,7)
 order by 1
) b
on a.trx_mon = b.trx_mon
order by a.trx_mon
2024-11-27 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
select 
distinct 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
(select substr(date_value,1,7) as trx_mon
 from date_table
 where substr(date_value,1,7) between '2023-01' and '2024-06'
) a
left join
(select 
 substr(n.trx_time,1,7) as trx_mon,
 last_day(max(n.trx_time)) as last_day,
 day(last_day(max(n.trx_time))) as day_of_mon,
 sum(n.trx_amt) as trx_amt,
 count(1) as trx_cnt,
 sum(n.trx_amt)/day(last_day(max(n.trx_time))) as avg_day_amt,
 count(1)/day(last_day(max(n.trx_time))) as avg_day_cnt
 from
 cmb_usr_trx_rcd n
 left join
 cmb_mch_typ m
 on n.mch_nm=m.mch_nm
 where
 n.usr_id = '5201314520'
 and
 substr(n.trx_time,1,7) between '2023-01' and '2024-06'
 and (m.mch_typ = '休闲娱乐' or m.mch_typ is NULL)
 and hour(n.trx_time) in (23,0,1,2)
 and n.trx_amt >=288
 group by substr(n.trx_time,1,7)
 order by 1
) b
on a.trx_mon = b.trx_mon
order by a.trx_mon
2024-11-27 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
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
(select distinct substr(date_value,1,7) as trx_mon
 from date_table
 where substr(date_value,1,7) between '2023-01' and '2024-06'
) a
left join
(select 
 substr(n.trx_time,1,7) as trx_mon,
 last_day(max(n.trx_time)) as last_day,
 day(last_day(max(n.trx_time))) as day_of_mon,
 sum(n.trx_amt) as trx_amt,
 count(1) as trx_cnt,
 sum(n.trx_amt)/day(last_day(max(n.trx_time))) as avg_day_amt,
 count(1)/day(last_day(max(n.trx_time))) as avg_day_cnt
 from
 cmb_usr_trx_rcd n
 left join
 cmb_mch_typ m
 on n.mch_nm=m.mch_nm
 where
 n.usr_id = '5201314520'
 and
 substr(n.trx_time,1,7) between '2023-01' and '2024-06'
 and (m.mch_typ = '休闲娱乐' or m.mch_typ is NULL)
 and hour(n.trx_time) in (23,0,1,2)
 and n.trx_amt >=288
 group by substr(n.trx_time,1,7)
 order by 1
) b
on a.trx_mon = b.trx_mon
order by a.trx_mon
2024-11-27 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
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
(select substr(date_value,1,7) as trx_mon
 from date_table
 where substr(date_value,1,7) between '2023-01' and '2024-06'
) a
left join
(select 
 substr(n.trx_time,1,7) as trx_mon,
 last_day(max(n.trx_time)) as last_day,
 day(last_day(max(n.trx_time))) as day_of_mon,
 sum(n.trx_amt) as trx_amt,
 count(1) as trx_cnt,
 sum(n.trx_amt)/day(last_day(max(n.trx_time))) as avg_day_amt,
 count(1)/day(last_day(max(n.trx_time))) as avg_day_cnt
 from
 cmb_usr_trx_rcd n
 left join
 cmb_mch_typ m
 on n.mch_nm=m.mch_nm
 where
 n.usr_id = '5201314520'
 and
 substr(n.trx_time,1,7) between '2023-01' and '2024-06'
 and (m.mch_typ = '休闲娱乐' or m.mch_typ is NULL)
 and hour(n.trx_time) in (23,0,1,2)
 and n.trx_amt >=288
 group by substr(n.trx_time,1,7)
 order by 1
) b
on a.trx_mon = b.trx_mon
order by a.trx_mon
2024-11-26 小结(2)越花越多是死罪,按月统计Substr 
select 
substr(trx_time,1,7) as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from
cmb_usr_trx_rcd
where
usr_id = 5201314520 and
date(trx_time) > '2022-11-01' and
((truncate(trx_amt,0) rlike "88$|98$" and trx_amt>200) and
hour(trx_time) in (23,1,2)
or
upper(mch_nm) rlike "足疗|保健|按摩|养生|SPA")
group by trx_mon
order by trx_mon
2024-11-26 小结(2)越花越多是死罪,按月统计Substr 
select 
substr(trx_time,1,7) as trx_mon
,count(1) as trx_cnt
,sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where usr_id='5201314520'
and 
(
(truncate(trx_amt,0) like '%88' or truncate(trx_amt,0) like '%98')
and 
(hour(trx_time) between 0 and 2 or hour(trx_time)=23)
or mch_nm rlike '.*(足疗|按摩|养生|保健|SPA).*'
)
and 
substr(trx_time,1,7) between '2022-11' and '2024-12'
group by 1
order by 1
2024-11-26 小结(1)大数据早就能扫黄,找足证据不慌张 
select case 
when
truncate(trx_amt,0) rlike "88$|98$" and trx_amt>=200 and hour(trx_time) in (23,0,1,2,3) then 'illegal'
else
'other'
end as trx_typ,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt,
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
group by trx_typ
order by mch_cnt desc
2024-11-26 小结(1)大数据早就能扫黄,找足证据不慌张 
select case 
when
trx_amt rlike "88$|98$" and trx_amt>=200 and hour(trx_time) in (23,0,1,2) then 'illegal'
else
'other'
end as trx_typ,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt,
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
group by trx_typ
order by mch_cnt desc
2024-11-26 字符串与通配符(2)好多关键词做规则,可以使用rlike 
select 
case 
when
mch_nm like "%按摩保健休闲%" then '按摩保健休闲'
when
upper(mch_nm) rlike "按摩|保健|休闲|养生|SPA|会所" then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
count(distinct mch_nm) as mch_cnt
from
cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
or
upper(mch_nm) rlike"按摩|保健|休闲|养生|SPA|会所"
group by reg_rules
order by mch_cnt desc
2024-11-26 找出所有以酒店为起点的类别组合的最热门路线 
WITH start_hotel AS (
SELECT start_loc 
FROM didi_sht_rcd
WHERE start_loc LIKE '%酒店%'
),
ranked_routes AS (
SELECT 
a.start_loc, 
a.end_loc, 
b.loc_ctg, 
COUNT(*) AS trip_count,
ROW_NUMBER() OVER (PARTITION BY b.loc_ctg ORDER BY COUNT(*) DESC) AS rnk
FROM didi_sht_rcd a
LEFT JOIN loc_nm_ctg b
ON a.end_loc = b.loc_nm
WHERE a.start_loc IN (SELECT start_loc FROM start_hotel)
GROUP BY a.start_loc, a.end_loc, b.loc_ctg
)
SELECT 
start_loc,
end_loc,
loc_ctg,
trip_count
FROM ranked_routes
WHERE rnk = 1
order by trip_count desc
2024-11-26 找出所有以酒店为起点的类别组合的最热门路线 
WITH start_hotel AS (
SELECT start_loc 
FROM didi_sht_rcd
WHERE start_loc LIKE '%酒店%'
)
SELECT 
a.start_loc, 
a.end_loc,
b.loc_ctg,
COUNT(b.loc_ctg) AS trip_count
FROM didi_sht_rcd a
LEFT JOIN loc_nm_ctg b
ON a.end_loc = b.loc_nm
WHERE a.start_loc IN (SELECT start_loc FROM start_hotel) 
GROUP BY a.start_loc, a.end_loc, b.loc_ctg
ORDER BY trip_count DESC;
2024-11-26 销售金额前10的商品信息 
select 
goods_id,
sum(order_gmv) as total_gmv,
rank() over (order by sum(order_gmv) desc) as ranking
from
order_info
where
date(order_time) = '2024-9-10'
group by goods_id
order by total_gmv desc
limit 10
2024-11-26 销售金额前10的商品信息 
select 
goods_id,
sum(order_gmv) as total_gmv,
rank() over (order by sum(order_gmv) desc) as ranking
from
order_info
where
date(order_time) = '2024-10-01'
group by goods_id
order by total_gmv desc
limit 10
2024-11-25 时间日期(6)爽完来根事后烟,不羡鸳鸯不羡仙 
with first_trx as(
select min(trx_time) as first_trx_time
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
)
select *
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
trx_time between (select first_trx_time from first_trx) 
and 
(select date_add(first_trx_time ,interval 2 hour) from first_trx)
order by trx_time
2024-11-25 时间日期(6)爽完来根事后烟,不羡鸳鸯不羡仙 
with first_trx as(
select min(trx_time) as first_trx_time
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
)
select *
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
and
trx_time between (select first_trx_time from first_trx) 
and 
(select date_add(first_trx_time ,interval 2 hour) from first_trx)
order by trx_time