力扣之1355.活动参与者

题目：

Sql 测试用例：

Create table If Not Exists Friends (id int, name varchar(30), activity varchar(30));
Create table If Not Exists Activities (id int, name varchar(30));
Truncate table Friends;
insert into Friends (id, name, activity) values ('1', 'Jonathan D.', 'Eating');
insert into Friends (id, name, activity) values ('2', 'Jade W.', 'Singing');
insert into Friends (id, name, activity) values ('3', 'Victor J.', 'Singing');
insert into Friends (id, name, activity) values ('4', 'Elvis Q.', 'Eating');
insert into Friends (id, name, activity) values ('5', 'Daniel A.', 'Eating');
insert into Friends (id, name, activity) values ('6', 'Bob B.', 'Horse Riding');
Truncate table Activities;
insert into Activities (id, name) values ('1', 'Eating');
insert into Activities (id, name) values ('2', 'Singing');
insert into Activities (id, name) values ('3', 'Horse Riding');

分析：首先，我们可以先按照活动分组，然后算出每个活动中的id个数，然后按照这个总数进行排序，正序和倒序，然后不要排名为一的数据。

sql实现：

with t1 as (select activity,count(1) nu from Friends group by activity  -- 按照活动分组，然后算出每个组内id的个数
),t2 as (select activity,rank() over (order by nu) rk1,rank() over (order by nu desc ) rk2 from t1        -- 然后按照个数排序，算出升序和降序的排名（这里使用rank函数，因为需要考虑并列的情况）)
select activity from t2 where rk1 !=1 and rk2 !=1  -- 然后取出升序和降序排名不为1的活动名称

pandas测试例子：

data = [[1, 'Jonathan D.', 'Eating'], [2, 'Jade W.', 'Singing'], [3, 'Victor J.', 'Singing'], [4, 'Elvis Q.', 'Eating'], [5, 'Daniel A.', 'Eating'], [6, 'Bob B.', 'Horse Riding']]
friends = pd.DataFrame(data, columns=['id', 'name', 'activity']).astype({'id':'Int64', 'name':'object', 'activity':'object'})
data = [[1, 'Eating'], [2, 'Singing'], [3, 'Horse Riding']]
activities = pd.DataFrame(data, columns=['id', 'name']).astype({'id':'Int64', 'name':'object'})

pandas分析：和sql 的解法差不多

pandas实现：

import pandas as pddef activity_participants(friends: pd.DataFrame, activities: pd.DataFrame) -> pd.DataFrame:friend=friends.groupby('activity')['id'].count().reset_index()  -- 分组，算出每个组内id个数friend['rn']=friend['id'].rank(method='min')  -- 升序排序考虑次数相同friend['rn1']=friend['id'].rank(method='min',ascending=False)  -- 降序排序考虑次数相同friend=friend[(friend['rn']!=1) & (friend['rn1']!=1)]['activity'] --然后取出升序和降序排名不为1的活动名称return friend.to_frame() -- 转换成dataframe对象