單表查詢
常量查詢
讓我們來看一個具體的 SQL 代碼和結果示例,假設有一張名為?orders
?的數據表,它存儲了訂單信息,包括訂單編號(order_id)、商品單價(unit_price)、購買數量(quantity)等:
數據表orders
:
order_id | unit_price | quantity |
---|---|---|
1001 | 10.00 | 5 |
1002 | 20.00 | 3 |
1003 | 15.00 | 2 |
1004 | 25.00 | 4 |
現在,我們需要計算每個訂單的總金額(total_amount),即商品單價(unit_price)乘以購買數量(quantity)。
SQL 查詢語句如下:
select order_id, unit_price, quantity, unit_price * quantity as total_amount from orders;
between and 運算符
使用 “BETWEEN” 運算符篩選出年齡在 25 到 30 之間的員工:
-- SQL查詢語句
select name, age, salary from employees where age between 25 and 30;
判斷是否為空值
在數據庫中,有時候數據表的某些字段可能沒有值,即為空值(NULL)。
空值表示該字段的值是未知的、不存在的或者沒有被填寫的。在SQL查詢中,我們可以使用 “IS NULL” 和 “IS NOT NULL” 來判斷字段是否為空值或非空值
模糊查詢
模糊查詢是一種特殊的條件查詢,它允許我們根據模式匹配來查找符合特定條件的數據,可以使用 LIKE 關鍵字實現模糊查詢。
在 LIKE 模糊查詢中,我們使用通配符來代表零個或多個字符,從而能夠快速地找到匹配的數據。
有如下 2 種通配符:
- 百分號(%):表示任意長度的任意字符序列。
- 下劃線(_ ):表示任意單個字符。
同理,可以使用?not like
?來查詢不包含某關鍵字的信息
邏輯運算符
邏輯運算是一種在條件查詢中使用的運算符,它允許我們結合多個條件來過濾出符合特定條件的數據。
在邏輯運算中,常用的運算符有:
- AND:表示邏輯與,要求同時滿足多個條件,才返回 true。
- OR:表示邏輯或,要求滿足其中任意一個條件,就返回 true。
- NOT:表示邏輯非,用于否定一個條件(本來是 true,用了 not 后轉為 false)
去重
在數據表中,可能存在重復的數據記錄,但如果我們想要過濾掉重復的記錄,只保留不同的記錄,就要使用 SQL 的去重功能。
在 SQL 中,我們可以使用?DISTINCT
?關鍵字來實現去重操作。
-- SQL 查詢語句
select distinct class_id from students;
除了按照單字段去重外,DISTINCT
?關鍵字還支持根據多個字段的組合來進行去重操作,確保多個字段的組合是唯一的。
示例語法如下:
distinct 字段1, 字段2, 字段3, ...
排序
在查詢數據時,我們有時希望對結果按照某個字段的值進行排序,以便更好地查看數據。
在 SQL 中,我們可以使用?ORDER BY
?關鍵字來實現排序操作。ORDER BY
?后面跟上需要排序的字段,可以選擇升序(ASC)或降序(DESC)排列。
-- SQL 查詢語句 1
select name, age from students order by age asc;-- SQL 查詢語句 2
select name, score from students order by score desc;
在排序的基礎上,我們還可以根據多個字段的值進行排序。當第一個字段的值相同時,再按照第二個字段的值進行排序,以此類推。
示例語法如下:
order by 字段1 [升序/降序], 字段2 [升序/降序], ...
截斷和偏移
在 SQL 中,我們使用?LIMIT
?關鍵字來實現數據的截斷和偏移。
截斷和偏移的一個典型的應用場景是分頁,即網站內容很多時,用戶可以根據頁號每次只看部分數據。
-- LIMIT 后只跟一個整數,表示要截斷的數據條數(一次獲取幾條)
select task_name, due_date from tasks limit 2;-- LIMIT 后跟 2 個整數,依次表示從第幾條數據開始、一次獲取幾條
select task_name, due_date from tasks limit 2, 2;
查詢語句 2 結果,從下標為 2(第 3 條)數據的位置開始獲取 2 條數據:
注:左閉的,但是是下標的索引值
條件分支
使用?case when
?可以在查詢結果中根據特定的條件動態生成新的列或對現有的列進行轉換。
示例 SQL 如下:
SELECTname,CASE WHEN (name = '雞哥') THEN '會' ELSE '不會' END AS can_rap
FROMstudent;
查詢結果:
name | can_rap |
---|---|
小明 | 不會 |
雞哥 | 會 |
李華 | 不會 |
王五 | 不會 |
case when
?支持同時指定多個分支,示例語法如下:
CASE WHEN (條件1) THEN 結果1WHEN (條件2) THEN 結果2...ELSE 其他結果 END
select name,
case when(age > 60) then "老同學"when(age > 20) then "年輕"else "小同學" end as age_level
from student order by name asc
注:case 返回的是一個字段
時間函數
常用的時間函數有:
- DATE:獲取當前日期
- DATETIME:獲取當前日期時間
- TIME:獲取當前時間
示例
假設有一個訂單表?orders
,包含以下字段:order_id
(訂單號)、order_date
(下單日期)、order_time
(下單時間)。數據如下:
order_id | order_date | order_time |
---|---|---|
1 | 2023-08-01 | 12:30:45 |
2 | 2023-08-01 | 14:20:10 |
3 | 2023-08-02 | 09:15:00 |
4 | 2023-08-02 | 18:05:30 |
使用時間函數獲取當前日期、當前日期時間和當前時間:
-- 獲取當前日期
SELECT DATE() AS current_date;-- 獲取當前日期時間
SELECT DATETIME() AS current_datetime;-- 獲取當前時間
SELECT TIME() AS current_time;
查詢結果:
為了方便對比,放到同一個表格
current_date | current_datetime | current_time |
---|---|---|
2023-08-01 | 2023-08-01 14:30:00 | 14:30:00 |
注意,這里的日期、日期時間和時間將根據當前的系統時間來生成,實際運行結果可能會因為當前時間而不同。
字符串
在 SQL 中,字符串處理是一類用于處理文本數據的函數。它們允許我們對字符串進行各種操作,如轉換大小寫、計算字符串長度以及搜索和替換子字符串等。字符串處理函數可以幫助我們在數據庫中對字符串進行加工和轉換,從而滿足不同的需求。
- upper(xxx)
- length(xxx)
- lower(xxx)
聚合函數
在 SQL 中,聚合函數是一類用于對數據集進行?匯總計算?的特殊函數。它們可以對一組數據執行諸如計數、求和、平均值、最大值和最小值等操作。聚合函數通常在 SELECT 語句中配合 GROUP BY 子句使用,用于對分組后的數據進行匯總分析。
常見的聚合函數包括:
- COUNT:計算指定列的行數或非空值的數量。
- SUM:計算指定列的數值之和。
- AVG:計算指定列的數值平均值。
- MAX:找出指定列的最大值。
- MIN:找出指定列的最小值。
示例
假設有一個訂單表?orders
,包含以下字段:order_id
(訂單號)、customer_id
(客戶編號)、amount
(訂單金額)。數據如下:
order_id | customer_id | amount |
---|---|---|
1 | A001 | 100 |
2 | A002 | 200 |
3 | A001 | 150 |
4 | A003 | 50 |
1)使用聚合函數?COUNT
?計算訂單表中的總訂單數:
SELECT COUNT(*) AS order_num
FROM orders;
查詢結果:
order_num |
---|
4 |
2)使用聚合函數?COUNT(DISTINCT 列名)
?計算訂單表中不同客戶的數量:
SELECT COUNT(DISTINCT customer_id) AS customer_num
FROM orders;
查詢結果:
customer_num |
---|
3 |
3)使用聚合函數?SUM
?計算總訂單金額:
SELECT SUM(amount) AS total_amount
FROM orders;
查詢結果:
total_amount |
---|
500 |
單字段分組
在 SQL 中,分組聚合是一種對數據進行分類并對每個分類進行聚合計算的操作。它允許我們按照指定的列或字段對數據進行分組,然后對每個分組應用聚合函數,如 COUNT、SUM、AVG 等,以獲得分組后的匯總結果。
舉個例子:某個學校可以按照班級將學生分組,并對每個班級進行統計。查看每個班級有多少學生、每個班級的平均成績。這樣我們就能夠對學校各班的學生情況有一個整體的了解,而不是單純看個別學生的信息。
在 SQL 中,通常使用?GROUP BY
?關鍵字對數據進行分組。
示例
假設有一個訂單表?orders
,包含以下字段:order_id
(訂單號)、product_id
(商品編號)、customer_id
(客戶編號)、amount
(訂單金額)。
數據如下:
order_id | product_id | customer_id | amount |
---|---|---|---|
1 | 1 | A001 | 100 |
2 | 1 | A002 | 200 |
3 | 1 | A001 | 150 |
4 | 1 | A003 | 50 |
5 | 2 | A001 | 50 |
要查詢使用多字段分組查詢表中?每個客戶?購買的?每種商品?的總金額,相當于按照客戶編號和商品編號分組:
-- 查詢每個用戶購買的每種商品的總金額,按照客戶編號和商品編號分組
SELECT customer_id, product_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, product_id;
查詢結果:
customer_id | product_id | total_amount |
---|---|---|
A001 | 1 | 250 |
A001 | 2 | 50 |
A002 | 1 | 200 |
A003 | 1 | 50 |
having
在 SQL 中,HAVING 子句用于在分組聚合后對分組進行過濾。它允許我們對分組后的結果進行條件篩選,只保留滿足特定條件的分組。
HAVING 子句與條件查詢 WHERE 子句的區別在于,WHERE 子句用于在?分組之前?進行過濾,而 HAVING 子句用于在?分組之后?進行過濾。
多表查詢
cross join
在 SQL 中,關聯查詢是一種用于聯合多個數據表中的數據的查詢方式。
其中,CROSS JOIN
?是一種簡單的關聯查詢,不需要任何條件來匹配行,它直接將左表的?每一行?與右表的?每一行?進行組合,返回的結果是兩個表的笛卡爾積。
SELECT e.emp_name, e.salary, d.department, d.manager
FROM employees e
CROSS JOIN departments d;
上面的 SQL 還可以簡化為:
SELECT e.emp_name, e.salary, d.department, d.manager
FROM employees e, departments d;
關聯查詢(inner-join)
在 SQL 中,INNER JOIN 是一種常見的關聯查詢方式,它根據兩個表之間的關聯條件,將滿足條件的行組合在一起。
注意,INNER JOIN 只返回兩個表中滿足關聯條件的交集部分,即在兩個表中都存在的匹配行。
示例
假設有一個員工表?employees
,包含以下字段:emp_id
(員工編號)、emp_name
(員工姓名)、department
(所屬部門)、salary
(工資)。數據如下:
emp_id | emp_name | department | salary |
---|---|---|---|
1 | 小明 | 技術部 | 5000 |
2 | 雞哥 | 財務部 | 6000 |
3 | 李華 | 銷售部 | 4500 |
假設還有一個部門表?departments
,包含以下字段:department
(部門名稱)、manager
(部門經理)、location
(所在地)。數據如下:
department | manager | location |
---|---|---|
技術部 | 張三 | 上海 |
財務部 | 李四 | 北京 |
銷售部 | 王五 | 廣州 |
摸魚部 | 趙二 | 吐魯番 |
使用 INNER JOIN 進行關聯查詢,根據員工表和部門表之間的公共字段?部門名稱(department)
?進行匹配,將員工的姓名、工資以及所屬部門和部門經理組合在一起:
SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
JOIN departments d ON e.department = d.department;
查詢結果如下:
emp_name | salary | department | manager |
---|---|---|---|
小明 | 5000 | 技術部 | 張三 |
雞哥 | 6000 | 財務部 | 李四 |
李華 | 4500 | 銷售部 | 王五 |
我們會發現,使用 INNER_JOIN 后,只有兩個表之間存在對應關系的數據才會被放到查詢結果中。
關聯查詢(outer-join)
在 SQL 中,OUTER JOIN 是一種關聯查詢方式,它根據指定的關聯條件,將兩個表中滿足條件的行組合在一起,并?包含沒有匹配的行?。
在 OUTER JOIN 中,包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 兩種類型,它們分別表示查詢左表和右表的所有行(即使沒有被匹配),再加上滿足條件的交集部分。
SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
LEFT JOIN departments d ON e.department = d.department;
注:左查詢會將
employees
所有的行都羅列出來,然后滿足條件的會將departments
中的信息帶著
子查詢
子查詢是指在一個查詢語句內部?嵌套?另一個完整的查詢語句,內層查詢被稱為子查詢。子查詢可以用于獲取更復雜的查詢結果或者用于過濾數據。
當執行包含子查詢的查詢語句時,數據庫引擎會首先執行子查詢,然后將其結果作為條件或數據源來執行外層查詢。
打個比方,子查詢就像是在一個盒子中的盒子,外層查詢是大盒子,內層查詢是小盒子。執行查詢時,我們首先打開小盒子獲取結果,然后將小盒子的結果放到大盒子中繼續處理。
示例
假設我們有以下兩個數據表:orders
?和?customers
,分別包含訂單信息和客戶信息。
orders 表:
order_id | customer_id | order_date | total_amount |
---|---|---|---|
1 | 101 | 2023-01-01 | 200 |
2 | 102 | 2023-01-05 | 350 |
3 | 101 | 2023-01-10 | 120 |
4 | 103 | 2023-01-15 | 500 |
customers 表:
customer_id | name | city |
---|---|---|
101 | Alice | New York |
102 | Bob | Los Angeles |
103 | Charlie | Chicago |
現在,我們希望查詢出有訂單總金額 > 200 的客戶的姓名和城市信息,示例 SQL 如下:
-- 主查詢
SELECT name, city
FROM customers
WHERE customer_id IN (-- 子查詢SELECT DISTINCT customer_idFROM ordersWHERE total_amount > 200
);
在上述 SQL 中,先通過子查詢從訂單表中過濾查詢出了符合條件的客戶 id,然后再根據客戶 id 到客戶信息表中查詢客戶信息,這樣可以少查詢很多客戶信息數據。
上述語句的查詢結果:
name | city |
---|---|
Bob | Los Angeles |
Charlie | Chicago |
子查詢(exists)
之前的教程講到,子查詢是一種強大的查詢工具,它可以嵌套在主查詢中,幫助我們進行更復雜的條件過濾和數據檢索。
其中,子查詢中的一種特殊類型是 “exists” 子查詢,用于檢查主查詢的結果集是否存在滿足條件的記錄,它返回布爾值(True 或 False),而不返回實際的數據。
示例
假設我們有以下兩個數據表:orders
?和?customers
,分別包含訂單信息和客戶信息。
orders 表:
order_id | customer_id | order_date | total_amount |
---|---|---|---|
1 | 101 | 2023-01-01 | 200 |
2 | 102 | 2023-01-05 | 350 |
3 | 101 | 2023-01-10 | 120 |
4 | 103 | 2023-01-15 | 500 |
customers 表:
customer_id | name | city |
---|---|---|
101 | Alice | New York |
102 | Bob | Los Angeles |
103 | Charlie | Chicago |
104 | 趙二 | China |
現在,我們希望查詢出?存在訂單的?客戶姓名和訂單金額。
使用 exists 子查詢的方式,SQL 代碼如下:
-- 主查詢
SELECT name, total_amount
FROM customers
WHERE EXISTS (-- 子查詢SELECT 1FROM ordersWHERE orders.customer_id = customers.customer_id
);
上述語句中,先遍歷客戶信息表的每一行,獲取到客戶編號;然后執行子查詢,從訂單表中查找該客戶編號是否存在,如果存在則返回結果。
查詢結果如下:
name | total_amount |
---|---|
Alice | 200 |
Bob | 350 |
Charlie | 500 |
和 exists 相對的是 not exists,用于查找不滿足存在條件的記錄。
組合查詢
在 SQL 中,組合查詢是一種將多個 SELECT 查詢結果合并在一起的查詢操作。
包括兩種常見的組合查詢操作:UNION 和 UNION ALL。
-
UNION 操作:它用于將兩個或多個查詢的結果集合并,?并去除重復的行?。即如果兩個查詢的結果有相同的行,則只保留一行。
-
UNION ALL 操作:它也用于將兩個或多個查詢的結果集合并,?但不去除重復的行?。即如果兩個查詢的結果有相同的行,則全部保留。
示例
假設我們有以下兩個數據表:table1
?和?table2
,分別包含不同部門的員工信息。
table1 表:
emp_id | name | age | department |
---|---|---|---|
101 | Alice | 25 | HR |
102 | Bob | 28 | Finance |
103 | Charlie | 22 | IT |
table2 表:
emp_id | name | age | department |
---|---|---|---|
101 | Alice | 25 | HR |
201 | David | 27 | Finance |
202 | Eve | 24 | HR |
203 | Frank | 26 | IT |
現在,我們想要合并這兩張表的數據,分別執行 UNION 操作和 UNION ALL 操作。
UNION 操作:
SELECT name, age, department
FROM table1
UNION
SELECT name, age, department
FROM table2;
UNION 操作的結果,去除了重復的行(名稱為 Alice):
name | age | department |
---|---|---|
Alice | 25 | HR |
Bob | 28 | Finance |
Charlie | 22 | IT |
David | 27 | Finance |
Eve | 24 | HR |
Frank | 26 | IT |
UNION ALL 操作:
-- UNION ALL操作
SELECT name, age, department
FROM table1
UNION ALL
SELECT name, age, department
FROM table2;
結果如下,保留了重復的行:
name | age | department |
---|---|---|
Alice | 25 | HR |
Bob | 28 | Finance |
Charlie | 22 | IT |
Alice | 25 | HR |
David | 27 | Finance |
Eve | 24 | HR |
Frank | 26 | IT |
開窗函數
在 SQL 中,開窗函數是一種強大的查詢工具,它允許我們在查詢中進行對分組數據進行計算、?同時保留原始行的詳細信息?。
開窗函數可以與聚合函數(如 SUM、AVG、COUNT 等)結合使用,但與普通聚合函數不同,開窗函數不會導致結果集的行數減少。
打個比方,可以將開窗函數想象成一種 “透視鏡”,它能夠將我們聚焦在某個特定的分組,同時還能看到整體的全景。
sum over
本節我們先講第一個開窗函數:sum over。
該函數用法為:
SUM(計算字段名) OVER (PARTITION BY 分組字段名)
示例
假設我們有訂單表?orders
,表格數據如下:
order_id | customer_id | order_date | total_amount |
---|---|---|---|
1 | 101 | 2023-01-01 | 200 |
2 | 102 | 2023-01-05 | 350 |
3 | 101 | 2023-01-10 | 120 |
4 | 103 | 2023-01-15 | 500 |
現在,我們希望計算每個客戶的訂單總金額,并顯示每個訂單的詳細信息。
示例 SQL 如下:
SELECT order_id, customer_id, order_date, total_amount,SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total_amount
FROMorders;
查詢結果:
order_id | customer_id | order_date | total_amount | customer_total_amount |
---|---|---|---|---|
1 | 101 | 2023-01-01 | 200 | 320 |
3 | 101 | 2023-01-10 | 120 | 320 |
2 | 102 | 2023-01-05 | 350 | 350 |
4 | 103 | 2023-01-15 | 500 | 500 |
在上面的示例中,我們使用開窗函數 SUM 來計算每個客戶的訂單總金額(customer_total_amount),并使用 PARTITION BY 子句按照customer_id 進行分組。從前兩行可以看到,開窗函數保留了原始訂單的詳細信息,同時計算了每個客戶的訂單總金額。
示例
假設我們有訂單表?orders
,表格數據如下:
order_id | customer_id | order_date | total_amount |
---|---|---|---|
1 | 101 | 2023-01-01 | 200 |
2 | 102 | 2023-01-05 | 350 |
3 | 101 | 2023-01-10 | 120 |
4 | 103 | 2023-01-15 | 500 |
現在,我們希望計算每個客戶的訂單總金額,并顯示每個訂單的詳細信息。
示例 SQL 如下:
SELECT order_id, customer_id, order_date, total_amount,SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total_amount
FROMorders;
查詢結果:
order_id | customer_id | order_date | total_amount | customer_total_amount |
---|---|---|---|---|
1 | 101 | 2023-01-01 | 200 | 320 |
3 | 101 | 2023-01-10 | 120 | 320 |
2 | 102 | 2023-01-05 | 350 | 350 |
4 | 103 | 2023-01-15 | 500 | 500 |
在上面的示例中,我們使用開窗函數 SUM 來計算每個客戶的訂單總金額(customer_total_amount),并使用 PARTITION BY 子句按照customer_id 進行分組。從前兩行可以看到,開窗函數保留了原始訂單的詳細信息,同時計算了每個客戶的訂單總金額。
sum over (partition … by … order by … asc)
注:這個是一個一個累加上去,并且按照order by 的升降序
示例
假設我們有訂單表?orders
,表格數據如下:
order_id | customer_id | order_date | total_amount |
---|---|---|---|
1 | 101 | 2023-01-01 | 200 |
2 | 102 | 2023-01-05 | 350 |
3 | 101 | 2023-01-10 | 120 |
4 | 103 | 2023-01-15 | 500 |
現在,我們希望計算每個客戶的歷史訂單累計金額,并顯示每個訂單的詳細信息。
SELECT order_id, customer_id, order_date, total_amount,SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS cumulative_total_amount
FROMorders;
結果將是:
order_id | customer_id | order_date | total_amount | cumulative_total_amount |
---|---|---|---|---|
1 | 101 | 2023-01-01 | 200 | 200 |
3 | 101 | 2023-01-10 | 120 | 320 |
2 | 102 | 2023-01-05 | 350 | 350 |
4 | 103 | 2023-01-15 | 500 | 500 |
在上面的示例中,我們使用開窗函數 SUM 來計算每個客戶的歷史訂單累計金額(cumulative_total_amount),并使用 PARTITION BY 子句按照 customer_id 進行分組,并使用 ORDER BY 子句按照 order_date 進行排序。從結果的前兩行可以看到,開窗函數保留了原始訂單的詳細信息,同時計算了每個客戶的歷史訂單累計金額;相比于只用 sum over,同組內的累加列名稱
rank
示例
假設我們有訂單表?orders
,表格數據如下:
order_id | customer_id | order_date | total_amount |
---|---|---|---|
1 | 101 | 2023-01-01 | 200 |
2 | 102 | 2023-01-05 | 350 |
3 | 101 | 2023-01-10 | 120 |
4 | 103 | 2023-01-15 | 500 |
現在,我們希望為每個客戶的訂單按照訂單金額降序排名,并顯示每個訂單的詳細信息。
SELECT order_id, customer_id, order_date, total_amount,RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS customer_rank
FROMorders;
查詢結果:
order_id | customer_id | order_date | total_amount | customer_rank |
---|---|---|---|---|
1 | 101 | 2023-01-01 | 200 | 1 |
3 | 101 | 2023-01-10 | 120 | 2 |
2 | 102 | 2023-01-05 | 350 | 1 |
4 | 103 | 2023-01-15 | 500 | 1 |
在上面的示例中,我們使用開窗函數 RANK 來為每個客戶的訂單按照訂單金額降序排名(customer_rank),并使用 PARTITION BY 子句按照 customer_id 進行分組,并使用 ORDER BY 子句按照 total_amount 從大到小進行排序。
可以看到,開窗函數保留了原始訂單的詳細信息,同時計算了每個客戶的訂單金額排名。
row_number
示例
假設我們有訂單表?orders
,表格數據如下:
order_id | customer_id | order_date | total_amount |
---|---|---|---|
1 | 101 | 2023-01-01 | 200 |
2 | 102 | 2023-01-05 | 350 |
3 | 101 | 2023-01-10 | 120 |
4 | 103 | 2023-01-15 | 500 |
現在,我們希望為每個客戶的訂單按照訂單金額降序排列,并且分配一個 row_number 編號,示例 SQL 語句如下:
SELECT order_id, customer_id, order_date, total_amount,ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS row_number
FROMorders;
結果將是:
order_id | customer_id | order_date | total_amount | row_number |
---|---|---|---|---|
4 | 103 | 2023-01-15 | 500 | 1 |
2 | 102 | 2023-01-05 | 350 | 1 |
1 | 101 | 2023-01-01 | 200 | 1 |
3 | 101 | 2023-01-10 | 120 | 2 |
在上面的示例中,我們使用開窗函數 ROW_NUMBER 為每個客戶的訂單按照訂單金額降序排列,并為每個訂單分配了一個編號(row_number),并使用 PARTITION BY 子句按照 customer_id 進行分組,并使用 ORDER BY 子句按照 total_amount 進行排序。
lag / lead
開窗函數 Lag 和 Lead 的作用是獲取在當前行之前或之后的行的值,這兩個函數通常在需要比較相鄰行數據或進行時間序列分析時非常有用。
1)Lag 函數
Lag 函數用于獲取?當前行之前?的某一列的值。它可以幫助我們查看上一行的數據。
Lag 函數的語法如下:
LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
參數解釋:
column_name
:要獲取值的列名。offset
:表示要向上偏移的行數。例如,offset為1表示獲取上一行的值,offset為2表示獲取上兩行的值,以此類推。default_value
:可選參數,用于指定當沒有前一行時的默認值。PARTITION BY
和ORDER BY
子句可選,用于分組和排序數據。
2)Lead 函數
Lead 函數用于獲取?當前行之后?的某一列的值。它可以幫助我們查看下一行的數據。
Lead 函數的語法如下:
LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
參數解釋:
column_name
:要獲取值的列名。offset
:表示要向下偏移的行數。例如,offset為1表示獲取下一行的值,offset為2表示獲取下兩行的值,以此類推。default_value
:可選參數,用于指定當沒有后一行時的默認值。PARTITION BY
和ORDER BY
子句可選,用于分組和排序數據。
示例
以下是一個示例,假設我們有一個學生成績表scores
,其中包含學生的成績和考試日期:
student_id | exam_date | score |
---|---|---|
101 | 2023-01-01 | 85 |
101 | 2023-01-05 | 78 |
101 | 2023-01-10 | 92 |
101 | 2023-01-15 | 80 |
現在我們想要查詢每個學生的考試日期和上一次考試的成績,以及下一次考試的成績,示例 SQL 如下:
SELECT student_id,exam_date,score,LAG(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_score,LEAD(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_score
FROMscores;
結果將是:
student_id | exam_date | score | previous_score | next_score |
---|---|---|---|---|
101 | 2023-01-01 | 85 | NULL | 78 |
101 | 2023-01-05 | 78 | 85 | 92 |
101 | 2023-01-10 | 92 | 78 | 80 |
101 | 2023-01-15 | 80 | 92 | NULL |
在上面的示例中,我們使用 Lag 函數獲取每個學生的上一次考試成績(previous_score),使用 Lead 函數獲取每個學生的下一次考試成績(next_score)。如果沒有上一次或下一次考試,對應的列將顯示為 NULL。