SQL Server中聚合函数的用法(2)-木庄网络博客

此示例使用STRING_AGG()函数生成城市客户的电子邮件列表：

SELECT  city,   STRING_AGG(email,';') email_list FROM sales.customers GROUP BY city;

执行上面查询语句，得到以下结果：

要对email列表进行排序，请使用WITHIN GROUP子句：

SELECT city, STRING_AGG(email,';') WITHIN GROUP (ORDER BY email) email_list FROM  sales.customers GROUP BY city;

执行上面查询语句，得到以下结果：

注意：STRING_SPLIT（）函数：一个表值函数，它根据指定的分隔符将字符串拆分为子字符串行。

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

10、approx_count_distinct：唯一非空值的近似数

SQL Server 2019引入了新函数Approx_Count_distinct以提供行的近似计数。Count（distinct（））函数提供实际的行数。

该函数APPROX_COUNT_DISTINCT应该使用较少的内存和CPU资源，以便可以获取数据结果而不会出现任何问题，例如溢出到磁盘或CPU峰值。这对于数十亿行的需求很有用。

11、cube或 rollup 汇总运算符

CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

查询出插入的全部数据：

select * from dbo.PeopleInfo

select [name],numb,sum(fenshu) from dbo.PeopleInfo group by [name],numb             //用group by 
select [name],numb,sum(fenshu) from dbo.PeopleInfo group by [name],numb with cube;  //用with cube
select [name],numb,sum(fenshu) from dbo.PeopleInfo group by [name],numb with rollup //用with rollup

12、grouping：指示是否聚合GROUP BY 列：

当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。

仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相关联的选择列表中才允许分组。

select [name],numb,grouping(numb) from dbo.PeopleInfo group by [name],numb with rollup

13、grouping_id：计算分组级别

仅当指定了 GROUP BY 时，GROUPING_ID 才能在 SELECT 列表、HAVING 或 ORDER BY 子句中使用。使用 GROUPING_ID 标识分组级别下面的示例返回按 AdventureWorks2012 数据库的 Name 和 Title 汇总的雇员计数以及 Name, 和公司总计。 GROUPING_ID() 用于为 Title 列中的每行创建一个值以标识聚合级别。

SELECT D.Name  
    ,CASE   
    WHEN GROUPING_ID(D.Name, E.JobTitle) = 0 THEN E.JobTitle  
    WHEN GROUPING_ID(D.Name, E.JobTitle) = 1 THEN N'Total: ' + D.Name   
    WHEN GROUPING_ID(D.Name, E.JobTitle) = 3 THEN N'Company Total:'  
        ELSE N'Unknown'  
    END AS N'Job Title'  
    ,COUNT(E.BusinessEntityID) AS N'Employee Count'  
FROM HumanResources.Employee E  
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH  
        ON E.BusinessEntityID = DH.BusinessEntityID  
    INNER JOIN HumanResources.Department D  
        ON D.DepartmentID = DH.DepartmentID       
WHERE DH.EndDate IS NULL  
    AND D.DepartmentID IN (12,14)  
GROUP BY ROLLUP(D.Name, E.JobTitle);

14、partition by :聚合开窗函数

很多聚合函数都可以用作窗口函数的运算，如SUM,AVG,MAX,MIN。聚合开窗函数只能使用PARTITION BY子句或都不带任何语句，ORDER BY不能与聚合开窗函数一同使用。例如，查询雇员的定单总数及定单信息。

WITH OrderInfo AS
(
    SELECT COUNT(OrderID) OVER(PARTITION BY EmployeeID) AS TotalCount,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
)
SELECT OrderID,CustomerID, EmployeeID ,OrderDate,TotalCount From OrderInfo ORDER BY EmployeeID

如果窗口函数不使用PARTITION BY 语句的话，那么就是不对数据进行分组，聚合函数计算所有的行的值。

WITH OrderInfo AS
(
  SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
)

到此这篇关于SQL Server聚合函数的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持。

标签：SQL

返回前面的内容

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » SQL Server中聚合函数的用法

SQL Server中聚合函数的用法