with a1 as (
select user1 as user_id,
user2 as friend_id
from friend_relationships
union
select user2 as user_id,
user1 as friend_id
from friend_relationships
),
a2 as (
select a1.user_id,
friend_id,
steps
from a1
join user_steps u
on a1.friend_id = u.user_id
),
a3 as (
select user_id,
user_id as friend_id,
steps
from user_steps
union
select user_id,
friend_id,
steps
from a2
),
a4 as (
select user_id,
friend_id,
steps,
rank() over (partition by user_id order by steps desc) as row_num
from a3
)
select user_id,
row_num
from a4
where user_id = friend_id
执行得到的结果相同，为什么说我错了呀
with At as (
select distinct usr_id,
date(login_time) as login_date
from user_login_log
where datediff(curdate(), login_time) <= 30
)
select At.login_date,
concat(round(count(distinct Bt.usr_id)/count(distinct At.usr_id)*100, 2), '%') as T1_retention_rate
from At
left join At Bt on At.usr_id = Bt.usr_id
and datediff(Bt.login_date, At.login_date) = 1
group by At.login_date
with new1 as (
select *,
date(login_time) as login_date
from user_login_log
where datediff(curdate(), date(login_time)) <= 180
),
new2 as (
select usr_id,
count(distinct login_date) as freq
from new1
group by usr_id
),
new3 as (
select *,
if(freq between 1 and 5, 1,
if(freq between 6 and 10, 2,
if(freq between 11 and 20, 3, 4))) as type
from new2
)
select sum(type = 1) as days_1_to_5,
sum(type = 2) as days_6_to_10,
sum(type = 3) as days_11_to_20,
sum(type = 4) as days_over_20
from new3
select lpad(hour_entered, 2, '0') as hour_entered, enter_count
from(
select hour(enter_time) as hour_entered,
count(1) as enter_count
from ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
group by hour(enter_time)) as new_table
order by hour_entered asc这样才对吧
select a.mch_nm asshole_tried,
trx_cnt,
b.mch_nm darling_triedfrom
from
(select mch_nm,count(*)
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5201314520'
group by mch_nm
having count(*) >=20) a (mch_nm, trx_cnt)
left join
(select mch_nm
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5211314521'
group by mch_nm) b
on a.mch_nm = b.mch_nm
order by trx_cnt desc
select case when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(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;
select case when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(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;
select case when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(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 lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;
select case when (truncate(trx_amt, 0) like '%88' or truncate(trx_amt, 0) like '%98')
and trx_amt >= 200
and (hour(trx_time) between 0 and 3 or hour(trx_time) = 23)
then 'illegal'
else 'other'
end as trx_typ,
count(*) 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 trx_cnt desc;
select
case when trx_amt=288 then '1.WithHand'
when trx_amt=388 then '2.WithMimi'
when trx_amt=588 then '3.BlowJobbie'
when trx_amt=888 then '4.Doi'
when trx_amt=1288 then '5.DoubleFly'
else '6.other'
end as ser_typ
,count(1) as trx_cnt
,min(date(trx_time)) as first_date
from
cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by ser_typ
order by 1
select
trx_amt,
count(1) as trx_cnt
from
cmb_usr_trx_rcd
where
mch_nm = '红玫瑰按摩保健休闲'
and year(trx_time) = 2024
and month(trx_time) in (1, 2, 3, 4, 5, 6, 7)
group by
trx_amt
order by
trx_cnt desc
limit 5;
select
*
from
cmb_usr_trx_rcd
where
date(trx_time)
between '2024-09-01' and '2024-09-30'
and (
(hour(trx_time) >= 22)
or
(hour(trx_time) between 0 and 5)
)
and usr_id = '5201314520'
order by trx_time
select
*
from
cmb_usr_trx_rcd
where
trx_time
between '2024-09-01' and '2024-09-30'
and hour(trx_time) between 1 and 5
and usr_id = '5201314520'
order by trx_time
select t1.live_id,
live_nm,
count(distinct case when '2021-09-12 23:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
having online_users > 0
order by online_users desc
select t1.live_id,
live_nm,
count(case when '2021-09-12 23:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
select t1.live_id,
live_nm,
count(distinct case when '2021-09-12 23:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
select t1.live_id,
live_nm,
count(case when '2021-09-12 11:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
WITH OnlineUsers AS (
SELECT
t1.live_id,
t1.usr_id
FROM
ks_live_t1 t1
WHERE
'2021-09-12 23:48:38' BETWEEN t1.leave_time AND t1.enter_time
)
SELECT
ou.live_id,
t2.live_nm,
COUNT(distinct ou.usr_id) AS online_users
FROM
OnlineUsers ou
JOIN
ks_live_t2 t2
ON
ou.live_id = t2.live_id
GROUP BY
ou.live_id, t2.live_nm
ORDER BY
online_users DESC
select t1.live_id,
live_nm,
count(distinct case when '2021-09-12 11:48:38' between enter_time and leave_time then usr_id else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
select t1.live_id,
live_nm,
count(case when '2021-09-12 11:48:38' between enter_time and leave_time
then 1 else null end) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
group by t1.live_id, live_nm
order by online_users desc
with a1 as (
select user1 as user_id,
user2 as friend_id,
steps
from friend_relationships f
join user_steps u
on f.user2 = u.user_id
union
select user2 as user_id,
user1 as friend_id,
steps
from friend_relationships f
join user_steps u
on f.user1 = u.user_id
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
a2 as (
select user_id,
friend_id,
steps,
row_number() over (partition by user_id order by steps desc) as row_num
from a1
)
select user_id,
row_num
from a2
where user_id = friend_id
with a1 as (
select user1 as user_id,
user2 as friend_id,
steps
from friend_relationships f
join user_steps u
on f.user2 = u.user_id
union
select user2 as user_id,
user1 as friend_id,
steps
from friend_relationships f
join user_steps u
on f.user1 = u.user_id
union
select user_id,
user_id as friend_id,
steps
from user_steps
),
a2 as (
select user_id,
friend_id,
steps,
rank() over (partition by user_id order by steps desc) as row_num
from a1
)
select user_id,
row_num
from a2
where user_id = friend_id
with a1 as ( select user1 as user_id, user2 as friend_id from friend_relationships union select user2 as user_id, user1 as friend_id from friend_relationships ), a2 as ( select a1.user_id, friend_id, steps from a1 join user_steps u on a1.friend_id = u.user_id ), a3 as ( select user_id, user_id as friend_id, steps from user_steps union select user_id, friend_id, steps from a2 ), a4 as ( select user_id, friend_id, steps, rank() over (partition by user_id order by steps desc) as row_num from a3 ) select user_id, row_num from a4 where user_id = friend_id 执行得到的结果相同，为什么说我错了呀with At as ( select distinct usr_id, date(login_time) as login_date from user_login_log where datediff(curdate(), login_time) <= 30 ) select At.login_date, concat(round(count(distinct Bt.usr_id)/count(distinct At.usr_id)*100, 2), '%') as T1_retention_rate from At left join At Bt on At.usr_id = Bt.usr_id and datediff(Bt.login_date, At.login_date) = 1 group by At.login_datewith new1 as ( select *, date(login_time) as login_date from user_login_log where datediff(curdate(), date(login_time)) <= 180 ), new2 as ( select usr_id, count(distinct login_date) as freq from new1 group by usr_id ), new3 as ( select *, if(freq between 1 and 5, 1, if(freq between 6 and 10, 2, if(freq between 11 and 20, 3, 4))) as type from new2 ) select sum(type = 1) as days_1_to_5, sum(type = 2) as days_6_to_10, sum(type = 3) as days_11_to_20, sum(type = 4) as days_over_20 from new3