排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-11-30 好友步数排名-不考虑反向好友关系 
select u.usr_id user_id, u.rnk row_num
from 
  (select t.*,
      rank() over (partition by usr_id order by steps desc) rnk
  from
    (select fr.user1 usr_id, fr.user2 frd_id, us.steps
    from friend_relationships fr
    inner join user_steps us on fr.user2 = us.user_id
    union all
    select user_id, user_id frd_id, steps
    from user_steps
    order by usr_id, frd_id) t
  ) u
where usr_id = frd_id
order by usr_id
读了好几遍才搞明白,要的是查询每个人在自己的单向朋友圈中位列第几,排名的时候可重复可跳号,用rank
啥也没说
2024-11-29 7月之后再也没活跃过的用户 
select count(1) inactive_user_count
from 
  (select usr_id
  from 
    (select left(login_time, 7) mon, usr_id,
        count(*) days
    from user_login_log
    group by mon, usr_id
    order by mon, usr_id) t
  group by usr_id
  having count((mon <= '2024-07' and days >= 10) or null) > 0
  and count((mon > '2024-07' and days>=10) or null) = 0) u
这样也可以,先统计出每月每人活跃天数,再筛选计数
啥也没说
2024-11-29 10月1日后再也没活跃过的用户 
select count(1) inactive_user_count
from 
  (select usr_id, 
      count(login_time <= '2024-10-01' or null) cnt1,
      count(login_time > '2024-10-01' or null) cnt2
  from user_login_log
  group by usr_id) t
where cnt1 > 0 and cnt2 = 0
这样倒是也可以
妙不可言!
2024-11-29 销售金额前10的商品信息(2) 
select *
from 
  (select date(order_time) order_date,
      goods_id, sum(order_gmv) total_gmv,
      rank() over(partition by date(order_time) order by sum(order_gmv)) ranking
  from order_info
  where order_time like '2024-10%'
  group by order_date, goods_id) t
where ranking <= 3
直接这样就行了
啥也没说
2024-11-26 大结局(😊)渣男9月爽翻天,罪证送他去西天 
select t.trx_dt date_value,
	coalesce(u.fvck_cnt, 0) FvckCnt,
	coalesce(u.hand_cnt, 0) WithHand,
    coalesce(u.ball_cnt, 0) WithBalls,
    coalesce(u.mouth_cnt, 0) BlowJobbie,
    coalesce(u.doi_cnt, 0) Doi,
    coalesce(u.df_cnt, 0) DoubleFly,
    if(v.trx_dt is not null, 1, 0) Ohya
from
  (select distinct date(trx_time) trx_dt 
  from cmb_usr_trx_rcd 
  where trx_time like '2024-09%'
  order by trx_dt) t
left join
  (select date(trx_time) trx_dt,
   count(1) fvck_cnt,
   count(trx_amt = 288 or null) hand_cnt,
   count(trx_amt = 388 or null) ball_cnt,
   count(trx_amt = 588 or null) mouth_cnt,
   count(trx_amt = 888 or null) doi_cnt,
   count(trx_amt = 1288 or null) df_cnt
  from cmb_usr_trx_rcd
  where usr_id = 5201314520 and trx_time like '2024-09%'
  and mch_nm like '红玫瑰按摩保健休闲'
  group by trx_dt order by trx_dt) u
on t.trx_dt = u.trx_dt
left join
  (select distinct trx_dt
from 
  (select date(trx_time) trx_dt, trx_amt, trx_time,
     lag(trx_amt, 1) over (partition by usr_id order by trx_time) prev_amt,
     lag(trx_time, 1) over (partition by usr_id order by trx_time) prev_time
  from cmb_usr_trx_rcd
  where usr_id = 5201314520 and trx_time like '2024-09%'
  and mch_nm = '红玫瑰按摩保健休闲') p
where prev_amt = 888 and trx_amt = 1288 and date(trx_time) = date(prev_time)) v
on t.trx_dt = v.trx_dt
order by date_value
这个为啥不对
终于被你发现了!!算你刷的最认真~😘
2024-11-21 窗口函数(7)三天吃四餐,你特么是不是乔杉? 
这种应该就是“连续登录”问题,需要三天之内去4次,那只需要最近的4次发生在三天内就行,于是按用户partition按时间排序,找到此人的时间上往下第三行的记录,算出时间差发生在三天内即可。
啥也没说
2024-11-21 窗口函数(5)越来越喜欢召妓,窗口函数用累计(3) 
这里有个几个问题:任务描述里写的统计2023-Q1以来的数据,但是题解没有加这个条件;输出示例的WithHand和Doi,题解用的全小写。给出一个按当前题解基础的简单解法:
select trx_quarter, 
	sum(WithHand) over (order by trx_quarter) withhand,
    sum(Doi) over (order by trx_quarter) doi
from 
  (select concat(year(trx_time), '-Q', quarter(trx_time)) trx_quarter,
      count(trx_amt = 288 or null) WithHand,
      count(trx_amt = 888 or null) Doi
  from cmb_usr_trx_rcd
  where usr_id = 5201314520 and mch_nm = '红玫瑰按摩保健休闲'
  group by trx_quarter order by trx_quarter) t
已修正,感谢指出
2024-11-21 窗口函数(4)越来越喜欢召妓,窗口函数用累计(2) 
这里有个问题,按照上题的要求,以及输出的示例,应该是包含了2024的,也就是trx_time >= '2023-01-01',但是题解和答案都是按照year(trx_time) = 2023来做的,我试了半天才发现。给出一个按仅2023年的简单解法:
select m.trx_mon,
	sum(coalesce(n.trx_amt, 0)) over (order by m.trx_mon) trx_amt
from
  (select distinct left(trx_time, 7) trx_mon 
  from cmb_usr_trx_rcd where trx_time like '2023%'
  order by trx_mon) m
left join 
  (select left(a.trx_time, 7) trx_mon,
   sum(a.trx_amt) trx_amt
  from cmb_usr_trx_rcd a
  inner join cmb_mch_typ b on a.mch_nm = b.mch_nm
  where a.usr_id = 5201314520 and a.trx_time like '2023%'
  and b.mch_typ = '休闲娱乐'
  group by trx_mon) n
on m.trx_mon = n.trx_mon
order by trx_mon
2023-至今,没毛病呀,答案也能包含2024~
2024-11-21 窗口函数(3)越来越喜欢召妓,窗口函数用累计(1) 
select left(a.trx_time, 7) trx_mon, 
	sum(sum(a.trx_amt)) over (order by left(a.trx_time, 7)) trx_amt
from cmb_usr_trx_rcd a
inner join cmb_mch_typ b on a.mch_nm = b.mch_nm
where a.usr_id = 5201314520 and b.mch_typ = '休闲娱乐'
and a.trx_time >= '2023-01-01'
group by trx_mon
这样也可以,直接分组累计的基础上,进行滚动求和
啥也没说
2024-11-19 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数 
select u.*
from
  (select null mch_typ, mch_nm, count(1) trx_cnt,
      dense_rank() over(order by count(1) desc) rnk
  from cmb_usr_trx_rcd 
  where usr_id = 5201314520 
  group by mch_nm
  union all
  select t.mch_typ, r.mch_nm, count(1) trx_cnt,
      dense_rank() over(partition by t.mch_typ order by count(*) desc) rnk
  from cmb_usr_trx_rcd r
  inner join cmb_mch_typ t on r.mch_nm = t.mch_nm
  where usr_id = 5201314520 
  group by t.mch_typ, r.mch_nm
  ) u
where rnk <= 2
order by mch_typ, rnk
这个是为啥不对呢
对的,你这个解法更简单
2024-11-19 窗口函数(1)年度前三和每月前三,搞懂排序窗口函数 
提供一个简单一些的解法,可以先分组统计并union all拼起来,然后统一使用窗口函数:
select u.trx_mon, u.mch_nm, u.sum_trx_amt
from 
  (select t.*,
      row_number() over(partition by trx_mon order by sum_trx_amt desc) rn
  from 
    (select year(trx_time) trx_mon, mch_nm, sum(trx_amt) sum_trx_amt
    from cmb_usr_trx_rcd
    where usr_id = '5201314520' and trx_time like '2024%'
    group by trx_mon, mch_nm
    union all
    select left(trx_time, 7) trx_mon, mch_nm, sum(trx_amt) sum_trx_amt
    from cmb_usr_trx_rcd
    where usr_id = '5201314520' and trx_time like '2024%'
    group by trx_mon, mch_nm) t
   ) u
where rn <= 3
这里还有一个坑:示例输出的总金额字段为trx_amt,而题解和答案用的sum_trx_amt
已修正,🌹
2024-11-19 时间日期(6)爽完来根事后烟,不羡鸳鸯不羡仙 
select a.*
from cmb_usr_trx_rcd a
inner join
(select min(trx_time) first_time 
from cmb_usr_trx_rcd
where usr_id = 5201314520 and mch_nm = '红玫瑰按摩保健休闲') b
on a.trx_time between b.first_time and date_add(b.first_time, interval 2 hour)
where a.usr_id = 5201314520
order by a.trx_time
我觉得使用inner join条件筛选也可以
思路新奇哥,不管白猫黑猫,能跑就是好猫!
2024-11-19 时间日期(5)三腿爱往会所走,全当良心喂了狗 
mysql的这两个函数,减数和被减数相反,太逆天了
服不服?细不细?
2024-11-19 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
还有个巨坑,当月天数的字段,输出示例是days_of_mon,但是题解和正确结果都是day_of_mon,给我怀疑人生了半天
改了改了,难免有点差错
2024-11-19 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
输出要求last_day默认值为'1990-01-01',但是输出示例和题解中都是'1900-01-01'
哥,你太严谨了,已改,🌹
2024-11-19 时间日期(1)按月统计日花费,一天都不要浪费 
提取年月应该是substr(trx_time,1,7)是吧,另外group by应该可以直接用别名的
是的;group 1,2,3 非常方便
2024-11-18 表连接(5)哪些没被分出来,用左用内你来猜 
本来就是找没有分类的数据,示例的输出还有分类非空的,给我整的自我怀疑了都
啥也没说
2024-11-18 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
第二列,输出是asshole_tried_cnt,答案是trx_cnt
已改
2024-11-18 表连接(1)你们难道都去过?那就试试用InnerJoin 
不考虑考察点join的话,解决这个问题还有个更简单的思路──分组计数作为条件:
select mch_nm 
from cmb_usr_trx_rcd
where trx_time like '2024%'
group by mch_nm
having count(usr_id = '5201314520' or null) > 0 
and count(usr_id = '5211314521' or null) > 0
order by mch_nm desc
啥也没说
2024-11-18 子查询(1)玩的最嗨那天在做甚?要用Where子查询 
select * 
from cmb_usr_trx_rcd
where usr_id = '5201314520' and trx_time like '2024%'
order by trx_amt desc
limit 1
和这个好像一样?
啥也没说
2024-11-18 小结(2)越花越多是死罪,按月统计Substr 
这里年月用left(trx_time, 7)也可
啥也没说
2024-11-18 小结(1)大数据早就能扫黄,找足证据不慌张 
第一类的金额条件可以用truncate(trx_amt, 0) rlike '.*(88|98)'
手动赞
2024-11-18 字符串与通配符(2)好多关键词做规则,可以使用rlike 
where条件感觉只需要upper(mch_nm) rlike '.*(按摩|保健|休闲|SPA|养生|会所).*'就行了,因为它已经将六个字完整匹配的情况包含进去了,只需要在case中用标签分离出来就行
是的,case when一旦多了,容易晕
2024-11-18 分类(1)姿势太多很过分,分类要用CaseWhen 
case..when..else..end要括号包起来,并且要按标签分组
啥也没说
2024-11-18 分组与聚合函数(5)五花八门的项目,其实都有固定套路(2) 
left(trx_time, 7) between '2023-01' and '2024-06' 这样筛选范围更简单
好办法!
2024-11-18 分组与聚合函数(3)五花八门的项目,其实都有固定套路(1) 
left(trx_time, 7) between '2024-01' and '2024-07' 日期范围这样也可以
还得是你 总是能想出多种解法
2024-11-18 分组与聚合函数(2)擦边营收怎么样,聚合函数可看出 
trx_time like '2024-09%' 日期用这个也行
举一反三,就得这样学!👍
2024-11-18 分组与聚合函数(1)Money全都花在哪,GroupBy来查一查 
trx_time like '2024%' 这样也行
思路新奇,给你点赞👍

提交记录

提交日期 题目名称 提交代码
2024-11-30 好友步数排名-不考虑反向好友关系 
select u.usr_id user_id, u.rnk row_num
from 
(select t.*,
rank() over (partition by usr_id order by steps desc) rnk
from
(select fr.user1 usr_id, fr.user2 frd_id, us.steps
from friend_relationships fr
inner join user_steps us on fr.user2 = us.user_id
union all
select user_id, user_id frd_id, steps
from user_steps
order by usr_id, frd_id) t
) u
where usr_id = frd_id
order by usr_id
2024-11-30 好友步数排名-不考虑反向好友关系 
select u.usr_id, u.rnk
from 
(select t.*,
rank() over (partition by usr_id order by steps desc) rnk
from
(select fr.user1 usr_id, fr.user2 frd_id, us.steps
from friend_relationships fr
inner join user_steps us on fr.user2 = us.user_id
union all
select user_id, user_id frd_id, steps
from user_steps
order by usr_id, frd_id) t
) u
where usr_id = frd_id
order by usr_id
2024-11-29 7月之后再也没活跃过的用户 
select count(1) inactive_user_count
from 
(select usr_id
from 
(select left(login_time, 7) mon, usr_id,
count(*) days
from user_login_log
group by mon, usr_id
order by mon, usr_id) t
group by usr_id
having count((mon <= '2024-07' and days >= 10) or null) > 0
and count((mon > '2024-07' and days>=10) or null) = 0) u
2024-11-29 7月之后再也没活跃过的用户 
select count(1) inactive_user_count
from 
(select usr_id
from 
(select left(login_time, 7) mon, usr_id,
count(*) cnt
from user_login_log
group by mon, usr_id
order by mon, usr_id) t
group by usr_id
having count(mon <= '2024-07' or null) > 0
and count(mon > '2024-07' or null) = 0) u
2024-11-29 10月1日后再也没活跃过的用户 
select count(1) inactive_user_count
from 
(select usr_id, 
count(login_time < '2024-10-01' or null) cnt1,
count(login_time >= '2024-10-01' or null) cnt2
from user_login_log
group by usr_id) t
where cnt1 > 0 and cnt2 = 0
2024-11-29 10月1日后再也没活跃过的用户 
select count(1) inactive_user_count
from 
(select usr_id, 
count(login_time <= '2024-10-01' or null) cnt1,
count(login_time > '2024-10-01' or null) cnt2
from user_login_log
group by usr_id) t
where cnt1 > 0 and cnt2 = 0
2024-11-29 10月1日后再也没活跃过的用户 
select count(1) inactive_user_count
from 
(select usr_id, 
count(login_time < '2024-10-02' or null) before_cnt,
count(login_time >= '2024-10-02' or null) after_cnt
from user_login_log
group by usr_id) t
2024-11-29 销售金额前10的商品信息(2) 
select *
from 
(select date(order_time) order_date,
goods_id, sum(order_gmv) total_gmv,
rank() over(partition by date(order_time) order by sum(order_gmv)) ranking
from order_info
where order_time like '2024-10%'
group by order_date, goods_id) t
where ranking <= 3
2024-11-29 销售金额前10的商品信息 
select *
from 
(select goods_id, sum(order_gmv) total_gmv,
rank() over(order by sum(order_gmv) desc) ranking
from order_info 
where order_time like '2024-09-10%'
group by goods_id) t
where ranking <= 10
2024-11-29 销售金额前10的商品信息 
select *
from 
(select goods_id, sum(order_gmv) total_gmv,
dense_rank() over(order by sum(order_gmv) desc) ranking
from order_info 
where order_time like '2024-09-10%'
group by goods_id) t
where ranking <= 10
2024-11-26 大结局(😊)渣男9月爽翻天,罪证送他去西天 
select t.trx_dt date_value,
	coalesce(u.fvck_cnt, 0) FvckCnt,
	coalesce(u.hand_cnt, 0) WithHand,
coalesce(u.ball_cnt, 0) WithBalls,
coalesce(u.mouth_cnt, 0) BlowJobbie,
coalesce(u.doi_cnt, 0) Doi,
coalesce(u.df_cnt, 0) DoubleFly,
if(v.trx_dt is not null, 1, 0) Ohya
from
(select distinct date(trx_time) trx_dt 
from cmb_usr_trx_rcd 
where trx_time like '2024-09%'
order by trx_dt) t
left join
(select date(trx_time) trx_dt,
 count(1) fvck_cnt,
 count(trx_amt = 288 or null) hand_cnt,
 count(trx_amt = 388 or null) ball_cnt,
 count(trx_amt = 588 or null) mouth_cnt,
 count(trx_amt = 888 or null) doi_cnt,
 count(trx_amt = 1288 or null) df_cnt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and trx_time like '2024-09%'
and mch_nm like '%红玫瑰按摩保健休闲%'
group by trx_dt order by trx_dt) u
on t.trx_dt = u.trx_dt
left join
(select distinct trx_dt
from 
(select date(trx_time) trx_dt, trx_amt, trx_time,
 lag(trx_amt, 1) over (partition by usr_id order by trx_time) prev_amt,
 lag(trx_time, 1) over (partition by usr_id order by trx_time) prev_time
from cmb_usr_trx_rcd
where usr_id = 5201314520 and trx_time like '2024-09%'
and mch_nm like '%红玫瑰按摩保健休闲%') p
where prev_amt = 888 and trx_amt = 1288 and date(trx_time) = date(prev_time)) v
on t.trx_dt = v.trx_dt
order by date_value
2024-11-26 大结局(😊)渣男9月爽翻天,罪证送他去西天 
select t.trx_dt date_value,
	coalesce(u.fvck_cnt, 0) FvckCnt,
	coalesce(u.hand_cnt, 0) WithHand,
coalesce(u.ball_cnt, 0) WithBalls,
coalesce(u.mouth_cnt, 0) BlowJobbie,
coalesce(u.doi_cnt, 0) Doi,
coalesce(u.df_cnt, 0) DoubleFly,
if(v.trx_dt is not null, 1, 0) Ohya
from
(select distinct date(trx_time) trx_dt 
from cmb_usr_trx_rcd 
where trx_time like '2024-09%'
order by trx_dt) t
left join
(select date(trx_time) trx_dt,
 count(1) fvck_cnt,
 count(trx_amt = 288 or null) hand_cnt,
 count(trx_amt = 388 or null) ball_cnt,
 count(trx_amt = 588 or null) mouth_cnt,
 count(trx_amt = 888 or null) doi_cnt,
 count(trx_amt = 1288 or null) df_cnt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and trx_time like '2024-09%'
and mch_nm = '红玫瑰按摩保健休闲'
group by trx_dt order by trx_dt) u
on t.trx_dt = u.trx_dt
left join
(select distinct trx_dt
from 
(select date(trx_time) trx_dt, trx_amt, trx_time,
 lag(trx_amt, 1) over (partition by usr_id order by trx_time) prev_amt,
 lag(trx_time, 1) over (partition by usr_id order by trx_time) prev_time
from cmb_usr_trx_rcd
where usr_id = 5201314520 and trx_time like '2024-09%'
and mch_nm = '红玫瑰按摩保健休闲') p
where prev_amt = 888 and trx_amt = 1288 and date(trx_time) = date(prev_time)) v
on t.trx_dt = v.trx_dt
order by date_value
2024-11-26 大结局(😊)渣男9月爽翻天,罪证送他去西天 
select t.trx_dt date_value,
	coalesce(u.fvck_cnt, 0) FvckCnt,
	coalesce(u.hand_cnt, 0) WithHand,
coalesce(u.ball_cnt, 0) WithBalls,
coalesce(u.mouth_cnt, 0) BlowJobbie,
coalesce(u.doi_cnt, 0) Doi,
coalesce(u.df_cnt, 0) DoubleFly,
if(v.trx_dt is not null, 1, 0) Ohya
from
(select distinct date(trx_time) trx_dt 
from cmb_usr_trx_rcd 
where trx_time like '2024-09%'
order by trx_dt) t
left join
(select date(trx_time) trx_dt,
 count(1) fvck_cnt,
 count(trx_amt = 288 or null) hand_cnt,
 count(trx_amt = 388 or null) ball_cnt,
 count(trx_amt = 588 or null) mouth_cnt,
 count(trx_amt = 888 or null) doi_cnt,
 count(trx_amt = 1288 or null) df_cnt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and trx_time like '2024-09%'
and mch_nm = '红玫瑰按摩保健休闲'
group by trx_dt order by trx_dt) u
on t.trx_dt = u.trx_dt
left join
(select *
from 
(select date(trx_time) trx_dt, trx_amt, trx_time,
 lag(trx_amt, 1) over (partition by usr_id order by trx_time) prev_amt,
 lag(trx_time, 1) over (partition by usr_id order by trx_time) prev_time
from cmb_usr_trx_rcd
where usr_id = 5201314520 and trx_time like '2024-09%'
and mch_nm = '红玫瑰按摩保健休闲') p
where prev_amt = 888 and trx_amt = 1288 and date(trx_time) = date(prev_time)) v
on t.trx_dt = v.trx_dt
order by date_value
2024-11-26 大结局(😊)渣男9月爽翻天,罪证送他去西天 
select t.trx_dt date_value,
	coalesce(u.fvck_cnt, 0) FvckCnt,
	coalesce(u.hand_cnt, 0) WithHand,
coalesce(u.ball_cnt, 0) WithBalls,
coalesce(u.mouth_cnt, 0) BlowJobbie,
coalesce(u.doi_cnt, 0) Doi,
coalesce(u.df_cnt, 0) DoubleFly,
if(v.trx_dt is not null, 1, 0) Ohya
from
(select distinct date(trx_time) trx_dt 
from cmb_usr_trx_rcd 
where trx_time like '2024-09%'
order by trx_dt) t
left join
(select date(trx_time) trx_dt,
 count(1) fvck_cnt,
 count(trx_amt = 288 or null) hand_cnt,
 count(trx_amt = 388 or null) ball_cnt,
 count(trx_amt = 588 or null) mouth_cnt,
 count(trx_amt = 888 or null) doi_cnt,
 count(trx_amt = 1288 or null) df_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520' and trx_time like '2024-09%'
and mch_nm = '红玫瑰按摩保健休闲'
group by trx_dt order by trx_dt) u
on t.trx_dt = u.trx_dt
left join
(select *
from 
(select date(trx_time) trx_dt, trx_amt, trx_time,
 lag(trx_amt, 1) over (partition by usr_id order by trx_time) prev_amt,
 lag(trx_time, 1) over (partition by usr_id order by trx_time) prev_time
from cmb_usr_trx_rcd
where usr_id = '5201314520' and trx_time like '2024-09%'
and mch_nm = '红玫瑰按摩保健休闲') p
where prev_amt = 888 and trx_amt = 1288 and date(trx_time) = date(prev_time)) v
on t.trx_dt = v.trx_dt
order by date_value
2024-11-26 窗口函数(7)三天吃四餐,你特么是不是乔杉? 
select distinct usr_id
from 
(select usr_id,
datediff(trx_time, lag(trx_time, 3) over (partition by usr_id order by trx_time)) days
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲') t
where days <= 3
order by usr_id
2024-11-26 窗口函数(7)三天吃四餐,你特么是不是乔杉? 
select distinct usr_id
from 
(select usr_id,
datediff(trx_time, lag(trx_time, 3) over (partition by usr_id order by trx_time desc)) days
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲') t
where days <= 3
order by usr_id
2024-11-22 大结局(😊)渣男9月爽翻天,罪证送他去西天 
select t.trx_dt date_value,
	t.cnt FvckCnt,
t.hand_cnt WithHand,
t.ball_cnt WithBalls,
t.mouth_cnt BlowJobbie,
t.doi_cnt Doi,
t.double_cnt DoubleFly,
	if((doi_cnt > 0 and double_cnt > 0), 1, 0) Ohya 
from
(select date(trx_time) trx_dt,
count(1) cnt,
count(trx_amt = 288 or null) hand_cnt,
count(trx_amt = 388 or null) ball_cnt,
count(trx_amt = 588 or null) mouth_cnt,
count(trx_amt = 888 or null) doi_cnt,
count(trx_amt = 1288 or null) double_cnt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and trx_time like '2024-09%'
group by trx_dt order by trx_dt) t
order by date_value
2024-11-22 窗口函数(7)三天吃四餐,你特么是不是乔杉? 
selectdistinct usr_id
from 
(select usr_id,
datediff(trx_time, lag(trx_time, 3) over (partition by usr_id order by trx_time)) days
from cmb_usr_trx_rcd 
where mch_nm = '红玫瑰按摩保健休闲') t
where days <= 3
order by usr_id
2024-11-21 窗口函数(7)三天吃四餐,你特么是不是乔杉? 
select distinct usr_id
from 
(select usr_id, 
datediff(trx_time, lag(trx_time, 3) over(partition by usr_id order by trx_time)) days
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲') t
where days <=3
order by usr_id
2024-11-21 窗口函数(7)三天吃四餐,你特么是不是乔杉? 
select usr_id
from 
(select usr_id, 
datediff(trx_time, lag(trx_time, 3) over(partition by usr_id order by trx_time)) days
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲') t
where days <=3
group by usr_id
order by usr_id