分割槽函式partition by的基本用法【轉載】

谢友海發表於2024-12-02
函式

本章將和大家分享分割槽函式partition by的基本用法（此處以MySQL為例）。廢話不多說，下面我們直接進入主題。

一、建表語句

-- 建立商品表
CREATE TABLE commodity (
    id int NOT NULL PRIMARY KEY COMMENT '主鍵',
    position VARCHAR(50) COMMENT '位置(商品放置的貨架)',
    type VARCHAR(50) COMMENT '型別',
    price DECIMAL(10, 2) COMMENT '價格'
) COMMENT='商品表';

-- 初始化資料
INSERT INTO commodity VALUES (1, '1-001', '蘋果', 8.00);
INSERT INTO commodity VALUES (2, '2-002', '蘋果', 10.00);
INSERT INTO commodity VALUES (3, '3-003', '蘋果', 12.00);
INSERT INTO commodity VALUES (6, '1-001', '橘子', 5.00);
INSERT INTO commodity VALUES (7, '1-001', '橙子', 6.00);
INSERT INTO commodity VALUES (8, '3-003', '橙子', 8.00);
INSERT INTO commodity VALUES (10, '2-002', '菠蘿', 10.00);
INSERT INTO commodity VALUES (9, '2-002', '香蕉', 5.00);
INSERT INTO commodity VALUES (4, '1-001', '蘋果', 12.00);
INSERT INTO commodity VALUES (5, '1-001', '香蕉', 5.00);

二、視窗函式

1、partition by視窗函式 和 group by分組的區別

partition by關鍵字是分析性函式的一部分，它和聚合函式（如group by）不同的地方在於它能返回一個分組中的多條記錄，而聚合函式一般只有一條反映統計值的記錄。

partition by用於給結果集分組，如果沒有指定那麼它把整個結果集作為一個分組。

partition by與group by不同之處在於前者返回的是分組裡的每一條資料，並且可以對分組資料進行排序操作。後者只能返回聚合之後的組的資料統計值的記錄。

partition by相比較於group by，能夠在保留全部資料的基礎上，只對其中某些欄位做分組排序（類似excel中的操作），而group by則只保留參與分組的欄位和聚合函式的結果；簡單來說視窗函式對部分資料進行排序、計算等操作，group by對一組值進行聚合，即視窗函式是每一行都會保留，group by是從多行濃縮為少數行。

2、視窗函式基本語法

<視窗函式> over (partition by<用於分組的列名> order by <用於排序的列名>)

3、視窗函式

專用視窗函式：rank(), dense_rank(), row_number()
聚合函式：sum(), max(), min(), count(), avg() 等

三、視窗函式的使用

1、over函式的寫法

-- 先對 type 中相同的進行分割槽，在 type 中相同的情況下對 price 進行排序
over(partition by type order by price desc)

2、專用視窗函式 rank() 和 row_number() 以及 dense_rank()

SELECT *,rank() over(partition by type order by price desc) as  mm from commodity;
SELECT *,row_number() over(partition by type order by price desc) as  mm from commodity;
SELECT *,dense_rank() over(partition by type order by price desc) as  mm from commodity;

從以上結果來看：

rank()函式：如果存在並列名次的行，會佔用下一個名次的位置，比如蘋果的組內排名【1,2,3,4】，但是由於有兩個是並列的，所以顯示的排名是【1,1,3,4】，其中 2 的位置還是被佔用了。

row_number()函式：不考慮並列的情況，此函式即使遇到了 price 相同的情況，還是會預設排出一個先後來。

dense_rank()函式：如果存在並列名次的行，不會佔用下一個名次的位置，例如：圖片的最後顯示的是【1,1,2,3】。

3、聚合函式作為視窗函式

1）sum()

SELECT *,sum(price) over(partition by type order by price) as sum  from commodity;

在進行求和的時候是這樣的，當前行的 sum 值是組內當前行與其組內當前行之前所有行的和，例如紅色圈出來的資料，橙子第一行是 6 ，第二行是兩行的和 6 +8 = 14，同樣的紅色圈出來的 蘋果的也是同樣的道理。需要注意的是當在排序出現相同的時候，同樣的都是 12 或者 同樣的都是 5 無法進行區分，所以在計算的時候會把兩個或多個相同值的都加進去，這樣也就是 橙色圈出來的部分了 從 8 => 8+10 = 18 => 18+12+12 = 42 =>18+12+12 = 42 ，大概就是這個意思，下文會告訴大家如何解決這種問題 (rows between unbounded preceding and current row)。

我們來多看幾種排序的結果是否符合上面的描述：

-- order by type
SELECT *,sum(price) over(partition by type order by type) as sum  from commodity;
-- order by position
SELECT *,sum(price) over(partition by type order by position) as sum  from commodity;
-- order by id
SELECT *,sum(price) over(partition by type order by id) as sum  from commodity;

2）max(), min(), avg(), count()

SELECT *,sum(price) over(partition by type order by price) as sum,
         max(price) over(partition by type order by price) as max,
         min(price) over(partition by type order by price) as min,
         avg(price) over(partition by type order by price) as avg,
         count(price) over(partition by type order by price) as count
from commodity;

我們可以看到，不管是sum(), avg() 還是min(), max(), count() 他們在視窗函式中，都是對自身記錄以及位於自身記錄之前的資料進行聚合，求和、求平均、最小值、最大值等。所以，聚合函式作為視窗函式的時候可以在每一行的資料裡直觀的看到，截止到本行資料統計資料是多少，也可以看出每一行資料對整體的影響。（注意：資料重複的除外，有點特殊）也就是說 sum(), max(), min(), avg(), count() 都是類似的。

4、rows 與 range

rows是物理視窗，即根據order by子句排序後，取的前N行及後N行的資料計算（與當前行的值無關，只與排序後的行號相關）。

range是邏輯視窗，是指定當前行對應值的範圍取值，列數不固定，只要行值在範圍內，對應列都包含在內。

通俗點來講就是說：rows 取的時候是取當前行的前幾行以及後幾行，包括當前行在內一起進行計算的；而 range 不受行的限制，他跟當前行的值有關，當前行的值減去幾，加上幾，這個範圍內的值都是要進行計算的資料，具體例子如下所示：

-- 在當前行往前1行，往後2行，一共4行範圍內進行計算
rows between 1 preceding and 2 following

-- 在當前行的數值往前1個數值，往後2個數值，進行計算，範圍不一定，因為可能會出現重複值
range between 1 preceding and 2 following
-- rows 
SELECT *,sum(price) over(partition by type order by price rows between 1 preceding and 2 following) as sum from commodity where type = '蘋果';
-- 第一行 8 ，前一行沒有，後兩行是10、12 => 8 + 10 + 12 = 30
-- 第二行是 10 ，前一行8，後兩行是12、12 => 8 + 10 + 12 + 12 = 42
-- 第三行是 12 ，前一行10，後兩行只有一個12 => 10 + 12 + 12 = 34
-- 第四行是 12 ，前一行12，後兩行沒有 => 12 + 12 = 24

-- range 
SELECT *,sum(price) over(partition by type order by price range between 1 preceding and 2 following) as sum from commodity where type = '蘋果';
-- 第一行 8 ，往前一個數值 8-1 = 7，往後兩個數值 8+2 = 10 --> 7 <= price <= 10  --> 8 + 10 = 18
-- 第二行 10 ，往前一個數值 10-1 = 9，往後兩個數值 10+2 = 12 --> 9 <= price <= 12  --> 10 + 12 + 12 = 34
-- 第三行 12 ，往前一個數值 12-1 = 11，往後兩個數值 12+2 = 14 --> 11 <= price <= 14  --> 12 + 12 = 24
-- 第四行 12 ，往前一個數值 12-1 = 11，往後兩個數值 12+2 = 14 --> 11 <= price <= 14  --> 12 + 12 = 24

5、unbound 和 current row

-- 在當前行往前1行，往後2行，一共4行範圍內進行計算
rows between 1 preceding and 2 following

-- 在當前行的數值往前1個數值，往後2個數值，區間內進行計算，行的範圍不一定，因為可能會出現重複值
range between 1 preceding and 2 following

between ... and ... 後面的數字可以隨著需求進行替換，當然也可以使用 unbound 和 current row；其中 unbounded 表示不做限制，current row 表示當前行。

-- 按照分組內全部行求和，不做任何限制
rows between unbounded preceding and unbounded following

-- 從分組內排序的起始行到當前行
rows between unbounded preceding and current row

-- 按照分組內全部行求和，不做任何限制
range between unbounded preceding and unbounded following 

-- 從分組內排序的起始行的值到當前行的值
range between unbounded preceding and current row 

-- rows between unbounded preceding and unbounded following
SELECT *,sum(price) over(partition by type order by price rows between unbounded preceding and unbounded following) as sum from commodity where type = '蘋果';

-- rows between unbounded preceding and current row
SELECT *,sum(price) over(partition by type order by price rows between unbounded preceding and current row) as sum from commodity where type = '蘋果';

-- range between unbounded preceding and unbounded following
SELECT *,sum(price) over(partition by type order by price range between unbounded preceding and unbounded following) as sum from commodity where type = '蘋果';

-- range between unbounded preceding and current row
SELECT *,sum(price) over(partition by type order by price range between unbounded preceding and current row) as sum from commodity where type = '蘋果';

按照分組內全部行求和，不做任何限制，如下：

從分組內排序的起始行到當前行，如下：

按照分組內全部行求和，不做任何限制，如下：

從分組內排序的起始行的值到當前行的值，如下：

6、first_value(), last_valus(), lag(), lead()

-- 取出分組後的第一個值
first_value(欄位) over(partition by … order by …)
-- 取出分組後的最後一個值
last_value(欄位) over(partition by … order by …)

-- 取出分組後的第一個值
SELECT *,first_value(price) over(partition by type order by price) as mm  from commodity;
-- 取出分組後的最後一個值
SELECT *,last_value(price) over(partition by type order by price) as mm  from commodity;

-- 取出分組後前第n行的資料
lag(expresstion,<offset>,<default>) over(partition by … order by …)
-- 取出分組後後第n行的資料
lead(expresstion,<offset>,<default>) over(partition by … order by …)

-- 取分組後的前第2行的資料/後第2行的資料，預設值設定為0
SELECT *,lag(price,2,0) over(partition by type order by price) as mm  from commodity;
SELECT *,lead(price,2,0) over(partition by type order by price) as mm  from commodity;

-- 第一個引數：要取的欄位
-- 第二個引數：取排序後的第幾條記錄
-- 第三個引數：預設值，如果後面的記錄取不到值就預設取第三個引數的值，注意引數的型別要與第一個引數所取欄位的型別保持一致，不傳的話預設為空
SELECT *,lag(price,1,0) over(partition by type order by price) as lagval,lead(price,1,0) over(partition by type order by price) as leadval  from commodity;

-- 注：具體的sql輸出結果，上文已經放置了建表語句，可以執行一下，自己體驗體驗！！！

7、preceding 和 following

preceding：往前，following：往後，這兩個視窗函式不僅可以實現滑窗求和（指定rows範圍）或者指定範圍內資料求和（指定range範圍），也可以用來計算移動平均值：

SELECT *,sum(price) over(partition by type order by price) as sum,avg(price) over(partition by type order by price) as avg,
avg(price) over(partition by type order by price rows 2 preceding) as avg2 from commodity where type = '蘋果';

本文轉載自博文：https://blog.csdn.net/weixin_44711823/article/details/135966741

相關文章