當前位置:首頁 » 編程語言 » 實現sql聚合演算法
擴展閱讀
webinf下怎麼引入js 2023-08-31 21:54:13
堡壘機怎麼打開web 2023-08-31 21:54:11

實現sql聚合演算法

發布時間: 2023-05-11 21:53:23

A. sql 語句中那些是聚合函數

常見聚合函數,max(最大)、min(最小)、sum(求和)、avg(平均)等,

示例如下,

1、創建測試表,

create table test_group(id number, value number);

B. 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
檢查的行進行分組和聚集計算。

C. 第十三章 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 :

D. 第十章 SQL聚合函數 STDDEV, STDDEV_SAMP, STDDEV_POP

返回數據集的統計標准差的聚合函數。

除非表達式的數據類型為 DOUBLE ,否則這些函數將返回數字數據類型。如果表達式為 DOUBLE ,則返回 DOUBLE 。

這三個標准差聚合函數在丟棄NULL值後返回表達式值分布的統計標准差。
也就是說,從數據集的平均值的標准差的量,表示為一個正數。
返回值越大,值的數據集的變化就越大。

STDDEV、STDDEV_SAMP (sample) 和 STDDEV_POP (population) 函數是由相應的方差聚合函數派生而來的:

標准差是相應方差值的平方根。

這些標准差函數可以在引用表或視圖的 SELECT 查詢或子查詢中使用。
它們可以在 SELECT 列表或 HAVING 子句中與普通欄位值一起出現。

這些標准偏差函數不能在 WHERE 子句中使用。
它們不能在 JOIN 的 ON 子句中使用,除非 SELECT 是子查詢。

這些標准偏差函數返回數據類型 NUMERIC 的值,精度為 36 ,刻度為 17 ,除非表達式是數據類型 DOUBLE ,在這種情況下它返回數據類型 DOUBLE 。

這些函數通常應用於具有數值的欄位或表達式。
它們將非數值值(包括空字元串( " ))計算為零( 0 )。

這些標准偏差函數會忽略數據欄位中的 NULL 值。
如果查詢沒有返回行,或者返回的所有行的數據欄位值為 NULL ,則返回 NULL 。

與所有聚合函數一樣,標准差函數可以採用一個可選的 DISTINCT 子句。
STDDEV(DISTINCT col1) 返回那些不同(唯一)的 col1 欄位值的標准偏差。
STDDEV(DISTINCT BY(col2) col1) 返回記錄中 col1 欄位值的標准差,其中 col2 值是不同的(唯一的)。
但是請注意,不同的 col2 值可能包含一個單獨的 NULL 值。

與所有聚合函數一樣,標准偏差函數總是返回數據的當前狀態,包括未提交的更改,而不考慮當前事務的隔離級別。

下面的示例使用 STDDEV 返回員工年齡的標准偏差。
,以及一個或多個員工代表的不同年齡的標准差:

下面的示例使用 STDDEV_POP 返回員工年齡的總體標准差。
,以及一個或多個員工代表的不同年齡的標准差:

E. 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

F. SqlServer如何用Sql語句自定義聚合函數

有兩個寫法:

select*fromtblDept
whereidin(
count(casewhenSalary>3000then1elsenullend)*1.0/count(*)>0.5)

select*fromtblDept
whereidin(
sum(casewhenSalary>3000then1else0end)*1.0/count(*)>0.5)

G. 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;

H. 在SQL中的聚合函數

原則上,你要用group by,那麼前面抽取的欄位必須要作為分組依據,否則就需要sum,avg,max等函數的處理。