SQL-窗口函数

这篇具有很好参考价值的文章主要介绍了SQL-窗口函数。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

什么是窗口函数

可以像聚合函数一样对一组数据进行分析并返回结果,二者的不同之处在于,窗口函数不是将一组数据汇总成单个结果,而是为每一行数据都返回一个结果。

SQL-窗口函数,数据库,运维

窗口函数组成部分

1.创建数据分区

窗口函数OVER子句中的PARTITION BY选项用于定义分区,其作用类似于查询语句中的GROUP BY子句。如果我们指定了分区选项,窗口函数将会分别针对每个分区单独进行分析。

1.另外开一列,求出每个部门的平均年龄

select *,avg(age) over(partition by dept) as 平均年龄 from testfunc order by id;

SQL-窗口函数,数据库,运维

解释:另外添加一列,用于记录以分组到每个部门的窗口中,以deft为窗口分区,计算出每个部门的平均年龄

2.每位学生的总成绩

select s_id,sum(convert(score,double))as 总成绩 from sc group by s_id;

SQL-窗口函数,数据库,运维

3.#以总成绩进行排名:窗口函数

dense_rank()是的排序数字是连续的、不间断。当有相同的分数时,它们的排名结果是并列的

select s_id,sum(convert(score,double))as 总成绩,

dense_rank() over(order by sum(convert(score,double))desc)as 排名

from sc group by s_id;

SQL-窗口函数,数据库,运维

解释:指定总成绩为窗口分区,并且总成绩降序排序。再接着dense_rank()再一次排序

4.#每科目下的总成绩进行排名

select c_id,sum(convert(score,double))as 总成绩,

dense_rank() over(partition by c_id order by sum(convert(score,double)) desc)as 排名

from sc group by c_id;

解释:分组到c_id窗口,以总成绩的降序排列,对c_id窗口分区进行对每一行匹配,并且再一次排序

#以平均分降序排列成绩信息:

select *,avg(convert(score,double)) over(partition by s_id)as 平均成绩 from sc order by 平均成绩 desc;

SQL-窗口函数,数据库,运维

#按总成绩进行降序排列

-- 若按学生总成绩进行降序排序

select *,sum(convert(score,double)) over(partition by s_id) as 总成绩 from sc order by 总成绩 desc;

-- 若按科目的总成绩进行排序

select *,sum(convert(score,double)) over(partition by c_id) as 总成绩 from sc order by 总成绩 desc;

SQL-窗口函数,数据库,运维

5.-- 求每个访客每个月访问次数,和累计访问次数

select *from visitor;

select userId,month(visitDate)as 月,sum(visitCount)as 月访问次数 from visitor group by userId,月;

SQL-窗口函数,数据库,运维

-- 月累计访问次数,月累计:sum(sum(visitCount))

select userId,month(visitDate)as 月,sum(visitCount)月访问次数,sum(sum(visitCount))over(partition by userId order by month(visitDate))as 该客户月累计次数

from visitor group by use9999rId,月 order by userId;

SQL-窗口函数,数据库,运维

6.-- 尝试不使用窗口函数得到并列形式排名(1,2,2,4...)

select a.name ,a.subject ,max(a.score) 主成绩 ,count(b.name)+1 行统计值【排名】

from score a left join score b on a.subject =b.subject and b.score >a.score

group by a.name, a.subject order by a.subject ,主成绩 desc;

select * from books_goods;

SQL-窗口函数,数据库,运维

7.-- 对同个类别【t_categor】的价格进行降序排序,并给与排名值(但是row_number()不会跳过重复序号)

select row_number() over(partition by t_category order by t_price desc)as 排名,t_category,t_name,t_price,t_upper_time

from books_goods;

SQL-窗口函数,数据库,运维

8.rank() 序号函数

能够对序号进行并列排序,并且会跳过重复的序号,得到并列排名 --- 效果与 excel 中 rank.eq()类似

select rank() over(partition by t_category order by t_price desc)as 排名,t_category,t_name,t_price,t_upper_time

from books_goods;

SQL-窗口函数,数据库,运维

dense_rank() 函数

DENSE_RANK()函数对序号进行并列排序,并且不会跳过重复的序号,比如序号为1、1、2。

select rank() over(partition by t_category order by t_price desc)as 排名,t_category,t_name,t_price,t_upper_time

from books_goods;

SQL-窗口函数,数据库,运维

9.percent_rank() 分布函数

于计算分区或结果集中行的百分位数

percent_rank() 返回一个从0到1的数字

对于指定的行, percent_rank()计算行的等级减1,除以评估的分区或查询结果集中的行数减一

select percent_rank() over(partition by t_category order by t_price desc) as 排名百分位,

rank() over(partition by t_category order by t_price desc) as 排名,

t_category,t_name, t_price,t_upper_time

from books_goods;

SQL-窗口函数,数据库,运维

即:当前的排名-1/当前的行量-1;

10.cume_dist() 分布函数

主用于查询小于或等于某个值的比例

-- 比如统计大于等于当前售价的产品数占总产品数的比例,其窗口函数中的排序为降序即可

select cume_dist() over(order by t_price desc) as 占比,

t_category,t_name, t_price,t_upper_time

from books_goods;

SQL-窗口函数,数据库,运维

- 比如统计小于等于当前售价的产品数占总产品数的比例

select cume_dist() over(order by t_price asc) as 占比,

t_category,t_name, t_price,t_upper_time

from books_goods;

SQL-窗口函数,数据库,运维

前后函数:lag(expr,n)/lead(expr,n)

11.现想查看统一组别中的价格差值

- 2、计算当前价格与上一个价格之间的差值

select *,t_price-pre_price as 差值 from(

#1、得到当前商品的前一个商品价格(价格先按低的排序)

select t_category_id t_category,t_name, t_price,

lag(t_price,1) over(partition by t_category order by t_price asc) as pre_price

from books_goods

) t

SQL-窗口函数,数据库,运维

把over 后的窗口分组排序方式语句单独提出来,设置别名:w 【名字可自取】,同时将其可应用于多个窗口函数上

想要输出分组后的前一个价格和后一个价格

select t_category_id t_category,t_name, t_price,

lag(t_price,1) over h as pre_price ,

lead(t_price,1) over h as last_price

from books_goods

window h as (partition by t_category order by t_price asc);

SQL-窗口函数,数据库,运维

12.首尾函数FIRST_VALUE(expr)/LAST_VALUE(expr)

头尾函数应用于:返回第一个或最后一个expr的值;

应用场景:截止到当前,按照日期排序查询当前最大的月收入【LAST_VALUE】 或最小月收入值【FIRST_VALUE】是多少

比如:按价格排序,查询每个类目中最低和最高的价格是多少,方便与后续计算当前书籍的价格与最大价格 或最小价格的差值(但是没有分组来返回值)

select t_category_id t_category,t_name, t_price,

first_value(t_price) over h as 最小价格 ,

last_value(t_price) over h as 最大价格

from books_goods

window h as (partition by t_category order by t_price asc );

SQL-窗口函数,数据库,运维

但结果发现:last_value 的结果并没有按照我们所想的以当前分组的窗口表中的所有数据进行判断最大值的

原因:last_value默认统计范围是取当前行数据 与 当前行之前的数据做比较的

解决方案:over 中的排序 order by 条件后加上一个固定语句:rows between unbounded preceding and unbounded following ,也是前面无界 和 后面无界 之间的行比较

select t_category_id t_category,t_name, t_price,

first_value(t_price) over h as 最小价格 ,

last_value(t_price) over h as 最大价格

from books_goods

window h as (partition by t_category order by t_price asc rows between unbounded preceding and unbounded following);

SQL-窗口函数,数据库,运维

13.请利用窗口函数找出每门学科的前三名【并列且连续的排名效果】

select t.* from(

select name,subject,score,dense_rank() over(partition by subject order by score desc) as 排名 from score

) t where t.排名 3;

SQL-窗口函数,数据库,运维文章来源地址https://www.toymoban.com/news/detail-803658.html

到了这里,关于SQL-窗口函数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • [运维|数据库] docker postgresql数据库环境变量配置

    要配置Docker中的PostgreSQL数据库的环境变量,可以使用以下方法: 使用Docker命令行: 将 用户名 , 密码 , 数据库名 替换为你想要设置的实际值。这将创建一个名为 mypostgres 的容器,并将 PostgreSQL 的用户名、密码和数据库名设置为指定的值。 -p 5432:5432 指定了容器内部和主机之间

    2024年02月09日
    浏览(65)
  • 数据库运维——备份恢复

    数据库备份,数据库为school,素材如下 1.创建student和score表 CREATE  TABLE  student ( id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  , name  VARCHAR(20)  NOT NULL , sex  VARCHAR(4)  , birth  YEAR, department  VARCHAR(20) , address  VARCHAR(50)  ); 创建score表。SQL代码如下: CREATE  TABLE  score ( id  INT(10)  NOT NU

    2024年02月16日
    浏览(66)
  • MySQL数据库 --- 运维篇

    错误日志是 MySQL 中最重要的日志之一,它记录了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时,建议首先查看此日志。 该日志是默认开启的,默认存放目录 /var/log/,默认的日志文件名为 mysqld

    2024年02月08日
    浏览(103)
  • Oracle运维(数据库、监听、重启)

    shutdown有四个参数,四个参数的含义如下: Normal 需要等待所有的用户断开连接 Immediate 等待用户完成当前的语句 Transactional 等待用户完成当前的事务 Abort 不做任何等待,直接关闭数据库 normal需要在所有连接用户断开后才执行关闭数据库任务,所以有的时候看起来好象命令没

    2024年02月06日
    浏览(72)
  • 【openGauss数据库】--运维指南03--数据导出

    👈【上一篇】 💖The Begin💖 点点关注,收藏不迷路💖 【下一篇】👉 🔰 1.1 概述 openGauss提供的 gs_dump 和 gs_dumpall 工具,能够帮助用户导出需要的数据库对象或其相关信息。通过导入工具将导出的数据信息导入至需要的数据库,可以完成数据库信息的迁移。 gs_dump 支持 导出

    2024年02月12日
    浏览(61)
  • 关于运维·关于数据库面试题

    目录 一、数据库类型 二、数据库引擎 三、mysql数据库类型 四、mysql的约束添加 五、主从复制原理 六、主从方式有几种 七、mysql主从数据不一致的原因 八、mysql的优化 九、什么是事务的特征 十、数据库读写分离的好处 十一、怎样优化sql语句 十二、mysql的同步方式 十三、m

    2024年01月21日
    浏览(54)
  • 【 openGauss数据库】--运维指南01

    👈【上一篇】 💖The Begin💖 点点关注,收藏不迷路💖 【下一篇】👉 该篇详细介绍了openGauss数据库常用的运维操作指导,方便更好地使用和管理openGauss数据库。 🔰 1.1 启停openGauss 🔰 1.2 查看openGauss数据库状态 🔰 2.1 检查实例状态 🔰 2.2 检查锁信息 锁机制是数据库保证数

    2024年02月12日
    浏览(65)
  • 【运维工程师学习五】数据库之MariaDB

    db根据存储模型划分,数据库类型主要可分为: 网状数据库(Network Database)、关系数据库(Relational Database)、树状数据库(Hierarchical Database)、面向对象数据库(Object-oriented Database) 等。商业应用中主要是关系数据库,而关系数据库中常见的软件有比如Oracle、DB2、Sybase、MS SQL Server、In

    2024年02月16日
    浏览(48)
  • 运维笔记之centos7安装mysql数据库

    如果使用的4.1以上版本的rpm的话,除了import mysql的公钥到个人用户的配置中,还需要import mysql的公钥到RPM的配置中

    2024年02月04日
    浏览(66)
  • 开源数据库MYSQL DBA运维实战 第一章 部署

    数据库管理系统  DBMS    SQL(RDS关系型数据库) DBA  工程师 DDL  数据库定义语言:数据库,表,视图,索引,存储过程,函数 DML  数据库操纵语言:插入数据insert,删除数据delete,更新数据update DQL  数据库查询语言:查询数据selete DCL  数据库控制语言:例如控制用户的访

    2024年02月21日
    浏览(59)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包