SQL | 窗口函数 row number + partition by 排序

SQL | 窗口函数 row number + partition by 排序目录1  背景2  SQL牛逼函数走起来2.1  Step12.2  SQL实现12.3  Step22.4  SQL实现22.5  补充-collect函数2.5.1  SQL语句3  头条面试SQL题3.1&nbs…

大家好,又见面了,我是你们的朋友全栈君。

1 背景

今天实习学到了一个很牛逼的sql函数,而且解决了一个之前面试头条时候的SQL问题!(也知道了为啥头条挂了…毕竟当时SQL做错了)

  • 前一篇关于SQL的推文(给链接)最后形成的表是这样的:
import pandas as pd
df = pd.read_excel('./sql数据处理与提取-窗口函数-0327.xlsx', sheet_name='interest0')
df
deviceid categoryinterest interests1 interests_news interests_score
0 65762973 军事:1.0,娱乐:0.917,军事/装备:0.916,娱乐/明星八卦:0.874,历史/古… 体育/冰雪运动:0.641 体育/冰雪运动 0.641
1 774830731 历史/古代史:1.0,历史:0.5 历史/古代史:1.0 历史/古代史 1.000
2 65762973 军事:1.0,娱乐:0.917,军事/装备:0.916,娱乐/明星八卦:0.874,历史/古… 人文/人文科普:0.584 人文/人文科普 0.584
3 124901984 情感/两性:1.0,历史/古代史:0.933,娱乐:0.901,情感:0.742,体育/乒乓… 体育/NBA:0.566 体育/NBA 0.566
4 874657455 体育/国际足球:9.063,体育/中国足球:3.019,娱乐/综艺:1.947,体育/NBA… 体育/乒乓球:0.207 体育/乒乓球 0.207

现在的需求是这样:

  • 计算每一个deviceid下interests_score前三以及对应的interests_news
  • 并且interests_news的前面的标签不能有“娱乐” “要闻” “社会”
  • 根据第二步拆开之后,如果后面为空,即没有联合标签,取前面的,非空则取后面的。

2 SQL牛逼函数走起来

2.1 Step1

  • Step1:使用pslit函数根据反斜杠将interests_news进行切分为tag_1和tag_2

2.2 SQL实现1

(select
		deviceid,interests_news,split(interests_news,'/')[0] as tag_1,split(interests_news,'/')[1] as tag_2, 
		interests_score
		from
		(select
			deviceid,categoryinterest,interests1,
			split(interests1, ':')[0] as interests_news,
			split(interests1, ':')[1] as interests_score
		from
			(select
			deviceid,categoryinterest,interests1
			from
			table1
			lateral view explode(split(categoryinterest,',')) tb1 as interests1
			where
			day=20190313
			group by
			deviceid,categoryinterest,interests1
			)t1
		)t2
		where
		interests_score>=0.5 and interests_score<=1 --获取高分值兴趣点
		group by
		deviceid,interests_news,split(interests_news,'/')[0] ,split(interests_news,'/')[1],interests_score
		-- 疑问 为啥没有直接用tag_1 tag_2 
	)interests0

2.3 Step2

Step2:均在select中实现!+ where限制

  • 并且舍弃tag_1为“娱乐” “要闻” “社会”三类的 where判断
  • 合并tag_1与tag_2 使用case when 如果tag_2非空则取tag_2 否则取tag_1
  • 使用row number函数 + partition by + order by 并且取排名前三 where

3.4 SQL实现2

select
	deviceid,
	interests_score,
	tag_type
--	collect_set(tag_type)as interests_array --行转纵
from
	(select 	
	deviceid,interests_news,interests_score,
	case when tag_2 is not null then tag_2 when tag_2 is null then tag_1 end as tag_type,--二级标签不为空取二级标签值,若二级为空,则取一级标签
	row_number() over(partition by deviceid order by interests_score desc) rank --对单个用户,按照分值降序
	-- 表示根据deviceid分组,在分组内部根据 interests_score 降序,而此函数计算的值就表示每个id内部根据score排序后的顺序编号记为rank(组内连续的唯一的)
	
	from
	(select
		deviceid,interests_news,split(interests_news,'/')[0] as tag_1,split(interests_news,'/')[1] as tag_2, 
		interests_score
		from
		(select
			deviceid,categoryinterest,interests1,
			split(interests1, ':')[0] as interests_news,
			split(interests1, ':')[1] as interests_score
		from
			(select
			deviceid,categoryinterest,interests1
			from
			portal.ddm_user_multiprod_user_portrait_d
			lateral view explode(split(categoryinterest,',')) tb1 as interests1
			where
			day=20190313
			group by
			deviceid,categoryinterest,interests1
			)t1
		)t2
		where
		interests_score>=0.5 and interests_score<=1 --获取高分值兴趣点
		group by
		deviceid,interests_news,split(interests_news,'/')[0] ,split(interests_news,'/')[1],interests_score
		-- 疑问 为啥没有直接用tag_1 tag_2 
	)interests0
	where
	tag_1 not in ('娱乐','社会','要闻','未知') --去除主流兴趣标签影响
	)interests00
	where
	rank<=4 --获取几类二级标签的组合 取top4
group by
	deviceid,
	interests_score,
	tag_type
  • 结果为:
df = pd.read_excel('./sql数据处理与提取-窗口函数-0327.xlsx', sheet_name='step3')
df
deviceid interests_news tag_type interests_score rank
0 65762973 体育/冰雪运动 冰雪运动 0.641 1
1 65762973 人文/人文科普 人文科普 0.584 2
2 774830731 历史/古代史 古代史 1.000 1
3 124901984 体育/NBA NBA 0.566 1
4 874657455 体育/乒乓球 乒乓球 0.207 1

总结:

  • row number()函数特别好用 往往配合 partition by 以及 order by
    参考:https://www.cnblogs.com/icebutterfly/archive/2009/08/05/1539657.html

  • 上述函数适用情况:当需要根据某一个标签进行分组并且在组内需要根据另一个字段进行降序or升序排列 打上排名 视情况取前几名 【下面还会举一个头条面试的例子】

  • case when函数语句:使用在select语句中,并且是

    case when *** then *** when *** then *** (else *** ) end as 新字段名称

2.5 补充-collect函数

作用:用来多行转为一行的方法 它返回一个消除了重复元素的对象集合, 其返回值类型是 array 。

SQL语句:

select deviceid, collect_set(tag_type) as interests_array 
from 上述表
  • 结果为:
deviceid interests_array
0 65762973 [“冰雪运动”, “人文科普”]
1 774830731 [“古代史”]
2 124901984 [“NBA”]
3 874657455 [“乒乓球”]

3 头条面试SQL题

3.1 题目

前段时间找实习去头条面试,最后被虐的体无完肤…其中面试官灵魂拷问:你SQL咋样?如果1-10分,你给自己的SQL打几分?我当时内心就无语了!?还有这种操作?嗨呀,SQL当时完全自学,当时没有实习,没有任何场景应用,所以水平明显很次,内心TMD脸上笑嘻嘻的说:6分,及格吧。面试官同样皮笑肉不笑的说,好,那我出个题哈,(面试官内心os:小样,那我就出个6分水平的题,看你答不答得出来!)

题目是这样的:
现在有一张表,有三列,一列是学生的姓名,一列是学生的雅思考试成绩,一列是考试时间,学生可以多次参加雅思考试,现在想要统计出不同学生最近一次考试时间的成绩!你说说思路吧!

3.2 实现

嗨呀,当时稍微想了一下,感觉不难嘛,于是脱口而出:

select name, score, max(time) from table group by name 

当时信心满满,但是回来和同学一聊,有说对的有说错的,尴尬!直到今天在公司遇到一个和这个很类似的问题啊!于是请教了我的leader,并且当场给我建了个临时表show了一把,结果是我错了,这种方法是不对的!

正确答案现在来看,肯定不难了:

  • 首先取出三列,然后使用row number函数 根据姓名进行partition by 然后对时间进行降序排列 取rank小于等于1的即可
select *, 
Row_Number() over (partition by name order by time desc) rank 
from IELTS
where rank <= 1

那当时的做法对不对呢?废话不多话,建一个表试试!

3.2 建表

在这里插入图片描述

结果为:

在这里插入图片描述
注:这次的代码刚在本机竟然没有跑通,后天去公司再请教下leader…不过逻辑肯定是没问题的~

未完待续


0716更新:后续请参考博客:SQL | 关于窗口函数的补充

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/139467.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 计算机主机网关的作用是什么?(转 )[通俗易懂]

    计算机主机网关的作用是什么?(转 )[通俗易懂]假设你的名字叫小不点,你住在一个大院子里,你的邻居有很多小伙伴,在门口传达室还有个看大门的李大爷,李大爷就是你的网关。当你想跟院子里的某个小伙伴玩,只要你在院子里大喊一声他的名字,他听到了就会回应你,

  • mysql 分区 扩充_mysql分区LVM扩容

    mysql 分区 扩充_mysql分区LVM扩容场景:mysql的数据库目录为/data随着数据量的增加,该分区不够用了,准备添加一块ssd硬盘,扩容该分区!下面是测试环境的整个过程:测试环境:在原有LVM卷标上添加一块50G的新硬盘对LVM进行扩容1、将添加的新硬盘进行分区:fdisk/dev/sdaWARNING:DOS-compatiblemodeisdeprecated.It’sstronglyrecommendedto…

  • 主流自动化运维工具支持的功能(运维自动化工具排行)

    主流的自动化运维工具有3种:Puppet、Saltstack和Ansible,用的最多的还是Ansible。Puppet:官网:www.puppetlabs.com,基于rubby开发,C/S架构,支持多平台,可管理配置文件、用户、cron任务、软件包、系统服务等。分为社区版(免费)和企业版(收费),企业版支持图形化配置。Saltstack:官网:https://saltsta…

  • Vue学习笔记之Es6转ES5的babel应用

    Vue学习笔记之Es6转ES5的babel应用1、由于目前ES6还不能很好的支持目前常见的浏览器,所以在打包的时候将ES6的代码转换为ES5,转换时可以通过babel进行转换;2、官网说明:3、环境配置,为了更好地匹配项目环境,我这边安装的是7的版本:cnpminstall–save-devbabel-loader@7babel-corebabel-preset-es2015可以使用options属性来给loader传递选项:4、重新编译后,发现编译后的js文件中,没有了ES6中的const,全部通过E

  • 程序员计算器在线计算_安卓程序员计算器

    程序员计算器在线计算_安卓程序员计算器在线标准程序员计算器在线标准程序员计算器本工具支持在线提供免费计算器工具,包括标准、科学及程序员计算工具https://tooltt.com/calc/

  • java日志级别(一般业务处理日志用什么级别)

    https://www.cnblogs.com/lingduqianli/p/7589173.html

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号