-- SQL 的执行顺序（非书写顺序）如下：

-- 1、FROM        → 加载数据源（表、视图）
-- 2、JOIN / ON   → 连接其他表
-- 3、WHERE       → 行级过滤（不能使用 SELECT 中的别名）
-- 4、GROUP BY    → 按字段分组（每组生成一行）
-- 5、HAVING      → 对分组后的结果过滤（可使用聚合函数）
-- 6、SELECT      → 选择要返回的列或表达式
-- 7、DISTINCT    → 去除完全重复的行
-- 8、ORDER BY    → 对最终结果排序
-- 9、LIMIT / OFFSET → 分页，限制返回行数

-- ❌ 错误写法
SELECT age + 1 AS next_age FROM users WHERE next_age > 19; 

-- ✅ 正确写法
SELECT age + 1 AS next_age FROM users WHERE age + 1 > 19; 

-- 目的：从表中查询满足条件的数据
-- 使用场景：查看用户信息、订单列表等
-- ⚠️ 注意：WHERE 条件应尽量使用索引列，避免全表扫描

-- 示例数据（users 表）：
-- | user_id | name    | age | city      | status  | email         | created_at  |
-- |---------|---------|-----|-----------|---------|---------------|-------------|
-- | 1       | Alice   | 25  | Beijing   | active  | a@x.com       | 2025-01-10  |
-- | 2       | Bob     | 30  | Shanghai  | active  | b@x.com       | 2025-02-15  |
-- | 3       | Charlie | 17  | Beijing   | inactive| c@x.com       | 2025-03-01  |

SELECT 
    -- 【SELECT 子句】定义要返回的字段或表达式
    user_id,                           -- 返回原始列
    name,                              -- 多列用逗号分隔
    UPPER(name) AS upper_name,         -- 函数处理：转大写，别名 upper_name
    age + 1 AS next_year_age,          -- 算术运算：明年年龄
    'Active User' AS status_label,     -- 添加常量字段
    NOW() AS query_timestamp           -- 当前查询时间（MySQL/PG）

FROM 
    -- 【FROM 子句】指定数据来源
    users                              -- 表名
    -- FROM schema.users                -- PostgreSQL 中指定 schema
    -- FROM users u                     -- 给表起别名 u，方便引用

WHERE 
    -- 【WHERE 子句】行级过滤，只保留符合条件的行
    -- 注意：WHERE 不能使用 SELECT 中的别名（如：upper_name）
    age >= 18                          -- 年龄 >= 18
    AND city IN ('Beijing', 'Shanghai') -- 匹配多个城市
    AND status = 'active'              -- 状态为 active
    AND name LIKE 'A%'                 -- 名字以 A 开头
    AND created_at >= '2025-01-01'     -- 注册时间在 2025 年后
    -- AND YEAR(created_at) = 2025    -- ❌ 错误！破坏索引，应避免
    -- ✅ 正确：created_at BETWEEN '2025-01-01' AND '2025-12-31'

    -- NULL 值处理
    AND email IS NOT NULL              -- 推荐：使用 IS NOT NULL
    -- AND email != NULL              -- ❌ 错误！NULL 不能用 = 或 != 比较
;

| user_id | name  | upper_name | next_year_age | status_label | query_timestamp     |
|---------|-------|------------|---------------|--------------|---------------------|
| 1       | Alice | ALICE      | 26            | Active User  | 2025-08-19 12:00:00 |

-- 目的：关联多个表，获取跨表信息
-- 使用场景：用户 + 订单信息联合查询
-- ⚠️ 注意：LEFT JOIN 中的 WHERE 条件可能改变 JOIN 行为

-- 示例数据（orders 表）：
-- | order_id | user_id | product     | amount | status     | order_date  |
-- |----------|---------|-------------|--------|------------|-------------|
-- | 1001     | 1       | iPhone      | 999    | completed  | 2025-01-15  |
-- | 1002     | 2       | Laptop      | 1299   | completed  | 2025-02-20  |
-- | 1003     | 3       | Headphones  | 199    | pending    | 2025-03-05  |

SELECT 
    u.user_id,
    u.name,
    o.order_id,
    o.product,
    o.order_date,
    COALESCE(o.amount, 0) AS amount    -- 处理 NULL，返回 0

FROM 
    users u                            -- 主表：用户（左表）

-- 【JOIN 子句】连接订单表
LEFT JOIN 
    orders o                           -- 被连接表（右表）
    ON u.user_id = o.user_id           -- 连接条件：通过 user_id 关联

    -- 可选：在 ON 中加入过滤条件（如：按日期筛选）
    -- ON u.user_id = o.user_id AND o.order_date >= '2025-02-01'
    -- 区别：
    --   - ON 中的条件：在连接时过滤右表
    --   - WHERE 中的条件：在连接后过滤整个结果

WHERE 
    -- WHERE 过滤最终结果
    u.city = 'Beijing'                 -- 只保留北京用户
    -- AND o.order_id IS NOT NULL     -- 如果加此条件，LEFT JOIN 会退化为 INNER JOIN！

-- 可选连接类型：
-- INNER JOIN：只保留两边都匹配的行
-- RIGHT JOIN：保留右表所有行（较少用）
-- FULL OUTER JOIN：两边都保留（MySQL 不支持）
;

| user_id | name    | order_id | product    | order_date  | amount |
|---------|---------|----------|------------|-------------|--------|
| 1       | Alice   | 1001     | iPhone     | 2025-01-15  | 999    |
| 3       | Charlie | NULL     | NULL       | NULL        | 0      |  -- LEFT JOIN 保留左表

-- 目的：对数据进行分组统计
-- 使用场景：按城市统计用户数量、平均年龄等
-- ⚠️ 注意：SELECT 中非聚合字段必须出现在 GROUP BY 中

SELECT 
    city,                              -- 分组字段
    COUNT(*) AS user_count,            -- 统计每组行数
    AVG(age) AS avg_age,               -- 平均年龄
    SUM(COALESCE(age, 0)) AS total_age,-- 总年龄
    MAX(created_at) AS latest_signup,  -- 最晚注册时间
    STRING_AGG(name, ', ') AS names    -- 拼接名字（PostgreSQL）
    -- GROUP_CONCAT(name) AS names     -- MySQL 等价函数

FROM 
    users

WHERE 
    status = 'active'                  -- 先过滤再分组，提升性能

GROUP BY 
    city                               -- 按城市分组

HAVING 
    -- 【HAVING 子句】对分组后的结果进行过滤
    COUNT(*) >= 1                      -- 至少有 1 个用户
    AND AVG(age) > 20                  -- 平均年龄 > 20

ORDER BY 
    user_count DESC,                   -- 按用户数降序
    city ASC                           -- 城市升序
;

| city      | user_count | avg_age | total_age | latest_signup | names   |
|-----------|------------|---------|-----------|---------------|---------|
| Shanghai  | 1          | 30.0    | 30        | 2025-02-15    | Bob     |
| Beijing   | 1          | 25.0    | 25        | 2025-01-10    | Alice   |

-- 目的：将复杂查询拆解为多个逻辑步骤
-- 使用场景：先统计订单，再筛选高价值用户
-- ⚠️ 注意：CTE 只在当前查询中有效，不保存数据

-- 【CTE】使用 WITH 定义临时结果集（推荐）
WITH user_stats AS (
    -- 第一步：统计每个用户的订单情况
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        AVG(amount) AS avg_amount
    FROM 
        orders
    GROUP BY 
        user_id
    HAVING 
        COUNT(*) >= 1                  -- 至少有一单
),
top_users AS (
    -- 第二步：筛选高价值用户
    SELECT 
        user_id
    FROM 
        user_stats
    WHERE 
        avg_amount > 500               -- 平均订单金额 > 500
)

-- 主查询：结合用户信息与统计结果
SELECT 
    u.name,
    u.city,
    us.order_count,
    us.avg_amount

FROM 
    users u

INNER JOIN 
    user_stats us 
    ON u.user_id = us.user_id

WHERE 
    u.user_id IN (SELECT user_id FROM top_users)  -- 子查询过滤

ORDER BY 
    us.avg_amount DESC

LIMIT 10;

| name  | city     | order_count | avg_amount |
|-------|----------|-------------|------------|
| Bob   | Shanghai | 1           | 1299.0     |
| Alice | Beijing  | 1           | 999.0      |

-- 目的：在不减少行数的前提下进行分组内计算
-- 使用场景：排名、移动平均、累计求和
-- ⚠️ 注意：窗口函数在 SELECT 阶段计算，不能在 WHERE 中使用

SELECT 
    user_id,
    product,
    amount,
    order_date,

    -- 【ROW_NUMBER】组内唯一排名
    ROW_NUMBER() OVER (
        PARTITION BY user_id            -- 按用户分组
        ORDER BY amount DESC            -- 组内按金额降序
    ) AS rn,                            -- 排名：1,2,3...

    -- 【RANK】并列排名（相同值同名次，跳过后续）
    RANK() OVER (
        ORDER BY amount DESC
    ) AS global_rank,                  -- 全局排名：1,2,2,4...

    -- 【SUM OVER】累计求和
    SUM(amount) OVER (
        PARTITION BY user_id
    ) AS user_total_amount,            -- 每行都显示该用户的总消费

    -- 【移动平均】过去3天平均
    AVG(amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3day

FROM 
    orders

WHERE 
    order_date >= '2025-01-01'  -- 基于订单日期过滤

ORDER BY 
    user_id, rn;

| user_id | product   | amount | order_date  | rn | global_rank | user_total_amount |
|---------|-----------|--------|-------------|----|-------------|-------------------|
| 1       | iPhone    | 999    | 2025-01-15  | 1  | 2           | 999               |
| 2       | Laptop    | 1299   | 2025-02-20  | 1  | 1           | 1299              |

-- 目的：实现 SQL 中的 if-else 逻辑
-- 使用场景：分类、状态转换、条件统计

SELECT 
    name,
    age,
    city,

    -- 【CASE WHEN】通用条件判断
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 60 THEN '成年人'
        ELSE '老年人'
    END AS age_group,

    -- 【简写 CASE】当判断单个字段时
    CASE status
        WHEN 'active' THEN 1
        WHEN 'inactive' THEN 0
        ELSE -1
    END AS status_code,

    -- 【条件聚合】统计北京用户数量
    SUM(
        CASE WHEN city = 'Beijing' THEN 1 ELSE 0 END
    ) AS beijing_count

FROM 
    users

GROUP BY 
    name, age, city, status;           -- 注意：非聚合字段必须出现在 GROUP BY

| name    | age | city    | age_group | status_code | beijing_count |
|---------|-----|---------|-----------|-------------|---------------|
| Alice   | 25  | Beijing | 成年人    | 1           | 2             |
| Bob     | 30  | Shanghai| 成年人    | 1           | 2             |
| Charlie | 17  | Beijing | 未成年    | 0           | 2             |

-- 【DISTINCT】去除完全重复的行
SELECT DISTINCT 
    city, status 
FROM 
    users;
-- 结果：('Beijing', 'active'), ('Shanghai', 'active'), ('Beijing', 'inactive')

-- 【分页】LIMIT + OFFSET
SELECT 
    user_id, name, city
FROM 
    users
ORDER BY 
    user_id
LIMIT 10 OFFSET 20;   -- 跳过前 20 行，取 10 行
-- 等价写法：LIMIT 10, 20（MySQL）

-- ⚠️ 深分页问题：OFFSET 越大，性能越差（需跳过大量行）
-- ✅ 优化：使用“游标分页”（基于上一页最后 ID）
-- WHERE user_id > 100 ORDER BY user_id LIMIT 10

-- ✅ 推荐：EXISTS（通常性能更好，可短路）
SELECT 
    u.name
FROM 
    users u
WHERE 
    EXISTS (
        SELECT 1                    -- 习惯写 1，不关心具体值
        FROM orders o
        WHERE o.user_id = u.user_id 
          AND o.amount > 1000
    );

-- ❌ 注意：IN 子查询若含 NULL，结果可能为空
-- WHERE col IN (1, 2, NULL) → 永远返回 FALSE 或 NULL

-- 插入数据
INSERT INTO users (name, age, city, status) 
VALUES ('David', 28, 'Guangzhou', 'active');

-- 更新数据
UPDATE users 
SET status = 'inactive', updated_at = NOW()
WHERE city = 'Shanghai';

-- 删除数据
DELETE FROM users 
WHERE user_id = 999;
-- ⚠️ 生产环境慎用！建议先 SELECT 验证
-- SELECT * FROM users WHERE user_id = 999;

/*
 优化小技巧：
 1、避免 SELECT *，只取需要的列；
 2、WHERE 条件优先使用索引列（如：user_id, city）；
 3、避免在索引列上使用函数（如：WHERE YEAR(date)=2025）；
 4、大表 JOIN 时，小表驱动大表；
 5、使用 EXPLAIN 分析执行计划；
 6、考虑添加复合索引：
    CREATE INDEX idx_users_city_status ON users(city, status);
    CREATE INDEX idx_orders_user_amount ON orders(user_id, amount);
*/

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,  -- 基于订单日期分组
    COUNT(*) AS order_count,
    SUM(amount) AS gmv,
    AVG(amount) AS avg_order_value
FROM orders
WHERE order_status IN ('paid', 'shipped', 'completed')
  AND order_date >= '2025-01-01'  -- 按订单日期筛选时间范围
GROUP BY month
ORDER BY month;

WITH user_orders AS (
    SELECT 
        user_id,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT 
    COUNT(CASE WHEN order_count >= 2 THEN 1 END) * 100.0 / COUNT(*) AS repurchase_rate
FROM user_orders;

SELECT 
    p.product_name,
    SUM(oi.quantity) AS total_sold,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_sold DESC
LIMIT 10;

SELECT 
    user_id,
    SUM(amount) AS ltv_12m
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)  -- 基于订单日期计算时间范围
  AND order_status = 'completed'
GROUP BY user_id;

SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    SUM(CASE WHEN trans_type = 'deposit' THEN amount ELSE 0 END) AS income,
    SUM(CASE WHEN trans_type = 'withdrawal' THEN amount ELSE 0 END) AS expense,
    SUM(CASE WHEN trans_type = 'deposit' THEN amount ELSE -amount END) AS net_flow
FROM transactions
GROUP BY month
ORDER BY month;

SELECT 
    c.name,
    c.phone,
    t.trans_date,
    t.amount,
    t.trans_type
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
JOIN customers c ON a.customer_id = c.customer_id
WHERE t.amount > 100000
  AND t.trans_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
ORDER BY t.amount DESC;

SELECT 
    CASE 
        WHEN total_assets < 10000 THEN '0-1万'
        WHEN total_assets BETWEEN 10000 AND 100000 THEN '1-10万'
        WHEN total_assets BETWEEN 100000 AND 1000000 THEN '10-100万'
        ELSE '100万以上'
    END AS asset_level,
    COUNT(*) AS customer_count,
    AVG(total_assets) AS avg_assets
FROM (
    SELECT 
        customer_id,
        SUM(balance) AS total_assets
    FROM accounts
    GROUP BY customer_id
) t
GROUP BY asset_level
ORDER BY avg_assets;

SELECT 
    DATE(timestamp) AS log_date,
    COUNT(*) AS pv,
    COUNT(DISTINCT ip) AS uv
FROM web_logs
WHERE timestamp >= '2025-08-01'
GROUP BY log_date
ORDER BY log_date;

SELECT 
    DATE(timestamp) AS day,
    COUNT(*) AS total_requests,
    COUNT(CASE WHEN status >= 500 THEN 1 END) AS error_count,
    ROUND(
        COUNT(CASE WHEN status >= 500 THEN 1 END) * 100.0 / COUNT(*), 2
    ) AS error_rate_percent
FROM web_logs
GROUP BY day
HAVING error_rate_percent > 1.0  -- 只看错误率 >1% 的天
ORDER BY error_rate_percent DESC;

SELECT 
    url,
    COUNT(*) AS visit_count,
    COUNT(DISTINCT ip) AS unique_visitors
FROM web_logs
WHERE status = 200
  AND url NOT LIKE '%favicon%'   -- 过滤无关请求
GROUP BY url
ORDER BY visit_count DESC
LIMIT 10;

-- 假设会话定义为同一 IP 30 分钟内连续访问
-- 此为简化版，真实需用窗口函数定义会话
WITH session_logs AS (
    SELECT 
        ip,
        url,
        timestamp,
        -- 模拟会话 ID：每 30 分钟重置
        FLOOR(UNIX_TIMESTAMP(timestamp) / 1800) AS session_id
    FROM web_logs
    WHERE DATE(timestamp) = '2025-08-18'
)
SELECT 
    ip,
    session_id,
    GROUP_CONCAT(url ORDER BY timestamp) AS page_path
FROM session_logs
GROUP BY ip, session_id
HAVING COUNT(*) >= 3;  -- 至少访问 3 页

SELECT name, email
FROM users
WHERE city = 'Beijing'
  AND status = 'active'
  AND name LIKE 'A%'
  AND age BETWEEN 20 AND 30;

SELECT 
    u.name,
    COUNT(o.order_id) AS order_count
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
ORDER BY order_count DESC;

SELECT name
FROM users
WHERE user_id NOT IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE user_id IS NOT NULL
);
-- 或使用 LEFT JOIN
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.user_id IS NULL;

SELECT 
    city,
    AVG(age) AS avg_age
FROM users
GROUP BY city
HAVING AVG(age) > 22;

SELECT 
    order_id,
    amount,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS rank
FROM orders;

SELECT 
    user_id,
    order_id,
    amount,
    order_date
FROM (
    SELECT 
        user_id,
        order_id,
        amount,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn  -- 按订单日期排序
    FROM orders
) t
WHERE rn = 1;

SELECT 
    COUNT(*) AS new_users
FROM users
WHERE created_at >= '2025-08-01'
  AND created_at < '2025-09-01';
-- 或使用 DATE 函数
-- WHERE DATE_FORMAT(created_at, '%Y-%m') = '2025-08'

SELECT 
    CASE 
        WHEN age BETWEEN 18 AND 35 THEN '青年'
        WHEN age BETWEEN 36 AND 55 THEN '中年'
        WHEN age > 55 THEN '老年'
        ELSE '未成年'
    END AS age_group,
    COUNT(*) AS count
FROM users
GROUP BY age_group
ORDER BY count DESC;

SELECT 
    u.name,
    SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
ORDER BY total_spent DESC
LIMIT 1;

WITH user_stats AS (
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        AVG(amount) AS avg_amount
    FROM orders
    GROUP BY user_id
)
SELECT u.name
FROM users u
JOIN user_stats us ON u.user_id = us.user_id
WHERE us.order_count >= 2 AND us.avg_amount > 500;

-- 假设有表：user_actions (user_id, action_type, product_id, timestamp)
-- action_type: 'add_to_cart', 'place_order'

SELECT 
    COUNT(DISTINCT CASE WHEN action_type = 'place_order' THEN user_id END) * 100.0 / 
    COUNT(DISTINCT CASE WHEN action_type = 'add_to_cart' THEN user_id END) AS conversion_rate
FROM user_actions
WHERE timestamp >= '2025-08-01';

-- 简化版：使用窗口函数找连续登录
WITH daily_trans AS (
    SELECT 
        customer_id,
        DATE(trans_date) AS trans_day
    FROM transactions
    GROUP BY customer_id, trans_day
),
ranked AS (
    SELECT 
        customer_id,
        trans_day,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY trans_day) AS rn
    FROM daily_trans
),
grouped AS (
    SELECT 
        customer_id,
        DATE_SUB(trans_day, INTERVAL rn DAY) AS grp  -- 连续日期差为常数
    FROM ranked
)
SELECT DISTINCT customer_id
FROM grouped
GROUP BY customer_id, grp
HAVING COUNT(*) >= 3;

SELECT 
    COUNT(CASE WHEN user_agent LIKE '%Mobile%' THEN 1 END) * 100.0 / COUNT(*) AS mobile_ratio
FROM web_logs
WHERE DATE(timestamp) = '2025-08-18';

-- 以下 SQL 有语法错误，请修正：
SELECT name, age, AVG(age) 
FROM users 
WHERE AVG(age) > 20 
GROUP BY name;

-- 修正后：
SELECT name, age, AVG(age) 
FROM users 
GROUP BY name, age
HAVING AVG(age) > 20;
-- 但逻辑不合理，应改为：
SELECT name, age 
FROM users 
WHERE age > 20;
-- 或分组统计：
SELECT name, AVG(age) 
FROM users 
GROUP BY name 
HAVING AVG(age) > 20;

-- MySQL/PostgreSQL:
SELECT * FROM users 
ORDER BY user_id 
LIMIT 10 OFFSET 20;
-- 第1页：OFFSET 0；第2页：OFFSET 10；第3页：OFFSET 20

-- 深分页优化（游标分页）：
SELECT * FROM users 
WHERE user_id > 20  -- 假设上一页最后 user_id 是 20
ORDER BY user_id 
LIMIT 10;