CREATE TABLE product_info (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    tags        TEXT[],                    -- 數(shù)組類型
    attributes  JSONB,                     -- JSON 二進(jìn)制類型
    price       NUMERIC(10,2),
    status      SMALLINT DEFAULT 1,
    created_at  TIMESTAMP DEFAULT now(),
    geo_point   POINT                      -- 幾何點(diǎn)類型
);

-- 字符串拼接，兩種方式都行
SELECT concat(first_name, ' ', last_name) AS full_name FROM users;
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- 字符串截取和定位
SELECT substring(description FROM 1 FOR 100) AS brief FROM articles;
SELECT position('error' IN log_message) AS pos FROM app_logs;

-- 正則替換，批量清洗數(shù)據(jù)時(shí)很有用
SELECT regexp_replace(phone, '(\d{3})\d{4}(\d{4})', '\1****\2') AS masked_phone
FROM user_contacts;

-- 按分隔符拆分成多行
SELECT unnest(string_to_array('apple,banana,cherry', ',')) AS fruit;

-- 日期加減
SELECT now() + INTERVAL '30 days' AS next_month;
SELECT created_at - INTERVAL '7 days' AS week_ago FROM orders;

-- 取日期部分
SELECT date_trunc('month', created_at) AS month_start,
       count(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

-- 兩個(gè)日期的間隔
SELECT age(now(), created_at) AS account_age FROM users WHERE id = 1;

-- 提取星期幾、第幾周
SELECT extract(dow FROM now()) AS day_of_week,
       extract(week FROM now()) AS week_number;

-- 各部門內(nèi)按薪資排名
SELECT department_id,
       employee_name,
       salary,
       RANK() OVER w AS rank_num,
       DENSE_RANK() OVER w AS dense_rank_num,
       ROW_NUMBER() OVER w AS row_num
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);

SELECT month,
       revenue,
       LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue, 1) OVER (ORDER BY month) AS diff,
       ROUND(
           (revenue - LAG(revenue, 1) OVER (ORDER BY month))::numeric
           / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0) * 100, 2
       ) AS growth_pct
FROM monthly_revenue;

-- 計(jì)算累計(jì)銷售額（Running Total）
SELECT order_date,
       daily_total,
       SUM(daily_total) OVER (ORDER BY order_date) AS cumulative
FROM daily_sales;

-- 每個(gè)部門的薪資占比
SELECT employee_name,
       department_id,
       salary,
       ROUND(salary::numeric / SUM(salary) OVER (PARTITION BY department_id) * 100, 2) AS dept_pct
FROM employees;

SELECT d.department_name, e.employee_name, e.salary
FROM departments d
JOIN LATERAL (
    SELECT employee_name, salary
    FROM employees
    WHERE department_id = d.id
    ORDER BY salary DESC
    LIMIT 3
) e ON true;

-- 關(guān)聯(lián)子查詢寫法：只能在 SELECT 里返回單個(gè)值
SELECT d.department_name,
       (SELECT employee_name FROM employees
        WHERE department_id = d.id
        ORDER BY salary DESC LIMIT 1) AS top_employee
FROM departments d;

-- LATERAL JOIN 寫法：可以返回多列多行
SELECT d.department_name, e.employee_name, e.salary, e.hire_date
FROM departments d
JOIN LATERAL (
    SELECT employee_name, salary, hire_date
    FROM employees
    WHERE department_id = d.id
    ORDER BY salary DESC
    LIMIT 3
) e ON true;

SELECT u.username, u.phone, o.order_no, o.amount, o.created_at
FROM users u
LEFT JOIN LATERAL (
    SELECT order_no, amount, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 1
) o ON true
WHERE u.status = 1;

SELECT w.warehouse_name, d.station_name, d.distance_km
FROM warehouses w
JOIN LATERAL (
    SELECT station_name,
           ROUND(
               (point(d.longitude, d.latitude) <-> point(w.longitude, w.latitude))::numeric, 2
           ) AS distance_km
    FROM delivery_stations d
    ORDER BY point(d.longitude, d.latitude) <-> point(w.longitude, w.latitude)
    LIMIT 3
) d ON true;

-- 用 CTE 拆分復(fù)雜邏輯
WITH active_users AS (
    SELECT id, username, last_login
    FROM users
    WHERE status = 1
      AND last_login > now() - INTERVAL '30 days'
),
user_orders AS (
    SELECT u.id AS user_id,
           u.username,
           count(o.id) AS order_count,
           sum(o.amount) AS total_amount
    FROM active_users u
    LEFT JOIN orders o ON o.user_id = u.id
    GROUP BY u.id, u.username
)
SELECT *
FROM user_orders
WHERE total_amount > 1000
ORDER BY total_amount DESC;

-- 遞歸查某個(gè)節(jié)點(diǎn)的所有下級(jí)
WITH RECURSIVE dept_tree AS (
    -- 起點(diǎn)：根節(jié)點(diǎn)
    SELECT id, name, parent_id, 1 AS level, name::text AS path
    FROM departments
    WHERE id = 1

    UNION ALL

    -- 遞歸：找下級(jí)
    SELECT d.id, d.name, d.parent_id, t.level + 1,
           t.path || ' > ' || d.name
    FROM departments d
    JOIN dept_tree t ON d.parent_id = t.id
)
SELECT * FROM dept_tree ORDER BY path;

-- 插入 JSONB 數(shù)據(jù)
INSERT INTO user_profiles (user_id, profile) VALUES
(1, '{"name": "張三", "age": 28, "skills": ["Java", "Python", "SQL"], "address": {"city": "北京", "district": "朝陽(yáng)"}}'),
(2, '{"name": "李四", "age": 32, "skills": ["Go", "Rust"], "address": {"city": "上海", "district": "浦東"}}');

-- 提取單個(gè)字段
SELECT profile->>'name' AS name,
       (profile->'address'->>'city') AS city
FROM user_profiles;

-- 條件查詢（支持索引）
SELECT * FROM user_profiles
WHERE profile @> '{"address": {"city": "北京"}}';

-- 檢查數(shù)組是否包含某個(gè)元素
SELECT profile->>'name' AS name
FROM user_profiles
WHERE profile->'skills' ? 'Python';

-- 更新 JSONB 中的某個(gè)字段
UPDATE user_profiles
SET profile = jsonb_set(profile, '{age}', '29')
WHERE user_id = 1;

-- 刪除 JSONB 中的某個(gè) key
UPDATE user_profiles
SET profile = profile - 'address'
WHERE user_id = 2;

CREATE INDEX idx_profile_gin ON user_profiles USING GIN (profile);

-- 把每個(gè)部門的員工聚合成 JSON 數(shù)組
SELECT department_id,
       jsonb_agg(
           jsonb_build_object(
               'id', id,
               'name', employee_name,
               'salary', salary
           )
       ) AS employees
FROM employees
GROUP BY department_id;

-- 把配置項(xiàng)的 key-value 聚合成一個(gè) JSON 對(duì)象
SELECT jsonb_object_agg(config_key, config_value) AS settings
FROM app_config
WHERE app_name = 'order-service';

-- 組裝用戶完整畫像
SELECT jsonb_build_object(
    'user_id', u.id,
    'username', u.username,
    'tags', COALESCE(
        (SELECT jsonb_agg(t.tag_name)
         FROM user_tags t WHERE t.user_id = u.id), '[]'::jsonb
    ),
    'recent_orders', COALESCE(
        (SELECT jsonb_agg(
            jsonb_build_object(
                'order_no', o.order_no,
                'amount', o.amount,
                'created_at', to_char(o.created_at, 'YYYY-MM-DD')
            )
         )
         FROM (SELECT * FROM orders WHERE user_id = u.id
               ORDER BY created_at DESC LIMIT 5) o
        ), '[]'::jsonb
    )
) AS user_profile
FROM users u
WHERE u.id = 1024;

-- 關(guān)系表轉(zhuǎn) JSON：把查詢結(jié)果轉(zhuǎn)成 JSON 數(shù)組
SELECT jsonb_agg(to_jsonb(t))
FROM (
    SELECT id, product_name, price, stock
    FROM products
    WHERE category = '電子產(chǎn)品'
    ORDER BY price DESC
    LIMIT 10
) t;

-- JSON 轉(zhuǎn)關(guān)系表：把 JSON 數(shù)組展開成多行
SELECT item->>'name' AS product_name,
       (item->>'price')::numeric AS price,
       (item->>'quantity')::int AS quantity
FROM (
    SELECT jsonb_array_elements(order_items) AS item
    FROM orders
    WHERE order_no = 'ORD-2025-001'
) t;

-- row_to_json 把整行記錄轉(zhuǎn)成 JSON
SELECT row_to_json(t) FROM (
    SELECT id, username, email, created_at
    FROM users WHERE id = 1
) t;

CREATE TABLE work_orders (
    id            BIGSERIAL PRIMARY KEY,
    order_no      VARCHAR(50) NOT NULL,
    order_type    VARCHAR(30) NOT NULL,
    submitter_id  BIGINT NOT NULL,
    status        SMALLINT DEFAULT 0,
    extra_fields  JSONB DEFAULT '{}',
    created_at    TIMESTAMP DEFAULT now()
);

-- IT 報(bào)修工單
INSERT INTO work_orders (order_no, order_type, submitter_id, extra_fields)
VALUES ('WO-2025-001', 'IT_REPAIR', 101,
    '{"device_id": "PC-1024", "fault_type": "藍(lán)屏", "location": "3樓會(huì)議室", "urgency": "high"}');

-- 行政申請(qǐng)工單
INSERT INTO work_orders (order_no, order_type, submitter_id, extra_fields)
VALUES ('WO-2025-002', 'ADMIN_REQUEST', 205,
    '{"request_type": "辦公用品", "budget_code": "BUD-2025-Q2", "approval_chain": ["張經(jīng)理", "李總監(jiān)"], "estimated_cost": 1500}');

-- 按類型查詢，直接取 JSONB 里的字段
SELECT order_no,
       extra_fields->>'device_id' AS device_id,
       extra_fields->>'fault_type' AS fault_type
FROM work_orders
WHERE order_type = 'IT_REPAIR'
  AND extra_fields->>'urgency' = 'high';

-- 查看當(dāng)前兼容模式
SHOW db_compatibility;

-- 創(chuàng)建數(shù)據(jù)庫(kù)時(shí)指定兼容模式
CREATE DATABASE myapp_db WITH db_compatibility = 'oracle';
CREATE DATABASE myapp_db WITH db_compatibility = 'mysql';

-- DUAL 表
SELECT sysdate FROM dual;

-- NVL 函數(shù)（等同于 COALESCE）
SELECT NVL(phone, '未填寫') FROM users;

-- DECODE 函數(shù)
SELECT DECODE(status, 1, '啟用', 0, '禁用', '未知') FROM accounts;

-- 字符串連接用 ||
SELECT '工號(hào): ' || emp_id || ' 姓名: ' || emp_name FROM employees;

-- ROWNUM 偽列（限制行數(shù)）
SELECT * FROM orders WHERE ROWNUM <= 10;

-- TO_CHAR / TO_DATE
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') FROM orders;
SELECT TO_DATE('2025-06-01', 'YYYY-MM-DD') + 30 FROM dual;

-- IFNULL
SELECT IFNULL(nickname, username) AS display_name FROM users;

-- LIMIT 語(yǔ)法
SELECT * FROM products ORDER BY price DESC LIMIT 20 OFFSET 40;

-- GROUP_CONCAT（等同于 STRING_AGG）
SELECT department, GROUP_CONCAT(employee_name SEPARATOR ', ')
FROM employees
GROUP BY department;

-- 反引號(hào)引用標(biāo)識(shí)符
SELECT `order`.`id`, `user`.`name`
FROM `order`
JOIN `user` ON `order`.`user_id` = `user`.`id`;

INSERT INTO user_scores (user_id, score, updated_at)
VALUES (1, 95, now())
ON CONFLICT (user_id)
DO UPDATE SET score = EXCLUDED.score, updated_at = EXCLUDED.updated_at;

-- 返回第一個(gè)非 NULL 的值
SELECT COALESCE(phone, email, '無聯(lián)系方式') AS contact FROM users;

-- 聚合時(shí)處理 NULL
SELECT department,
       COALESCE(SUM(bonus), 0) AS total_bonus
FROM salaries
GROUP BY department;

-- 把多行拼成一個(gè)字符串
SELECT department,
       STRING_AGG(employee_name, ', ' ORDER BY employee_name) AS members
FROM employees
GROUP BY department;

-- 當(dāng)只需要判斷存在性時(shí)，EXISTS 通常比 IN 更高效
SELECT u.username
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
      AND o.created_at > now() - INTERVAL '7 days'
);

-- 一條 SQL 統(tǒng)計(jì)多個(gè)維度的數(shù)據(jù)
SELECT department,
       COUNT(*) AS total,
       COUNT(*) FILTER (WHERE status = 'active') AS active_count,
       COUNT(*) FILTER (WHERE salary > 20000) AS high_salary_count,
       AVG(salary) FILTER (WHERE status = 'active') AS avg_active_salary
FROM employees
GROUP BY department;

-- 創(chuàng)建物化視圖
CREATE MATERIALIZED VIEW mv_dashboard_stats AS
SELECT
    date_trunc('day', created_at) AS stat_date,
    count(*) AS order_count,
    sum(amount) AS total_amount,
    count(DISTINCT user_id) AS unique_buyers,
    avg(amount) AS avg_order_amount
FROM orders
WHERE status IN (1, 2, 3)
GROUP BY 1
ORDER BY 1;

-- 創(chuàng)建索引提升物化視圖的查詢速度
CREATE INDEX idx_mv_stats_date ON mv_dashboard_stats (stat_date);

-- 全量刷新（會(huì)鎖視圖，刷新期間不能查詢）
REFRESH MATERIALIZED VIEW mv_dashboard_stats;

-- 并發(fā)刷新（不鎖視圖，但要求物化視圖上有唯一索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_stats;

-- 先建唯一索引才能用 CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_stats_date_uniq
    ON mv_dashboard_stats (stat_date);

-- 然后就可以并發(fā)刷新了
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_stats;

-- 創(chuàng)建分區(qū)表
CREATE TABLE orders (
    id          BIGSERIAL,
    order_no    VARCHAR(50) NOT NULL,
    user_id     BIGINT NOT NULL,
    amount      NUMERIC(12,2),
    status      SMALLINT DEFAULT 0,
    created_at  TIMESTAMP DEFAULT now()
) PARTITION BY RANGE (created_at);

-- 按月創(chuàng)建分區(qū)
CREATE TABLE orders_2025_01 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_2025_02 PARTITION OF orders
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE orders_2025_03 PARTITION OF orders
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
-- ... 后續(xù)月份以此類推

CREATE TABLE sales_data (
    id          BIGSERIAL,
    region      VARCHAR(20) NOT NULL,
    product_id  BIGINT NOT NULL,
    amount      NUMERIC(12,2),
    sale_date   DATE DEFAULT CURRENT_DATE
) PARTITION BY LIST (region);

CREATE TABLE sales_east PARTITION OF sales_data
    FOR VALUES IN ('華東');
CREATE TABLE sales_south PARTITION OF sales_data
    FOR VALUES IN ('華南');
CREATE TABLE sales_north PARTITION OF sales_data
    FOR VALUES IN ('華北', '東北');
CREATE TABLE sales_west PARTITION OF sales_data
    FOR VALUES IN ('西南', '西北');

CREATE OR REPLACE FUNCTION create_monthly_partition(target_month DATE)
RETURNS void AS $$
DECLARE
    partition_name TEXT;
    start_date TEXT;
    end_date TEXT;
BEGIN
    partition_name := 'orders_' || to_char(target_month, 'YYYY_MM');
    start_date := to_char(target_month, 'YYYY-MM-DD');
    end_date := to_char(target_month + INTERVAL '1 month', 'YYYY-MM-DD');

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
        partition_name, start_date, end_date
    );

    RAISE NOTICE '分區(qū) % 已創(chuàng)建: [% ~ %)', partition_name, start_date, end_date;
END;
$$ LANGUAGE plpgsql;

-- 創(chuàng)建下個(gè)月的分區(qū)
SELECT create_monthly_partition(date_trunc('month', now()) + INTERVAL '1 month');

-- 這條查詢只會(huì)掃描 orders_2025_03 這個(gè)分區(qū)
SELECT count(*), sum(amount)
FROM orders
WHERE created_at >= '2025-03-01'
  AND created_at < '2025-04-01';

-- 這條查詢會(huì)掃描 orders_2025_01 到 orders_2025_03 三個(gè)分區(qū)
SELECT date_trunc('month', created_at) AS month,
       count(*) AS order_count,
       sum(amount) AS total_amount
FROM orders
WHERE created_at >= '2025-01-01'
  AND created_at < '2025-04-01'
GROUP BY 1
ORDER BY 1;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM orders
WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01';