📊 数据库分析技术

🪟 窗口函数（MySQL 8.0+）

窗口函数在不改变行数的情况下，对一组相关行进行计算，是数据分析的利器。

-- 每个作者的文章按浏览量排名
SELECT
  author_id, title, view_count,
  RANK() OVER (
    PARTITION BY author_id
    ORDER BY view_count DESC
  ) AS rank_in_author
FROM articles
WHERE status = 2;

-- 计算每篇文章浏览量占该作者总量的百分比
SELECT
  title, view_count,
  SUM(view_count) OVER (PARTITION BY author_id) AS author_total,
  ROUND(
    view_count * 100.0 /
    SUM(view_count) OVER (PARTITION BY author_id), 2
  ) AS pct
FROM articles;

-- 滚动累计浏览量（按时间）
SELECT
  created_at, view_count,
  SUM(view_count) OVER (
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_views
FROM articles;

常用窗口函数

函数	说明
ROW_NUMBER()	连续唯一行号（1,2,3,4）
RANK()	并列排名，跳号（1,1,3）
DENSE_RANK()	并列排名，不跳号（1,1,2）
LAG(col, n)	取前 n 行的值（同比计算）
LEAD(col, n)	取后 n 行的值
NTILE(n)	将结果集分成 n 个桶

📈 高级分组：ROLLUP

GROUP BY WITH ROLLUP 可以自动生成小计和合计行，常用于报表统计。

-- 按作者和状态统计文章数，并自动汇总
SELECT
  IFNULL(author_id, '全部作者') AS author_id,
  IFNULL(status, '全部状态') AS status,
  COUNT(*) AS cnt,
  SUM(view_count) AS total_views
FROM articles
GROUP BY author_id, status WITH ROLLUP;

🗂️ JSON 数据处理

MySQL 5.7+ 支持原生 JSON 类型，8.0 进一步增强了 JSON 函数。

-- 存储 JSON 数据
CREATE TABLE user_profiles (
  id      INT PRIMARY KEY,
  profile JSON
);

INSERT INTO user_profiles VALUES (
  1,
  '{"name":"小明","skills":["MySQL","Redis"],"level":3}'
);

-- 提取 JSON 字段
SELECT
  id,
  profile->'$.name'           AS name,       -- 带引号
  profile->>'$.name'          AS name_str,   -- 不带引号
  JSON_EXTRACT(profile, '$.level') AS level
FROM user_profiles;

-- 查询 JSON 数组中包含特定值
SELECT * FROM user_profiles
WHERE JSON_CONTAINS(profile->'$.skills', '"MySQL"');

📦 OLAP vs OLTP

维度	OLTP（联机事务处理）	OLAP（联机分析处理）
典型系统	MySQL、PostgreSQL	ClickHouse、Doris、Hive
操作类型	增删改查，事务频繁	复杂聚合查询，只读为主
数据量	GB 级，热数据	TB~PB 级，历史数据
响应时间	毫秒级	秒~分钟级
数据模型	高度规范化（3NF）	星形/雪花模型

🧰 常用数据分析工具

工具	定位	特点
MySQL Workbench	MySQL 官方 GUI	免费，集成 EER 图、执行计划可视化
DBeaver	通用数据库客户端	开源，支持几乎所有数据库，功能强大
DataGrip	JetBrains 出品	智能补全强，付费，开发效率高
Redash / Metabase	BI 可视化	连接 MySQL，拖拽建图表，适合非技术人员
Python + pandas	数据分析编程	灵活，结合 matplotlib/seaborn 绘图