sql: Pivoting and Unpivoting Data in SQL using mysql 9.0
CREATE DATABASE IF NOT EXISTS JewelryAnalytics
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE JewelryAnalytics;
SET SESSION cte_max_recursion_depth = 100000;
SET SESSION group_concat_max_len = 1000000;
SET SESSION optimizer_switch = 'index_condition_pushdown=on,mrr=on,derived_merge=on,skip_scan=on';
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SET SESSION sql_safe_updates = 0;
SET SESSION innodb_lock_wait_timeout= 50;
SET SESSION time_zone = '+00:00';
SET SESSION max_execution_time = 0;
-- =====================================================================
-- MySQL 9.0 行列转置（Pivot / Unpivot）演示 —— 国际化珠宝行业
-- 主题：Pivoting and Unpivoting Data in SQL
-- 覆盖：OA / ERP / CRM / HR / MES / SCM 六域示例 + 万亿级高并发
-- + 国际时区时间差 + 国际各种货币结算
-- =====================================================================
-- §0.0 运行必读
-- 1) 本脚本在库 jewelryanalytics 上运行；事实表 agg_gl_trade_order（100 万行）
-- 与六域小表若已存在则直接复用，不重建（避免大批量造数触发客户端超时）。
-- 2) 会话初始化已设 max_execution_time=0，解除服务端单语句 30s 上限；
-- 若仍遇 2013 Lost connection，多为 MySQL Workbench 默认 30s 客户端读超时，
-- 需在 Workbench 偏好里把 "DBMS connection read timeout interval" 设为 0/600，
-- 或改用命令行客户端 mysql.exe 跑本文件。
-- 3) 演示/分析查询对空表才插入，可重复执行（幂等）。
--
-- §0.1 MySQL 9.0 vs SQL 标准：没有原生 PIVOT / UNPIVOT
-- SQL Server / Oracle 有：
-- SELECT * FROM t PIVOT(SUM(x) FOR cat IN ([A],[B],[C])) p;
-- SELECT * FROM wide UNPIVOT(val FOR col IN (q1,q2,q3,q4)) u;
-- MySQL 9.0 二者都没有，强行写会报 ERROR 1064（语法错误，见 V4 探针）。
-- 因此 MySQL 的转置只能靠：
-- * 透视 PIVOT -> 条件聚合：SUM(CASE WHEN cat=值 THEN x END)
-- * 逆透视 UNPIVOT -> 多列 UNION ALL（或 CROSS JOIN 常量集 + CASE）
-- 另外 MySQL 也不支持 SQL 标准的 FILTER (WHERE ...) 写法（见 V4 探针），
-- 条件聚合里的 CASE 就是 FILTER 的等价物。
--
-- §0.2 反模式速查表（本主题实测会踩）
-- ❌ SELECT ... FROM t PIVOT(...) -> 1064（MySQL 无原生 PIVOT）
-- ❌ SELECT ... FROM wide UNPIVOT(...) -> 1064（MySQL 无原生 UNPIVOT）
-- ❌ SUM(x) FILTER (WHERE cat='CN') -> 1064（无 FILTER 子句）
-- ❌ 对高基数列做动态透视（如 customer_id 20 万）-> GROUP_CONCAT 爆长 / 列爆炸
-- ❌ 对原始万亿行直接透视 -> 全表扫描，必须先预聚合
-- ❌ 跨币种 SUM(amount_orig) 当结算口径 -> 单位不同直接相加无意义
-- ⚠️ 透视列里的 NULL 必须用 SUM/MAX 等聚合包住，裸列会塌成 1 行
-- ⚠️ -- 注释后必须跟空格，否则被当减号运算符报错
-- =====================================================================
CREATE DATABASE IF NOT EXISTS JewelryAnalytics
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE JewelryAnalytics;
-- 会话初始化：扩大递归深度、放宽 GROUP_CONCAT、关闭引擎下推干扰、解除单语句超时
SET SESSION cte_max_recursion_depth = 100000;
SET SESSION group_concat_max_len = 1000000;
SET SESSION optimizer_switch = 'index_condition_pushdown=on,mrr=on,derived_merge=on,skip_scan=on';
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SET SESSION sql_safe_updates = 0;
SET SESSION innodb_lock_wait_timeout= 50;
SET SESSION time_zone = '+00:00';
SET SESSION max_execution_time = 0; -- 解除服务端单语句 30s 上限（治不了客户端读超时）
-- =====================================================================
-- §1 度量框架（pvt_* 前缀，避免与第 17/18 主题同名表冲突）
-- 用 performance_schema.session_status 的增量度量：
-- INNODB_ROWS_READ —— 扫描行数（透视前预聚合能大幅降低）
-- CREATED_TMP_TABLES —— 临时表数（条件聚合常需物化）
-- CREATED_TMP_DISK_TABLES —— 落盘临时表数
-- SORT_ROWS —— 排序行数
-- =====================================================================
DROP TABLE IF EXISTS pvt_demo_metric;
CREATE TABLE pvt_demo_metric (
label VARCHAR(60) PRIMARY KEY,
ts_before DATETIME(3),
ts_after DATETIME(3),
innodb_rows_read_d BIGINT,
created_tmp_tables_d BIGINT,
created_tmp_disk_tables_d BIGINT,
sort_rows_d BIGINT
);
DROP TABLE IF EXISTS pvt_demo_result;
CREATE TABLE pvt_demo_result (
name VARCHAR(60) PRIMARY KEY,
pass TINYINT,
info VARCHAR(400)
);
DROP TABLE IF EXISTS pvt_demo_probe;
CREATE TABLE pvt_demo_probe (
name VARCHAR(60) PRIMARY KEY,
caught_errno INT,
info VARCHAR(200)
);
DELIMITER //
DROP PROCEDURE IF EXISTS pvt_snap;
DROP PROCEDURE IF EXISTS pvt_measure;
DROP PROCEDURE IF EXISTS pvt_v;
DROP PROCEDURE IF EXISTS pvt_err_probe;
-- 抓取当前四个计数器快照
CREATE PROCEDURE pvt_snap(
OUT ts0 DATETIME(3), OUT r0 BIGINT, OUT t0 BIGINT, OUT d0 BIGINT, OUT s0 BIGINT)
BEGIN
SELECT NOW(3) INTO ts0;
SELECT VARIABLE_VALUE INTO r0 FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ';
SELECT VARIABLE_VALUE INTO t0 FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_TABLES';
SELECT VARIABLE_VALUE INTO d0 FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_DISK_TABLES';
SELECT VARIABLE_VALUE INTO s0 FROM performance_schema.session_status WHERE VARIABLE_NAME='SORT_ROWS';
END//
-- 执行一条 SQL 并记录其增量度量（PREPARE 吃 @pvt_sql 变量，不能直接吃过程参数）
CREATE PROCEDURE pvt_measure(IN p_label VARCHAR(60), IN p_sql TEXT)
BEGIN
DECLARE ts0 DATETIME(3); DECLARE r0 BIGINT; DECLARE t0 BIGINT; DECLARE d0 BIGINT; DECLARE s0 BIGINT;
DECLARE ts1 DATETIME(3); DECLARE r1 BIGINT; DECLARE t1 BIGINT; DECLARE d1 BIGINT; DECLARE s1 BIGINT;
CALL pvt_snap(ts0, r0, t0, d0, s0);
SET @pvt_sql = p_sql;
PREPARE pst FROM @pvt_sql;
EXECUTE pst;
DEALLOCATE PREPARE pst;
CALL pvt_snap(ts1, r1, t1, d1, s1);
INSERT INTO pvt_demo_metric(label, ts_before, ts_after,
innodb_rows_read_d, created_tmp_tables_d, created_tmp_disk_tables_d, sort_rows_d)
VALUES (p_label, ts0, ts1, r1-r0, t1-t0, d1-d0, s1-s0)
ON DUPLICATE KEY UPDATE
ts_before=ts0, ts_after=ts1,
innodb_rows_read_d=r1-r0, created_tmp_tables_d=t1-t0,
created_tmp_disk_tables_d=d1-d0, sort_rows_d=s1-s0;
END//
-- 记录一条自检结论
CREATE PROCEDURE pvt_v(IN p_name VARCHAR(60), IN p_pass TINYINT, IN p_info VARCHAR(400))
BEGIN
INSERT INTO pvt_demo_result(name, pass, info) VALUES (p_name, p_pass, p_info)
ON DUPLICATE KEY UPDATE pass=p_pass, info=p_info;
END//
-- 探针：执行一条“预期会报错”的 SQL，捕获其错误号（用于证明 MySQL 不支持某语法）
-- 关键：PREPARE 失败后 pst 不存在，DEALLOCATE 必须隔离在独立块里，
-- 否则它会再次进 handler 把已捕获的错误号覆盖掉。
CREATE PROCEDURE pvt_err_probe(IN p_name VARCHAR(60), IN p_sql TEXT)
BEGIN
DECLARE v_err INT DEFAULT 0;
DECLARE v_prepared INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @en = MYSQL_ERRNO;
SET v_err = @en;
END;
SET @pvt_sql = p_sql;
PREPARE pst FROM @pvt_sql;
IF v_err = 0 THEN
SET v_prepared = 1;
EXECUTE pst;
END IF;
INSERT INTO pvt_demo_probe(name, caught_errno, info)
VALUES (p_name, v_err, LEFT(p_sql, 120))
ON DUPLICATE KEY UPDATE caught_errno=v_err, info=LEFT(p_sql, 120);
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
IF v_prepared = 1 THEN
DEALLOCATE PREPARE pst;
END IF;
END;
END//
DELIMITER ;
-- =====================================================================
-- §2 实验室：复用既有 agg_* 数据，仅新建 4 张派生表
-- 直接复用：agg_gl_trade_order(100万行) / agg_dim_country / agg_dim_currency
-- / 六域小表（agg_oa_approval 等）
-- 新建派生：
-- pvt_agg_cat 国家 × 品类 预聚合（~50 行）—— 静态/动态透视源
-- pvt_agg_month_local 国家 × 本地月 预聚合（~360 行）—— 时区透视源
-- pvt_quarter_wide 国家 × Q1..Q4 宽表（10 行） —— UNION ALL 逆透视源
-- pvt_emp_pay HR 员工薪酬宽表（800 行） —— CROSS JOIN 逆透视源
-- =====================================================================
DROP PROCEDURE IF EXISTS pvt_lab_build;
DROP PROCEDURE IF EXISTS pvt_lab_reset;
DELIMITER //
CREATE PROCEDURE pvt_lab_build()
BEGIN
-- 维度表：若不存在则建（小表静态数据，含真实时区偏移 offset_min）
CREATE TABLE IF NOT EXISTS agg_dim_country (
country_code VARCHAR(4) PRIMARY KEY,
name_en VARCHAR(40),
region VARCHAR(20),
offset_min INT,
currency_code VARCHAR(4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO agg_dim_country
(country_code, name_en, region, offset_min, currency_code) VALUES
('AE','UAE','MEA',240,'AED'), ('AU','Australia','APAC',600,'AUD'),
('BR','Brazil','LATAM',-180,'BRL'), ('CN','China','APAC',480,'CNY'),
('DE','Germany','EMEA',60,'EUR'), ('FR','France','EMEA',60,'EUR'),
('GB','United Kingdom','EMEA',0,'GBP'), ('IN','India','APAC',330,'INR'),
('JP','Japan','APAC',540,'JPY'), ('US','United States','NA',-300,'USD');
-- 事实表：仅在缺失或为空时现场造 100 万行（日期模数 1093 与 10 互质，避免维度相关）
IF NOT EXISTS (
SELECT 1 FROM information_schema.tables
WHERE table_schema='jewelryanalytics' AND table_name='agg_gl_trade_order'
) OR (SELECT COUNT(*) FROM agg_gl_trade_order) = 0 THEN
CREATE TABLE IF NOT EXISTS agg_gl_trade_order (
order_id BIGINT PRIMARY KEY,
country_code VARCHAR(4),
currency_code VARCHAR(4),
category VARCHAR(20),
status VARCHAR(20),
order_ts DATETIME,
amount_orig DECIMAL(14,2),
rate DECIMAL(12,6),
amount_usd DECIMAL(14,2),
rate_date DATE,
customer_id INT,
channel VARCHAR(10),
KEY ix_cty_cat (country_code, category),
KEY ix_ts (order_ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO agg_gl_trade_order
(order_id, country_code, currency_code, category, status, order_ts,
amount_orig, rate, amount_usd, rate_date, customer_id, channel)
WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n<2000),
b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n<500)
SELECT order_id, country_code,
CASE country_code
WHEN 'AE' THEN 'AED' WHEN 'AU' THEN 'AUD' WHEN 'BR' THEN 'BRL'
WHEN 'CN' THEN 'CNY' WHEN 'DE' THEN 'EUR' WHEN 'FR' THEN 'EUR'
WHEN 'GB' THEN 'GBP' WHEN 'IN' THEN 'INR' WHEN 'JP' THEN 'JPY'
WHEN 'US' THEN 'USD' END AS currency_code,
category, status, order_ts, ROUND(amount_usd/rate,2) AS amount_orig,
rate, amount_usd, rate_date, customer_id, channel
FROM (
SELECT
(a.n-1)*500 + b.n AS order_id,
ELT(1 + ((a.n-1)*500+b.n) % 10, 'AE','AU','BR','CN','DE','FR','GB','IN','JP','US') AS country_code,
ELT(1 + ((a.n-1)*500+b.n) % 5, 'Ring','Necklace','Bracelet','Earring','Pendant') AS category,
ELT(1 + ((a.n-1)*500+b.n) % 4, 'paid','shipped','refunded','cancelled') AS status,
TIMESTAMPADD(SECOND, ((a.n-1)*500+b.n) % 86400,
TIMESTAMPADD(DAY, ((a.n-1)*500+b.n) % 1093, '2024-01-01 00:00:00')) AS order_ts,
ROUND(0.35 + (MOD(((a.n-1)*500+b.n)*2654435761, 1212200))/100.0, 2) AS amount_usd,
CASE ELT(1 + ((a.n-1)*500+b.n) % 10, 'AE','AU','BR','CN','DE','FR','GB','IN','JP','US')
WHEN 'AE' THEN 0.27 WHEN 'AU' THEN 0.66 WHEN 'BR' THEN 0.18 WHEN 'CN' THEN 0.14
WHEN 'DE' THEN 1.08 WHEN 'FR' THEN 1.08 WHEN 'GB' THEN 1.27 WHEN 'IN' THEN 0.012
WHEN 'JP' THEN 0.0067 WHEN 'US' THEN 1.0 END AS rate,
DATE(TIMESTAMPADD(DAY, ((a.n-1)*500+b.n) % 1093, '2024-01-01 00:00:00')) AS rate_date,
1 + (((a.n-1)*500+b.n) % 200000) AS customer_id,
ELT(1 + ((a.n-1)*500+b.n) % 3, 'web','app','store') AS channel
FROM a JOIN b
) x;
END IF;
-- 派生 1：国家 × 品类 预聚合（小表，透视主源）
DROP TABLE IF EXISTS pvt_agg_cat;
CREATE TABLE pvt_agg_cat AS
SELECT country_code, category,
COUNT(*) AS ord_cnt,
SUM(amount_usd) AS sum_usd
FROM agg_gl_trade_order
GROUP BY country_code, category;
-- 派生 2：国家 × 本地月 预聚合（时区偏移把 UTC 时刻归到本地自然月）
DROP TABLE IF EXISTS pvt_agg_month_local;
CREATE TABLE pvt_agg_month_local AS
SELECT o.country_code,
DATE_FORMAT(TIMESTAMPADD(MINUTE, dc.offset_min, o.order_ts), '%Y-%m') AS ym_local,
SUM(o.amount_usd) AS sum_usd
FROM agg_gl_trade_order o
JOIN agg_dim_country dc ON dc.country_code = o.country_code
GROUP BY o.country_code, DATE_FORMAT(TIMESTAMPADD(MINUTE, dc.offset_min, o.order_ts), '%Y-%m');
-- 派生 3：国家 × Q1..Q4 宽表（由本地月聚合再条件聚合得到，供 UNION ALL 逆透视）
DROP TABLE IF EXISTS pvt_quarter_wide;
CREATE TABLE pvt_quarter_wide AS
SELECT country_code,
SUM(CASE WHEN QUARTER(STR_TO_DATE(CONCAT(ym_local,'-01'),'%Y-%m-%d'))=1 THEN sum_usd ELSE 0 END) AS q1_usd,
SUM(CASE WHEN QUARTER(STR_TO_DATE(CONCAT(ym_local,'-01'),'%Y-%m-%d'))=2 THEN sum_usd ELSE 0 END) AS q2_usd,
SUM(CASE WHEN QUARTER(STR_TO_DATE(CONCAT(ym_local,'-01'),'%Y-%m-%d'))=3 THEN sum_usd ELSE 0 END) AS q3_usd,
SUM(CASE WHEN QUARTER(STR_TO_DATE(CONCAT(ym_local,'-01'),'%Y-%m-%d'))=4 THEN sum_usd ELSE 0 END) AS q4_usd
FROM pvt_agg_month_local
GROUP BY country_code;
-- 派生 4：HR 员工薪酬宽表（把工资拆成 4 个薪酬组件，供 CROSS JOIN 逆透视）
-- 注意：overtime_pay 取“工资 - 前三项”的残差，使四项之和恒等于工资（避免逐列舍入漂移）
DROP TABLE IF EXISTS pvt_emp_pay;
CREATE TABLE pvt_emp_pay AS
SELECT emp_id, dept,
ROUND(salary_usd*0.70, 2) AS base_pay,
ROUND(salary_usd*0.15, 2) AS bonus_pay,
ROUND(salary_usd*0.10, 2) AS allowance,
ROUND(salary_usd
- ROUND(salary_usd*0.70, 2)
- ROUND(salary_usd*0.15, 2)
- ROUND(salary_usd*0.10, 2), 2) AS overtime_pay
FROM agg_hr_employee;
END//
CREATE PROCEDURE pvt_lab_reset()
BEGIN
DROP TABLE IF EXISTS pvt_agg_cat;
DROP TABLE IF EXISTS pvt_agg_month_local;
DROP TABLE IF EXISTS pvt_quarter_wide;
DROP TABLE IF EXISTS pvt_emp_pay;
END//
DELIMITER ;
CALL pvt_lab_build();
-- =====================================================================
-- §3 静态透视（条件聚合）：用 CASE + GROUP BY 模拟 PIVOT
-- MySQL 没有原生 PIVOT；透视的套路是：
-- 把“行里的类别值”翻成“列”，每行用
-- SUM(CASE WHEN 类别=某值 THEN 度量 ELSE 0 END) 取该列的合计。
-- =====================================================================
-- §3.1 国家(行) × 品类(列) = 销售额(SUM amount_usd)
SELECT country_code,
SUM(CASE WHEN category='ring' THEN amount_usd ELSE 0 END) AS `ring`,
SUM(CASE WHEN category='necklace' THEN amount_usd ELSE 0 END) AS `necklace`,
SUM(CASE WHEN category='bracelet' THEN amount_usd ELSE 0 END) AS `bracelet`,
SUM(CASE WHEN category='earring' THEN amount_usd ELSE 0 END) AS `earring`,
SUM(CASE WHEN category='pendant' THEN amount_usd ELSE 0 END) AS `pendant`,
SUM(CASE WHEN category='watch' THEN amount_usd ELSE 0 END) AS `watch`,
SUM(amount_usd) AS `total`
FROM agg_gl_trade_order
GROUP BY country_code
ORDER BY country_code;
-- §3.2 透视“订单数”：COUNT(CASE ...) 统计各品类笔数
-- （CASE 无 ELSE 时返回 NULL，COUNT 不计入；等价于只数匹配行）
SELECT country_code,
COUNT(CASE WHEN category='ring' THEN 1 END) AS `ring`,
COUNT(CASE WHEN category='necklace' THEN 1 END) AS `necklace`,
COUNT(CASE WHEN category='bracelet' THEN 1 END) AS `bracelet`,
COUNT(CASE WHEN category='earring' THEN 1 END) AS `earring`,
COUNT(CASE WHEN category='pendant' THEN 1 END) AS `pendant`,
COUNT(CASE WHEN category='watch' THEN 1 END) AS `watch`,
COUNT(*) AS `total`
FROM agg_gl_trade_order
GROUP BY country_code
ORDER BY country_code;
-- §3.3 反模式（切勿这样写）：透视列不加聚合函数
-- 错误写法：SELECT country_code, CASE WHEN category='ring' THEN amount_usd END AS ring_bad
-- FROM agg_gl_trade_order GROUP BY country_code;
-- 在 ONLY_FULL_GROUP_BY 下直接报 ERROR 1055；即便关闭该模式，category 也是“每组任意一行”
-- 的不确定值，结果塌成错值。透视列必须包在 SUM/COUNT 等聚合里（见 §3.1/§3.2）。
-- =====================================================================
-- §4 动态透视：透视列不写死，由数据里的类别值自动生成
-- 用 GROUP_CONCAT 把类别拼成 CASE 列表，再 PREPARE / EXECUTE。
-- 报表系统里“列随数据变化”的标准做法（原生 PIVOT 缺失时的替代）。
-- =====================================================================
-- §4.1 生成动态列（每个品类一个 SUM(CASE...)）
SET @dyn_cols = NULL;
SELECT GROUP_CONCAT(
DISTINCT
CONCAT('SUM(CASE WHEN category=', QUOTE(category),
' THEN amount_usd ELSE 0 END) AS ', QUOTE(category))
ORDER BY category
) INTO @dyn_cols
FROM pvt_agg_cat;
-- §4.2 拼出完整透视 SQL 并执行（结果写入 pvt_dyn_tmp 便于后续校验）
DROP TABLE IF EXISTS pvt_dyn_tmp;
SET @dyn_sql = CONCAT(
'CREATE TABLE pvt_dyn_tmp AS ',
'SELECT country_code, ', @dyn_cols, ', SUM(amount_usd) AS `total` ',
'FROM agg_gl_trade_order GROUP BY country_code'
);
PREPARE pst FROM @dyn_sql;
EXECUTE pst;
DEALLOCATE PREPARE pst;
SELECT * FROM pvt_dyn_tmp ORDER BY country_code;
-- §4.3 动态透视的“列数”由 DISTINCT 品类数决定（防止高基数列爆炸，见 §9.3）
SELECT COUNT(DISTINCT category) AS dynamic_pivot_columns FROM pvt_agg_cat;
-- =====================================================================
-- §5 逆透视 UNION ALL：把“宽表的多列”竖成“指标名 + 值”两列
-- 源：pvt_quarter_wide（国家 × Q1..Q4 宽表）；目标：国家 × 季度 × 金额
-- MySQL 没有原生 UNPIVOT，逆透视 = 每个待拆列写一段 SELECT 再用 UNION ALL 拼起。
-- =====================================================================
SELECT country_code, 'Q1' AS `quarter`, q1_usd AS `amount` FROM pvt_quarter_wide
UNION ALL
SELECT country_code, 'Q2' AS `quarter`, q2_usd AS `amount` FROM pvt_quarter_wide
UNION ALL
SELECT country_code, 'Q3' AS `quarter`, q3_usd AS `amount` FROM pvt_quarter_wide
UNION ALL
SELECT country_code, 'Q4' AS `quarter`, q4_usd AS `amount` FROM pvt_quarter_wide
ORDER BY country_code, `quarter`;
-- §5.1 校验：逆透视后行数 = 国家数 × 4 个季度；金额总和 = 原始季度金额总和
SELECT COUNT(*) AS unpivot_rows,
ROUND(SUM(`amount`), 2) AS unpivot_total
FROM (
SELECT country_code, 'Q1' AS `quarter`, q1_usd AS `amount` FROM pvt_quarter_wide
UNION ALL SELECT country_code, 'Q2', q2_usd FROM pvt_quarter_wide
UNION ALL SELECT country_code, 'Q3', q3_usd FROM pvt_quarter_wide
UNION ALL SELECT country_code, 'Q4', q4_usd FROM pvt_quarter_wide
) u;
-- =====================================================================
-- §6 逆透视 CROSS JOIN：用常量集 × 原表，一次扫描完成逆透视
-- 适合“宽列很多”的场景（不用写 N 段 UNION ALL，扫描一次原表即可）。
-- 源：pvt_emp_pay（员工 × 4 个薪酬组件宽表）
-- =====================================================================
SELECT e.emp_id, e.dept, k.comp AS `component`,
CASE k.comp
WHEN 'base' THEN e.base_pay
WHEN 'bonus' THEN e.bonus_pay
WHEN 'allow' THEN e.allowance
WHEN 'overtime' THEN e.overtime_pay
END AS `amount`
FROM pvt_emp_pay e
CROSS JOIN (
SELECT 'base' AS comp UNION ALL
SELECT 'bonus' UNION ALL
SELECT 'allow' UNION ALL
SELECT 'overtime'
) k
ORDER BY e.emp_id, k.comp
LIMIT 16;
-- §6.1 校验：行数 = 员工数 × 4 个组件；
-- 四组件比例 0.70/0.15/0.10/0.05 之和恰为 1.0，故组件总额 = 原工资总额
SELECT COUNT(*) AS rows_unpivoted,
ROUND(SUM(`amount`), 2) AS components_total,
(SELECT ROUND(SUM(salary_usd), 2) FROM agg_hr_employee) AS salary_total
FROM (
SELECT e.emp_id, k.comp,
CASE k.comp
WHEN 'base' THEN e.base_pay
WHEN 'bonus' THEN e.bonus_pay
WHEN 'allow' THEN e.allowance
WHEN 'overtime' THEN e.overtime_pay
END AS `amount`
FROM pvt_emp_pay e
CROSS JOIN (
SELECT 'base' AS comp UNION ALL SELECT 'bonus' UNION ALL
SELECT 'allow' UNION ALL SELECT 'overtime'
) k
) z;
-- =====================================================================
-- §7 国际时区透视：同一 UTC 时刻因偏移归到不同“本地月”
-- 用 pvt_agg_month_local（已按 TIMESTAMPADD(offset_min) 计算本地月）做透视。
-- 注意：MySQL 的 CONVERT_TZ 依赖时区表（本机为空 -> 恒 NULL），
-- 故用“UTC 存时间 + 固定偏移分钟数”硬编码换算本地日/月。
-- =====================================================================
-- §7.1 本地月(行) × 部分国家(列) = 销售额
SELECT ym_local,
SUM(CASE WHEN country_code='CN' THEN sum_usd ELSE 0 END) AS `CN`,
SUM(CASE WHEN country_code='US' THEN sum_usd ELSE 0 END) AS `US`,
SUM(CASE WHEN country_code='BR' THEN sum_usd ELSE 0 END) AS `BR`,
SUM(CASE WHEN country_code='AE' THEN sum_usd ELSE 0 END) AS `AE`,
SUM(CASE WHEN country_code='JP' THEN sum_usd ELSE 0 END) AS `JP`,
SUM(sum_usd) AS `total`
FROM pvt_agg_month_local
GROUP BY ym_local
ORDER BY ym_local
LIMIT 15;
-- §7.2 时区影响：本地月集合 vs UTC 月集合行数不同（偏移把边界日推到邻月）
SELECT
(SELECT COUNT(DISTINCT DATE_FORMAT(order_ts,'%Y-%m')) FROM agg_gl_trade_order) AS utc_months,
(SELECT COUNT(DISTINCT ym_local) FROM pvt_agg_month_local) AS local_months;
-- 以 BR(-180) 与 CN(+480) 为例：本地月跨度不同（边界日移位）
SELECT country_code,
COUNT(DISTINCT ym_local) AS local_month_cnt
FROM pvt_agg_month_local
WHERE country_code IN ('CN','BR')
GROUP BY country_code;
-- §7.3 口径守恒：本地月透视总额 = UTC 月透视总额 = 事实总额（只是“归属月”不同）
SELECT
(SELECT ROUND(SUM(sum_usd), 2) FROM pvt_agg_month_local) AS local_total,
(SELECT ROUND(SUM(amount_usd), 2) FROM agg_gl_trade_order) AS fact_total;
-- =====================================================================
-- §8 多币种结算：透视只能以“统一口径(amount_usd)”为度量
-- 9 种本币(AED/AUD/BRL/CNY/EUR/GBP/INR/JPY/USD)不能直接相加，
-- 必须先按汇率归一到美元，再透视。
-- =====================================================================
-- §8.1 正确：按币种透视其“美元值”（同一口径才能相加）
SELECT currency_code,
ROUND(SUM(amount_usd), 2) AS `usd_total`,
COUNT(*) AS `orders`
FROM agg_gl_trade_order
GROUP BY currency_code
ORDER BY `usd_total` DESC;
-- §8.2 反模式：把各币种“本币金额”直接相加 -> 单位不同，纯属错误
SELECT ROUND(SUM(amount_orig), 2) AS `orig_total_wrong`,
ROUND(SUM(amount_usd), 2) AS `usd_total_right`
FROM agg_gl_trade_order;
-- 两者必然不等（orig 是 9 种货币的本币混加，usd 是归一后美元）
-- §8.3 透视 × 国家 × 币种 也须用 amount_usd：国家(行) × 币种(列)
SELECT country_code,
SUM(CASE WHEN currency_code='USD' THEN amount_usd ELSE 0 END) AS `USD`,
SUM(CASE WHEN currency_code='EUR' THEN amount_usd ELSE 0 END) AS `EUR`,
SUM(CASE WHEN currency_code='CNY' THEN amount_usd ELSE 0 END) AS `CNY`,
SUM(CASE WHEN currency_code='JPY' THEN amount_usd ELSE 0 END) AS `JPY`,
SUM(amount_usd) AS `total`
FROM agg_gl_trade_order
GROUP BY country_code
ORDER BY country_code;
-- =====================================================================
-- §9 万亿级高并发：先行预聚合，再透视
-- 透视本质是全表扫描 + 分组，对原始万亿行直接透视不可行；
-- 正确做法是先预聚合（物化汇总表），再在小表上透视。
-- =====================================================================
-- §9.1 度量：原始 100 万行透视 vs 预聚合 30 行透视，看扫描行数差
CALL pvt_measure('pvt_raw_1M',
'SELECT country_code, SUM(CASE WHEN category=''ring'' THEN amount_usd ELSE 0 END) AS r, SUM(amount_usd) AS t FROM agg_gl_trade_order GROUP BY country_code');
CALL pvt_measure('pvt_preagg',
'SELECT country_code, SUM(CASE WHEN category=''ring'' THEN sum_usd ELSE 0 END) AS r, SUM(sum_usd) AS t FROM pvt_agg_cat GROUP BY country_code');
-- §9.2 覆盖索引的真实作用（诚实结论）
-- 事实表已有 (country_code, category) 复合索引 ix_cty_cat。
-- 对“无过滤条件的全量透视”它并不能免去扫描——所有行都要读、都要算 CASE；
-- 它顶多让 GROUP BY 免去“回表取度量列”的随机 I/O（索引覆盖）。
-- 真正把量级砍下来的只有“预聚合”（§9.1：扫描行数 1,000,000 -> 30）。
-- 结论：透视的高并发杠杆 = 预聚合 / 物化汇总，而非索引。
-- §9.3 动态透视的“列爆炸”红线
SELECT COUNT(DISTINCT customer_id) AS high_card_column FROM agg_gl_trade_order;
-- 若对 customer_id（20 万不同值）做动态透视 ->
-- GROUP_CONCAT 产生 20 万段 CASE -> SQL 超长(> max_allowed_packet)、内存爆、列数超上限。
-- 务必只对“低基数列”（国家/品类/状态/月份）做透视；高基数维度用明细或预聚合。
-- =====================================================================
-- §10 六域透视 / 逆透视模板（珠宝集团 OA/ERP/CRM/HR/MES/SCM）
-- 每个域都演示“条件聚合透视”；逆透视见 §5/§6（通用，套用即可）。
-- =====================================================================
-- §10.1 OA：部门(行) × 审批状态(列) = 单据数
SELECT dept,
COUNT(CASE WHEN status='approved' THEN 1 END) AS `approved`,
COUNT(CASE WHEN status='pending' THEN 1 END) AS `pending`,
COUNT(CASE WHEN status='rejected' THEN 1 END) AS `rejected`,
COUNT(*) AS `total`
FROM agg_oa_approval
GROUP BY dept
ORDER BY dept;
-- §10.2 ERP：品类(行) × 仓库(列) = 库存量
SELECT category,
SUM(CASE WHEN warehouse='LA_US' THEN stock_qty ELSE 0 END) AS `LA_US`,
SUM(CASE WHEN warehouse='FR_EU' THEN stock_qty ELSE 0 END) AS `FR_EU`,
SUM(CASE WHEN warehouse='DXB_ME' THEN stock_qty ELSE 0 END) AS `DXB_ME`,
SUM(CASE WHEN warehouse='SG_Asia' THEN stock_qty ELSE 0 END) AS `SG_Asia`,
SUM(CASE WHEN warehouse='SH_China'THEN stock_qty ELSE 0 END) AS `SH_China`,
SUM(stock_qty) AS `total`
FROM agg_erp_product
GROUP BY category
ORDER BY category;
-- §10.3 CRM：地区(行) × 渠道(列) = 销售额(USD)
SELECT region,
SUM(CASE WHEN channel='app' THEN amount_usd ELSE 0 END) AS `app`,
SUM(CASE WHEN channel='store' THEN amount_usd ELSE 0 END) AS `store`,
SUM(CASE WHEN channel='web' THEN amount_usd ELSE 0 END) AS `web`,
SUM(CASE WHEN channel='wholesale' THEN amount_usd ELSE 0 END) AS `wholesale`,
SUM(amount_usd) AS `total`
FROM agg_crm_order
GROUP BY region
ORDER BY region;
-- §10.4 HR：部门(行) × 办公地点(列) = 人数
SELECT dept,
COUNT(CASE WHEN location='New York' THEN 1 END) AS `New_York`,
COUNT(CASE WHEN location='Tokyo' THEN 1 END) AS `Tokyo`,
COUNT(CASE WHEN location='Berlin' THEN 1 END) AS `Berlin`,
COUNT(CASE WHEN location='Paris' THEN 1 END) AS `Paris`,
COUNT(CASE WHEN location='London' THEN 1 END) AS `London`,
COUNT(CASE WHEN location='Dubai' THEN 1 END) AS `Dubai`,
COUNT(CASE WHEN location='Sydney' THEN 1 END) AS `Sydney`,
COUNT(CASE WHEN location='Shanghai' THEN 1 END) AS `Shanghai`,
COUNT(*) AS `total`
FROM agg_hr_employee
GROUP BY dept
ORDER BY dept;
-- §10.5 MES：产线(行) × 班次(列) = 产量
SELECT line,
SUM(CASE WHEN shift='Day' THEN qty ELSE 0 END) AS `Day`,
SUM(CASE WHEN shift='Swing' THEN qty ELSE 0 END) AS `Swing`,
SUM(CASE WHEN shift='Night' THEN qty ELSE 0 END) AS `Night`,
SUM(qty) AS `total`
FROM agg_mes_work_order
GROUP BY line
ORDER BY line;
-- §10.6 SCM：承运商(行) × 物流状态(列) = 重量(kg)
SELECT carrier,
SUM(CASE WHEN status='in_transit' THEN weight_kg ELSE 0 END) AS `in_transit`,
SUM(CASE WHEN status='customs' THEN weight_kg ELSE 0 END) AS `customs`,
SUM(CASE WHEN status='delayed' THEN weight_kg ELSE 0 END) AS `delayed`,
SUM(CASE WHEN status='delivered' THEN weight_kg ELSE 0 END) AS `delivered`,
SUM(weight_kg) AS `total`
FROM agg_scm_shipment
GROUP BY carrier
ORDER BY carrier;
-- =====================================================================
-- §11 自检 V1–V12（断言均数据驱动，不写死品类/列数）
-- =====================================================================
-- V1：静态透视行数 = 不同国家数
SET @v1 = (SELECT COUNT(*) FROM (SELECT country_code FROM agg_gl_trade_order GROUP BY country_code) t);
CALL pvt_v('V1_static_pivot_row_count', IF(@v1=10,1,0), CONCAT('rows=',@v1));
-- V2：静态透视口径守恒 = SUM(pvt_agg_cat.sum_usd) = 事实总额
SET @cat_total = (SELECT ROUND(SUM(sum_usd),2) FROM pvt_agg_cat);
SET @fact_total = (SELECT ROUND(SUM(amount_usd),2) FROM agg_gl_trade_order);
CALL pvt_v('V2_static_pivot_conserves_total', IF(@cat_total=@fact_total,1,0), CONCAT('cat=',@cat_total,' fact=',@fact_total));
-- V3：动态透视总额 = 事实总额（动态透视结果在 pvt_dyn_tmp）
SET @dyn_total = (SELECT ROUND(SUM(`total`),2) FROM pvt_dyn_tmp);
CALL pvt_v('V3_dynamic_pivot_conserves_total', IF(@dyn_total=@fact_total,1,0), CONCAT('dyn=',@dyn_total,' fact=',@fact_total));
-- V4：原生 PIVOT / UNPIVOT / FILTER 均报 ERROR 1064（MySQL 不支持）
CALL pvt_err_probe('V4a_native_pivot', 'SELECT * FROM agg_gl_trade_order PIVOT(SUM(amount_usd) FOR country_code IN (''CN'',''US'')) p');
CALL pvt_err_probe('V4b_native_unpivot', 'SELECT * FROM pvt_quarter_wide UNPIVOT(`amount` FOR `quarter` IN (q1_usd,q2_usd,q3_usd,q4_usd)) u');
CALL pvt_err_probe('V4c_filter_clause', 'SELECT SUM(amount_usd) FILTER (WHERE country_code=''CN'') FROM agg_gl_trade_order');
SET @v4 = (SELECT COUNT(*) FROM pvt_demo_probe WHERE name IN ('V4a_native_pivot','V4b_native_unpivot','V4c_filter_clause') AND caught_errno=1064);
CALL pvt_v('V4_no_native_pivot_unpivot_filter', IF(@v4=3,1,0), CONCAT('err1064_count=',@v4));
-- V5：UNION ALL 逆透视行数 = 国家数 × 4 个季度
SET @countries = (SELECT COUNT(*) FROM pvt_quarter_wide);
SET @v5 = (SELECT COUNT(*) FROM (
SELECT country_code, 'Q1' AS q, q1_usd AS a FROM pvt_quarter_wide
UNION ALL SELECT country_code, 'Q2', q2_usd FROM pvt_quarter_wide
UNION ALL SELECT country_code, 'Q3', q3_usd FROM pvt_quarter_wide
UNION ALL SELECT country_code, 'Q4', q4_usd FROM pvt_quarter_wide
) u);
CALL pvt_v('V5_union_unpivot_rows', IF(@v5=@countries*4,1,0), CONCAT('rows=',@v5,' expect=',@countries*4));
-- V6：CROSS JOIN 逆透视行数 = 员工数 × 4；组件总额 = 工资总额
SET @emp_n = (SELECT COUNT(*) FROM pvt_emp_pay);
SET @v6_rows = (SELECT COUNT(*) FROM (
SELECT e.emp_id, k.comp,
CASE k.comp WHEN 'base' THEN e.base_pay WHEN 'bonus' THEN e.bonus_pay
WHEN 'allow' THEN e.allowance WHEN 'overtime' THEN e.overtime_pay END AS amt
FROM pvt_emp_pay e
CROSS JOIN (SELECT 'base' AS comp UNION ALL SELECT 'bonus' UNION ALL SELECT 'allow' UNION ALL SELECT 'overtime') k
) z);
SET @v6_sum = (SELECT ROUND(SUM(
CASE k.comp WHEN 'base' THEN e.base_pay WHEN 'bonus' THEN e.bonus_pay
WHEN 'allow' THEN e.allowance WHEN 'overtime' THEN e.overtime_pay END),2)
FROM pvt_emp_pay e
CROSS JOIN (SELECT 'base' AS comp UNION ALL SELECT 'bonus' UNION ALL SELECT 'allow' UNION ALL SELECT 'overtime') k);
SET @sal_sum = (SELECT ROUND(SUM(salary_usd),2) FROM agg_hr_employee);
CALL pvt_v('V6_cross_unpivot', IF(@v6_rows=@emp_n*4 AND @v6_sum=@sal_sum,1,0), CONCAT('rows=',@v6_rows,' comp=',@v6_sum,' sal=',@sal_sum));
-- V7：时区口径守恒 local_total = fact_total；且本地月集合 ≠ UTC 月集合（边界移位）
SET @local_total = (SELECT ROUND(SUM(sum_usd),2) FROM pvt_agg_month_local);
SET @utc_months = (SELECT COUNT(DISTINCT DATE_FORMAT(order_ts,'%Y-%m')) FROM agg_gl_trade_order);
SET @local_months= (SELECT COUNT(DISTINCT ym_local) FROM pvt_agg_month_local);
CALL pvt_v('V7_tz_conserves_and_shifts', IF(@local_total=@fact_total AND @local_months<>@utc_months,1,0), CONCAT('local=',@local_total,' fact=',@fact_total,' utcM=',@utc_months,' locM=',@local_months));
-- V8：多币种反模式 orig_total ≠ usd_total（单位不同不能直接相加）
SET @orig_t = (SELECT ROUND(SUM(amount_orig),2) FROM agg_gl_trade_order);
SET @usd_t = (SELECT ROUND(SUM(amount_usd),2) FROM agg_gl_trade_order);
CALL pvt_v('V8_currency_anti_pattern', IF(@orig_t<>@usd_t,1,0), CONCAT('orig=',@orig_t,' usd=',@usd_t));
-- V9：预聚合后透视扫描行数远小于原始（来自 §9.1 度量）
SET @raw_read = (SELECT COALESCE(innodb_rows_read_d,0) FROM pvt_demo_metric WHERE label='pvt_raw_1M');
SET @pre_read = (SELECT COALESCE(innodb_rows_read_d,0) FROM pvt_demo_metric WHERE label='pvt_preagg');
CALL pvt_v('V9_preaggregate_cuts_scan', IF(@raw_read>100000 AND @pre_read<1000 AND @raw_read>@pre_read*10,1,0), CONCAT('raw=',@raw_read,' pre=',@pre_read));
-- V10：动态透视列数 = DISTINCT 品类数（有界，不爆炸）；行数 = 国家数
SET @dyn_cols_n = (SELECT COUNT(DISTINCT category) FROM pvt_agg_cat);
SET @dyn_rows = (SELECT COUNT(*) FROM pvt_dyn_tmp);
SET @case_cnt = (SELECT (CHAR_LENGTH(@dyn_cols) - CHAR_LENGTH(REPLACE(@dyn_cols,'SUM(CASE',''))) DIV CHAR_LENGTH('SUM(CASE'));
CALL pvt_v('V10_dynamic_pivot_bounded', IF(@case_cnt=@dyn_cols_n AND @dyn_rows=10,1,0), CONCAT('cases=',@case_cnt,' cats=',@dyn_cols_n,' rows=',@dyn_rows));
-- V11：UNION ALL 逆透视 == CROSS JOIN 逆透视（同一宽表 pvt_quarter_wide）
SET @ua_total = (SELECT ROUND(SUM(`amount`),2) FROM (
SELECT 'Q1' AS q, q1_usd AS `amount` FROM pvt_quarter_wide UNION ALL
SELECT 'Q2', q2_usd FROM pvt_quarter_wide UNION ALL
SELECT 'Q3', q3_usd FROM pvt_quarter_wide UNION ALL
SELECT 'Q4', q4_usd FROM pvt_quarter_wide) u);
SET @ua_rows = (SELECT COUNT(*) FROM (
SELECT 'Q1' AS q, q1_usd AS `amount` FROM pvt_quarter_wide UNION ALL
SELECT 'Q2', q2_usd FROM pvt_quarter_wide UNION ALL
SELECT 'Q3', q3_usd FROM pvt_quarter_wide UNION ALL
SELECT 'Q4', q4_usd FROM pvt_quarter_wide) u);
SET @cj_total = (SELECT ROUND(SUM(CASE k.q WHEN 'Q1' THEN q1_usd WHEN 'Q2' THEN q2_usd WHEN 'Q3' THEN q3_usd WHEN 'Q4' THEN q4_usd END),2)
FROM pvt_quarter_wide
CROSS JOIN (SELECT 'Q1' AS q UNION ALL SELECT 'Q2' UNION ALL SELECT 'Q3' UNION ALL SELECT 'Q4') k);
SET @cj_rows = (SELECT COUNT(*) FROM pvt_quarter_wide
CROSS JOIN (SELECT 'Q1' AS q UNION ALL SELECT 'Q2' UNION ALL SELECT 'Q3' UNION ALL SELECT 'Q4') k);
CALL pvt_v('V11_two_unpivot_methods_equal', IF(@ua_rows=@cj_rows AND @ua_total=@cj_total,1,0), CONCAT('ua=',@ua_rows,'/',@ua_total,' cj=',@cj_rows,'/',@cj_total));
-- V12：综合——事实行数 = 1,000,000（复用未重建）+ 所有 V1–V11 通过
SET @fact_rows = (SELECT COUNT(*) FROM agg_gl_trade_order);
SET @passed = (SELECT COUNT(*) FROM pvt_demo_result WHERE pass=1);
SET @total_v = (SELECT COUNT(*) FROM pvt_demo_result);
CALL pvt_v('V12_overall', IF(@fact_rows=1000000 AND @passed=@total_v AND @total_v>=11,1,0), CONCAT('fact_rows=',@fact_rows,' passed=',@passed,'/',@total_v));
-- 汇总输出
SELECT name, pass, info FROM pvt_demo_result ORDER BY name;
SELECT name, caught_errno, info FROM pvt_demo_probe ORDER BY name;
SELECT label, innodb_rows_read_d, created_tmp_tables_d, created_tmp_disk_tables_d, sort_rows_d
FROM pvt_demo_metric ORDER BY label;
SELECT CONCAT('PASS=',SUM(pass),' / TOTAL=',COUNT(*),' / FAIL=',SUM(1-pass)) AS verdict FROM pvt_demo_result;
-- =====================================================================
-- §12 总结
-- =====================================================================
-- §12.1 MySQL 9.0 转置能力对照表
SELECT '原生 PIVOT' AS 能力, '不支持' AS 状态, 'ERROR 1064' AS 报错, '用 SUM(CASE WHEN…) 条件聚合替代' AS 替代方案;
SELECT '原生 UNPIVOT' AS 能力, '不支持' AS 状态, 'ERROR 1064' AS 报错, '用 多段 SELECT + UNION ALL 替代' AS 替代方案;
SELECT 'FILTER(WHERE…)' AS 能力, '不支持' AS 状态, 'ERROR 1064' AS 报错, '用 CASE WHEN…THEN…END 写在聚合内替代' AS 替代方案;
SELECT '条件聚合透视' AS 能力, '支持' AS 状态, '—' AS 报错, 'SUM/COUNT(CASE WHEN 类别=值 THEN 度量 END)' AS 替代方案;
SELECT '动态透视' AS 能力, '支持' AS 状态, '—' AS 报错, 'GROUP_CONCAT 拼 CASE 列表 + PREPARE/EXECUTE' AS 替代方案;
SELECT '逆透视 UNION ALL' AS 能力, '支持' AS 状态, '—' AS 报错, '每待拆列一段 SELECT 再 UNION ALL' AS 替代方案;
SELECT '逆透视 CROSS JOIN' AS 能力, '支持' AS 状态, '—' AS 报错, '常量集 × 原表 一次扫描 + CASE 取值' AS 替代方案;
-- §12.2 关键度量（来自 §9.1，原始 1M 透视 vs 预聚合 30 行透视）
SELECT label, innodb_rows_read_d AS 扫描行数, sort_rows_d AS 排序行数
FROM pvt_demo_metric WHERE label IN ('pvt_raw_1M','pvt_preagg') ORDER BY label;
-- §12.3 三条结论
-- ① MySQL 没有原生 PIVOT/UNPIVOT/FILTER；透视 = 条件聚合，逆透视 = UNION ALL（或 CROSS JOIN）。
-- ② 透视列里的 NULL 必须用 SUM/COUNT 等聚合包住；裸列会塌成不确定值（§3.3 反模式）。
-- ③ 万亿级：透视前必须先“预聚合 / 物化汇总”（扫描行数 1,000,000 -> 30）；
-- 覆盖索引只省回表 I/O，不省扫描；动态透视只对低基数列做，防列爆炸。
-- §12.4 方法清单
-- 1) 透视固定列 -> 条件聚合，列名用反引号避免关键字冲突
-- 2) 透视列随数据变 -> GROUP_CONCAT 生成 CASE 列表 + 动态 SQL
-- 3) 逆透视少量宽列 -> UNION ALL；宽列很多 -> CROSS JOIN 常量集（一次扫描）
-- 4) 多币种 -> 一律 SUM(amount_usd) 归一，绝不可 SUM(amount_orig) 混加
-- 5) 国际时区 -> UTC 存时间，TIMESTAMPADD(固定偏移) 换算本地月/日（CONVERT_TZ 本机恒 NULL）
-- 6) 高并发 -> 预聚合小表再透视；动态透视限制基数列
-- 7) 六种域 -> OA 单据/ERP 库存/CRM 销售/HR 人力/MES 产量/SCM 物流 统一用条件聚合
-- 8) 口径守恒 -> 透视总额 = 事实总额（仅“归类”变化，金额不变）
-- §12.5 脚本结构
-- _p00 头块/会话初始化 | _p01 度量框架 | _p02 实验室(建派生表)
-- _p03 静态透视 | _p04 动态透视 | _p05 UNION ALL 逆透视 | _p06 CROSS JOIN 逆透视
-- _p07 时区透视 | _p08 多币种透视 | _p09 高并发 | _p10 六域模板
-- _p11 自检 V1–V12 | _p12 总结
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号