HiveSql-微信运动在好友中的排名

数据仓库晨曦

发布于 2024-01-08 15:43:03

2320

发布于 2024-01-08 15:43:03

文章被收录于专栏：数据仓库技术

一、题目

有两个表，朋友关系表user_friend，用户步数表user_steps。朋友关系表包含两个字段，用户id，用户好友的id；用户步数表包含两个字段，用户id，用户的步数

1.用户在好友中的排名

user_friend 数据

+----------+------------+
| user_id  | friend_id  |
+----------+------------+
| 1        | 2          |
| 1        | 3          |
| 2        | 1          |
| 2        | 3          |
| 2        | 4          |
| 3        | 1          |
| 3        | 4          |
| 4        | 2          |
| 4        | 3          |
| 5        | 2          |
| 5        | 3          |
| 5        | 4          |
+----------+------------+

user_friend数据

+---------------------+-------------------+
| user_steps.user_id  | user_steps.steps  |
+---------------------+-------------------+
| 1                   | 100               |
| 2                   | 95                |
| 3                   | 90                |
| 4                   | 80                |
| 5                   | 10                |
+---------------------+-------------------+

二、题目分析

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务契合度	⭐️⭐️⭐️⭐️⭐️

解法分析

1.要求解的是自己在好友中的排名，那么需要有自己和好友的步数，可是好友关系表中只有“好友”，需要加入自己的数据；

2.求排名，需要分组开窗；

3.需要筛选出自己名次的那一行数据；

三、SQL

1.列出好友步数，并将自己步数添加到结果中

--好友步数
select t1.user_id,t1.friend_id,t2.steps
from user_friend t1
join user_steps t2
on t1.friend_id = t2.user_id
union all
-- 自己步数
select user_id,user_id as friend_id,steps
from user_steps

查询结果如下：

2.按照用户分组，给每个用户的“好友”进行排名

select
  tt1.user_id,
  tt1.friend_id,
  tt1.steps,
  row_number()over(partition by tt1.user_id order by tt1.steps desc) as row_num
from
  (
  --好友步数
  select
    t1.user_id,
    t1.friend_id,
    t2.steps
  from user_friend t1 join user_steps t2
    on t1.friend_id = t2.user_id
  union all
  -- 自己步数
  select
    user_id,
    user_id as friend_id,
    steps
  from user_steps
  ) tt1

结果如下，我们最终需要的是红色框出来的行，所以再筛选出来,去掉非必要字段。

3求取最终结果

select
	user_id,
	row_num
from
	(
		select
			tt1.user_id,
			tt1.friend_id,
			tt1.steps,
			row_number()over(partition by tt1.user_id order by tt1.steps desc) as row_num
		from
		(
		--好友步数
		select
			t1.user_id,
			t1.friend_id,
			t2.steps
		from user_friend t1 join user_steps t2
		on t1.friend_id = t2.user_id
		union all
		-- 自己步数
		select
			user_id,
			user_id as friend_id,
			steps
		from user_steps
		) tt1
	)tt2
where user_id = friend_id

查询结果