欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQL窗口函數(shù)之聚合窗口函數(shù)的使用(count,max,min,sum)

 更新時(shí)間：2022年04月21日 10:09:39   作者：梁萌  
許多常見的聚合函數(shù)也可以作為窗口函數(shù)使用，包括AVG()、SUM()、COUNT()、MAX()以及MIN()等函數(shù)，本文就詳細(xì)的介紹了SQL窗口函數(shù)之聚合窗口函數(shù)的使用，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

關(guān)于窗口函數(shù)的基礎(chǔ)，請看文章SQL窗口函數(shù)

許多常見的聚合函數(shù)也可以作為窗口函數(shù)使用，包括AVG()、SUM()、COUNT()、MAX()以及MIN()等函數(shù)。

案例分析

案例使用的示例表

下面的查詢中會(huì)用到兩個(gè)表，其中sales_monthly表中存儲(chǔ)了不同產(chǎn)品（蘋果、香蕉、橘子）每個(gè)月的銷售額情況。以下是該表中的部分?jǐn)?shù)據(jù)：

transfer_log表中記錄了一些銀行賬戶的交易日志。以下是該表中的部分?jǐn)?shù)據(jù)： 

該表中的字段分別表示交易日志編號(hào)、交易時(shí)間、交易發(fā)起賬戶、交易接收賬戶、交易類型以及交易金額。這兩個(gè)表的初始化腳本可以在文章底部獲取。

1.移動(dòng)平均值

AVG函數(shù)在作為窗口函數(shù)使用時(shí)，可以用于計(jì)算隨著當(dāng)前行移動(dòng)的窗口內(nèi)數(shù)據(jù)行的平均值。

例如，以下語句用于查找不同產(chǎn)品每個(gè)月以及截至當(dāng)前月最近3個(gè)月的平均銷售額

SELECT m.product,m.ym,m.amount,
AVG(m.amount) OVER(
 PARTITION BY m.product
 ORDER BY m.ym
 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
FROM sales_monthly m
ORDER BY m.product,m.ym

AVG函數(shù)OVER子句中的PARTITION BY選項(xiàng)表示按照產(chǎn)品進(jìn)行分區(qū)。

ORDER BY選項(xiàng)表示按照月份進(jìn)行排序；ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示窗口從當(dāng)前行的前2行開始，直到當(dāng)前行結(jié)束。該查詢返回的結(jié)果如下：

對于“橘子”：

第一個(gè)月的分析窗口只有1行數(shù)據(jù)，因此平均銷售額為“10154”。

第二個(gè)月的分析窗口為第1行和第2行數(shù)據(jù)，因此平均銷售額為“10168.5”（（10154+10183）/2）。

第三個(gè)月的分析窗口為第1行到第3行數(shù)據(jù)，因此平均銷售額為“10194”（（10154+10183+10245）/3）。

依此類推，直到計(jì)算完“橘子”所有月份的平均銷售額，然后開始計(jì)算其他產(chǎn)品的平均銷售額。

2.累計(jì)求和（ROW）

SUM函數(shù)作為窗口函數(shù)時(shí)，可以用于統(tǒng)計(jì)指定窗口內(nèi)的累計(jì)值。

例如，以下語句用于查找不同產(chǎn)品截至當(dāng)前月份的累計(jì)銷售額：

SELECT m.product,m.ym,m.amount,
SUM(m.amount) OVER(
 PARTITION BY m.product
 ORDER BY m.ym
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM sales_monthly m
ORDER BY m.product,m.ym

SUM函數(shù)OVER子句中的PARTITION BY選項(xiàng)表示按照產(chǎn)品進(jìn)行分區(qū)。

ORDER BY選項(xiàng)表示按照月份進(jìn)行排序。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示窗口從當(dāng)前分區(qū)第1行開始，直到當(dāng)前行結(jié)束。

該查詢返回的結(jié)果如下：

對于“橘子”：

第一個(gè)月的分析窗口只有1行數(shù)據(jù)，因此累計(jì)銷售額為“10154”。

第二個(gè)月的分析窗口為第1行和第2行數(shù)據(jù)，因此累計(jì)銷售額為“20337”（10154+10183）。

第三個(gè)月的分析窗口為第1行到第3行數(shù)據(jù)，因此累計(jì)銷售額為“30582”（10154+10183+10245）。

依此類推，直到計(jì)算完“橘子”所有月份的累計(jì)銷售額，然后開始計(jì)算其他產(chǎn)品的累計(jì)銷售額。

提示：對于聚合窗口函數(shù)，如果我們沒有指定ORDER BY選項(xiàng)，默認(rèn)的窗口大小就是整個(gè)分區(qū)。

如果我們指定了ORDER BY選項(xiàng)，默認(rèn)的窗口大小就是分區(qū)的第一行到當(dāng)前行。

因此，以上示例語句中的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW選項(xiàng)可以省略。省略后的語句：

 執(zhí)行結(jié)果與上面相同。

如果去掉ORDER BY選項(xiàng)，查詢的窗口大小就是整個(gè)分區(qū)，如下圖所示：

這時(shí)，合計(jì)值就變成了分區(qū)內(nèi)所有記錄的合計(jì)。

3.累計(jì)求和（RANGE）

除使用ROWS關(guān)鍵字以數(shù)據(jù)行為單位指定窗口的偏移量外，我們也可以使用RANGE關(guān)鍵字以數(shù)值為單位指定窗口的偏移量。

例如，以下語句用于查找短期之內(nèi)（5天）累計(jì)轉(zhuǎn)賬超過100萬元的賬戶：

SELECT log_ts,from_user,total_amount FROM (
    SELECT to_char(t.log_ts,'yyyy-mm-dd hh24:mi:ss') log_ts,t.from_user,t.amount,
      SUM(t.amount) OVER(
        PARTITION BY t.from_user
        ORDER BY t.log_ts
        RANGE INTERVAL '5' DAY PRECEDING
      ) AS total_amount
    FROM transfer_log t 
    WHERE t.type = '轉(zhuǎn)賬'
)
WHERE total_amount >= 1000000;

其中，SUM函數(shù)OVER子句中的RANGE選項(xiàng)指定了一個(gè)5天之內(nèi)的時(shí)間窗口。該查詢返回的結(jié)果如下：

截至2021年1月10日7時(shí)46分02秒，賬戶“62221234567890”在最近5天之內(nèi)累計(jì)轉(zhuǎn)賬105萬元。

示例表和腳本

-- 創(chuàng)建銷量表sales_monthly
-- product表示產(chǎn)品名稱，ym表示年月，amount表示銷售金額（元）
CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
 
-- 生成測試數(shù)據(jù)
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('蘋果','201906',11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201801',10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201802',10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201803',10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201804',10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201805',10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201806',10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201807',10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201808',10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201809',10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201810',10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201811',10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201812',11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201901',11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201902',11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201903',11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201904',11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201905',11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201906',11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201801',10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201802',10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201803',10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201804',10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201805',10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201806',10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201807',10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201808',10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201809',10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201810',10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201811',10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201812',10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201901',11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201902',11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201903',11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201904',11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201905',11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201906',11524.00);
 
 
-- 創(chuàng)建銀行交易日志表transfer_log
-- Oracle、MySQL、PostgreSQL以及SQLite
CREATE TABLE transfer_log
( log_id    INTEGER NOT NULL PRIMARY KEY, -- 交易日志編號(hào)
  log_ts    TIMESTAMP NOT NULL, -- 交易時(shí)間
  from_user VARCHAR(50) NOT NULL, -- 交易發(fā)起賬號(hào)
  to_user   VARCHAR(50), -- 交易接收賬號(hào)
  type      VARCHAR(10) NOT NULL, -- 交易類型
  amount    NUMERIC(10) NOT NULL -- 交易金額（元）
);
 
 
-- 生成測試數(shù)據(jù)
-- Oracle 需要執(zhí)行以下ALTER語句
ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS';
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (1,'2021-01-02 10:31:40','62221234567890',NULL,'存款',50000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (2,'2021-01-02 10:32:15','62221234567890',NULL,'存款',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (3,'2021-01-03 08:14:29','62221234567890','62226666666666','轉(zhuǎn)賬',200000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (4,'2021-01-05 13:55:38','62221234567890','62226666666666','轉(zhuǎn)賬',150000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (5,'2021-01-07 20:00:31','62221234567890','62227777777777','轉(zhuǎn)賬',300000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (6,'2021-01-09 17:28:07','62221234567890','62227777777777','轉(zhuǎn)賬',500000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (7,'2021-01-10 07:46:02','62221234567890','62227777777777','轉(zhuǎn)賬',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (8,'2021-01-11 09:36:53','62221234567890',NULL,'存款',40000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (9,'2021-01-12 07:10:01','62221234567890','62228888888881','轉(zhuǎn)賬',10000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (10,'2021-01-12 07:11:12','62221234567890','62228888888882','轉(zhuǎn)賬',8000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (11,'2021-01-12 07:12:36','62221234567890','62228888888883','轉(zhuǎn)賬',5000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (12,'2021-01-12 07:13:55','62221234567890','62228888888884','轉(zhuǎn)賬',6000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (13,'2021-01-12 07:14:24','62221234567890','62228888888885','轉(zhuǎn)賬',7000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (14,'2021-01-21 12:11:16','62221234567890','62228888888885','轉(zhuǎn)賬',70000);

到此這篇關(guān)于SQL窗口函數(shù)之聚合窗口函數(shù)的使用的文章就介紹到這了,更多相關(guān)SQL 聚合窗口函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評(píng)論