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
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
这样也可以,先统计出每月每人活跃天数,再筛选计数
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
这样倒是也可以
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
直接这样就行了
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
这个为啥不对
这里有个几个问题:任务描述里写的统计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的,也就是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
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
这样也可以,直接分组累计的基础上,进行滚动求和
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
这个是为啥不对呢
提供一个简单一些的解法,可以先分组统计并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
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条件筛选也可以
不考虑考察点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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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