hive sql分组后取每组前100

时间：2025-04-16 08:26:38

最近在进行风控系统-反黑策略的时候遇见个有趣的SQL 

select  * 
  from (
    select *,RANK() OVER (PARTITION BY strategy_id ORDER BY result_id DESC ) as sort_id 
    from distinct_uid_strategy_result
  ) t
where t.sort_id<=100

解释一下，我的这个是想要获取每个策略的前100名

strategy_id：就是策略的id（当然你可以是班级id，部门id之类的）

result_id：策略表的主键id（当然你想要根据某些值排序，这里可以是学生成绩、员工绩效之类的）

sort_id：排序后的顺序，根据他获取你想要的名词

上面的这个sql可以用在获取每个种类的某种指标下的前几名。

 

后来又发现可以查看用户最后一次登录的ip

SELECT * FROM
  (SELECT t.*, row_number() over(partition BY user_id ORDER BY created_at DESC) rn FROM user_login_log t) order_status
  WHERE rn = 1
  ORDER BY user_id ;

当然上面只是SQL，开头提到了在做风控系统，考虑的角度还是挺多的，给大家推荐几个网站：

 

/WalterInSH/risk-management-note

/yangliang1415/awesome-risk-control

 

“Where not in”使用两列

select ct.* from
completedTasks ct
left outer join plannedTasks pt on  =  and  = 
where  is null

您可以使用此(更紧凑的语法)：

SELECT *
FROM CompletedTasks
WHERE (userID, taskID) NOT IN
      ( SELECT userID, taskID
        FROM PlannedTasks
      ) ;

或NOT EXISTS版本(虽然更复杂，但使用适当的索引应该更有效)：

SELECT c.*
FROM CompletedTasks AS c
WHERE NOT EXISTS 
      ( SELECT 1
        FROM PlannedTasks AS p
        WHERE  = 
          AND  = 
      ) ;

后来又遇见一组股票在一个字段里，那么怎么进行一行拆多行，explode()函数就是干这件事的

问题：

有一个表（表名：book，两个字段：id,desc）

id    　desc

001    书名: 追风筝的人, ISBN编号: 9787208061644, 作者: 卡勒德.胡赛尼

002    书名: 秘密花园, ISBN编号: 9787550252585, 作者: 乔汉娜·贝斯福

需求：

其中desc字段包含了几个参数信息，需把它拆分开来，一行一个参数，结果如下

001    书名: 追风筝的人

001    ISBN编号: 9787208061644

001     作者: 卡勒德.胡赛尼

002    书名: 秘密花园

002    ISBN编号: 9787550252585

002    作者: 乔汉娜·贝斯福

解决办法：

select id,sp from book a  lateral view explode(split(,',')) t as sp where 1=1;

id   sp

001书名: 追风筝的人

001ISBN编号: 9787208061644

....

其中sp是拆出来的字段的，结果和需求一样。

非常简练，虽然hive有不能insert,update的缺点，但很多sql语法都支持，不失为大数据分析的一个好工具！

后面又看到删除和修改表

truncate table tmp.water_army_ip_strategy_mouth;

alter table tmp.water_army_ip_strategy_mouth CHANGE type type STRING;

相关文章