如何使用Oracle中的SUM OVER PARTITION BY對數據進行分組求和

一、概述

在Oracle數據庫中，SUM OVER PARTITION BY是一種強大的功能，它可以對數據進行分組求和，以便更好地分析或展示數據。在本文中，我們將深入探討如何使用SUM OVER PARTITION BY實現對數據的分組求和。

二、基礎語法

要使用SUM OVER PARTITION BY功能，需要先了解它的基礎語法。下面是基礎語法的示例：

SELECT column1, column2, sum(column3) OVER (PARTITION BY column4)
FROM table_name; 

其中的column1、column2、column3和column4都代表表中的列。這條語句實現了對column3列進行分組求和，以column4列進行分組。

三、基本用法

當我們有一個表需要進行一些統計時，可以使用SUM OVER PARTITION BY對表格進行操作。接下來，以以下數據表為例：

CREATE TABLE score (
    subject VARCHAR2(20),
    student VARCHAR2(20),
    score NUMBER
);

若要按照學生對這些科目的成績進行求和，並顯示每個學生的成績總分，可以運行以下查詢：

SELECT student, SUM(score) OVER (PARTITION BY student) AS total_score
FROM score;

在SQL Developer中，運行上述查詢將會輸出每個學生的總分：

STUDENT     TOTAL_SCORE
---------- -----------
Alice              180
Bob                230
Charlie            200
David              180 

四、多列分組求和

在某些情況下，可能需要對多列進行分組求和，這時我們可以使用多列分組。下面是一個“成績單”樣例，其中包括了三個科目的成績數據：

INSERT INTO score (subject, student, score) VALUES ('Math', 'Alice', 60);
INSERT INTO score (subject, student, score) VALUES ('Math', 'Bob', 70);
INSERT INTO score (subject, student, score) VALUES ('Math', 'Charlie', 80);
INSERT INTO score (subject, student, score) VALUES ('Math', 'David', 50);
INSERT INTO score (subject, student, score) VALUES ('English', 'Alice', 70);
INSERT INTO score (subject, student, score) VALUES ('English', 'Bob', 80);
INSERT INTO score (subject, student, score) VALUES ('English', 'Charlie', 60);
INSERT INTO score (subject, student, score) VALUES ('English', 'David', 70);
INSERT INTO score (subject, student, score) VALUES ('History', 'Alice', 50);
INSERT INTO score (subject, student, score) VALUES ('History', 'Bob', 80);
INSERT INTO score (subject, student, score) VALUES ('History', 'Charlie', 60);
INSERT INTO score (subject, student, score) VALUES ('History', 'David', 60);
COMMIT;

如果按照學生姓名和科目分別進行分組求和，可以使用以下查詢語句：

SELECT subject, student, SUM(score) OVER (PARTITION BY subject, student) AS total_score
FROM score;

結果將會輸出多列的分組求和數據：

SUBJECT   STUDENT   TOTAL_SCORE
--------  --------  -----------
English   Alice             70
English   Bob               80
English   Charlie           60
English   David             70
History   Alice             50
History   Bob               80
History   Charlie           60
History   David             60
Math      Alice             60
Math      Bob               70
Math      Charlie           80
Math      David             50

五、條件分組求和

有時候我們需要按照條件進行分組，這時我們可以使用條件語句進行求和。例如，我們有一張訂單表格，其中包含訂單金額和訂單狀態。我們想要按照訂單狀態對訂單金額進行求和：

CREATE TABLE orders (
    id NUMBER,
    amount NUMBER,
    status VARCHAR2(10)
);
INSERT INTO orders VALUES (1, 100, 'paid');
INSERT INTO orders VALUES (2, 50, 'paid');
INSERT INTO orders VALUES (3, 80, 'pending');
INSERT INTO orders VALUES (4, 70, 'paid');
COMMIT;

我們可以運行以下查詢語句，按照不同的狀態進行分組求和：

SELECT status, SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) OVER () AS paid_total,
       SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) OVER () AS pending_total
FROM orders;

這將會輸出以下分組求和結果：

STATUS     PAID_TOTAL PENDING_TOTAL
---------- ---------- -------------
paid              220             80
paid              220             80
pending           220             80
paid              220             80

六、總結

本文介紹了在Oracle中如何使用SUM OVER PARTITION BY對數據進行分組求和。我們從基本語法、基本用法、多列分組求和和條件分組求和四個方面詳細講解了SUM OVER PARTITION BY的使用方法，並給出了對應示例代碼，希望讀者們能夠通過本文了解SUM OVER PARTITION BY的使用方法，提高對Oracle數據庫的熟練度。