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;