SQLite高级查询 #

-- 子查询：嵌套在其他查询中的查询
-- 可以出现在：
-- 1. SELECT 子句
-- 2. FROM 子句
-- 3. WHERE 子句
-- 4. HAVING 子句

-- 示例表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department_id INTEGER
);

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    total REAL,
    order_date DATE
);

-- 返回单个值的子查询
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

-- 在WHERE中使用
SELECT * FROM users
WHERE department_id = (SELECT id FROM departments WHERE name = 'Engineering');

-- 使用比较运算符
SELECT * FROM users
WHERE salary > (SELECT AVG(salary) FROM users);

-- 返回单列多行的子查询
-- 使用 IN
SELECT * FROM users
WHERE department_id IN (SELECT id FROM departments WHERE budget > 100000);

-- 使用 ANY/SOME
SELECT * FROM products
WHERE price > ANY (SELECT price FROM competitor_products);

-- 使用 ALL
SELECT * FROM products
WHERE price > ALL (SELECT price FROM competitor_products);

-- 返回单行多列的子查询
SELECT * FROM users
WHERE (department_id, salary) = (
    SELECT department_id, MAX(salary)
    FROM users
    GROUP BY department_id
    HAVING department_id = users.department_id
);

-- 返回多行多列的子查询（派生表）
SELECT u.name, o.total
FROM users u
JOIN (SELECT user_id, SUM(total) AS total FROM orders GROUP BY user_id) o
ON u.id = o.user_id;

-- 在FROM中使用
SELECT avg_total
FROM (
    SELECT user_id, AVG(total) AS avg_total
    FROM orders
    GROUP BY user_id
) AS user_averages
WHERE avg_total > 100;

-- EXISTS：检查子查询是否返回行
SELECT name FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- NOT EXISTS：检查子查询是否不返回行
SELECT name FROM users
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- 相关子查询
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
    AND o.total > 1000
);

-- WITH 子句定义CTE
WITH user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.name,
    uo.order_count,
    uo.total_spent
FROM users u
LEFT JOIN user_orders uo ON u.id = uo.user_id;

-- 多个CTE
WITH 
    active_users AS (
        SELECT * FROM users WHERE status = 1
    ),
    user_totals AS (
        SELECT user_id, SUM(total) AS total
        FROM orders
        GROUP BY user_id
    )
SELECT 
    au.name,
    ut.total
FROM active_users au
LEFT JOIN user_totals ut ON au.id = ut.user_id;

-- 递归CTE用于处理层级数据
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT,
    parent_id INTEGER
);

INSERT INTO categories VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers', 1),
    (3, 'Phones', 1),
    (4, 'Laptops', 2),
    (5, 'Desktops', 2);

-- 递归查询层级结构
WITH RECURSIVE category_tree AS (
    -- 基础查询：顶级分类
    SELECT id, name, parent_id, 0 AS level, name AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归查询：子分类
    SELECT 
        c.id, 
        c.name, 
        c.parent_id, 
        ct.level + 1,
        ct.path || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY path;

-- 输出：
-- id | name       | parent_id | level | path
-- ---|------------|-----------|-------|------------------------
-- 1  | Electronics| NULL      | 0     | Electronics
-- 2  | Computers  | 1         | 1     | Electronics > Computers
-- 4  | Laptops    | 2         | 2     | Electronics > Computers > Laptops
-- 5  | Desktops   | 2         | 2     | Electronics > Computers > Desktops
-- 3  | Phones     | 1         | 1     | Electronics > Phones

-- 生成数字序列
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

-- 输出：1 到 10

-- 计算斐波那契数列
WITH RECURSIVE fibonacci AS (
    SELECT 0 AS n, 0 AS value, 1 AS next_value
    UNION ALL
    SELECT n + 1, next_value, value + next_value
    FROM fibonacci
    WHERE n < 10
)
SELECT n, value FROM fibonacci;

-- 组织结构图
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT 
    printf('%*s%s', level * 2, '', name) AS org_structure
FROM org_chart;

-- 窗口函数：对一组行进行计算，但不减少行数
-- SQLite 3.25.0+ 支持

-- 基本语法
function_name() OVER (
    PARTITION BY column
    ORDER BY column
    frame_clause
)

-- 行号
SELECT 
    name,
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num
FROM users;

-- 分组内行号
SELECT 
    name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

-- RANK：排名，有并列跳过
SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

-- 输出：
-- name  | score | rank
-- ------|-------|-----
-- Alice | 100   | 1
-- Bob   | 95    | 2
-- Carol | 95    | 2
-- Dave  | 90    | 4    -- 跳过3

-- DENSE_RANK：排名，不跳过
SELECT 
    name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

-- 输出：
-- name  | score | dense_rank
-- ------|-------|-----------
-- Alice | 100   | 1
-- Bob   | 95    | 2
-- Carol | 95    | 2
-- Dave  | 90    | 3    -- 不跳过

-- 累计求和
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

-- 移动平均
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM daily_sales;

-- 分组聚合
SELECT 
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;

-- LAG：获取前一行
SELECT 
    order_date,
    amount,
    LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
    amount - LAG(amount) OVER (ORDER BY order_date) AS diff
FROM daily_sales;

-- LAG带偏移量
SELECT 
    order_date,
    amount,
    LAG(amount, 7) OVER (ORDER BY order_date) AS amount_7_days_ago
FROM daily_sales;

-- LEAD：获取后一行
SELECT 
    order_date,
    amount,
    LEAD(amount) OVER (ORDER BY order_date) AS next_amount
FROM daily_sales;

-- FIRST_VALUE：窗口内第一个值
SELECT 
    name,
    department_id,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_max_salary
FROM employees;

-- LAST_VALUE：窗口内最后一个值
SELECT 
    name,
    department_id,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS dept_min_salary
FROM employees;

-- NTILE：将行分成N组
SELECT 
    name,
    score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM students;

-- 输出：
-- name  | score | quartile
-- ------|-------|----------
-- Alice | 100   | 1
-- Bob   | 95    | 1
-- Carol | 90    | 2
-- Dave  | 85    | 2
-- Eve   | 80    | 3
-- Frank | 75    | 3
-- Grace | 70    | 4
-- Henry | 65    | 4

-- 简单CASE表达式
SELECT 
    name,
    status,
    CASE status
        WHEN 1 THEN 'Active'
        WHEN 0 THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_name
FROM users;

-- 搜索CASE表达式
SELECT 
    name,
    score,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

-- 条件计数
SELECT 
    COUNT(*) AS total,
    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS inactive_count
FROM users;

-- 条件求和
SELECT 
    department_id,
    SUM(CASE WHEN gender = 'M' THEN salary ELSE 0 END) AS male_salary,
    SUM(CASE WHEN gender = 'F' THEN salary ELSE 0 END) AS female_salary
FROM employees
GROUP BY department_id;

-- 每个部门薪资前三名
WITH ranked_employees AS (
    SELECT 
        name,
        department_id,
        salary,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
    FROM employees
)
SELECT * FROM ranked_employees WHERE dept_rank <= 3;

-- 月度销售同比环比
WITH monthly_sales AS (
    SELECT 
        strftime('%Y-%m', order_date) AS month,
        SUM(total) AS total_sales
    FROM orders
    GROUP BY month
)
SELECT 
    month,
    total_sales,
    LAG(total_sales, 1) OVER (ORDER BY month) AS prev_month,
    LAG(total_sales, 12) OVER (ORDER BY month) AS same_month_last_year,
    ROUND((total_sales - LAG(total_sales, 1) OVER (ORDER BY month)) * 100.0 / 
          LAG(total_sales, 1) OVER (ORDER BY month), 2) AS mom_growth
FROM monthly_sales;

-- 找出连续登录超过3天的用户
WITH login_dates AS (
    SELECT DISTINCT 
        user_id,
        login_date,
        login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
    FROM user_logins
),
consecutive_groups AS (
    SELECT 
        user_id,
        grp,
        COUNT(*) AS consecutive_days
    FROM login_dates
    GROUP BY user_id, grp
)
SELECT DISTINCT user_id
FROM consecutive_groups
WHERE consecutive_days >= 3;