with allzuhe as(
select
rcd.start_loc,
start_nm.loc_ctg as start_ctg,
rcd.end_loc,
end_nm.loc_ctg as end_ctg,
count(*)as trip_count
from didi_sht_rcd rcd
join loc_nm_ctg start_nm
on rcd.start_loc=start_nm.loc_nm
join loc_nm_ctg end_nm
on rcd.end_loc=end_nm.loc_nm
group byrcd.start_loc,start_nm.loc_ctg,rcd.end_loc,end_nm.loc_ctg),
yourank as(
select
start_loc,
end_loc,
start_ctg,
end_ctg,
trip_count,
rank() over(partition by start_ctg,end_ctg order by trip_count desc)as rankings
from allzuhe
)
select
start_loc,
end_loc,
start_ctg,
end_ctg,
trip_count
from yourank
where rankings=1
order by trip_count desc
with norank as(
select
cust_uid,
count( cust_uid) as transaction_count,
count(distinct trx_dt) as active_days_count
from mt_trx_rcd_f
group by cust_uid),
nodiff as(
select
cust_uid,
transaction_count,
rank()over( order by transaction_count desc) as transaction_rank,
active_days_count,
rank()over( order by active_days_count desc) as active_days_rank
from norank)
select
cust_uid,
transaction_count,
transaction_rank,
active_days_count,
active_days_rank,
ABS(CAST(transaction_rank AS SIGNED) - CAST(active_days_rank AS SIGNED)) as rank_difference
from nodiff
order by rank_difference desc,cust_uid asc
with norank as(
select
cust_uid,
count( cust_uid) as transaction_count,
count(distinct trx_dt) as active_days_count
from mt_trx_rcd_f
group by cust_uid),
nodiff as(
select
cust_uid,
transaction_count,
rank()over( order by transaction_count desc) as transaction_rank,
active_days_count,
rank()over( order by active_days_count desc) as active_days_rank
from norank)
select
cust_uid,
transaction_count,
transaction_rank,
active_days_count,
active_days_rank,
ABS(CAST(transaction_rank AS SIGNED) - CAST(active_days_rank AS SIGNED)) as rank_difference
from nodiff
with norank as(
select
cust_uid,
count( cust_uid) as transaction_count,
count(distinct trx_dt) as active_days_count
from mt_trx_rcd_f
group by cust_uid),
nodiff as(
select
cust_uid,
transaction_count,
rank()over(partition by cust_uid order by transaction_count desc) as transaction_rank,
active_days_count,
rank()over(partition by cust_uid order by active_days_count desc) as active_days_rank
from norank)
select
cust_uid,
transaction_count,
transaction_rank,
active_days_count,
active_days_rank,
abs(transaction_rank-active_days_rank) as rank_difference
from nodiff
select
rcd.cust_uid,
rcd.start_loc,
rcd.end_loc,
rcd.start_tm,
rcd.car_cls
from didi_sht_rcd rcd
join loc_nm_ctg ctg
on rcd.end_loc=ctg.loc_nm
where loc_ctg='餐饮'
order by start_tm
with meandfriends as(
(select
fr.user1 as me,
fr.user2 as battles,
us.steps as steps
from friend_relationships fr
join user_steps us
on fr.user2=us.user_id)
union
(select
us.user_id as me,
us.user_id as battles,
us.steps as steps
from user_steps us)
union
(select
fr.user2 as me,
fr.user1 as battles,
us.steps as steps
from friend_relationships fr
join user_steps us
on fr.user1=us.user_id )
),
allrankings as(
select
mf.me ,
mf.battles,
mf.steps,
rank() over(partition by mf.me order by mf.steps desc)as row_num
from meandfriends as mf
)
select
ar.me as user_id,
ar.row_num as row_num
from allrankings as ar
where ar.me=ar.battles
order by user_id asc
with meandfriends as(
(select
fr.user1 as me,
fr.user2 as battles,
us.steps as steps
from friend_relationships fr
join user_steps us
on fr.user2=us.user_id)
union
(select
us.user_id as me,
us.user_id as battles,
us.steps as steps
from user_steps us)
union
(select
fr.user2 as me,
fr.user1 as battles,
us.steps as steps
from friend_relationships fr
join user_steps us
on fr.user2=us.user_id )
),
allrankings as(
select
mf.me ,
mf.battles,
mf.steps,
rank() over(partition by mf.me order by mf.steps desc)as row_num
from meandfriends as mf
)
select
ar.me as user_id,
ar.row_num as row_num
from allrankings as ar
where ar.me=ar.battles
order by user_id asc
with meandfriends as(
(select
fr.user1 as me,
fr.user2 as battles,
us.steps as steps
from friend_relationships fr
join user_steps us
on fr.user2=us.user_id)
union
(select
us.user_id as me,
us.user_id as battles,
us.steps as steps
from user_steps us)
),
allrankings as(
select
mf.me ,
mf.battles,
mf.steps,
rank() over(partition by mf.me order by mf.steps desc)as row_num
from meandfriends as mf
)
select
ar.me as user_id,
ar.row_num as row_num
from allrankings as ar
where ar.me=ar.battles
order by user_id asc
with meandfriends as(
(select
fr.user1 as me,
fr.user2 as battles,
us.steps as steps
from friend_relationships fr
join user_steps us
on fr.user2=us.user_id)
union
(select
fr.user1 as me,
fr.user1 as battles,
us.steps as steps
from friend_relationships fr
join user_steps us
on fr.user1=us.user_id)
),
allrankings as(
select
mf.me ,
mf.battles,
mf.steps,
rank() over(partition by mf.me order by mf.steps desc)as row_num
from meandfriends as mf
)
select
ar.me as user_id,
ar.row_num as row_num
from allrankings as ar
where ar.me=ar.battles
order by user_id asc
with meandfriends as(
(select
fr.user1 as me,
fr.user2 as battles,
us.steps as steps
from friend_relationships fr
join user_steps us
on fr.user2=us.user_id)
union
(select
fr.user1 as me,
fr.user1 as battles,
us.steps as steps
from friend_relationships fr
join user_steps us
on fr.user1=us.user_id)
),
allrankings as(
select
mf.me ,
mf.battles,
mf.steps,
rank()over(partition by mf.me order by mf.steps desc)as row_num
from meandfriends as mf
)
select
ar.me as user_id,
ar.row_num as row_num
from allrankings as ar
where ar.me=ar.battles
order by user_id asc
with firstranking as(select
scores.student_id,
name,
score,
rank()over(partition by subject order by score desc) as ranking
from scores
join students
on scores.student_id=students.student_id
where subject='物理' and students.grade_code='S1'
)
select student_id,
name,
score,
ranking
from firstranking
where ranking <= 10
with firstranking as(select
scores.student_id,
name,
score,
dense_rank()over(partition by subject order by score desc) as ranking
from scores
join students
on scores.student_id=students.student_id
where subject='物理' and students.grade_code='S1'
)
select student_id,
name,
score,
ranking
from firstranking
where ranking <= 10
select
scores.student_id,
name,
score,
dense_rank()over(partition by subject order by score desc) as ranking
from scores
join students
on scores.student_id=students.student_id
where subject='物理' and students.grade_code='S1'
limit 10
select
scores.student_id,
name,
score,
row_number()over(partition by subject order by score desc) as ranking
from scores
join students
on scores.student_id=students.student_id
where subject='物理' and students.grade_code='S1'
limit 10
select
scores.student_id,
name,
score,
row_number()over(partition by subject order by score desc) as ranking
from scores
join students
on scores.student_id=students.student_id
where subject='物理'
limit 10
select
scores.student_id,
name,
score,
row_number()over(partition by subject order by score desc) as ranking
from scores
join students
on scores.student_id=students.student_id
where subject='物理'
WITH TOTALTRIP AS(
select
r.start_loc,
r.end_loc,
end_c.loc_ctg,
count(*) as trip_count
from didi_sht_rcd r
join loc_nm_ctg start_c
on r.start_loc = start_c.loc_nm
join loc_nm_ctg end_c
on r.end_loc = end_c.loc_nm
where start_c.loc_ctg="酒店"
GROUP BY r.start_loc,r.end_loc,end_c.loc_ctg
),
rankedtrip as(
select
start_loc,
end_loc,
loc_ctg,
trip_count,
row_number()over(partition by loc_ctg order by trip_count desc) as rn
from TOTALTRIP
)
SELECT
start_loc,
end_loc,
loc_ctg,
trip_count
FROM
rankedtrip
WHERE rn = 1
ORDER BY trip_count DESC
select
s.student_id,
s.name,
scores.score,
row_number()over(partition by s.grade_code order by scores.score desc)as rnk
from scores join students s
on scores.student_id=s.student_id
where scores.subject='物理'
and s.grade_code="S1"
limit 10
select
s.student_id,
s.name,
scores.score,
row_number()over(order by scores.score desc)as rnk
from scores join students s
on scores.student_id=s.student_id
where scores.subject='物理'
and s.grade_code="S1"
limit 10
select
s.student_id,
s.name,
scores.score,
row_number()over(order by scores.score)as rnk
from scores join students s
on scores.student_id=s.student_id
where scores.subject='物理'
limit 10