-- 创建测试数据
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary REAL NOT NULL,
    manager_id INTEGER
);

INSERT INTO employees VALUES
    (1, '张总',   '管理层', 50000, NULL),
    (2, '李经理', '技术部', 30000, 1),
    (3, '王经理', '销售部', 28000, 1),
    (4, '赵工',   '技术部', 20000, 2),
    (5, '钱工',   '技术部', 22000, 2),
    (6, '孙工',   '技术部', 18000, 2),
    (7, '周销售', '销售部', 15000, 3),
    (8, '吴销售', '销售部', 16000, 3),
    (9, '郑实习', '技术部', 8000,  4);

-- 基本 CTE：计算部门平均薪资
WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.department, e.salary, d.avg_salary,
       CASE WHEN e.salary > d.avg_salary THEN '高于平均' ELSE '低于平均' END AS status
FROM employees e
JOIN dept_avg d ON e.department = d.department
ORDER BY e.department, e.salary DESC;

WITH
high_salary AS (
    SELECT * FROM employees WHERE salary >= 20000
),
dept_count AS (
    SELECT department, COUNT(*) AS cnt
    FROM high_salary
    GROUP BY department
)
SELECT h.name, h.department, h.salary, d.cnt AS dept_high_count
FROM high_salary h
JOIN dept_count d ON h.department = d.department
ORDER BY h.salary DESC;

-- 递归查询：组织架构树
WITH RECURSIVE org_tree AS (
    -- 锚点：根节点（没有上级的人）
    SELECT id, name, manager_id, 0 AS level, name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归部分
    SELECT e.id, e.name, e.manager_id, t.level + 1,
           t.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT
    printf('%' || (level * 4 + 1) || 's', '') || name AS tree,
    level,
    path
FROM org_tree
ORDER BY path;

-- 递归 CTE：生成数字序列
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 100
)
SELECT n FROM nums;

-- 递归 CTE：计算阶乘
WITH RECURSIVE factorial AS (
    SELECT 1 AS n, 1 AS result
    UNION ALL
    SELECT n + 1, result * (n + 1)
    FROM factorial
    WHERE n < 10
)
SELECT n, result FROM factorial;

-- CTE 配合 DELETE
WITH to_delete AS (
    SELECT id FROM orders WHERE status = 'cancelled' AND created_at < '2024-01-01'
)
DELETE FROM order_items WHERE order_id IN (SELECT id FROM to_delete);

-- CTE 配合 UPDATE
WITH salary_update AS (
    SELECT id, salary * 1.1 AS new_salary
    FROM employees
    WHERE department = '技术部'
)
UPDATE employees SET salary = (
    SELECT new_salary FROM salary_update WHERE salary_update.id = employees.id
)
WHERE id IN (SELECT id FROM salary_update);

-- 窗口函数语法
function_name(args) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC|DESC]]
    [frame_clause]
)

-- 1. 排名
SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

-- 2. 部门内排名
SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 3. 累计求和
SELECT
    name, salary,
    SUM(salary) OVER (ORDER BY salary DESC) AS running_total
FROM employees;

-- 4. 移动平均（3 行窗口）
SELECT
    name, salary,
    AVG(salary) OVER (
        ORDER BY id
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg
FROM employees;

-- 5. LAG/LEAD：与前/后行比较
SELECT
    name, salary,
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary,
    salary - LAG(salary, 1) OVER (ORDER BY salary DESC) AS diff
FROM employees;

-- 6. 占比计算
SELECT
    name, department, salary,
    ROUND(salary * 100.0 / SUM(salary) OVER (), 2) AS pct_of_total,
    ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY department), 2) AS pct_of_dept
FROM employees;

-- Frame 范围定义
-- ROWS BETWEEN ... AND ...
-- RANGE BETWEEN ... AND ...

-- 常用 frame：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 从头到当前行（默认）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   -- 从当前行到末尾
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING           -- 前后各 2 行
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- 整个窗口

-- 示例：部门内最高与最低薪资
SELECT
    name, department, salary,
    FIRST_VALUE(name) OVER w AS highest_name,
    FIRST_VALUE(salary) OVER w AS highest_salary,
    LAST_VALUE(name) OVER w AS lowest_name,
    LAST_VALUE(salary) OVER w AS lowest_salary
FROM employees
WINDOW w AS (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

-- 创建 JSON
SELECT json_object('name', '张三', 'age', 25);
-- {"name":"张三","age":25}

SELECT json_array(1, 'hello', NULL, 3.14);
-- [1,"hello",null,3.14]

-- 提取 JSON 值
SELECT json_extract('{"name":"张三","age":25}', '$.name');  -- 张三
SELECT json_extract('{"name":"张三","age":25}', '$.age');   -- 25

-- -> 和 ->> 操作符（SQLite 3.38.0+）
SELECT '{"name":"张三","age":25}' -> '$.name';   -- "张三"（带引号）
SELECT '{"name":"张三","age":25}' ->> '$.name';  -- 张三（纯文本）

-- json_each：展开 JSON 数组
SELECT * FROM json_each('[1,2,3,4,5]');
-- key  value  type  atom  id  parent  fullkey  path
-- 0    1      integer  1   1   0       $[0]     $
-- 1    2      integer  2   3   0       $[1]     $
-- ...

-- 实际使用
SELECT
    json_each.key AS idx,
    json_each.value AS val
FROM json_each('["apple","banana","cherry"]');

-- json_tree：递归展开嵌套 JSON
SELECT * FROM json_tree('{"a":{"b":1},"c":[2,3]}');

-- json_set：设置值（不存在则创建）
SELECT json_set('{"name":"张三"}', '$.age', 25);
-- {"name":"张三","age":25}

-- json_insert：仅在路径不存在时插入
SELECT json_insert('{"name":"张三"}', '$.age', 25, '$.name', '李四');
-- {"name":"张三","age":25}（name 不变）

-- json_replace：仅在路径存在时替换
SELECT json_replace('{"name":"张三","age":25}', '$.age', 26, '$.email', '[email protected]');
-- {"name":"张三","age":26}（email 不创建）

-- json_remove：删除路径
SELECT json_remove('{"name":"张三","age":25,"city":"北京"}', '$.city');
-- {"name":"张三","age":25}

-- json_patch：合并两个 JSON 对象
SELECT json_patch('{"a":1,"b":2}', '{"b":3,"c":4}');
-- {"a":1,"b":3,"c":4}

-- 验证 JSON
SELECT json_valid('{"name":"张三"}');   -- 1（有效）
SELECT json_valid('{name:张三}');       -- 0（无效）
SELECT json_valid('[1,2,3]');           -- 1

-- JSON 类型
SELECT json_type('{"a":1}', '$.a');     -- integer
SELECT json_type('{"a":"hello"}', '$.a'); -- text
SELECT json_type('[1,2,3]');             -- array
SELECT json_type('{"a":1}');             -- object

-- 当前日期时间
SELECT date('now');                      -- 2026-05-10
SELECT time('now');                      -- 12:30:45
SELECT datetime('now');                  -- 2026-05-10 12:30:45
SELECT datetime('now', 'localtime');     -- 本地时间

-- 时间运算
SELECT date('now', '+1 month');          -- 下个月
SELECT date('now', '-7 days');           -- 一周前
SELECT datetime('now', '+2 hours');      -- 两小时后

-- 提取时间部分
SELECT strftime('%Y', 'now');            -- 年
SELECT strftime('%m', 'now');            -- 月
SELECT strftime('%d', 'now');            -- 日
SELECT strftime('%H:%M:%S', 'now');      -- 时分秒
SELECT strftime('%w', 'now');            -- 星期几（0=周日）

-- 计算时间差
SELECT julianday('2026-12-31') - julianday('2026-01-01');  -- 天数差

-- UNION（合并去重）
SELECT name FROM employees WHERE department = '技术部'
UNION
SELECT name FROM employees WHERE salary > 20000;

-- UNION ALL（合并不去重，性能更好）
SELECT name FROM employees WHERE department = '技术部'
UNION ALL
SELECT name FROM employees WHERE salary > 20000;

-- INTERSECT（交集）
SELECT name FROM employees WHERE department = '技术部'
INTERSECT
SELECT name FROM employees WHERE salary > 20000;

-- EXCEPT（差集）
SELECT name FROM employees WHERE department = '技术部'
EXCEPT
SELECT name FROM employees WHERE salary > 20000;

-- 标量子查询
SELECT name, salary,
    (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

-- IN 子查询
SELECT * FROM employees
WHERE department IN (
    SELECT department FROM employees GROUP BY department HAVING AVG(salary) > 15000
);

-- EXISTS 子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 相关子查询
SELECT name, salary, department
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 2
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    RANK() OVER (ORDER BY salary DESC) AS company_rank
FROM employees;

CREATE TABLE settings (
    key TEXT PRIMARY KEY,
    value TEXT  -- JSON 字符串
);

INSERT INTO settings VALUES ('theme', '{"color":"dark","font_size":14}');
UPDATE settings SET value = json_set(value, '$.font_size', 16) WHERE key = 'theme';
SELECT json_extract(value, '$.color') FROM settings WHERE key = 'theme';