sql: SQL Aggregate Functions and Grouping Sets 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';
-- =====================================================================
-- 第 17 主题：SQL Aggregate Functions and Grouping Sets
-- 用 MySQL 9.0 演示「聚合函数 + GROUP BY + HAVING + 分组集」在
-- 国际化珠宝集团（OA/ERP/CRM/HR/MES/SCM）全链路的应用，并兼顾
-- 万亿数据高并发、国际时区、国际多币种结算。
-- =====================================================================
--
-- ┌──────────────────────────────────────────────────────────────────┐
-- │ §0.0 运行方式必读（务必先看） │
-- └──────────────────────────────────────────────────────────────────┘
-- 1) 用 MySQL 9.0 客户端运行，字符集必须 utf8mb4：
-- mysql -u root -p --default-character-set=utf8mb4 -t < 本文件
-- 或在脚本开头已内置：USE JewelryAnalytics; 与若干 SET SESSION。
-- 2) 本脚本幂等：连跑多次结果一致（先 CREATE TABLE IF NOT EXISTS +
-- 数据仅在空表时插入），可反复执行证明稳定性。
-- 3) 时区：本机 mysql.time_zone_name 表为空（命名时区未加载），
-- CONVERT_TZ() 一律返回 NULL —— 因此本脚本统一用
-- SET SESSION time_zone='+00:00' 让 TIMESTAMP/DATETIME 当 UTC 存，
-- 本地时间用 TIMESTAMPADD(MINUTE, 固定偏移, ts) 硬编码换算。
-- 4) 本主题最大坑：MySQL 没有 SQL 标准的 GROUPING SETS / CUBE 子句！
-- · GROUP BY ... WITH ROLLUP —— MySQL 原生支持（层次小计）
-- · GROUPING() 函数 —— MySQL 原生支持（区分超聚行）
-- · GROUP BY CUBE(...) —— 报 3889（被路由到 HeatWave 副引擎，本机未配）
-- · GROUP BY GROUPING SETS(...)—— 报 1064（语法不支持）
-- → 任意分组集 / 立方体必须用「多个 GROUP BY 的 UNION ALL」模拟。
-- 5) APPROX_COUNT_DISTINCT 在本 9.0.1 构建中不存在（报 1305）；
-- 万亿级去重计数改用「预聚合汇总表」或应用层 HLL（如 Redis PFCOUNT）。
--
-- ┌──────────────────────────────────────────────────────────────────┐
-- │ §0.1 MySQL 9.0 与 SQL Server / PostgreSQL 的分组集差异 │
-- └──────────────────────────────────────────────────────────────────┘
-- SQL Server / PostgreSQL 支持：
-- GROUP BY ROLLUP(a,b)
-- GROUP BY CUBE(a,b)
-- GROUP BY GROUPING SETS ((a),(b),(a,b),())
-- MySQL 9.0 仅支持：
-- GROUP BY a,b WITH ROLLUP （原生，层次小计）
-- GROUPING(a) （原生，0/1 标识超聚行）
-- 任意 SETS/CUBE （必须用 UNION ALL 模拟，见 §06）
--
-- ┌──────────────────────────────────────────────────────────────────┐
-- │ §0.2 反模式速查表 │
-- └──────────────────────────────────────────────────────────────────┘
-- 反模式 后果 / 报错
-- -------------------------------------- ------------------------------
-- GROUP BY CUBE(...) 3889（HeatWave 副引擎未配）
-- GROUP BY GROUPING SETS(...) 1064（语法不支持）
-- HAVING 引用未分组也未聚合的列 1054（ONLY_FULL_GROUP_BY）
-- APPROX_COUNT_DISTINCT(...) 1305（本构建缺失）
-- CONVERT_TZ(ts,'UTC','Asia/Shanghai') NULL（命名时区表为空）
-- 跨币种 SUM(amount_orig) 数值“正确”但语义错误（混币相加）
-- 高基数 GROUP BY 无覆盖索引 临时表落盘、内存暴涨（高并发雪崩）
-- =====================================================================
-- §01 度量框架（srch→agg 前缀，避免与前序主题表冲突）
-- 用 performance_schema.session_status 取增量指标，把“读了多少行 /
-- 建了多少临时表 / 临时表是否落盘”用数字钉死，杜绝空谈。
-- =====================================================================
DROP TABLE IF EXISTS agg_demo_metric;
CREATE TABLE agg_demo_metric(
tag VARCHAR(40) PRIMARY KEY,
innodb_rows BIGINT NOT NULL DEFAULT 0,
tmp_tables BIGINT NOT NULL DEFAULT 0,
tmp_disk BIGINT NOT NULL DEFAULT 0,
rnd_next BIGINT NOT NULL DEFAULT 0,
ms INT NOT NULL DEFAULT 0,
note VARCHAR(255)
);
DROP TABLE IF EXISTS agg_demo_result;
CREATE TABLE agg_demo_result(
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(80),
passed TINYINT,
note VARCHAR(255)
);
DROP TABLE IF EXISTS agg_demo_probe;
CREATE TABLE agg_demo_probe(
id INT AUTO_INCREMENT PRIMARY KEY,
label VARCHAR(60),
errno INT,
sql_state VARCHAR(10),
msg VARCHAR(300),
caught_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DROP PROCEDURE IF EXISTS agg_snap;
DELIMITER //
CREATE PROCEDURE agg_snap()
BEGIN
SELECT VARIABLE_VALUE INTO @agg_snap_ir FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ';
SELECT VARIABLE_VALUE INTO @agg_snap_tt FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_TABLES';
SELECT VARIABLE_VALUE INTO @agg_snap_td FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_DISK_TABLES';
SELECT VARIABLE_VALUE INTO @agg_snap_rn FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT';
SET @agg_snap_ts = UNIX_TIMESTAMP(NOW(6));
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS agg_measure;
DELIMITER //
CREATE PROCEDURE agg_measure(IN p_tag VARCHAR(40))
BEGIN
DECLARE v_ir BIGINT; DECLARE v_tt BIGINT; DECLARE v_td BIGINT; DECLARE v_rn BIGINT; DECLARE v_ms INT;
SELECT VARIABLE_VALUE INTO v_ir FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ';
SELECT VARIABLE_VALUE INTO v_tt FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_TABLES';
SELECT VARIABLE_VALUE INTO v_td FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_DISK_TABLES';
SELECT VARIABLE_VALUE INTO v_rn FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT';
SET v_ms = ROUND((UNIX_TIMESTAMP(NOW(6)) - @agg_snap_ts)*1000);
INSERT INTO agg_demo_metric(tag,innodb_rows,tmp_tables,tmp_disk,rnd_next,ms)
VALUES(p_tag, v_ir-@agg_snap_ir, v_tt-@agg_snap_tt, v_td-@agg_snap_td, v_rn-@agg_snap_rn, v_ms)
ON DUPLICATE KEY UPDATE innodb_rows=v_ir-@agg_snap_ir, tmp_tables=v_tt-@agg_snap_tt,
tmp_disk=v_td-@agg_snap_td, rnd_next=v_rn-@agg_snap_rn, ms=v_ms;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS agg_v;
DELIMITER //
CREATE PROCEDURE agg_v(IN p_title VARCHAR(80), IN p_pass TINYINT, IN p_note VARCHAR(255))
BEGIN
INSERT INTO agg_demo_result(title, passed, note) VALUES (p_title, p_pass, p_note);
END//
DELIMITER ;
-- 错误探针：捕获 PREPARE 阶段（1064/1305）与 EXECUTE 阶段（3889）的错误，
-- 把 errno 写进 agg_demo_probe，让“不支持的语法”成为可被自检引用的实测事实。
DROP PROCEDURE IF EXISTS agg_err_probe;
DELIMITER //
CREATE PROCEDURE agg_err_probe(IN p_label VARCHAR(60), IN p_sql TEXT)
BEGIN
DECLARE v_no INT DEFAULT 0;
DECLARE v_st VARCHAR(10) DEFAULT '';
DECLARE v_msg VARCHAR(300) DEFAULT '';
DECLARE v_fired INT DEFAULT 0;
DECLARE v_prep INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 v_no = MYSQL_ERRNO, v_st = RETURNED_SQLSTATE, v_msg = MESSAGE_TEXT;
SET v_fired = 1;
END;
SET @agg_sql = p_sql;
PREPARE s1 FROM @agg_sql;
IF v_fired = 0 THEN
SET v_prep = 1;
EXECUTE s1;
END IF;
IF v_fired = 1 THEN
INSERT INTO agg_demo_probe(label, errno, sql_state, msg) VALUES (p_label, v_no, v_st, LEFT(v_msg,200));
ELSE
INSERT INTO agg_demo_probe(label, errno, sql_state, msg) VALUES (p_label, 0, '00000', 'OK(未报错)');
END IF;
IF v_prep = 1 THEN
DEALLOCATE PREPARE s1;
END IF;
END//
DELIMITER ;
SELECT '§01 框架就绪：agg_demo_metric / agg_demo_result / agg_demo_probe' AS `-- 度量框架 --`;
-- =====================================================================
-- §02 实验台：维度表 + 中心事实表(100万) + 六域样例表 + 造数
-- =====================================================================
-- 国家/地区维度（offset_minutes 为相对 UTC 的固定偏移，演示用；DST 另见说明）
DROP TABLE IF EXISTS agg_dim_country;
CREATE TABLE agg_dim_country(
country_code CHAR(2) PRIMARY KEY,
name_en VARCHAR(40),
region VARCHAR(20),
offset_min INT, -- 相对 UTC 的分钟偏移（演示用，非 DST 感知）
currency_code CHAR(3)
);
INSERT INTO agg_dim_country(country_code,name_en,region,offset_min,currency_code) VALUES
('CN','China','APAC',480,'CNY'),('US','United States','NA',-300,'USD'),
('JP','Japan','APAC',540,'JPY'),('DE','Germany','EMEA',60,'EUR'),
('FR','France','EMEA',60,'EUR'),('GB','United Kingdom','EMEA',0,'GBP'),
('AE','UAE','MEA',240,'AED'),('IN','India','APAC',330,'INR'),
('BR','Brazil','LATAM',-180,'BRL'),('AU','Australia','APAC',600,'AUD');
DROP TABLE IF EXISTS agg_dim_currency;
CREATE TABLE agg_dim_currency(
currency_code CHAR(3) PRIMARY KEY,
name_en VARCHAR(40),
usd_rate DECIMAL(12,6) -- 1 单位本币 = ? USD（静态快照，演示用）
);
INSERT INTO agg_dim_currency(currency_code,name_en,usd_rate) VALUES
('CNY','Chinese Yuan',0.14),('USD','US Dollar',1.0),('JPY','Japanese Yen',0.0067),
('EUR','Euro',1.08),('GBP','British Pound',1.27),('AED','UAE Dirham',0.27),
('INR','Indian Rupee',0.012),('BRL','Brazilian Real',0.18),('AUD','Australian Dollar',0.66);
-- 中心交易事实表（全球化电商交易，存 UTC）
DROP TABLE IF EXISTS agg_gl_trade_order;
CREATE TABLE agg_gl_trade_order(
order_id BIGINT PRIMARY KEY,
country_code CHAR(2),
currency_code CHAR(3),
category VARCHAR(20),
status VARCHAR(20),
order_ts DATETIME, -- 存 UTC
amount_orig DECIMAL(18,2), -- 原始币种金额
rate DECIMAL(12,6), -- 当时汇率
amount_usd DECIMAL(18,2), -- 归一化美元（结算依据）
rate_date DATE, -- 汇率快照日（按日快照）
customer_id INT,
channel VARCHAR(20),
KEY ix_cc_status (country_code, status, amount_usd),
KEY ix_category (category, amount_usd),
KEY ix_order_ts (order_ts, country_code),
KEY ix_country_ts (country_code, order_ts)
);
DROP PROCEDURE IF EXISTS agg_lab_build;
DELIMITER //
CREATE PROCEDURE agg_lab_build()
BEGIN
-- 仅当事实表为空时才造 100 万行（保证后续运行秒级、幂等）
IF (SELECT COUNT(*) FROM agg_gl_trade_order) = 0 THEN
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)
SELECT
rid,
ELT(idx,'CN','US','JP','DE','FR','GB','AE','IN','BR','AU'),
ELT(idx,'CNY','USD','JPY','EUR','EUR','GBP','AED','INR','BRL','AUD'),
ELT(1+rid%6,'ring','necklace','bracelet','earring','pendant','watch'),
ELT(1+rid%5,'paid','pending','shipped','cancelled','refunded'),
DATE_ADD('2024-01-01 00:00:00', INTERVAL rid%1093 DAY) + INTERVAL rid%86400 SECOND,
ROUND(50 + rid%9500, 2),
ELT(idx,0.14,1.0,0.0067,1.08,1.08,1.27,0.27,0.012,0.18,0.66),
ROUND((50 + rid%9500) * ELT(idx,0.14,1.0,0.0067,1.08,1.08,1.27,0.27,0.012,0.18,0.66), 2),
DATE(DATE_ADD('2024-01-01 00:00:00', INTERVAL rid%1093 DAY)),
1 + rid%200000,
ELT(1+rid%4,'web','app','store','wholesale')
FROM (
SELECT (a.n-1)*500 + b.n AS rid, 1 + ((a.n-1)*500+b.n)%10 AS idx
FROM (WITH RECURSIVE a(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM a WHERE n<2000) SELECT n FROM a) a
CROSS JOIN (WITH RECURSIVE b(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM b WHERE n<500) SELECT n FROM b) b
) z;
END IF;
-- 六域小表：每次重建（体积小，保证幂等且数据新鲜）
DROP TABLE IF EXISTS agg_oa_approval;
CREATE TABLE agg_oa_approval(
approval_id INT PRIMARY KEY, dept VARCHAR(30), applicant VARCHAR(30),
status VARCHAR(20), submitted_ts DATETIME, approved_ts DATETIME, hours_to_approve DECIMAL(6,2));
INSERT INTO agg_oa_approval
SELECT n,
ELT(1+n%6,'Finance','HR','IT','Legal','Procurement','Sales'),
CONCAT('emp',n),
ELT(1+n%4,'approved','pending','rejected','approved'),
DATE_ADD('2026-01-01 00:00:00', INTERVAL n%270 DAY) + INTERVAL (n*7%86400) SECOND,
DATE_ADD('2026-01-01 00:00:00', INTERVAL n%270 DAY) + INTERVAL (n*7%86400 + n%72*3600) SECOND,
ROUND((n%72) + (n%60)/100.0, 2)
FROM (WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n<500) SELECT n FROM r) z;
DROP TABLE IF EXISTS agg_erp_product;
CREATE TABLE agg_erp_product(
product_id INT PRIMARY KEY, category VARCHAR(20), warehouse VARCHAR(20),
stock_qty INT, unit_cost_usd DECIMAL(12,2), material VARCHAR(20));
INSERT INTO agg_erp_product
SELECT n,
ELT(1+n%6,'ring','necklace','bracelet','earring','pendant','watch'),
ELT(1+n%5,'SH_China','LA_US','FR_EU','DXB_ME','SG_Asia'),
(n*13%900)+10,
ROUND(50 + (n*7%9500)/100.0, 2),
ELT(1+n%4,'18K Gold','PT950','Silver','Diamond')
FROM (WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n<1000) SELECT n FROM r) z;
DROP TABLE IF EXISTS agg_crm_order;
CREATE TABLE agg_crm_order(
cust_id INT, region VARCHAR(20), channel VARCHAR(20),
order_ts DATETIME, amount_usd DECIMAL(18,2), status VARCHAR(20),
KEY ix_region_channel (region, channel, amount_usd));
INSERT INTO agg_crm_order
SELECT 1 + n%50000,
ELT(1+n%5,'APAC','NA','EMEA','LATAM','MEA'),
ELT(1+n%4,'web','app','store','wholesale'),
DATE_ADD('2026-01-01 00:00:00', INTERVAL n%270 DAY) + INTERVAL (n*5%86400) SECOND,
ROUND(50 + (n*11%9500)/10.0, 2),
ELT(1+n%3,'paid','pending','shipped')
FROM (WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n<2000) SELECT n FROM r) z;
DROP TABLE IF EXISTS agg_hr_employee;
CREATE TABLE agg_hr_employee(
emp_id INT PRIMARY KEY, dept VARCHAR(30), location VARCHAR(30), salary_usd DECIMAL(12,2), hire_date DATE);
INSERT INTO agg_hr_employee
SELECT n,
ELT(1+n%6,'Finance','HR','IT','Legal','Procurement','Sales'),
ELT(1+n%8,'Shanghai','New York','Tokyo','Berlin','Paris','London','Dubai','Sydney'),
ROUND(4000 + (n*37%16000)/10.0, 2),
DATE_ADD('2015-01-01', INTERVAL (n*3%4000) DAY)
FROM (WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n<800) SELECT n FROM r) z;
DROP TABLE IF EXISTS agg_mes_work_order;
CREATE TABLE agg_mes_work_order(
wo_id INT PRIMARY KEY, line VARCHAR(20), shift VARCHAR(20), qty INT, defect_qty INT, prod_ts DATETIME);
INSERT INTO agg_mes_work_order
SELECT n,
ELT(1+n%4,'Line-A','Line-B','Line-C','Line-D'),
ELT(1+n%3,'Day','Night','Swing'),
(n*5%800)+50,
(n*5%800)%20,
DATE_ADD('2026-01-01 00:00:00', INTERVAL n%270 DAY) + INTERVAL (n*3%86400) SECOND
FROM (WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n<1500) SELECT n FROM r) z;
DROP TABLE IF EXISTS agg_scm_shipment;
CREATE TABLE agg_scm_shipment(
ship_id INT PRIMARY KEY, carrier VARCHAR(30), status VARCHAR(20), weight_kg DECIMAL(10,2), ship_ts DATETIME);
INSERT INTO agg_scm_shipment
SELECT n,
ELT(1+n%5,'DHL','FedEx','SF-Express','Maersk','UPS'),
ELT(1+n%4,'delivered','in_transit','customs','delayed'),
ROUND(2 + (n*9%5000)/10.0, 2),
DATE_ADD('2026-01-01 00:00:00', INTERVAL n%270 DAY) + INTERVAL (n*2%86400) SECOND
FROM (WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n<1200) SELECT n FROM r) z;
END//
DELIMITER ;
-- 重置（彻底清空本主题表，便于从零复现）
DROP PROCEDURE IF EXISTS agg_lab_reset;
DELIMITER //
CREATE PROCEDURE agg_lab_reset()
BEGIN
DROP TABLE IF EXISTS agg_gl_trade_order, agg_dim_country, agg_dim_currency,
agg_oa_approval, agg_erp_product, agg_crm_order, agg_hr_employee,
agg_mes_work_order, agg_scm_shipment, agg_sales_rollup, agg_part_demo;
END//
DELIMITER ;
CALL agg_lab_build();
SELECT CONCAT('§02 实验台就绪：agg_gl_trade_order 行数 = ', COUNT(*)) AS `-- 实验台 --` FROM agg_gl_trade_order;
-- =====================================================================
-- §03 基础聚合：COUNT / SUM / AVG / MIN / MAX + GROUP BY + HAVING
-- =====================================================================
SELECT '§03.1 单值聚合（全表汇总，不分组）' AS `-- 第03章 --`;
SELECT
COUNT(*) AS 总订单数,
COUNT(DISTINCT customer_id) AS 独立客户数,
ROUND(SUM(amount_usd),2) AS 总销售额USD,
ROUND(AVG(amount_usd),2) AS 客单价USD,
ROUND(MIN(amount_usd),2) AS 最小单USD,
ROUND(MAX(amount_usd),2) AS 最大单USD
FROM agg_gl_trade_order;
SELECT '§03.2 GROUP BY 国家 + 货币：每国销售额与订单数（多币种先归一 USD）' AS `-- 第03章 --`;
SELECT country_code, currency_code,
COUNT(*) AS 订单数,
ROUND(SUM(amount_orig),2) AS 原始币种合计,
ROUND(SUM(amount_usd),2) AS 归一USD合计
FROM agg_gl_trade_order
GROUP BY country_code, currency_code
ORDER BY 归一USD合计 DESC
LIMIT 12;
SELECT '§03.3 HAVING 过滤聚合结果（正确写法：过滤聚合列）' AS `-- 第03章 --`;
SELECT country_code,
COUNT(*) AS 订单数,
ROUND(SUM(amount_usd),2) AS 销售USD
FROM agg_gl_trade_order
GROUP BY country_code
HAVING SUM(amount_usd) > 800000 -- 只保留销售额高的国家
ORDER BY 销售USD DESC;
SELECT '§03.4 HAVING 引用「未分组也未聚合」的列 → 1054（ONLY_FULL_GROUP_BY 拦截）' AS `-- 第03章 --`;
CALL agg_err_probe('HAVING裸列', 'SELECT country_code, SUM(amount_usd) FROM agg_gl_trade_order GROUP BY country_code HAVING category=''paid''');
SELECT ' ★ 正确做法：要么把 category 加入 GROUP BY，要么用聚合函数包裹（如 HAVING SUM(CASE WHEN category=''paid'' THEN 1 ELSE 0 END)>0）。' AS `-- 提示 --`;
SELECT '§03.5 多条件分组 + 多聚合（品类 × 渠道 的销售额与退款率）' AS `-- 第03章 --`;
SELECT category, channel,
COUNT(*) AS 订单数,
ROUND(SUM(amount_usd),2) AS 销售USD,
ROUND(SUM(CASE WHEN status='refunded' THEN 1 ELSE 0 END)/COUNT(*),4) AS 退款率
FROM agg_gl_trade_order
GROUP BY category, channel
ORDER BY 销售USD DESC
LIMIT 10;
-- =====================================================================
-- §04 “行→串/JSON”聚合：GROUP_CONCAT / JSON_ARRAYAGG / JSON_OBJECTAGG
-- 把一组行压成一行，便于应用层直接消费。
-- =====================================================================
SELECT '§04.1 GROUP_CONCAT：每个国家的品类清单（有序、可分隔）' AS `-- 第04章 --`;
SELECT country_code,
COUNT(*) AS 订单数,
GROUP_CONCAT(DISTINCT category ORDER BY category SEPARATOR '|') AS 品类清单
FROM agg_gl_trade_order
GROUP BY country_code
ORDER BY 订单数 DESC;
SELECT '§04.2 JSON_ARRAYAGG：每个渠道的订单额数组（前端图表友好）' AS `-- 第04章 --`;
SELECT channel,
JSON_ARRAYAGG(ROUND(amount_usd,2)) AS 金额数组TOP
FROM (SELECT channel, amount_usd FROM agg_gl_trade_order ORDER BY channel, amount_usd DESC LIMIT 1000) t
GROUP BY channel;
SELECT '§04.3 JSON_OBJECTAGG：国家 → 总销售额 的键值对象（一键导出）' AS `-- 第04章 --`;
SELECT JSON_OBJECTAGG(country_code, 总USD) AS 各国销售USD对象
FROM (
SELECT country_code, ROUND(SUM(amount_usd),2) AS 总USD
FROM agg_gl_trade_order GROUP BY country_code
) t;
SELECT '§04.4 BIT_OR：按国家聚合“是否出现过某状态”的位标志（紧凑布尔汇总）' AS `-- 第04章 --`;
SELECT country_code,
BIT_OR(CASE WHEN status='refunded' THEN 1 ELSE 0 END) AS 有退款_1否0,
BIT_OR(CASE WHEN status='cancelled' THEN 1 ELSE 0 END) AS 有取消_1否0
FROM agg_gl_trade_order
GROUP BY country_code
ORDER BY country_code;
-- =====================================================================
-- §05 MySQL 原生分组集：WITH ROLLUP + GROUPING()
-- （CUBE / GROUPING SETS 在 MySQL 不可用，见 §05.4 错误探针 + §06 模拟）
-- =====================================================================
SELECT '§05.1 WITH ROLLUP：国家 → 国家内品类 的层次小计 + 总计' AS `-- 第05章 --`;
SELECT country_code,
category,
GROUPING(country_code) AS 是否国家小计,
GROUPING(category) AS 是否品类小计,
COUNT(*) AS 订单数,
ROUND(SUM(amount_usd),2) AS 销售USD
FROM agg_gl_trade_order
GROUP BY country_code, category WITH ROLLUP
ORDER BY country_code IS NULL, country_code, category IS NULL, category
LIMIT 25;
SELECT '§05.2 用 GROUPING() 生成易读的小计/总计标签' AS `-- 第05章 --`;
SELECT
CASE WHEN GROUPING(country_code)=1 THEN '【全球总计】' ELSE country_code END AS 国家,
CASE WHEN GROUPING(category)=1 THEN '【该国小计】' ELSE category END AS 品类,
COUNT(*) AS 订单数,
ROUND(SUM(amount_usd),2) AS 销售USD
FROM agg_gl_trade_order
GROUP BY country_code, category WITH ROLLUP
HAVING GROUPING(country_code)=0 AND GROUPING(category)=1 -- 只取“国家小计”行
ORDER BY 销售USD DESC;
SELECT '§05.3 三层 ROLLUP：年 → 月 → 日 的销售累计（按 UTC）' AS `-- 第05章 --`;
SELECT 年, 月, 日, 订单数, 销售USD
FROM (
SELECT y AS 年, m AS 月, d AS 日,
GROUPING(y) AS g_y, GROUPING(m) AS g_m, GROUPING(d) AS g_d,
COUNT(*) AS 订单数,
ROUND(SUM(amount_usd),2) AS 销售USD
FROM (SELECT YEAR(order_ts) AS y, MONTH(order_ts) AS m, DAY(order_ts) AS d, amount_usd FROM agg_gl_trade_order) t
GROUP BY y, m, d WITH ROLLUP
) z
WHERE g_y=0 AND g_m=0 AND g_d=1
LIMIT 15;
SELECT '§05.4 MySQL 不支持 CUBE / GROUPING SETS —— 错误探针（实测 errno）' AS `-- 第05章 --`;
CALL agg_err_probe('CUBE语法', 'SELECT country_code, category, SUM(amount_usd) FROM agg_gl_trade_order GROUP BY CUBE(country_code, category)');
CALL agg_err_probe('GROUPING SETS语法', 'SELECT country_code, category, SUM(amount_usd) FROM agg_gl_trade_order GROUP BY GROUPING SETS ((country_code),(category))');
SELECT ' ★ 结论：MySQL 9.0 要“任意分组集/立方体”，必须用「多个 GROUP BY 的 UNION ALL」模拟（见 §06）。' AS `-- 提示 --`;
-- =====================================================================
-- §06 用 UNION ALL 模拟 GROUPING SETS / CUBE（MySQL 唯一可行路径）
-- =====================================================================
SELECT '§06.1 模拟 GROUPING SETS ((country_code),(category),())' AS `-- 第06章 --`;
SELECT CAST(country_code AS CHAR(40)) AS 维度值, '国家' AS 层级,
COUNT(*) AS 订单数, ROUND(SUM(amount_usd),2) AS 销售USD
FROM agg_gl_trade_order GROUP BY country_code
UNION ALL
SELECT CAST(category AS CHAR(40)), '品类',
COUNT(*), ROUND(SUM(amount_usd),2)
FROM agg_gl_trade_order GROUP BY category
UNION ALL
SELECT '全球总计', '总计',
COUNT(*), ROUND(SUM(amount_usd),2)
FROM agg_gl_trade_order
ORDER BY 层级, 销售USD DESC;
SELECT '§06.2 模拟 CUBE(country_code, category) —— 四个聚合层级合并' AS `-- 第06章 --`;
SELECT CAST(COALESCE(country_code,'∅') AS CHAR(40)) AS 国家,
CAST(COALESCE(category,'∅') AS CHAR(40)) AS 品类,
COUNT(*) AS 订单数, ROUND(SUM(amount_usd),2) AS 销售USD
FROM agg_gl_trade_order GROUP BY country_code, category
UNION ALL
SELECT CAST(country_code AS CHAR(40)), '∅', COUNT(*), ROUND(SUM(amount_usd),2)
FROM agg_gl_trade_order GROUP BY country_code
UNION ALL
SELECT '∅', CAST(category AS CHAR(40)), COUNT(*), ROUND(SUM(amount_usd),2)
FROM agg_gl_trade_order GROUP BY category
UNION ALL
SELECT '∅','∅', COUNT(*), ROUND(SUM(amount_usd),2)
FROM agg_gl_trade_order
ORDER BY 国家, 品类
LIMIT 30;
-- 为自检 V6 准备：三种口径的“国家小计之和/总计”应完全一致
SELECT ROUND(SUM(amount_usd),2) INTO @a_tot FROM agg_gl_trade_order;
SELECT ROUND(SUM(s),2) INTO @a_roll_sub FROM (
SELECT country_code, SUM(amount_usd) s FROM agg_gl_trade_order
GROUP BY country_code, category WITH ROLLUP
HAVING GROUPING(country_code)=0 AND GROUPING(category)=1
) t;
SELECT ROUND(SUM(s),2) INTO @a_sets_sub FROM (
SELECT country_code, SUM(amount_usd) s FROM agg_gl_trade_order GROUP BY country_code
) t;
SELECT CONCAT('§06.3 一致性基准：总计=',@a_tot,' ROLLUP国家小计和=',@a_roll_sub,' SETS模拟国家小计和=',@a_sets_sub) AS `-- 第06章 --`;
-- =====================================================================
-- §07 万亿级去重计数：APPROX_COUNT_DISTINCT 缺失 → 替代方案
-- =====================================================================
SELECT '§07.1 本 9.0.1 构建没有 APPROX_COUNT_DISTINCT（HyperLogLog）—— 错误探针' AS `-- 第07章 --`;
CALL agg_err_probe('APPROX函数', 'SELECT APPROX_COUNT_DISTINCT(customer_id) FROM agg_gl_trade_order');
SELECT ' ★ 标准 MySQL 8.0.13+ 提供 APPROX_COUNT_DISTINCT（HLL 草图，内存恒定）；' AS `-- 提示 --`;
SELECT ' 本机 9.0.1 社区版未含该函数，故万亿级去重改用下方两条路径。' AS `-- 提示 --`;
SELECT '§07.2 COUNT(DISTINCT) 精确去重（高基数时会建内存哈希，万亿级有内存风险）' AS `-- 第07章 --`;
CALL agg_snap();
SELECT COUNT(DISTINCT customer_id) AS 独立客户数 FROM agg_gl_trade_order;
CALL agg_measure('DISTINCT_cust');
SELECT CONCAT(' 本查询建临时表增量见 agg_demo_metric.tag=DISTINCT_cust（高基数在万亿级会撑大内存/落盘）。') AS `-- 提示 --`;
SELECT '§07.3 万亿级“去重/汇总”正解：写入时预聚合 + 应用层 HLL 草图' AS `-- 第07章 --`;
SELECT ' ① 写入时按(日×国家×品类)落一张汇总表，查询扫小表而非万亿事实表（见 §08.2）。' AS `-- 提示 --`;
SELECT ' ② 去重计数在应用层用 Redis PFCOUNT / ClickHouse uniqState 等 HLL 草图，' AS `-- 提示 --`;
SELECT ' 内存恒定 O(KB)，与基数无关——这是万亿级 UV/客户数统计的事实标准。' AS `-- 提示 --`;
SELECT ' ③ 结论：COUNT(DISTINCT) 仅在中小表可用；大规模务必上“预聚合 + HLL”。' AS `-- 提示 --`;
-- =====================================================================
-- §08 万亿数据高并发：覆盖索引分组 / 预聚合回表 / 分区裁剪 / 大结果提示
-- =====================================================================
SELECT '§08.1 覆盖索引让 GROUP BY 免临时表（索引序 = 分组序）' AS `-- 第08章 --`;
CALL agg_snap();
SELECT country_code, status, COUNT(*), SUM(amount_usd)
FROM agg_gl_trade_order GROUP BY country_code, status; -- 命中 ix_cc_status 前缀顺序
CALL agg_measure('GB_cover');
CALL agg_snap();
SELECT status, country_code, COUNT(*), SUM(amount_usd)
FROM agg_gl_trade_order GROUP BY status, country_code; -- 非索引前缀 → 需排序/哈希
CALL agg_measure('GB_reverse');
SELECT CONCAT(' 覆盖序分组 临时表增量=', (SELECT tmp_tables FROM agg_demo_metric WHERE tag='GB_cover'),
'；反向分组 临时表增量=', (SELECT tmp_tables FROM agg_demo_metric WHERE tag='GB_reverse'),
'（反向分组因索引序不符必须建临时表，高并发下内存/落盘风险陡增）。')
AS `-- 提示 --`;
SELECT '§08.2 预聚合汇总表：万亿事实表 → 日×国家×品类 小表（扫描量骤降）' AS `-- 第08章 --`;
DROP TABLE IF EXISTS agg_sales_rollup;
CREATE TABLE agg_sales_rollup(
day DATE NOT NULL, country_code CHAR(2) NOT NULL, category VARCHAR(20) NOT NULL,
cnt INT, sum_usd DECIMAL(18,2),
PRIMARY KEY (day, country_code, category));
INSERT INTO agg_sales_rollup(day, country_code, category, cnt, sum_usd)
SELECT DATE(order_ts), country_code, category, COUNT(*), SUM(amount_usd)
FROM agg_gl_trade_order
GROUP BY DATE(order_ts), country_code, category;
CALL agg_snap();
SELECT country_code, ROUND(SUM(sum_usd),2) AS 销售USD FROM agg_sales_rollup GROUP BY country_code;
CALL agg_measure('ROLLUP_q');
CALL agg_snap();
SELECT country_code, ROUND(SUM(amount_usd),2) AS 销售USD FROM agg_gl_trade_order GROUP BY country_code;
CALL agg_measure('FACT_q');
SELECT CONCAT(' 汇总表(', (SELECT COUNT(*) FROM agg_sales_rollup), '行) 被扫；事实表 ',
(SELECT COUNT(*) FROM agg_gl_trade_order), ' 行被扫。',
' 同口径查询引擎读行：汇总=', (SELECT innodb_rows FROM agg_demo_metric WHERE tag='ROLLUP_q'),
' vs 事实=', (SELECT innodb_rows FROM agg_demo_metric WHERE tag='FACT_q'), '。')
AS `-- 提示 --`;
SELECT '§08.3 分区裁剪：按年分区，查 2026 只扫 p2026（EXPLAIN partitions 列）' AS `-- 第08章 --`;
DROP TABLE IF EXISTS agg_part_demo;
CREATE TABLE agg_part_demo(id INT, ts DATE)
PARTITION BY RANGE (YEAR(ts)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION pmax VALUES LESS THAN MAXVALUE);
EXPLAIN SELECT COUNT(*) FROM agg_part_demo WHERE ts >= '2026-01-01';
SELECT ' ★ EXPLAIN 的 partitions 列应只出现 p2026/pmax，证明其余分区被裁剪，避免全表扫。' AS `-- 提示 --`;
SELECT '§08.4 SQL_BIG_RESULT：高基数 GROUP BY 让优化器优先“排序”而非“内存哈希”' AS `-- 第08章 --`;
EXPLAIN SELECT SQL_BIG_RESULT customer_id, SUM(amount_usd)
FROM agg_gl_trade_order GROUP BY customer_id;
SELECT ' ★ 高并发下，内存哈希易把 buffer 打满；BIG_RESULT 改走落盘排序，单查询内存恒定。' AS `-- 提示 --`;
SELECT ' ★ 再往上：分库分表（按 country_code 或 order_id 取模）、读写分离、CQRS 把分析查询下沉到' AS `-- 提示 --`;
SELECT ' 列式/ES；这些是与“单机聚合优化”正交的架构层解法。' AS `-- 提示 --`;
-- =====================================================================
-- §09 六域分组模板：OA / ERP / CRM / HR / MES / SCM
-- =====================================================================
SELECT '§09.1 OA：各部门审批单状态分布 + 平均审批时长' AS `-- 第09章 --`;
SELECT dept, status,
COUNT(*) AS 单数,
ROUND(AVG(hours_to_approve),2) AS 平均审批小时
FROM agg_oa_approval
GROUP BY dept, status
ORDER BY dept, 单数 DESC;
SELECT '§09.2 ERP：各品类 × 仓库 的库存价值（stock_qty × unit_cost_usd）' AS `-- 第09章 --`;
SELECT category, warehouse,
SUM(stock_qty) AS 总库存,
ROUND(SUM(stock_qty*unit_cost_usd),2) AS 库存价值USD
FROM agg_erp_product
GROUP BY category, warehouse
ORDER BY 库存价值USD DESC
LIMIT 12;
SELECT '§09.3 CRM：各区域 × 渠道 的销售额与独立客户数（美元结算）' AS `-- 第09章 --`;
SELECT region, channel,
COUNT(*) AS 订单数,
COUNT(DISTINCT cust_id) AS 独立客户数,
ROUND(SUM(amount_usd),2) AS 销售USD
FROM agg_crm_order
GROUP BY region, channel
ORDER BY 销售USD DESC;
SELECT '§09.4 HR：各部门 × 工作地 的编制与平均薪资（美元）' AS `-- 第09章 --`;
SELECT dept, location,
COUNT(*) AS 编制,
ROUND(AVG(salary_usd),2) AS 平均薪资USD,
ROUND(SUM(salary_usd),2) AS 薪资总额USD
FROM agg_hr_employee
GROUP BY dept, location
ORDER BY 编制 DESC
LIMIT 12;
SELECT '§09.5 MES：各产线 × 班次的产量与不良率' AS `-- 第09章 --`;
SELECT line, shift,
SUM(qty) AS 总产量,
SUM(defect_qty) AS 不良数,
ROUND(SUM(defect_qty)/SUM(qty),4) AS 不良率
FROM agg_mes_work_order
GROUP BY line, shift
ORDER BY 总产量 DESC;
SELECT '§09.6 SCM：各承运商 × 状态的运单数与总重量' AS `-- 第09章 --`;
SELECT carrier, status,
COUNT(*) AS 运单数,
ROUND(SUM(weight_kg),2) AS 总重量KG
FROM agg_scm_shipment
GROUP BY carrier, status
ORDER BY 运单数 DESC
LIMIT 12;
SELECT '§09.7 六域通用“分组集”写法（以 ERP 为例，CUBE 用 UNION ALL 模拟）' AS `-- 第09章 --`;
SELECT CAST(category AS CHAR(40)) AS 维度, '品类' AS 层级, SUM(stock_qty) AS 库存, ROUND(SUM(stock_qty*unit_cost_usd),2) AS 价值USD
FROM agg_erp_product GROUP BY category
UNION ALL
SELECT '全部品类','总计', SUM(stock_qty), ROUND(SUM(stock_qty*unit_cost_usd),2)
FROM agg_erp_product
ORDER BY 层级, 价值USD DESC;
-- =====================================================================
-- §10 国际化：时区（本地营业日分组） + 多币种（归一 USD 结算）
-- =====================================================================
SELECT '§10.1 按“本地营业日”分组：UTC 存储，按国家固定偏移换算本地日期' AS `-- 第10章 --`;
SELECT c.country_code, c.name_en,
DATE(TIMESTAMPADD(MINUTE, c.offset_min, o.order_ts)) AS 本地日,
COUNT(*) AS 订单数,
ROUND(SUM(o.amount_usd),2) AS 销售USD
FROM agg_gl_trade_order o
JOIN agg_dim_country c ON c.country_code = o.country_code
WHERE c.country_code IN ('CN','US')
AND DATE(TIMESTAMPADD(MINUTE, c.offset_min, o.order_ts)) = '2026-03-15'
GROUP BY c.country_code, c.name_en, 本地日
ORDER BY c.country_code;
SELECT '§10.2 本地日 vs UTC 区间一致性（证明换算正确，无漂移）' AS `-- 第10章 --`;
SELECT c.country_code,
COUNT(*) AS `本地日2026-03-15订单数`
FROM agg_gl_trade_order o
JOIN agg_dim_country c ON c.country_code = o.country_code
WHERE c.country_code='CN'
AND DATE(TIMESTAMPADD(MINUTE, c.offset_min, o.order_ts)) = '2026-03-15'
GROUP BY c.country_code;
SELECT ' ★ CN 本地 2026-03-15 00:00 = UTC 2026-03-14 16:00（偏移+480），' AS `-- 提示 --`;
SELECT ' 等价于 WHERE order_ts >= ''2026-03-14 16:00:00'' AND order_ts < ''2026-03-15 16:00:00''。' AS `-- 提示 --`;
SELECT '§10.3 多币种结算：跨币种 SUM(amount_orig) 是“算得出但错误”的反模式' AS `-- 第10章 --`;
SELECT category,
ROUND(SUM(amount_orig),2) AS 原始币种混加_语义错误,
ROUND(SUM(amount_usd),2) AS 归一USD_唯一可结算
FROM agg_gl_trade_order
GROUP BY category
ORDER BY 归一USD_唯一可结算 DESC;
SELECT ' ★ amount_orig 混了 CNY/JPY/USD… 直接相加无经济意义；' AS `-- 提示 --`;
SELECT ' 全球结算只能 SUM(amount_usd)；rate_date 记录汇率快照日，便于回溯对账。' AS `-- 提示 --`;
SELECT '§10.4 单国单币种内可直加；跨国必须 USD（示例：中日美各自本币合计）' AS `-- 第10章 --`;
SELECT country_code, currency_code,
ROUND(SUM(amount_orig),2) AS 本币合计,
ROUND(SUM(amount_usd),2) AS 美元合计
FROM agg_gl_trade_order
GROUP BY country_code, currency_code
ORDER BY 美元合计 DESC
LIMIT 10;
-- =====================================================================
-- §11 自检（V1..V12）—— 把上面的“声称”全部用数字钉死
-- =====================================================================
SELECT '========== 开始自检 ==========' AS `-- 自检 --`;
-- V1 框架表齐备
SELECT COUNT(*) INTO @v1 FROM information_schema.tables
WHERE table_schema='JewelryAnalytics'
AND table_name IN ('agg_demo_metric','agg_demo_result','agg_demo_probe');
CALL agg_v('V1_框架表齐备', IF(@v1=3,1,0), CONCAT('表数=',@v1));
-- V2 CUBE 报 3889
SELECT errno INTO @v2 FROM agg_demo_probe WHERE label='CUBE语法';
CALL agg_v('V2_CUBE报3889', IF(@v2=3889,1,0), CONCAT('errno=',IFNULL(@v2,0)));
-- V3 GROUPING SETS 报 1064
SELECT errno INTO @v3 FROM agg_demo_probe WHERE label='GROUPING SETS语法';
CALL agg_v('V3_GROUPINGSETS报1064', IF(@v3=1064,1,0), CONCAT('errno=',IFNULL(@v3,0)));
-- V4 ROLLUP 超聚行存在
SELECT COUNT(*) INTO @v4 FROM (
SELECT country_code, category, SUM(amount_usd) s
FROM agg_gl_trade_order GROUP BY country_code, category WITH ROLLUP
HAVING GROUPING(country_code)=1 AND GROUPING(category)=1
) t;
CALL agg_v('V4_ROLLUP超聚行存在', IF(@v4>=1,1,0), CONCAT('grand_total行数=',@v4));
-- V5 HAVING 裸列 1054 且正确 HAVING 可用
SELECT errno INTO @v5a FROM agg_demo_probe WHERE label='HAVING裸列';
SELECT COUNT(*) INTO @v5b FROM (
SELECT country_code FROM agg_gl_trade_order GROUP BY country_code HAVING SUM(amount_usd)>0
) t;
CALL agg_v('V5_HAVING裸列1054且正确HAVING可用', IF(@v5a=1054 AND @v5b>0,1,0), CONCAT('errno=',IFNULL(@v5a,0),' 正确HAVING行=',@v5b));
-- V6 模拟 GROUPING SETS 与 ROLLUP 口径一致
CALL agg_v('V6_模拟集与ROLLUP一致', IF(@a_tot=@a_roll_sub AND @a_roll_sub=@a_sets_sub,1,0),
CONCAT('tot=',@a_tot,' roll=',@a_roll_sub,' sets=',@a_sets_sub));
-- V7 JSON 聚合非空
SELECT JSON_OBJECTAGG(country_code, ROUND(s,2)) INTO @v7
FROM (SELECT country_code, SUM(amount_usd) AS s FROM agg_gl_trade_order GROUP BY country_code) t;
CALL agg_v('V7_JSON聚合非空', IF(@v7 IS NOT NULL,1,0), CONCAT('obj_len=',IFNULL(CHAR_LENGTH(@v7),0)));
-- V8 APPROX_COUNT_DISTINCT 缺失（1305）已捕获
SELECT errno INTO @v8 FROM agg_demo_probe WHERE label='APPROX函数';
CALL agg_v('V8_APPROX缺失报1305', IF(@v8=1305,1,0), CONCAT('errno=',IFNULL(@v8,0)));
-- V9 本地营业日与 UTC 区间一致（无漂移）
SELECT COUNT(*) INTO @v9a FROM agg_gl_trade_order o
JOIN agg_dim_country c ON c.country_code=o.country_code
WHERE c.country_code='CN' AND DATE(TIMESTAMPADD(MINUTE,c.offset_min,o.order_ts))='2026-03-15';
SELECT COUNT(*) INTO @v9b FROM agg_gl_trade_order
WHERE country_code='CN' AND order_ts >= '2026-03-14 16:00:00' AND order_ts < '2026-03-15 16:00:00';
CALL agg_v('V9_本地日与UTC区间一致', IF(@v9a=@v9b AND @v9a>0,1,0), CONCAT('本地日=',@v9a,' UTC区间=',@v9b));
-- V10 币种归一一致，且 orig 合计 != usd 合计（证明跨币混加确为不同口径）
SELECT ROUND(SUM(amount_usd),2) INTO @v10tot FROM agg_gl_trade_order;
SELECT ROUND(SUM(s),2) INTO @v10cat FROM (SELECT category, SUM(amount_usd) s FROM agg_gl_trade_order GROUP BY category) t;
SELECT ROUND(SUM(amount_orig),2) INTO @v10orig FROM agg_gl_trade_order;
CALL agg_v('V10_币种归一一致且orig不等于usd', IF(@v10tot=@v10cat AND @v10orig<>@v10tot,1,0),
CONCAT('usd总=',@v10tot,' 品类汇总=',@v10cat,' orig总=',@v10orig));
-- V11 覆盖序分组临时表更少（或相等）
SELECT tmp_tables INTO @v11a FROM agg_demo_metric WHERE tag='GB_cover';
SELECT tmp_tables INTO @v11b FROM agg_demo_metric WHERE tag='GB_reverse';
CALL agg_v('V11_覆盖序分组临时表更少', IF(@v11a <= @v11b,1,0), CONCAT('cover=',IFNULL(@v11a,0),' reverse=',IFNULL(@v11b,0)));
-- V12 数据量稳定 100 万
SELECT COUNT(*) INTO @v12 FROM agg_gl_trade_order;
CALL agg_v('V12_数据量稳定100万', IF(@v12=1000000,1,0), CONCAT('行数=',@v12));
SELECT '========== 自检汇总 ==========' AS `-- 自检 --`;
SELECT id, title, passed, note FROM agg_demo_result ORDER BY id;
SELECT CONCAT('通过 ', SUM(passed), ' / ', COUNT(*), ' 项') AS 自检结论 FROM agg_demo_result;
-- =====================================================================
-- §12 总结：对照表 / 实测度量 / 结论 / 方法清单 / 脚本结构
-- =====================================================================
SELECT '§12.1 MySQL 9.0 分组能力与 SQL 标准对照' AS `-- 第12章 --`;
SELECT CAST('能力' AS CHAR(60)) AS 项, CAST('MySQL 9.0 支持情况' AS CHAR(160)) AS 说明 UNION ALL
SELECT 'GROUP BY a,b WITH ROLLUP', '✅ 原生支持（层次小计）' UNION ALL
SELECT 'GROUPING(a) 函数', '✅ 原生支持（0/1 标识超聚行）' UNION ALL
SELECT 'GROUP BY CUBE(a,b)', '❌ 报 3889（被路由 HeatWave 副引擎，本机未配）' UNION ALL
SELECT 'GROUP BY GROUPING SETS(...)','❌ 报 1064（语法不支持）' UNION ALL
SELECT '任意分组集/立方体', '✅ 用「多个 GROUP BY 的 UNION ALL」模拟（见 §06）' UNION ALL
SELECT 'APPROX_COUNT_DISTINCT', '⚠ 本 9.0.1 构建缺失（报 1305），万亿去重改用预聚合+HLL' UNION ALL
SELECT 'CONVERT_TZ(命名时区)', '⚠ 本机 time_zone_name 为空 → 恒 NULL，改用 TIMESTAMPADD 固定偏移';
SELECT '§12.2 引擎真实度量（来自 agg_demo_metric）' AS `-- 第12章 --`;
SELECT tag AS 标签, innodb_rows AS 引擎读行, tmp_tables AS 临时表, tmp_disk AS 落盘, rnd_next AS 回表, ms AS 耗时ms
FROM agg_demo_metric ORDER BY tag;
SELECT '§12.3 三条核心结论' AS `-- 第12章 --`;
SELECT '1) MySQL 没有 SQL 标准的 GROUPING SETS / CUBE：ROLLUP+GROUPING() 是原生层次小计手段，任意分组集必须用 UNION ALL 模拟，且模拟口径与 ROLLUP 完全一致（V6 已证）。' AS `结论1`;
SELECT '2) 高性能聚合靠“索引序=分组序”：覆盖索引让 GROUP BY 免临时表（V11），万亿级再加预聚合汇总表（扫描量从 100万→几万行）与分区裁剪，最后才是分库分表/读写分离/CQRS 架构层。' AS `结论2`;
SELECT '3) 国际化两条铁律：时间一律 UTC 存储、本地仅 TIMESTAMPADD 换算；结算一律按归一 amount_usd 聚合，绝不可跨币种 SUM(amount_orig)（V10 已证两口径数值不同）。' AS `结论3`;
SELECT '§12.4 方法清单（落地可直接照做）' AS `-- 第12章 --`;
SELECT '① GROUP BY 列顺序要与索引前缀一致，避免临时表/落盘。' AS `清单`;
SELECT '② 大表聚合前先看 EXPLAIN：有无 Using temporary / filesort；有则加覆盖索引或下沉预聚合表。' AS `清单`;
SELECT '③ 高基数 GROUP BY 用 SQL_BIG_RESULT 让优化器走落盘排序，单查询内存恒定。' AS `清单`;
SELECT '④ 万亿级去重用写入时 HLL 草图（Redis PFCOUNT / ClickHouse），不用 COUNT(DISTINCT)。' AS `清单`;
SELECT '⑤ 分层汇总：日→月→年 各建一张汇总表，报表查小表。' AS `清单`;
SELECT '⑥ HAVING 只能引用 GROUP BY 列或聚合函数；想按明细列过滤请先把列纳入 GROUP BY。' AS `清单`;
SELECT '⑦ 多币种、多时区先统一到 USD 与 UTC 再聚合。' AS `清单`;
SELECT '⑧ 行列转换用 GROUP_CONCAT / JSON_ARRAYAGG / JSON_OBJECTAGG，BIT_OR 做紧凑布尔汇总。' AS `清单`;
SELECT '§12.5 本脚本结构（12 段，幂等、自带造数、自带度量）' AS `-- 第12章 --`;
SELECT '00 头部(运行必读/差异/反模式) · 01 度量框架 · 02 实验台造数 · 03 基础聚合+HAVING · 04 JSON聚合 · 05 ROLLUP+GROUPING · 06 UNION ALL 模拟 SETS/CUBE · 07 万亿去重 · 08 高并发工程 · 09 六域模板 · 10 国际时区+币种 · 11 自检 V1-V12 · 12 本总结' AS `结构`;
SELECT '库 JewelryAnalytics 与全部 agg_* 表/过程有意保留，供后续主题复用。' AS `结构`;
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号