本文基於 sqlite3 進行測試，準備工作如下

import sqlite3

conn = sqlite3.connect('window.db')
cur = conn.cursor()

##### 原始數據
sql = '''select * from window;'''
cur.execute(sql)
print(cur.fetchall())
# (0, 10)
# (1, 11)
# (2, 12)
# (2, 13)
# (2, 13)
# (2, 15)

 

over 用法

over 需要和 窗口函數 結合使用，語法為 

function() + over + (partition by order by) as new_column

相當於在查詢時 多輸出一列 new_column；

其中 partition by 相當於分組，group by，order by 相當於排序

 

示例

sql = '''select *, row_number() over (partition by x order by y) from window;'''
cur.execute(sql)
print(cur.fetchall())
## 先按 x 進行分組，然後按 y 進行排序，最後一列為 每組 排序的 順序編號
# (0, 10, 1)
# (1, 11, 1)
# (2, 12, 1)
# (2, 13, 2)
# (2, 13, 3)    ### 只有 x = 2 時有4個 y，編號 1 2 3 4
# (2, 15, 4)

 

窗口函數

有很多窗口函數，持續更新吧

 

排序 - row_number() rank() dense_rank()

## row_number():
# partition by 可有可無，order by 必須有
# 相同值有不同的序號
## rank():
# partition by 可有可無，order by 必須有
# 相同值有相同的序號
# 相同值接下來的排序會受影響
## dense_rank():
# partition by 可有可無，order by 必須有
# 相同值有相同的序號
# 相同值接下來的排序不受影響

 

示例

sql = '''select *, 
    row_number() over (partition by x order by y) as row_number_result,
    rank() over (partition by x order by y) as rank_result,
    dense_rank() over (partition by x order by y) as dense_rank_result
    from window;'''
cur.execute(sql)
print(cur.fetchall())

## 第 3 列 row_number_result，排序 1 2 3 4，不同序號
## 第 4 列 rank_result，排序 1 2 2 4，相同值有相同序號，但影響 下一個排序,本應排 3，排成了 4
## 第 5 列 dense_rank_result，排序 1 2 2 3，相同值有相同序號，切不影響 下一個排序
# (0, 10, 1, 1, 1)
# (1, 11, 1, 1, 1)
# (2, 12, 1, 1, 1)
# (2, 13, 2, 2, 2)
# (2, 13, 3, 2, 2)
# (2, 15, 4, 4, 3)

 

sum

sql = '''select x, y, sum(y) over (partition by x order by y) from window;'''
cur.execute(sql)
# (0, 10, 10)
# (1, 11, 11)
# (2, 12, 12)
# (2, 13, 38)
# (2, 13, 38)
# (2, 15, 53)

 

其他如 first_value()、last_value()、lag()、lead() 等等

 

開窗的窗口範圍

按 value 設置窗口大小

sql = '''select *, sum(y) over (order by y range between 2 preceding and 2 following ) from window'''
cur.execute(sql)
# (0, 10, 33)       ### 10 減2 加2 範圍是 8-12，y 處於該範圍的數為 10+11+12=33
# (1, 11, 59)       ### 11 減2 加2 範圍是 9-13，y 處於該範圍的數為 10+11+12+13+13=59
# (2, 12, 59)
# (2, 13, 64)
# (2, 13, 64)
# (2, 15, 41)

 

按 row 設置窗口大小

sql = '''select *, sum(y) over (order by y rows between 2 preceding and 2 following ) from window'''
cur.execute(sql)
# (0, 10, 33)       ### 上下延伸2行，10+11+12=33
# (1, 11, 46)       ### 上下延伸2行，10+11+12+13=46
# (2, 12, 59)       ### 上下延伸2行，10+11+12+13+13=59
# (2, 13, 64)
# (2, 13, 53)
# (2, 15, 41)

 

不限制大小

over（order by salary range between unbounded preceding and unbounded following）或者
over（order by salary rows between unbounded preceding and unbounded following）