MS SQL Server partition by 函数实战 统计与输出

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

需求

假设有一课程项目,我们需要统计该项目中的课件数量,并提取课程信息,如课程标题名称、排序号等,如果使用 GROUP BY 聚合函数,则只能统计返回课件项目及对应的课件数量一条记录,无法显示明细信息,对于终端想要进行输出的话,此时 partition by 就派上用场了。


输出如下图:

在管理心理学项目里包括若干课程,我们将根据排序号进行分类输出,显示课程的明细内容并继续其它业务操作。


范例运行环境

操作系统: Windows Server 2019 DataCenter


数据库:Microsoft SQL Server 2016


表及视图样本设计

主表 [ms_project_ep] 项目课程明细表设计如下:


字段名 类型 说明 备注

1

cid

uniqueidentifier

唯一标识


2

project_cid uniqueidentifier 所属项目ID 对应项目表

3

lession_cid tinyint 所属课程ID 对应课程表

4

sortid int
排序号

总排序号

查询分析器结果数据显示如下图: 如图我们看到查询结果按项目ID进行分区,并统计课程数,


如项目ID:7CF46B88-0B4D-49A0-A0D8-08B9AF064AC5,包含了1个课程;


项目ID:EE8AFC3F-5E82-46FA-B81E-10F7F06F61C8,包含了122个课程;


并且每一个分区提取排序号,从1开始。


SQL 语句如下:

select project_cid,lession_cid,
count(lession_cid) over (partition by project_cid order by project_cid) as lcount,
sortid 
from ms_project_ep 
order by project_cid,sortid

关键说明见下表:

序号 关键语句 说明

1

count(lession_cid) over (partition by project_cid order by project_cid)

as lcount

partition by project_cid order by project_cid,按项目ID分区并排序;

使用count函数统计课程数;

小结

partition by 的聚合统计和使用还有很多种,如下表:

序号

统计项

说明

1

row_number() 记录总排序号

2

rank() 排序,有并列则按总数递增,如两个第1后是第3

3

dense_rank() 排序,有并列则按上一数值递增,如两个第1后是第2

4

count(字段名) 求个数

5

max(字段名) 求最大值

6

min(这段名) 求最小值

7

sum(字段名) 求和

8

avg(字段名)

求平均值

9

first_value(字段名) 求第一个值

10

last_value(字段名)

求最后一个值

11

lag(字段名,[行数]) 取指定列,将分区列的数据后错n行,行数不是必选项,默认为0,即不错行

12

lead(字段名,[行数])
取指定列,将分区列的数据前错n行,行数不是必选项,默认为0,即不错行
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2月前
|
SQL 数据库 UED
SQL性能提升秘籍:5步优化法与10个实战案例
在数据库管理和应用开发中,SQL查询的性能优化至关重要。高效的SQL查询不仅可以提高应用的响应速度,还能降低服务器负载,提升用户体验。本文将分享SQL优化的五大步骤和十个实战案例,帮助构建高效、稳定的数据库应用。
72 3
|
2月前
|
SQL
开启慢SQL设置long_query_time=0.1为啥会统计的sql却存在小于100毫秒的sql
开启慢SQL设置long_query_time=0.1为啥会统计的sql却存在小于100毫秒的sql
35 1
|
2月前
|
SQL 缓存 监控
SQL性能提升指南:五大优化策略与十个实战案例
在数据库性能优化的世界里,SQL优化是提升查询效率的关键。一个高效的SQL查询可以显著减少数据库的负载,提高应用响应速度,甚至影响整个系统的稳定性和扩展性。本文将介绍SQL优化的五大步骤,并结合十个实战案例,为你提供一份详尽的性能提升指南。
55 0
|
3月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
3月前
|
SQL 数据库 数据库管理
数据库SQL函数应用技巧与方法
在数据库管理中,SQL函数是处理和分析数据的强大工具
|
3月前
|
SQL 数据库 索引
SQL中COUNT函数结合条件使用的技巧与方法
在SQL查询中,COUNT函数是一个非常常用的聚合函数,用于计算表中满足特定条件的记录数
|
3月前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
52 0
|
3月前
|
SQL 关系型数据库 MySQL
SQL日期函数
SQL日期函数
|
存储 SQL 程序员
【Sql Server】存储过程通过作业定时执行按天统计记录
通过前两篇文章的学习,我们已经对创建表、存储过程、作业等功能点有所了解 本次将结合前面所学习的知识点,创建统计表以及结合作业定时按天以及实时统计域名各个长度的记录值
411 0
【Sql Server】存储过程通过作业定时执行按天统计记录
|
存储 SQL 数据库
SQL Server——为什么要使用存储过程?不使用是什么样的?
提高数据库执行速度,可能第一次见到这句话的小伙伴们感觉到非常的匪夷所思叭!怎么就提高了它的执行速度捏,从哪方面可以表现出来呢?既然这里要说到的是为什么要使用存储过程,也就是说它的优点是什么。那我们肯定就要对使用和不使用存储过程两方面来进行对比才能看出它的优点对吧。