---
title: 'Postgresql函數 & 基本SQL語法紀錄'
disqus: hackmd
---
# X. 子查詢運算子-Exists
* **作用**：檢查**某個子查詢是否有結果**
* **重點**：它不管子查詢回傳什麼資料，**只在意有沒有資料**
* **語法**：
```sql=
SELECT EXISTS (
SELECT 1 FROM table_name WHERE conditions
);
```
* `SELECT 1`是**習慣用法**，只要子查詢有資料就會回傳`true`
* `EXISTS` 會在**發現第一筆資料**後就**停止查詢**，**效率**通常比`COUNT(*) > 0`**好**
* **例**：**判斷某筆資料是否存在**
```sql=
SELECT EXISTS (
SELECT 1 FROM users WHERE username = 'nicolas'
);
```
* **例**： 在**條件式**中使用 `EXISTS` 做**邏輯判斷**
```sql=
DELETE FROM orders
WHERE EXISTS (SELECT 1 FROM users WHERE
users.id = orders.user_id AND
users.status = 'banned'
);
```
* **例**：搭配 `NOT EXISTS` 查找「孤兒資料」
```sql=
SELECT *
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.product_id = p.id
);
```
* 查找返回`False`
* **例**：**在 JOIN 無法簡單解的情境下，EXISTS 是救星**，找出**有修過 CS101 的學生**。
```sql=
SELECT name
FROM students s
WHERE EXISTS (
SELECT 1 FROM enrollments e WHERE
e.student_id = s.id AND
e.course_id = 'CS101'
);
```
# X. 模擬「最大值」查詢 - NOT EXISTS + HAVING
* **代碼**：
```sql=
SELECT s.name
FROM students s
JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name
HAVING NOT EXISTS (
SELECT 1
FROM enrollments e2
GROUP BY e2.student_id
HAVING COUNT(*) > COUNT(e.student_id)
);
```
* `GROUP BY s.id, s.name`： **把每個學生的資料「合併」**
* `COUNT(*) > COUNT(e.student_id)`：**比較「別人」選的課數 >「我」選的課數**
* `COUNT(*)`：**子查詢** (`e2`) 的**結果**
* `COUNT(e.student_id)`：**外層查詢**的**結果**
* **其他**：
* **最小值**：`HAVING COUNT(*) < COUNT(e.student_id)`
# X. 時間字段擷取-EXTRACT
* **語法**：
* `extract(field from timestamp)`
* `extract(field from interval)`
* **例**：**擷取天**
* select **extract**(`day` from now())
* **字串**(`轉成 timestamp`)：select **extract**(`day` from timestamp '2013-04-13')
* **例**：擷取相差**秒數**(`epoch`)
* select extract(`epoch` from `(timestamp '2012-09-02 00:00:00' - timestamp '2012-08-31 01:00:00')`)
* **回傳**`double precision`
* **參考**：https://www.postgresql.org/docs/9.2/functions-datetime.html
# X. 字串 to 時間
* **例**：**字串 to timestamp**
* select `timestamp '2013-04-13'`
* select `'2013-04-13'::timestamp`
* select `cast('2012-08-31 01:00:00' as timestamp);`
# X. 產生連續值的序列-generate_series()
* 數字序列、日期序列
* 語法：
```sql=
generate_series(start, stop [, step])
```
* **參數**：
* start: 起始值（可以是整數、timestamp）
* stop: 結束值
* step: 步長（預設是 1，interval 可自定）
## 1. 整數序列
* **代碼**：產生`1 ~ 10`
```sql=
SELECT generate_series(1, 10);
```
## 2. 時間序列
* **代碼**：產生`01/1 ~ 01/31`，**每天**(`'1 day'::interval`)
```sql=
SELECT generate_series(
'2025-01-01'::timestamp,
'2025-01-31'::timestamp,
'1 day'::interval
) AS time_point;
```
### 2.1 時間序列-每月月底
* **正確**：
```sql=
select (month + interval '1 month')- interval '1 day' ,
from (
select generate_series(
timestamp '2012-01-01',
timestamp '2012-12-01',
interval '1 month'
) as month
) a
```
* **錯誤**：
```sql=
SELECT generate_series(
'2012-01-31 00:00:00'::timestamp,
'2012-12-31 00:00:00'::timestamp,
'1 month'::interval
) AS time_point
```
* **結果**：`與預期的「每個月月底」不符`
```yaml=
2012-01-31 00:00:00
2012-02-29 00:00:00
2012-03-29 00:00:00
2012-04-29 00:00:00
...
2012-12-29 00:00:00
```
* **問題原因解析**：
* 它會「原樣保留」起始日的「日（day）」數字。
* 當**該月**`沒有這一天`，會 **「往前回推」** 到**當月**`最後『合法』日期`
* **例**：
* 1/31 + 1 month → 2/29（閏年，合法）
* `2/29 + 1 month` → `3/29`（`不是月底，因為是 2/29 + 1 month`）
# X. 日期時間型別-Interval
* `INTERVAL` 表示 **『時間的長度』** 或 **『時間差值』**
* **例**：
* 1 day
* 3 hours
* 5 minutes 30 seconds
* 不像 `timestamp` 是**一個時間點**，`interval` 是一個 **『持續多久』**
* **常見單位**：
| 單位 | 用法 |
| -------- | ----------------------- |
| `second` | `INTERVAL '30 seconds'` |
| `minute` | `INTERVAL '15 minutes'` |
| `hour` | `INTERVAL '4 hours'` |
| `day` | `INTERVAL '7 days'` |
| `month` | `INTERVAL '2 months'` |
| `year` | `INTERVAL '1 year'` |
* **時間加減**
* **返回**`Interval`
* **例**：select `timestamp '2012-08-31 01:00:00' - timestamp '2012-07-30 01:00:00'`
* **返回**：`32 days`
* 時間加減`Interval`
* **返回**`時間`
* 例：select timestamp '2012-08-31 01:00:00' + `'1 day'::interval`
* **返回**：`2012-09-01 01:00:00.000`
# X. 各種時間/日期運算的語法與結果清單
## 1. 加法運算
| 運算類型 | 結果類型 | 說明 |
| ---------------------- | ----------- | ------------------- |
| `date + integer` | `date` | 加上天數（單位是天） |
| `date + interval` | `timestamp` | 日期加時間段，轉成 timestamp |
| `timestamp + interval` | `timestamp` | 加上時間段，結果保留時間 |
| `time + interval` | `time` | 時間加時間段，不含日期 |
| `interval + interval` | `interval` | 相加組合時間段 |
## 2. 減法運算
| 運算類型 | 結果類型 | 說明 |
| ----------------------- | ----------- | ------------------ |
| `date - date` | `integer` | 計算兩日期間的天數差（回傳整數） |
| `date - integer` | `date` | 減去天數（單位是天） |
| `date - interval` | `timestamp` | 減去時間段，轉成 timestamp |
| `time - time` | `interval` | 計算時間差 |
| `time - interval` | `time` | 減去時間段 |
| `timestamp - interval` | `timestamp` | 減去時間段 |
| `interval - interval` | `interval` | 兩個時間段相減 |
| `timestamp - timestamp` | `interval` | 計算時間差（精確到秒） |
## 3. 程法運算
| 運算類型 | 結果類型 | 說明 |
| -------------------- | ---------- | --------------- |
| `numeric * interval` | `interval` | 擴大時間段，例如 3.5 小時 |
| `integer * interval` | `interval` | 同上，整數倍 |
## 4. 除法運算
| 運算類型 | 結果類型 | 說明 |
| -------------------- | ---------- | ---------------- |
| `interval / numeric` | `interval` | 把時間段除以倍數，等於縮短時間段 |
# X. 分層彙總（Hierarchical Aggregation）- ROLLUP
* 只能用在標準的 `GROUP BY`，不能混用 `GROUPING SETS` 和 `CUBE` 一起用（會混亂）
* **使用**：
| 你要的分析 | 寫法 |
| ----------------- | ---------------------------------------- |
| 每月/每季/每年營收 + 年度總和 | `GROUP BY ROLLUP (year, month)` |
| 每個業務員業績 + 部門總和 | `GROUP BY ROLLUP (department, employee)` |
| 商品每週銷售量 + 商品總銷售 | `GROUP BY ROLLUP (product_id, week)` |
# X.「截斷」日期/時間到指定的時間粒度-date_trunc
* **作用**：來 **「截斷」** 日期/時間到**指定的時間粒度**，回傳**截斷後的時間**
* **函數**：`date_trunc(text, timestamp)`
* 例：`2025-05-27 13:45:30`
* 截斷到`月`：SELECT `date_trunc('month', TIMESTAMP '2025-05-27 13:45:30');`
* **返回**：`2025-05-01 00:00:00`
* 截斷到`天`：SELECT `date_trunc('day', TIMESTAMP '2025-05-27 13:45:30');`
* **返回**：`2025-05-27 00:00:00`
* 截斷到`小時`：SELECT `date_trunc('hour', TIMESTAMP '2025-05-27 13:45:30');`
* **返回**：`2025-05-27 13:00:00`
* 截斷到`分鐘`：SELECT `date_trunc('minute', TIMESTAMP '2025-05-27 13:45:30');`
* **返回**：`2025-05-27 13:45:00`
* **使用場景**：
* **日期分組**：例如你想做某月、某天、某小時的統計時，把時間截到月初、日初、或小時初，**方便**`GROUP BY`
* **時間區間比較**：判斷兩筆時間是否落在同一週、同一月、同一天等
* **日期計算基準點**：如剛剛提的當月天數，**先算出當月第一天**，方便往後加減天數
# X. 窗口函數 over() - 滾動範圍語法
* **語法**：
```sql=
<aggregate_function>(column) OVER (
PARTITION BY partition_column -- 可選
ORDER BY sort_column -- 必要
ROWS|RANGE BETWEEN <start> AND <end>
)
```
* **語法片段**：
| 語法片段 | 說明 |
| ----------------- | ------------------------- |
| `PARTITION BY` | 對每個分組獨立計算（例如：每個使用者、每個地區） |
| `ORDER BY` | 必須，指定資料的時間或順序 |
| `ROWS` / `RANGE` | **定義視窗的方式**（**列數** vs **數值範圍**） |
| `BETWEEN A AND B` | **滾動**的`開始`與`結束`位置（例如：往前 14 天到今天） |
* **ROWS** VS **RANGE**
| 類型 | 說明 | 使用時機 |
| ------- | -------- | ---------------------- |
| `ROWS` | 精確的列數 | 適合資料間隔均勻 |
| `RANGE` | 根據排序欄位的值 | 適合時間戳不連續、使用 `INTERVAL` |
* **起始和結束的關鍵字**：
| 關鍵字語法 | 中文意思 | 範圍代表什麼 |
| --------------------- | -------- | --------------------- |
| `UNBOUNDED PRECEDING` | 最前面那筆開始 | 從 partition 的開頭開始 |
| `n PRECEDING` | **往前**第 n 筆 | 目前這一列**往前數** n 筆（不包含目前這筆） |
| `CURRENT ROW` | **當前這一筆** | 僅包含目前這一筆 |
| `n FOLLOWING` | **往後**第 n 筆 | 目前這一列**往後數** n 筆（包含目前這筆） |
| `UNBOUNDED FOLLOWING` |最後面那筆為止 | 直到 partition 的結尾 |
## 1. 常見滾動範圍寫法範例
* **往前**` 14 筆` + **當前筆**（`共 15 筆的視窗`）
* **ROWS** BETWEEN `UNBOUNDED PRECEDING` AND `CURRENT ROW`
* **從開頭到目前這筆**（累積平均、累積總和）
* **ROWS** BETWEEN `UNBOUNDED PRECEDING` AND `CURRENT ROW`
* **從目前這筆到結尾**（累積向後）
* **ROWS** BETWEEN `CURRENT ROW` AND `UNBOUNDED FOLLOWING`
* **往後 3 筆**（不含自己）
* ROWS BETWEEN `1 FOLLOWING` AND `3 FOLLOWING`
* **計算 15 天滾動平均收入**（`需要 RANGE 搭配 INTERVAL，PostgreSQL`）
* **RANGE** BETWEEN `INTERVAL '14 days' PRECEDING` AND `CURRENT ROW`
# X. 窗口函數 over() - 配合的函數
## 1. 聚合型窗口函數 (Aggregate Window Functions)
這類聚合函數在 `OVER()` 下不縮減筆數，而是在窗口上做計算。
- `SUM()`
- `AVG()`
- `MIN()`
- `MAX()`
- `COUNT()`
## 2. 排名與序列函數 (Ranking and Row Number Functions)
* 用來給資料排序、排名、編號。
* `ROW_NUMBER()`：排名相同時不重複，按順序排序
* `RANK()`：排名相同時重複，總數不會變
* `DENSE_RANK()`：排名相同時重複，總數減少
* `NTILE(n)`：資料平均分組**成 N 等份**
* 把一個集合數據，分成**N組**(`等份`)
## 3. 偏移與導航函數 (Offset and Navigation Functions)
* 用來在分區內取**前一筆**、**下一筆**或指定偏移的值。
* **前一筆**：`LAG(column, offset, default)`
* **下一筆**：`LEAD(column, offset, default)`
* 例：[Consecutive Numbers](https://leetcode.com/problems/consecutive-numbers/) 取前後一筆
``` sql=
SELECT
Num,
LAG(Num) OVER (ORDER BY Id) AS prev_num,
LEAD(Num) OVER (ORDER BY Id) AS next_num
FROM Logs
```
## 4. 統計與分布函數 (Statistical and Distribution Functions)
* `CUME_DIST()`：**累積分布百分比（Cumulative Distribution）**
* **回傳值範圍**：`0 ~ 1`
* 不會重複，每筆記錄都代表**累加的百分比**
* **應用場景**：
* 分析某商品在類別中價位分布百分比
* 判斷用戶消費佔比前幾%的 VIP 分群
* `PERCENT_RANK()`：**百分比排名**
* 計算目前行的百分比排名（`依據排名位置，而非值的分布`）
* **回傳值範圍**：`0 ~ 1`
* 第一筆永遠是 0
* **應用場景**：
* 中位數分層
* 前後段班評比
* `FIRST_VALUE(column)`：取得**分組**中**排序**後的**第一筆值**
* **應用場景**：
* 每個用戶的**第一筆**交易金額
* **最早**建立時間的產品價格
* `LAST_VALUE(column)`：取得**分組**中**排序**後的**最後一筆值**
* **應用場景**：
* 每個會員**最近一次**登入時間或 IP
* **最新**的產品價格
* `NTH_VALUE(column, n)`：取得**排序後**的**第 N 筆值**
* **應用場景**：
* 找出每個產品上架的**第3筆**價格
* 使用者的**第N筆**操作或交易紀錄
## 5. 使用注意
* `OVER()` 裡可以加 `PARTITION BY` 來分區，`ORDER BY` 來排序。
* Window Functions 不會減少結果筆數。
* 無法用在 `WHERE`，但能用在 `SELECT`、`ORDER BY`
# X. 整數四捨五入
## 1. 整數捨入技巧(integer rounding trick)
* **公式**：`((數值 + 基準/2) / 基準) * 基準`
* **四捨五入到最接近的「基準倍數」**
* **例**：`((x + 10/2 ) / 10 ) * 10`
* **概念**：
* **四捨五入的關鍵動作**：數據+半個基準
* 整數除法**自動捨棄小數**，會達到「四捨五入」效果
## 2. round()
* **公式**：`ROUND(數值 / 基準(小數)) * 基準`
* **例**：`ROUND(x / 10.0) * 10`
# X. `GROUP BY` 欄位選擇策略
## 1. `GROUP BY` 欄位選擇規則
| `GROUP BY` 條件 | SELECT 可否用其他欄位 | 原因說明 |
|----------------------------------|------------------------|----------------------------------------------|
| 欄位是 `Primary Key` (PK) | ✅ 可以 | PK 唯一識別一筆資料，其他欄位具**函數相依性** |
| 欄位是 `Unique Key` (UK) | ✅ 可以 | UK 同上，唯一性保障函數相依 |
| 是組合唯一鍵（Composite PK/UK） | ✅ 可以 | 若組合唯一，其他欄位也有穩定相依性 |
| 非唯一欄位（例如 `name`） | ❌ 不可以 | 不保證唯一性，可能產生多筆不穩定結果 |
## 2. 什麼是函數相依（Functional Dependency）？
* **概述**：若欄位 A 唯一**決定**欄位 B 的值，則稱 **B 函數相依於 A**
* 這表示當你 `GROUP BY` 某欄位（如 facid），若 facid 能唯一對應出 name、monthlymaintenance，PostgreSQL 就允許你直接 SELECT 它們。
* **代碼**：
```sql
-- ✅ facid 是唯一的，name 與 monthlymaintenance 就能被 SELECT
SELECT facid, name, monthlymaintenance, SUM(slots)
FROM cd.facilities f
JOIN cd.bookings b ON f.facid = b.facid
GROUP BY facid;
```
# X. 查看數據類型-pg_typeof()
* **函數**：**pg_typeof**(`col`)
* **例**：
```sql=
SELECT pg_typeof(id), pg_typeof(name), pg_typeof(created_at)
FROM your_table
LIMIT 1;
```
# X. 字串「補滿到指定長度」- lpad() & rpad()
* 字元**補**在
* **左邊**（`lpad`）
* **右邊**（`rpad`）
* **語法**：
```sql=
lpad(string text, length int [, fill text])
rpad(string text, length int [, fill text])
```
* **參數**：
* `string`：原始字串
* `length`：最終要**補到的總長度**（`字元數`）
* `fill`：**要補上的字元**（`預設是空格 ' '`）
* **例**：
* `左邊`**補**`0`到**6個字元**：`SELECT lpad('123', 6, '0'); -- 結果：'000123'`
* `右邊`**補**`0`到**6個字元**：`SELECT rpad('ABC', 6, '0'); -- 結果：'ABC '`
# X. 字符替換-TRANSLATE
* **多字符一對一替換**
* **情境**：
* **只想改字元**，比如：`a → x、b → y`
* 移除**幾個特定符號**
* **語法**：`translate ( string text, from text, to text )`
* **建議**：`from` & `to` 的**長度**建議**一樣**
* **例**：SELECT `TRANSLATE('a1b2c3', 'abc', 'xyz');`
* **結果**： `x1y2z3`
* 把 `'a'` 換成 `'x'`，`'b'` 換成 `'y'`，`'c'` 換成 `'z'`
* **例**：SELECT `TRANSLATE('(02)-1234/5678', '()/-', '');`
* **結果**：`0212345678`
# X. 字符替換-REGEXP_REPLACE
* **針對樣式或整段字串替換**
* **情境**：
* 處理**一堆奇怪的格式**、刪**特定 pattern**
* 刪掉**非數字、非字母字元**
* **例**：SELECT `TRANSLATE('(02)-1234/5678', '[^0-9]', '');`
* **結果**：`0212345678`
# X. 遞迴查詢（recursive query）- WITH RECURSIVE
* **作用**：處理**階層資料結構**（`樹狀結構、家族譜、組織架構、目錄等`）
* **語法**：
```sql=
WITH RECURSIVE cte_name (column1, column2, ...) AS (
-- Anchor member: 基礎查詢（起始點）
SELECT ...
FROM ...
WHERE ...
UNION [ALL]
-- Recursive member: 遞迴查詢（會引用前一層的結果）
SELECT ...
FROM some_table
JOIN cte_name ON ...
)
SELECT * FROM cte_name;
```
* **說明**：
| 組件 | 說明 |
| ---------------- | ------------------------------------------- |
| `WITH RECURSIVE` | 宣告一個具名的遞迴 CTE（Common Table Expression） |
|**Anchor member** | 遞迴的起點，**只跑一次** |
| **Recursive member** | 遞迴的本體，每次會用**上次結果**再去 `JOIN` 或`處理新的資料` |
| `UNION ALL` | **合併** `Anchor` 和 `Recursive` 的結果集（可用 `UNION` 去除重複） |
* **注意**：
* **遞迴終止條件**：不需要明確寫出終止條件，因為遞迴**無法 JOIN 到更多資料時**就會**自動停止**
* **避免無窮遞迴**：小心循環依賴，否則會爆 stack（PostgreSQL 預設遞迴最大 100 層，可調整 max_recursion_depth）。
* **性能**：**複雜的遞迴查詢**可考慮 `materialized views`、或`一次性展平表格`換取效能。
# X. 數值 & 日期格式化 - TO_CHAR
* [官網](https://www.postgresql.org/docs/17/functions-formatting.html)
## 1. 數值格式化
* **基本符號**
| 符號 | 說明 | 實例 |
| --- | --------------- | ------------------------------------- |
| `9` | **可省略位**（`不顯示前導空白`） | `TO_CHAR(5, '999') ➜ ' 5'` |
| `0` | **不可省略**，**強制**`補零` | `TO_CHAR(5, '000') ➜ '005'` |
| `.` | 小數點（非 locale 版） | `TO_CHAR(5.5, '999.99') ➜ ' 5.50'` |
| `,` | 千分位分隔符 | `TO_CHAR(12345, '99,999') ➜ '12,345'` |
* **例**：
* **整數9位，小數兩位(補0)**：`FM999999990.00`
* **小數點前**是`0`，避免數值**只有小數的部分**，格式化後，**整數第一位**可以**補0**
* `FM`(`修飾符`)：**清除前綴空格**(`整數部分，未達格式設定位數時，會有空格`)
# X. LATERAL 子查詢
* **用在子查詢**（`subquery`），它讓**子查詢**可以 **『參考 同一層查詢裡 前面表的欄位』**
* **子查詢**能 **『拿外層變數用』**
* **例**：針對 `table_a` 的每一筆資料，去 `table_b` 找**對應**的`最新一筆 val`
```sql
SELECT a.id, b.val
FROM table_a a
JOIN LATERAL (
SELECT val
FROM table_b
WHERE table_b.a_id = a.id
ORDER BY created_at DESC
LIMIT 1
) b ON true;
```
* **子查詢裡**的 `a.id` 來自**外層表**
* `JOIN`可以使用
* `{ [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN` + `LATERAL`
* `ON true;`：**無條件成立的 JOIN**
* **本質上是JOIN表，所以需要JOIN寫條件**
* 場景：
* 找`最新`或`最相關`的**子資料**（`One-to-Many場景`）
* **簡化**`複雜的聚合`或`分組查詢`
###### tags: `Postgresql` `sql`
Sign in with Wallet
Connect another wallet