---
# System prepended metadata

title: Postgresql函數 & 基本SQL語法紀錄
tags: [PostgreSQL, Sql]

---

---
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`