首頁 > 軟體

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

2022-04-21 13:01:52

關於視窗函數的基礎，請看文章SQL視窗函數

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

案例分析

案例使用的範例表

下面的查詢中會用到兩個表，其中sales_monthly表中儲存了不同產品（蘋果、香蕉、橘子）每個月的銷售額情況。以下是該表中的部分資料：

transfer_log表中記錄了一些銀行賬戶的交易紀錄檔。以下是該表中的部分資料： 

該表中的欄位分別表示交易紀錄檔編號、交易時間、交易發起賬戶、交易接收賬戶、交易型別以及交易金額。這兩個表的初始化指令碼可以在文章底部獲取。

1.移動平均值

AVG函數在作為視窗函數使用時，可以用於計算隨著當前行移動的視窗內資料行的平均值。

例如，以下語句用於查詢不同產品每個月以及截至當前月最近3個月的平均銷售額

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函數OVER子句中的PARTITION BY選項表示按照產品進行分割區。

ORDER BY選項表示按照月份進行排序；ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示視窗從當前行的前2行開始，直到當前行結束。該查詢返回的結果如下：

對於“橘子”：

第一個月的分析視窗只有1行資料，因此平均銷售額為“10154”。

第二個月的分析視窗為第1行和第2行資料，因此平均銷售額為“10168.5”（（10154+10183）/2）。

第三個月的分析視窗為第1行到第3行資料，因此平均銷售額為“10194”（（10154+10183+10245）/3）。

依此類推，直到計算完“橘子”所有月份的平均銷售額，然後開始計算其他產品的平均銷售額。

2.累計求和（ROW）

SUM函數作為視窗函數時，可以用於統計指定視窗內的累計值。

例如，以下語句用於查詢不同產品截至當前月份的累計銷售額：

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函數OVER子句中的PARTITION BY選項表示按照產品進行分割區。

ORDER BY選項表示按照月份進行排序。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示視窗從當前分割區第1行開始，直到當前行結束。

該查詢返回的結果如下：

對於“橘子”：

第一個月的分析視窗只有1行資料，因此累計銷售額為“10154”。

第二個月的分析視窗為第1行和第2行資料，因此累計銷售額為“20337”（10154+10183）。

第三個月的分析視窗為第1行到第3行資料，因此累計銷售額為“30582”（10154+10183+10245）。

依此類推，直到計算完“橘子”所有月份的累計銷售額，然後開始計算其他產品的累計銷售額。

提示：對於聚合視窗函數，如果我們沒有指定ORDER BY選項，預設的視窗大小就是整個分割區。

如果我們指定了ORDER BY選項，預設的視窗大小就是分割區的第一行到當前行。

因此，以上範例語句中的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW選項可以省略。省略後的語句：

 執行結果與上面相同。

如果去掉ORDER BY選項，查詢的視窗大小就是整個分割區，如下圖所示：

這時，合計值就變成了分割區內所有記錄的合計。

3.累計求和（RANGE）

除使用ROWS關鍵字以資料行為單位指定視窗的偏移量外，我們也可以使用RANGE關鍵字以數值為單位指定視窗的偏移量。

例如，以下語句用於查詢短期之內（5天）累計轉賬超過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 = '轉賬'
)
WHERE total_amount >= 1000000;

其中，SUM函數OVER子句中的RANGE選項指定了一個5天之內的時間視窗。該查詢返回的結果如下：

截至2021年1月10日7時46分02秒，賬戶“62221234567890”在最近5天之內累計轉賬105萬元。

範例表和指令碼

-- 建立銷量表sales_monthly
-- product表示產品名稱，ym表示年月，amount表示銷售金額（元）
CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
 
-- 生成測試資料
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);
 
 
-- 建立銀行交易紀錄檔表transfer_log
-- Oracle、MySQL、PostgreSQL以及SQLite
CREATE TABLE transfer_log
( log_id    INTEGER NOT NULL PRIMARY KEY, -- 交易紀錄檔編號
  log_ts    TIMESTAMP NOT NULL, -- 交易時間
  from_user VARCHAR(50) NOT NULL, -- 交易發起賬號
  to_user   VARCHAR(50), -- 交易接收賬號
  type      VARCHAR(10) NOT NULL, -- 交易型別
  amount    NUMERIC(10) NOT NULL -- 交易金額（元）
);
 
 
-- 生成測試資料
-- Oracle 需要執行以下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','轉賬',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','轉賬',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','轉賬',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','轉賬',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','轉賬',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','轉賬',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','轉賬',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','轉賬',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','轉賬',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','轉賬',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','轉賬',70000);

到此這篇關於SQL視窗函數之聚合視窗函數的使用的文章就介紹到這了,更多相關SQL 聚合視窗函數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！


相關文章

  • 德漢姆中學就讀條件 德漢姆中學就讀條件

    <em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011

    2021-06-01 09:32:01

  • 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列

    综合看Anker超能充系列的性价比很高，并且与不仅和iPhone12/苹果<em>Mac</em>Book很配，而且适合多设备充电需求的日常使用或差旅场景，不管是安卓还是Switch同样也能用得上它，希望这次分享能给准备购入充电器的小伙伴们有所

    2021-06-01 09:31:42

  • 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！ 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！

    除了L4WUDU与吴亦凡已经多次共事，成为了明面上的厂牌成员，吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节，这也是20XXCLUB首次全员合照，王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让

    2021-06-01 09:31:34

  • IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？ IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？

    目前应用IPFS的机构：1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆，历史资料永久保存在 IPFS 6 加

    2021-06-01 09:31:24

  • 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊

    开拓者的车机是兼容苹果和<em>安卓</em>，虽然我不怎么用，但确实兼顾了我家人的很多需求：副驾的门板还配有解锁开关，有的时候老婆开车，下车的时候偶尔会忘记解锁，我在副驾驶可以自己开门：第二排设计很好，不仅配置了一个很大的

    2021-06-01 09:30:48

  • iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？ iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？

    不仅是<em>安卓</em>手机，苹果手机的降价力度也是前所未有了，iPhone12也“跳水价”了，发布价是6799元，如今已经跌至5308元，降价幅度超过1400元，最新定价确认了。iPhone12是苹果首款5G手机，同时也是全球首款5nm芯片的智能机，它

    2021-06-01 09:30:45

IT145.com E-mail:sddin#qq.com