MySQL数据分析：日常工作中的SQL查询

数据分析师日常工作中，90%的SQL查询都是基础操作。本文展示企业数据分析中最实用、最常用的MySQL查询，帮你快速掌握核心技能。

一、基础数据表设计

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    reg_date DATE,
    city VARCHAR(50)
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    status VARCHAR(20)
);

-- 产品表  
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200),
    category VARCHAR(100),
    price DECIMAL(10,2)
);

二、最常用的5类查询

1. 数据统计（每天都要用）

-- 统计订单数量
SELECT COUNT(*) FROM orders;

-- 统计销售总额
SELECT SUM(amount) FROM orders;

-- 计算平均订单金额
SELECT AVG(amount) FROM orders;

-- 找出最大/最小订单
SELECT MAX(amount), MIN(amount) FROM orders;

2. 分组分析（每周都要用）

-- 按日期统计销售
SELECT order_date, COUNT(*), SUM(amount)
FROM orders
GROUP BY order_date
ORDER BY order_date DESC;

-- 按产品类别统计
SELECT category, COUNT(*), SUM(price)
FROM products
GROUP BY category;

-- 按城市统计用户
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city
ORDER BY user_count DESC;

3. 条件筛选（随时要用）

-- 查找特定条件的订单
SELECT * FROM orders
WHERE amount > 1000
  AND status = '已完成'
  AND order_date >= '2026-01-01';

-- 查找特定用户
SELECT * FROM users
WHERE city = '北京'
  AND reg_date >= '2025-01-01';

4. 多表关联（经常要用）

-- 用户和订单关联
SELECT u.name, u.email, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = '已完成';

-- 订单和产品关联
SELECT o.id, p.name, p.price, o.amount
FROM orders o
JOIN products p ON o.product_id = p.id;

5. 排序和限制（展示数据用）

-- 销售额前十的用户
SELECT u.name, SUM(o.amount) as total
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total DESC
LIMIT 10;

-- 最近一个月的订单
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY order_date DESC;

三、实际业务场景

场景1：日报数据

-- 今日销售数据
SELECT 
    COUNT(*) as 订单数,
    SUM(amount) as 销售额,
    AVG(amount) as 平均订单额
FROM orders
WHERE DATE(order_date) = CURDATE();

场景2：用户分析

-- 用户活跃度分析
SELECT 
    CASE 
        WHEN reg_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN '新用户'
        WHEN reg_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) THEN '活跃用户'
        ELSE '老用户'
    END as 用户类型,
    COUNT(*) as 用户数
FROM users
GROUP BY 用户类型;

场景3：产品分析

-- 热销产品排行
SELECT 
    p.name,
    p.category,
    COUNT(o.id) as 销量,
    SUM(o.amount) as 销售额
FROM products p
JOIN orders o ON p.id = o.product_id
GROUP BY p.id, p.name, p.category
ORDER BY 销售额 DESC
LIMIT 20;