sql的聚集函数

1. 第十三章 sql聚合函数 XMLAGG

一个聚合函数，它创建一个串接的值字符串。

XMLAGG 聚合函数返回由 string-expr 中的所有值组成的串接字符串。
返回值的数据类型为 VARCHAR ，默认长度为 4096 。

以下两个示例都返回相同的单个值，即 Sample.Person 表的 Home_State 列中列出的所有值的串联字符串。

请注意，此连接字符串包含重复值。

返回值中将省略 string-expr 为NULL的行。如果至少返回一个非空字符串值，则从返回值中省略 string-expr 为空字符串( '' )的行。如果唯一非空的 string-expr 值是空字符串( '' )，则返回值是单个空字符串。

XMLAGG 不支持数据流字段。为 string-expr 指定流字段会导致 SQLCODE-37 。

XMLAGG 的一个常见用法是标记列中的每个数据项。这是通过组合 XMLAGG 和 XMLELEMENT 来实现的，如下例所示：

这将产生如下所示的输出字符串：

XMLAGG 函数将来自多行的表列的值连接到单个字符串中。因为在计算所有聚合字段之后，将逐个从句应用于查询结果集，所以逐个不能直接影响该字符串中的值序列。在某些情况下， XMLAGG 结果可能会按顺序显示，但不应依赖此排序。给定聚合结果值中列出的值无法显式排序。

下面的示例创建在 Sample.Person 表的 FavoriteColors 列中找到的所有不同值的串联字符串。因含颂此，对于 ALL_COLLES 列，每行都有相同的值。请注意，虽然有些行的 FavoriteColors 为空值，但该值不包括在连接的字符串中。数据值以内部格式返回。

下面的示例返回以 “A” 开头的 Home_State 值的串联字顷旅符串。它返回不同的 Home_State 值(不同的 Home_State )；与不同的 Home_City 值对应的 Home_State 值(与( Home_City ) Home_State 不同)，其中可能包括 Home_City 的一个唯一NULL；以及所有 Home_State 值：

下面的示例为每个州创建在 Home_City 列中找到的所有不同值的串联字符串。同一州的每一行都包含该州所有不同城市值的列表：

以下示例使用 %AFTERHAVING 关键字。它为雀老凳每个包含至少一个满足 HAVING 子句条件(以 “C” 或 “K” 开头的名称)的 Name 值的 Home_State 返回一行。第一个 XMLAGG 函数返回由该州的所有名称组成的连接字符串。第二个 XMLAGG 函数返回仅由满足 HAVING 子句条件的名称组成的连接字符串：

对于以下示例，假设我们有下表 AutoClub ：

2. sql聚合函数有哪些

聚集函数
和大多数其它关系数据库产品一样，PostgreSQL
支持聚集函数。一个聚集函数从多个输入行中计算出一个结果。比如，我们有在一个行集合上计算
count(数目),
sum(总和),
avg(均值),
max(最大值),
min(最小值)的函数。
比如，我们可以用下面的语句找出所有低温中的最高温度：
SELECT
max(temp_lo)
FROM
weather;
max
-----
46
(1
row)如果我们想知道该读数发生在哪个城市，可能会用：
SELECT
city
FROM
weather
WHERE
temp_lo
=
max(temp_lo);
--
错！不过这个方法不能运转，因为聚集函数
max
不能用于
WHERE
子句中。存在这个限制是因为
WHERE
子句决定哪些行可以进入聚集阶段；因此它必需在聚集函数之前计算。不过，我们可以用其它方法实现这个目的；这里我们使用子查询：
SELECT
city
FROM
weather
WHERE
temp_lo
=
(SELECT
max(temp_lo)
FROM
weather);
city
---------------
San
Francisco
(1
row)这样做是可以的，因为子查询是一次独立的计算，它独立于外层查询计算自己的聚集。
聚集同样也常用于
GROUP
BY
子句。比如，我们可以获取每个城市低温的最高值：
SELECT
city,
max(temp_lo)
FROM
weather
GROUP
BY
city;
city
|
max
---------------+-----
Hayward
|
37
San
Francisco
|
46
(2
rows)这样每个城市一个输出。每个聚集结果都是在匹配该城市的行上面计算的。我们可以用
HAVING
过滤这些分组：
SELECT
city,
max(temp_lo)
FROM
weather
GROUP
BY
city
HAVING
max(temp_lo)
<
40;
city
|
max
---------+-----
Hayward
|
37
(1
row)这样就只给出那些
temp_lo
值曾经有低于
40
度的城市。最后，如果我们只关心那些名字以"S"开头的城市，我们可以用：
SELECT
city,
max(temp_lo)
FROM
weather
WHERE
city
LIKE
'S%'
GROUP
BY
city
HAVING
max(temp_lo)
<
40;语句中的
LIKE
执行模式匹配，在节9.7里有解释。
理解聚集和
SQL
的
WHERE
和
HAVING
子句之间的关系非常重要。WHERE
和
HAVING
的基本区别如下：WHERE
在分组和聚集计算之前选取输入行(它控制哪些行进入聚集计算)，而
HAVING
在分组和聚集之后选取输出行。因此，WHERE
子句不能包含聚集函数；因为试图用聚集函数判断那些行将要输入给聚集运算是没有意义的。相反，HAVING
子句总是包含聚集函数。当然，你可以写不使用聚集的
HAVING
子句，但这样做没什么好处，因为同样的条件可以更有效地用于
WHERE
阶段。
在前面的例子里，我们可以在
WHERE
里应用城市名称限制，因为它不需要聚集。这样比在
HAVING
里增加限制更加高效，因为我们避免了为那些未通过
WHERE
检查的行进行分组和聚集计算。

3. 第六章 SQL聚合函数 JSON_ARRAYAGG

创建 JSON 格式值数组的聚合函数。

注：IRIS可用，IRIS之前版本不可用。

JSON_ARRAYAGG 聚合函数返回指定列中值的JSON格式数组。

一个简单的 JSON_ARRAYAGG (或 JSON_ARRAYAGG ALL )返回一个 JSON 数组，其中包含所选行中 string-expr 的所有值。
字符串 -expr 为空字符串 (") 的行由数组中的( " u0000" )表示。
字符串 -expr 为NULL的行不包含在数组中。
如果只有一个字符串 -expr 值，并且是空字符串( " )， JSON_ARRAYAGG 将返回 JSON 数组 ["u0000"] 。
如果所有的 string-expr 值为 NULL , JSON_ARRAYAGG 返回一个空的 JSON 数组 [] 。

JSON_ARRAYAGG DISTINCT 返回一个 JSON 数组，由所选行中 string-expr 的所有不同(唯一)值组成: JSON_ARRAYAGG(DISTINCT col1) 。
NULL 字符串 -expr 不包含在 JSON 数组中。
JSON_ARRAYAGG(DISTINCT BY(col2) col1) 返回一个 JSON 数组，该数组只包含记录中 col2 值是不同的(唯一的)的那些 col1 字段值。
但是请注意，不同的 col2 值可能包含一个单独的 NULL 值。

JSON_ARRAYAGG string-expr 不能是流字段。
指定流字段的结果是 SQLCODE -37 。

默认的 JSON_ARRAYAGG 返回类型是 VARCHAR(8192) 。
这个长度包括 JSON 数组格式化字符以及字段数据字符。
如果预期返回的值将需要大于 8192 ，可以使用 CAST 函数指定一个更大的返回值。
例如 CAST(JSON_ARRAYAGG(value)) AS VARCHAR(12000)) 。
如果实际返回的JSON数组长于 JSON_ARRAYAGG 返回类型长度，IRIS将在返回类型长度处截断JSON数组，而不会发出错误。
因为截断JSON数组会删除其关闭的 ] 字符，这使得返回值无效。

可以使用 %SelectMode 属性为 JSON 数组中的元素指定数据显示值: 0=Logical (默认值)， 1=ODBC , 2= display 。
如果 string-expr 包含一个 %List 结构，则元素以ODBC模式表示，用逗号分隔，在逻辑和显示模式中以 %List 格式字符表示，用 转义序列表示。

JSON_ARRAYAGG 函数将表中多行列的值组合成一个包含元素值的 JSON 数组。
因为在计算所有聚合字段之后，查询结果集中应用了一个 ORDER BY 子句，所以 ORDER BY 不能直接影响这个列表中的值序列。
在某些情况下， JSON_ARRAYAGG 结果可能是按顺序出现的，但是不应该依赖于这种顺序。
在给定聚合结果值中列出的值不能显式排序。

下面的嵌入式SQL示例返回一个主机变量，该变量包含示例的 Home_State 列中所有值的 JSON 数组。
以字母 A 开头的人名表:

注意，这个 JSON 数组包含重复的值。

下面的动态SQL示例返回一个主机变量，该变量包含样本的 Home_State 列中所有不同(唯一)值的JSON数组。
以字母 A 开头的人名表:

下面的SQL示例为每个州创建了一个 JSON 数组，其中包含在 Home_City 列中找到的所有值，以及按州列出的这些城市值的计数。
每个 Home_State 行包含该状态的所有 Home_City 值的 JSON 数组。
这些JSON数组可能包含重复的城市名称:

更有用的是一个 JSON 数组的所有不同的值，发现在 Home_City 列为每个州，如下所示的动态SQL示例:

注意，这个示例返回每个州的不同城市名称和总城市名称的整数计数。

下面的动态SQL示例使用 %SelectMode 属性为 DOB 日期字段返回的JSON值数组指定 ODBC 显示模式:

下面的动态SQL示例使用 %FOREACH 关键字。
它为每个不同的 Home_State 返回一行，其中包含该 Home_State 的年龄值的JSON数组。

下面的动态SQL示例使用 %AFTERHAVING 关键字。
它为每个 Home_State 返回一行，其中至少包含一个满足 HAVING 子句条件的 Name 值(以 “M” 开头的名称)。
第一个 JSON_ARRAYAGG 函数返回一个包含该状态所有名称的 JSON 数组。
第二个 JSON_ARRAYAGG 函数返回的 JSON 数组只包含满足 HAVING 子句条件的名称:

4. SQL中只要用到聚合函数就一定要用到group by 吗

SQL中只要用到聚合函数就不一定要用到group by。聚合函数是对一组值执行计算，并返回单个值，也被称为组函数。 聚合函数可以应用于SELECT 查询语句的 GROUP BY 子句的HAVING子句中，但不可用于WHERE语句中，因为WHERE是对逐条的行记录进行筛选。

(4)sql的聚集函数扩展阅读：

Transact-SQL编程语言提供的聚合函数有：

1、AVG：返回指定组中的平均值，空值被忽略。例：select prd_no,avg(qty) from sales group by prd_no.

2、COUNT：返回指定组中项目的数量。例：select count(prd_no) from sales.

3、COUNT_BIG：返回举圆指定组中的项目数量码念，与COUNT函数不同的是COUNT_BIG返回迟答困bigint值，而COUNT返回的是int值。例：select count_big(prd_no) from sales.

4、MIN：返回指定数据的最小值。例：select prd_no,min(qty) from sales group by prd_no.

5、MAX：返回指定数据的最大值。例：select prd_no,max(qty) from sales group by prd_no.

5. SQL Server中的聚合函数有哪些

SQL Server中的聚合函数有：

1.count() 所有记录数

2.count(*)所有非null记录数

3.avg() 某一列平均值

4.min() 某一列最小值

5.max() 某一列最大值

6.sum() 某一列总和

使用方法：

1. SELECT COUNT(字段1) FROM 表A 统计字段1在表A中出现的次数

2. SELECT 学号，SUM(各科成绩) FROM student GROUP BY 学号 用来根据统计学生的总成绩

6. 关于sql聚集函数的正确描述是

SQL允许定义函数、过程和方法。定义可通过SQL的有关过程的组件，也可以通过外部的程序设计语言，如Java，C或C++。SQL标准所定义的语法，与大多数数据库实现的自身的非标准版本的语法不同。函数和过程允许“业务逻辑”作为存储过程记录在数据库中，并在数据库中执行。

求和函数SUM( )用于对数据求和，返回选取结果集中所有值的总和。语法如下。

SELECT SUM(column_name)

FROM table_name

说明：SUM()函数只能作用于数值型数据，即列column_name中的数据必须是数值型的。

(6)sql的聚集函数扩展阅读：

理解聚集和SQL的 WHERE 以及 HAVING 子句之间的关系对我们非常重要。 WHERE 和 HAVING 的基本区别如下： WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。

相反，HAVING 子句总是包含聚集函数。 （严格说来，你可以写不使用聚集的 HAVING 子句， 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。）

7. 第十二章 SQL聚合函数 VARIANCE, VAR_SAMP, VAR_POP

返回数据集统计方差的聚合函数。

这些函数返回 NUMERIC 数据类型，除非表达式是数据类型 DOUBLE 。
如果 expression 为 DOUBLE ，则返回 DOUBLE 。

这三个方差聚合函数在丢弃 NULL 值后返回表达式值的统计方差。
也就是说，从数据集的平均值变化的量，表示为一个正数。
返回值越大，值的数据集的变化就越大。
SQL还提供聚合函数来返回对应于每个方差函数的标准偏差。

在这种统计差异的推导过程中有一些细微的差异:

方差计算为:

VAR_POP 的计算是:

这些方差聚合函数可以在引用表或视图的 SELECT 查询或唤皮子查询中使用。
它们可以在 SELECT 列表或 HAVING 子句中与普通字段值一起出现。

这些方差聚合函数不能在WHERE子句中使用。
它们不能在 JOIN 的 ON 子句中使用，除非 SELECT 是子查询。

这些方差聚合函数返回数据类型NUMERIC的值，精度为 36 ，刻度为 17 ，除非表达式是数据类型 DOUBLE ，在这种情况下，函数返回数据类型 DOUBLE 。

这些方差聚合函数通常应用于具有数值的字段或表达式。
它们和册差将非数值值(包括空字符串( " ))计算为零( 0 )。

这些方差聚合函数忽略数姿拍据字段中的 NULL 值。
如果查询没有返回行，或者返回的所有行的数据字段值为 NULL ，则返回 NULL 。

与所有聚合函数一样，统计方差函数可以采用一个可选的 DISTINCT 子句。
方差( DISTINCT col1 )返回那些不同(唯一)的 col1 字段值的方差。
方差( DISTINCT BY(col2) col1 )返回记录中col1字段值的方差，其中col2值是不同的(唯一的)。
但是请注意，不同的col2值可能包含一个单独的NULL值。

与所有聚合函数一样，方差函数总是返回数据的当前状态，包括未提交的更改，而不管当前事务的隔离级别如何。

下面的示例使用方差返回 sample.employee 中雇员年龄的方差，以及由一个或多个雇员表示的不同年龄的方差:

下面的示例使用 VAR_POP 返回 sample.employee 中雇员年龄的总体差异，以及由一个或多个雇员表示的不同年龄的差异:

8. sql 语句中那些是聚合函数

常见聚合函数，max（最大）、min（最小）、sum（求和）、avg（平均）等，

示例如下亮旦扒，

1、创建测试表，

create table test_group(id number, value number);

9. sql中常用的聚合函数有哪些

聚合函数是对一组值执行计算并返回单一的值的函数，它经常与SELECT语句的GROUP BY子句一同使茄睁用，SQL SERVER 中具体有哪些聚合函数呢？我们来一一看一下：
1. AVG 返回指定组中的平均值，空值被忽略。
例：select prd_no,avg(qty) from sales group by prd_no

2. COUNT 返回指定组中项目的数量。
例：select count(prd_no) from sales

3. MAX 返回指定数据的最大值。
例：select prd_no,max(qty) from sales group by prd_no

4. MIN 返回指定数据的最小值。
例：select prd_no,min(qty) from sales group by prd_no

5. SUM 返回指定数据的和，只能用于数字列，空值被忽略。
例：select prd_no,sum(qty) from sales group by prd_no

6. COUNT_BIG 返回指定组中的项目数量，与COUNT函数不同的是COUNT_BIG返回bigint值，而COUNT返回的是int值。
例：select count_big(prd_no) from sales

7. GROUPING 产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，输出值为1.当所添加的行不是由CUBE或ROLLUP产生时，输出值为0.
例：select prd_no,sum(qty),grouping(prd_no) from sales group by prd_no with rollup

8. BINARY_CHECKSUM 返回对表中的行或表达式列表计算的二进制校验值，用于检测表中行态纳派的更改。
例：select prd_no,binary_checksum(qty) from sales group by prd_no

9. CHECKSUM_AGG 返回指定数据的校验值，空值被忽略。
例：select prd_no,checksum_agg(binary_checksum(*)) from sales group by prd_no

10. CHECKSUM 返帆贺回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引。

11. STDEV 返回给定表达式中所有值的统计标准偏差。
例：select stdev(prd_no) from sales

12. STDEVP 返回给定表达式中的所有值的填充统计标准偏差。
例：select stdevp(prd_no) from sales

13. VAR 返回给定表达式中所有值的统计方差。
例：select var(prd_no) from sales

14. VARP 返回给定表达式中所有值的填充的统计方差。
例：select varp(prd_no) from sales

10. sql语句中如何将聚合函数值和另一个值相乘

sql中提供的聚合函数，通常包括sum,count,avg,max,min，但不包括乘法；
比如有这样一个数据集：
id x
1 1
1 2
1 3
2 4
2 5
2 6
;
想实现成如下形式
id prob_x
1 6
2 120
即让第一组中1*2*3=6
第二组中4*5*6=120
用data步中first+retain可以实现，但data需要先排序；
而sql的聚合函数中也没有相乘这一函数，于是有些牛人想到通过数学转化，将乘法镇凳拍转为加法，而sql中可以
用sum实现加法聚合。
说来也简单，主要想法就是A*B=exp(lnA*B)=exp(lnA+lnB);
select id,exp(sum(log(x))) as prob_x from data_set group by id;
=============================================
但我觉得也需要补充一点东西：
首先有0的情况粗笑下，log函数的定御羡义域为{x>0}，有0出现的时候，改组最后结果肯定为0；
其次是有负数的情况，需要对绝对值进行计算,计算每组负数的个数，如果负数是偶数个，那么结果为正，
如果为奇数个，则需要在结果上添上负号。
最后可以写成这样
select id,(case when sum(case when x=0 then 1 else 0 end) >0 then 0
when mod(sum(case when x<0 then 1 else 0 end),2)=0 then exp(sum(log(x)))
else -1*exp(sum(log(abs(x)))) end) as prod_x from data_set group by id;