SQL轻松入门(5)窗口函数
01 前言
标题中有2个字让我在初次接触窗口函数时,真真切切明白了何谓"高级"?说来也是一番辛酸史!话说,我见识了窗口函数的强大后,便磨拳擦掌的要试验一番,结果在查询中输入语句,返回的结果却是报错,What?聪明的你,猜猜为啥?
害,原因可不就是MySQL的版本不匹配呗,我的原装是5.5,而窗口函数可运用的版本是MySQL8.0。经历了卸载重装,我对窗口函数的印象可是老深的啦!闲话到此,正题开始~
窗口函数的高级不仅体现在版本要求高,还体现在功能上的简洁易懂。以Excel中vlookup作类比,窗口函数≈vlookup,group by+order by +关联子查询≈index+match,虽不完全贴切,但能助你理解即可。
本文所涉及知识点,框架如下:
02 什么是窗口函数?
窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
基本语法: ‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)。语法中的‹窗口函数›主要由专用窗口函数(rank、dense_rank和row_number等组成)与聚合函数(sum、avg、count、max和min等)作为窗口函数组成。
从窗口函数组成上看,它是group by 和 order by的功能组合,既然我们已经学了group by和order by,为什么还要学窗口函数呢?group by分组汇总后改变了表的行数,一行只有一个类别,而partiition by则不会减少原表中的行数。
恰如窗口函数的组成,它同时具有分组和排序的功能,且不减少原表的行数。
03 窗口函数如何使用?
以截图问题为例:查看每个班级的排名情况
截图示例中使用了专有窗口函数rank,从整体执行效果来看,语句十分简洁。我们也可以理解:为什么要叫"窗口"函数?因为partition by分组后的结果被称为"窗口",这里的窗口不是指我们家里的门窗,而是表示"范围"的意思。
04 窗口函数分类和应用
从上例,我们可以清晰看到窗口函数的厉害之处,所以会在了解窗口函数组成部分的同时通过应用的讲解来加深理解。
排序函数rank、dense_rank和row_number的区别在于并列的排名表现。
rank函数:这个例子中是5位,5位,5位,8位,也就是如果有并列名次的行,会占用下一名次的位置。dense_rank函数:这个例子中是5位,5位,5位,6位,也就是如果有并列名次的行,不占用下一名次的位置。row_number函数:这个例子中是5位,6位,7位,8位,也就是不考虑并列名次的情况,和Excel中的row()函数一样,依据顺序排列下去。
topN问题:在日常工作中十分常用,以查找每个学生成绩最高的2个科目为例:
截图中红色标注是为了说明掌握sql语句运行顺序的重要性,能看懂报错十分重要,有时候很容易忽略语句的运行顺序,谨记!
topN的万能模板:
select * from (select *, row_number() over (partition by 要分组的列名 order by 要排序的列名 desc) as ranking from 表名) as a where ranking ‹= N;
如果是最小的话,只需变动order by的排序方式即可。
汇总分析版块中的常用聚合函数相信我们已掌握,那么在窗口函数中,它们区别于专用窗口函数的一个点是括号里需带有对应的列名。
上图以2列突出显示,可以观察到sum()数值不断累加,avg()也是,由此可以说明,窗口函数中可以看到1列的数值动态变化过程。
根据上面的观察,我们可以解决业务中的累计求和问题。
"累计求和"问题的万能模板是:
select 列1,列2, sum(列名) over (order by ‹用于排序的列名›) as 累计值的别名 from 表名;在开篇对比中,我有说到关联子查询,毫无疑问,接下来的问题和"每一组对比"相关,那么如何在每个组里比较?
通过上述2种方法,再一次验证了窗口函数的简洁高效,以后若是遇到每组比较的问题,要知道有2种解法,同时大脑中能反映出这2种语句,但执行可以选择窗口函数。
窗口函数除了能解决以上的应用外,还可以实现移动平均,或者移动求和等,以移动平均为例:
红色标注的就是移动平均的关键字,截图是包括本行再往上2行,也可以变成数字4,那么就是5行对比,重点可以观测到相邻数值之间的差异。
综上,窗口函数所包含的内容绝不仅是上面这些,随着我们在业务中的不断打磨,我们需要不断扩充自己的知识库,平时要多总结学习。
燕京啤酒二次创业发力,股价创历史新高文记者黄宇昆老牌酒企燕京啤酒(000729。SZ)股价近日创下历史新高。2月13日,燕京啤酒盘中最高触及13。58元,为公司上市二十多年以来的最高点。此前,燕京啤酒发布2022年度
湿气重,肝火旺,这几种方法可以祛湿降火,口干口臭没了湿气重和肝火旺是许多人都经历过的问题,它们可能会导致身体不适和影响健康。幸运的是,有几种方法可以帮助祛湿降火,并有助于消除口干和口臭的问题。首先,可以考虑饮食调养。饮食对于维护身体
保存香椿这么简单,教你3个方法,放1年照样新鲜,随吃随取特省事各位读者朋友们大家好,感谢阅读我分享的美食文章,经验和大家一起共享,今天我要和大家分享的内容是保存香椿这么简单,教你3个方法,放1年照样新鲜,随吃随取特省事!香椿是春天独有的一种美
唐末时期参谋的废罢与变迁问题考究一唐末参谋废立考究参谋是幕府中最不稳定的一个僚佐,在唐代几次被提出废罢。首先是在开元十二年(724),罢行军参谋,寻复置其次在三年后,亦即开元十五年(727)二月三日,敕诸军不得奏
石钟慈院士逝世2月14日,中国科学院数学与系统科学研究院发布讣告中国共产党优秀党员著名数学家中国计算数学事业的建设者和领导者之一中国科学院院士中国科学院数学与系统科学研究院研究员石钟慈先生,于2
又一个百亿级富豪去世,享年65岁!近日,A股上市公司杉杉股份发布讣告,公司创始人董事局主席郑永刚先生因为突发心脏病,经救治无效之后去世,享年65岁。郑永刚算是浙商里边比较有代表性的一个企业家,担任了上海市宁波商会会
李爱珍申请中科院院士被拒4次,爱国之心依旧没变全球工业4。0的核心是应用科技信息化技术对工业制造进行升级改革,从而进入工业智能化时代。科技新信息化技术离不开光电元器件传感器集成电路二极管三极管等,这些统称为半导体。半导体犹如工
西工大黄维当选美国工程院外籍院士中国青年报客户端讯(中青报中青网记者孙海华)日前,美国工程院(NationalAcademyofEngineering)公布了2023年新增院士名单,包括106名院士和18名外籍院
行业大咖何以为这座小城亮灯院士专家集聚津市助力县域经济高质量发展揭秘湖南日报全媒体记者姜鸿丽卓萌鲁融冰刘蓉2月10日至12日,来自全国各地的28名院士专家,百余名行业协会代表企业家投资与金融机构负责人,齐聚津市,参加创新引领县域经济高质量发展高峰论
亲子沟通有障碍?社区送上解压秘籍妈妈,我背着你就能绕过这些障碍了一位67岁的小朋友正努力地背着蒙着眼睛的妈妈,绕过椅子组成障碍。而背着的母亲只是象征性地靠在孩子的后背上,努力配合着。2月11日,在西华门的社区活动
喜报!卧龙山街道北海社区被评为全省最美志愿服务社区大众网海报新闻记者杨金鹏日照报道近日,2022年度全省学雷锋志愿服务四个100先进典型表彰名单公示完毕,日照市16个志愿服务先进典型上榜,山海天旅游度假区卧龙山街道北海社区榜上有名