// sql
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
    user_id VARCHAR(18) NOT NULL COMMENT '用户ID',
    order_amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
    order_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
    status TINYINT NOT NULL COMMENT '状态（0-取消，1-完成）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 

// sql
SELECT order_id, user_id, order_amount
FROM orders
WHERE order_time BETWEEN '2025-06-01' AND '2025-06-30'
  AND order_amount > 1000
ORDER BY order_amount DESC;
 

// sql
INSERT INTO orders (user_id, order_amount, status)
VALUES ('123456789', 1599.99, 1);
 

// sql
-- 更新订单状态
UPDATE orders SET status=0 WHERE order_id=10086;

-- 删除测试数据（生产环境禁用！）
DELETE FROM orders WHERE user_id='test_user';
 

// sql
SELECT product_id, product_name, price, sales
FROM products
WHERE category='电子产品' AND price>5000
ORDER BY sales DESC
LIMIT 10;
 

// sql
-- 内连接：仅返回匹配的用户和订单
SELECT o.order_id, u.user_name, o.order_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id;

-- 左连接：返回所有订单，无匹配用户则姓名为NULL
SELECT o.order_id, u.user_name, o.order_amount
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id;
 

// sql
-- 嵌套子查询
SELECT DISTINCT user_id
FROM orders
WHERE product_id IN (
    SELECT product_id FROM products WHERE product_name='智能手机'
);

-- 关联子查询（逐行执行，适合复杂条件）
SELECT o.order_id, o.user_id
FROM orders o
WHERE EXISTS (
    SELECT 1 FROM products p
    WHERE p.product_id = o.product_id
      AND p.category='电子产品'
);
 

// sql
SELECT
    product_id,
    category,
    sales,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS sales_rank
FROM products;
 

// sql
WITH user_active AS (
    SELECT
        user_id,
        DATE_FORMAT(order_time, '%Y-%m') AS month
    FROM orders
    GROUP BY user_id, month
)
SELECT
    current.month,
    COUNT(DISTINCT current.user_id) AS current_users,
    COUNT(DISTINCT prev.user_id) AS retained_users,
    ROUND(COUNT(DISTINCT prev.user_id)/COUNT(DISTINCT current.user_id), 2) AS retention_rate
FROM user_active current
LEFT JOIN user_active prev
  ON current.user_id = prev.user_id
  AND current.month = DATE_FORMAT(DATE_ADD(prev.month, INTERVAL 1 MONTH), '%Y-%m')
GROUP BY current.month;
 

// sql
CREATE INDEX idx_user_time ON orders(user_id, order_time) INCLUDE (order_amount);
 

优化以下SQL，目标执行时间<100秒，需保留所有字段且结果一致：
[粘贴SQL代码]
数据库表结构：
- orders: user_id(INT), order_time(DATETIME), order_amount(DECIMAL)
- 索引：现有idx_user_id(user_id)
 

// sql
WITH user_first_order AS (
    SELECT
        user_id,
        MIN(order_time) AS first_order_time,
        FIRST_VALUE(order_amount) OVER (PARTITION BY user_id ORDER BY order_time) AS first_amount
    FROM orders
    GROUP BY user_id
)
SELECT
    u.user_id,
    u.first_amount,
    COUNT(DISTINCT o.order_id) > 1 AS has_repurchase
FROM user_first_order u
LEFT JOIN orders o
  ON u.user_id = o.user_id
  AND o.order_time BETWEEN u.first_order_time AND DATE_ADD(u.first_order_time, INTERVAL 30 DAY)
GROUP BY u.user_id, u.first_amount;
 

活跃用户（日）：当日有下单行为或浏览时长>5分钟的用户ID
 

// sql
-- 删除关键字段缺失行（如订单金额为空）
DELETE FROM orders WHERE order_amount IS NULL;

-- 填充缺失值（如用均值填充评分）
UPDATE products
SET rating = (SELECT AVG(rating) FROM products)
WHERE rating IS NULL;
 

// sql
DELETE FROM orders
WHERE order_amount > 100000
  AND order_id NOT IN (SELECT order_id FROM high_value_orders); -- 排除已知大额订单
 

生成MySQL代码：创建“学生成绩表”，包含id(主键)、student_id(唯一)、name、score(保留1位小数)、create_time(默认当前时间)。

// sql
CREATE TABLE student_scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id VARCHAR(18) NOT NULL UNIQUE COMMENT '学号',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    score DECIMAL(5,1) NOT NULL COMMENT '成绩',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 

// sql
-- DeepSeek生成的SQL
SELECT
    JSON_EXTRACT_PATH_TEXT(body, 'params', 'userId') AS user_id,
    created_at
FROM api_logs
WHERE uri = '/api/login';
 

// sql
-- DeepSeek生成的SQL
SELECT
    patient_id,
    CONCAT('****', SUBSTRING(phone, 9, 4)) AS masked_phone
FROM medical_records;
 

任务：生成SQL查询
表结构：
- orders: order_id(INT), user_id(INT), order_time(DATETIME), amount(DECIMAL)
- products: product_id(INT), name(VARCHAR), category(VARCHAR)
需求：统计2025年Q1每个类目的销售额，按降序排列。
输出要求：仅返回SQL代码，无解释。
 

// sql
DELETE FROM orders
WHERE (user_id, order_time) IN (
    SELECT user_id, order_time
    FROM (
        SELECT
            user_id,
            order_time,
            ROW_NUMBER() OVER (PARTITION BY user_id, order_time ORDER BY order_id) AS rn
        FROM orders
    ) t
    WHERE rn > 1
);
 

// sql
ALTER TABLE user_behavior
ADD COLUMN hour INT GENERATED ALWAYS AS (HOUR(behavior_time)) STORED; -- 提取小时维度
 

// sql
WITH funnel AS (
    SELECT
        COUNT(DISTINCT CASE WHEN behavior_type='pv' THEN user_id END) AS pv_users,
        COUNT(DISTINCT CASE WHEN behavior_type='cart' THEN user_id END) AS cart_users,
        COUNT(DISTINCT CASE WHEN behavior_type='buy' THEN user_id END) AS buy_users
    FROM user_behavior
    WHERE behavior_time >= '2025-01-01'
)
SELECT
    cart_users / pv_users AS pv_to_cart,
    buy_users / cart_users AS cart_to_buy
FROM funnel;
 

// sql
WITH rfm AS (
    SELECT
        user_id,
        DATEDIFF(NOW(), MAX(order_time)) AS recency, -- 最近购买间隔
        COUNT(DISTINCT order_id) AS frequency,       -- 购买频次
        SUM(order_amount) AS monetary                 -- 消费金额
    FROM orders
    GROUP BY user_id
)
SELECT
    user_id,
    CASE
        WHEN recency < 30 AND frequency >= 5 AND monetary >= 5000 THEN '高价值用户'
        WHEN recency < 60 AND frequency >= 2 THEN '潜力用户'
        ELSE '流失风险用户'
    END AS user_level
FROM rfm;
 

// sql
SELECT
    DATE_FORMAT(order_time, '%Y-%m-%d') AS order_date,
    category,
    SUM(order_amount) AS total_sales
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY order_date, category;
 

// powerquery-m
let
    Source = Sql.Database("localhost", "ecommerce", [Query="SELECT * FROM orders WHERE order_time BETWEEN '" & Date.ToText(StartDate) & "' AND '" & Date.ToText(EndDate) & "'"])
in
    Source
 

// sql
-- 提取用户还款特征
SELECT
    user_id,
    COUNT(CASE WHEN repayment_status='逾期' THEN 1 END)/COUNT(*) AS overdue_rate,
    AVG(DATEDIFF(repayment_date, due_date)) AS avg_delay_days
FROM loan_repayments
GROUP BY user_id;
 

// sql
SELECT
    drgs_code,
    AVG(DATEDIFF(discharge_date, admission_date)) AS avg_stay_days,
    COUNT(CASE WHEN stay_days > 14 THEN 1 END) AS long_stay_count
FROM medical_records
GROUP BY drgs_code;
 

// sql
-- Flink SQL实时查询
SELECT
    device_id,
    current,
    ts
FROM iot_metrics
WHERE current > 20
  AND ts >= NOW() - INTERVAL '5' MINUTE;
 

// sql
SELECT
    IFNULL(
        (SELECT DISTINCT salary
         FROM employees
         ORDER BY salary DESC
         LIMIT 1 OFFSET 1),
        NULL
    ) AS second_highest_salary;
 

// sql
WITH user_login AS (
    SELECT
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS group_date
    FROM logins
)
SELECT
    user_id,
    MAX(连续天数) AS max_continuous_days
FROM (
    SELECT
        user_id,
        group_date,
        COUNT(*) AS 连续天数
    FROM user_login
    GROUP BY user_id, group_date
) t
GROUP BY user_id;