玩转数据:理解与应用SQL中的窗口函数
最编程
2024-07-23 08:05:06
...
碎语
- 学习的正态曲线:入门容易,精通难
- 积累的正态曲线:先越读越多,后越读越少
什么是开窗函数
很多场景比如排序,累计求和等,如果没有开窗函数,那么就要使用很复杂的子查询或是存储过程才能做到。为了解决这些问题,就有了现在的开窗函数,MySQL是从8.0版本之后才有了开窗函数,如果要使用,那么必须下载MySQL8.0以上的版本
开窗函数主要是为了给行定义一个窗口,可以理解就是一个分组,但是和group by 的区别在于它不是返回一个聚合值,而是每一行都能返回一个值。举个例子,咱们要求每个月销量和当年总销量以及每个月的销量占比,这时候用sum() 和开窗就很容易了
数据参考:数据分析师经常遇到的SQL场景解析
select
sal_year,
sal_month,
sum_sale,#销量
sum(sum_sale) over(partition by sal_year) as cumu_sal,##年总销量
sum_sale/(sum(sum_sale) over(partition by sal_year)) as ratio ##每个月销量占全年销量的占比
from
(select
year(date(order_date) )as sal_year,
month(date(order_Date) )as sal_month,
sum(sales) as sum_sale
from
chaoshi.order
group by
year(date(order_date) ),
month(date(order_Date) )
)a
order by
sal_year,
sal_month;
开窗结构
函数+开窗函数:row_number() over()
row_number() over(partition by xx order by yy rows between zz and aa)
- partition by:分组,顾名思义就是以什么字段进行分组,形式跟group by 一样
- order by : 排序,对分完组后的数据,进行组内的排序
- rows between :窗口,计算的窗口,between后可以跟如下的内容:
- unbounded preceding:第一行
- unbounded following:最后一行
- current row:当前行
- N preceding:前N行
- N following:后N行
- 一般省略了rows的时候默认都是从开窗后的第一行到当前行,后面的具体例子会讲解
- row_number() 就是一个函数,开窗一般都是与排序和聚合函数一起使用
函数
排名开窗函数
- row_number ():排序之后不管有没有重复值都是一直往上再加序号
- dense_rank():排序之后遇到重复值会生成一样的序号,但是接下来的序号连续
- rank():排序之后遇到重复值会生成一样的序号,接下来的序号不连续,具体如下栗子
栗子
with test as (##创建了一个临时表
select
1 as num
from
dual
union all
select
2 as num
from
dual
union all
select
2 as num
from
dual
union all
select
3 as num
from
dual
union all
select
4 as num
from
dual
)
select
num ,
row_number() over(order by num ) as row_number1,
rank() over(order by num) as rank1,
dense_rank() over(order by num) as dense_rank1
from
test ##上面的临时表test
结果
聚合开窗函数
with test as (##创建了一个临时表
select
1 as num
from
dual
union all
select
2 as num
from
dual
union all
select
2 as num
from
dual
union all
select
3 as num
from
dual
union all
select
4 as num
from
dual
)
select
num ,
row_number() over(order by num ) as row_number1,
rank() over(order by num) as rank1,
dense_rank() over(order by num) as dense_rank1
from
test ##上面的临时表test
可以和很多聚合函数一起使用,如:sum()/count()/min()/max()
结束语
如果我不能让您看懂,那是我的问题,如果有疑问可以关注我,然后私聊我,我会尽最大的努力帮助你。如果觉得对你有帮助,请帮忙点赞/关注,谢谢!
下一篇: 使用Hive的窗口函数操作指南
推荐阅读
-
深入理解JSP第四部分:EL表达式的探索与应用,包括各类数据的获取、内置对象的使用、运算功能实现、数据回显、自定义函数和fn方法库的应用
-
理解Oracle的listagg函数与Mysql的group_concat在实际应用中的差异与相似之处
-
转换你的数据:深入理解 SQL Server 中的 CONVERT 函数
-
SQL Server 日期转换方法大全:支持各种数据类型和格式样式的转换 说明: 本篇文章详细介绍了如何在SQL Server中进行日期转换,包括各种数据类型和格式样式的转换方法。其中包括了科威特算法的阿拉伯样式中的数据格式,并提供了多种样式可供选择。此外,还给出了详细的示例和注意事项,帮助读者更好地理解和应用这些转换方法。
-
【摩尔线程+Colossal-AI强强联手】MusaBert登上CLUE榜单TOP10:技术细节揭秘 - 技术实力:摩尔线程凭借"软硬兼备"的技术底蕴,让MusaBert得以从底层优化到顶层。其内置多功能GPU配备AI加速和并行计算模块,提供了全面的AI与科学计算支持,为AI推理和低资源条件下的大模型训练等场景带来了高效、经济且环保的算力。 - 算法层面亮点:依托Colossal-AI AI大模型开发系统,MusaBert在训练过程中展现出了卓越的并行性能与易用性,特别在预处理阶段对DataLoader进行了优化,适应低资源环境高效处理海量数据。同时,通过精细的建模优化、领域内数据增强以及Adan优化器等手段,挖掘和展示了预训练语言模型出色的语义理解潜力。基于MusaBert,摩尔线程自主研发的MusaSim通过对比学习方法微调,结合百万对标注数据,MusaSim在多个任务如语义相似度、意图识别和情绪分析中均表现出色。 - 数据资源丰富:MusaBert除了自家高质量语义相似数据外,还融合了悟道开源200GB数据、CLUE社区80GB数据,以及浪潮公司提供的1TB高质量数据,保证模型即便在较小规模下仍具备良好性能。 当前,MusaBert已成功应用于摩尔线程的智能客服与数字人项目,并广泛服务于语义相似度、情绪识别、阅读理解与声韵识别等领域。为了降低大模型开发和应用难度,MusaBert及其相关高质量模型代码已在Colossal-AI仓库开源,可快速训练优质中文BERT模型。同时,通过摩尔线程与潞晨科技的深度合作,仅需一张多功能GPU单卡便能高效训练MusaBert或更大规模的GPT2模型,显著降低预训练成本,进一步推动双方在低资源大模型训练领域的共享目标。 MusaBert荣登CLUE榜单TOP10,象征着摩尔线程与潞晨科技联合研发团队在中文预训练研究领域的领先地位。展望未来,双方将携手探索更大规模的自然语言模型研究,充分运用上游数据资源,产出更为强大的模型并开源。持续强化在摩尔线程多功能GPU上的大模型训练能力,特别是在消费级显卡等低资源环境下,致力于降低使用大模型训练的门槛与成本,推动人工智能更加普惠。而潞晨科技作为重要合作伙伴,将继续发挥关键作用。
-
理解与应用:Python中的isnumeric()函数
-
理解与应用:Python中的hasattr函数
-
理解与应用:htonl、htons、ntohl、ntobs: 大小端数据转换的实用函数
-
玩转Python:深入理解与应用基础的进制转换函数
-
在ABAP中:转化函数的简易理解与应用