sql: SQL Window Functions and Ranking Data 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';
-- 关闭服务端单语句执行时间上限：本主题实验室建表/预聚合可能跑 >30s，
-- 若服务端 max_execution_time=30000 会被掐（报 2013）；此处显式置 0 解除。
-- 注意：这只治服务端上限，治不了 MySQL Workbench 的客户端读超时
-- （DBMS connection read timeout interval），那种情况需在 Workbench 偏好里设为 0/600 或改用命令行客户端。
SET SESSION max_execution_time=0;
-- =====================================================================
-- 第 18 主题：SQL Window Functions and Ranking Data（窗口函数与排名分析）
-- 用 MySQL 9.0 演示「窗口函数 / 排名函数 / OVER 子句 / 窗口帧」在
-- 国际化珠宝集团（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) 本脚本幂等：连跑多次结果一致（实验室表仅空表时插入），可反复执行证明稳定性。
-- 3) 复用说明：本主题直接复用第 17 主题（聚合与分组集）已建好的
-- JewelryAnalytics 库与 agg_* 表（agg_gl_trade_order 100 万行、
-- 六域小表、维度/货币表）。仅当这些表不存在或为空时才现场重建，
-- 因此首次运行可能造数（>30s），复跑则秒过。
-- 4) 时区：本机 mysql.time_zone_name 表为空（命名时区未加载），
-- CONVERT_TZ() 一律返回 NULL —— 因此本脚本统一用
-- SET SESSION time_zone='+00:00' 让 DATETIME 当 UTC 存，
-- 本地时间用 TIMESTAMPADD(MINUTE, 固定偏移, ts) 硬编码换算（偏移来自 agg_dim_country.offset_min）。
-- 5) 多币种铁律：结算只 SUM(amount_usd)（已归一为美元），
-- 绝不可跨币种 SUM(amount_orig)（各币种量级不同，相加无意义）。
--
-- ┌──────────────────────────────────────────────────────────────────┐
-- │ §0.1 MySQL 9.0 窗口函数支持矩阵（实测可用） │
-- └──────────────────────────────────────────────────────────────────┘
-- 排名类： ROW_NUMBER() RANK() DENSE_RANK() NTILE() PERCENT_RANK() CUME_DIST()
-- 偏移类： LAG() LEAD() FIRST_VALUE() LAST_VALUE() NTH_VALUE()
-- 聚合类： SUM/AVG/MIN/MAX/COUNT(...) OVER (PARTITION BY .. ORDER BY .. [frame])
-- 语法： OVER ( [PARTITION BY c] [ORDER BY c [ASC|DESC]] [frame] )
-- 支持命名窗口 WINDOW w AS (...) 与在 OVER 中引用。
-- 窗口帧： ROWS BETWEEN n PRECEDING AND m FOLLOWING
-- RANGE BETWEEN ... AND ...（按 ORDER BY 值区间，非行数）
-- 默认帧：有 ORDER BY 无显式帧 → RANGE UNBOUNDED PRECEDING AND CURRENT ROW
-- 无 ORDER BY → ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
--
-- ┌──────────────────────────────────────────────────────────────────┐
-- │ §0.2 反模式速查表（本主题已踩/需规避） │
-- └──────────────────────────────────────────────────────────────────┘
-- ❌ 在 WHERE / HAVING 中直接引用窗口函数结果 —— 须包一层子查询/派生表再过滤。
-- ❌ 用默认 RANGE 帧做“近 N 行”移动平均 —— RANGE 按值区间，同值多行会被一并纳入；
-- 近 N 行必须用 ROWS BETWEEN n PRECEDING AND CURRENT ROW。
-- ❌ LAST_VALUE() 不加帧当作“分区最后一行” —— 默认 RANGE 帧让 LAST_VALUE 取到“当前行”，
-- 要取真正最后一行须显式 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
-- ❌ 万亿表直接对原始事实表开窗 —— 先按维度预聚合（如 win_daily_sales）再开窗，量级降百倍。
-- ❌ 对无覆盖索引的 (PARTITION BY a ORDER BY b) 开窗 —— 触发 filesort / 临时表；
-- 建 (a,b,..) 复合索引让窗口免排序。
-- ❌ 跨币种 SUM(amount_orig) 后做排名/占比 —— 必须先用 amount_usd 归一。
-- ❌ 用 RANK() 取“每组前 N”再 LIMIT —— 同值会多取；要精确前 N 用 ROW_NUMBER()。
-- =====================================================================
-- §01 度量框架（win_*）：用 performance_schema.session_status 的增量
-- 计数器证明“覆盖索引 / 预聚合 / 窗口帧”对临时表与排序的影响。
-- =====================================================================
DROP TABLE IF EXISTS win_demo_metric;
CREATE TABLE win_demo_metric (
label VARCHAR(60) NOT NULL,
ts DATETIME(6) NOT NULL,
innodb_rows_read BIGINT,
created_tmp_tables BIGINT,
created_tmp_disk_tables BIGINT,
handler_read_rnd_next BIGINT,
sort_rows BIGINT,
sort_scan BIGINT
);
DROP TABLE IF EXISTS win_demo_measure;
CREATE TABLE win_demo_measure (
label VARCHAR(60) NOT NULL,
innodb_rows_read_d BIGINT,
created_tmp_tables_d BIGINT,
created_tmp_disk_tables_d BIGINT,
handler_read_rnd_next_d BIGINT,
sort_rows_d BIGINT,
sort_scan_d BIGINT
);
DROP TABLE IF EXISTS win_demo_result;
CREATE TABLE win_demo_result (
name VARCHAR(40) NOT NULL,
passed TINYINT NOT NULL,
detail VARCHAR(400) DEFAULT NULL
);
DROP TABLE IF EXISTS win_demo_probe;
CREATE TABLE win_demo_probe (
label VARCHAR(60) NOT NULL,
errno INT NOT NULL,
msg VARCHAR(400) DEFAULT NULL,
expect_err INT NOT NULL,
ok TINYINT NOT NULL
);
-- 运行前清空上一轮的自检/度量结果（保持幂等）
TRUNCATE TABLE win_demo_result;
TRUNCATE TABLE win_demo_measure;
TRUNCATE TABLE win_demo_probe;
TRUNCATE TABLE win_demo_metric;
DELIMITER $$
-- 原始快照：把 6 个关键计数器当前值存一份，供人工核对
DROP PROCEDURE IF EXISTS win_snap$$
CREATE PROCEDURE win_snap(IN p_label VARCHAR(60))
BEGIN
INSERT INTO win_demo_metric (label, ts, innodb_rows_read, created_tmp_tables,
created_tmp_disk_tables, handler_read_rnd_next, sort_rows, sort_scan)
SELECT p_label, NOW(6),
(SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ'),
(SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_TABLES'),
(SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_DISK_TABLES'),
(SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT'),
(SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='SORT_ROWS'),
(SELECT VARIABLE_VALUE FROM performance_schema.session_status WHERE VARIABLE_NAME='SORT_SCAN');
END$$
-- 增量度量：执行一条 SQL 前后各取一次计数器，存入 delta
DROP PROCEDURE IF EXISTS win_measure$$
CREATE PROCEDURE win_measure(IN p_label VARCHAR(60), IN p_sql TEXT)
BEGIN
DECLARE v_irr0,v_irr1 BIGINT DEFAULT 0;
DECLARE v_ctt0,v_ctt1 BIGINT DEFAULT 0;
DECLARE v_ctd0,v_ctd1 BIGINT DEFAULT 0;
DECLARE v_hr0,v_hr1 BIGINT DEFAULT 0;
DECLARE v_sr0,v_sr1 BIGINT DEFAULT 0;
DECLARE v_ss0,v_ss1 BIGINT DEFAULT 0;
DECLARE v_sql TEXT;
SELECT VARIABLE_VALUE INTO v_irr0 FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ';
SELECT VARIABLE_VALUE INTO v_ctt0 FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_TABLES';
SELECT VARIABLE_VALUE INTO v_ctd0 FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_DISK_TABLES';
SELECT VARIABLE_VALUE INTO v_hr0 FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT';
SELECT VARIABLE_VALUE INTO v_sr0 FROM performance_schema.session_status WHERE VARIABLE_NAME='SORT_ROWS';
SELECT VARIABLE_VALUE INTO v_ss0 FROM performance_schema.session_status WHERE VARIABLE_NAME='SORT_SCAN';
SET v_sql = p_sql;
SET @w_sql = v_sql;
PREPARE s1 FROM @w_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT VARIABLE_VALUE INTO v_irr1 FROM performance_schema.session_status WHERE VARIABLE_NAME='INNODB_ROWS_READ';
SELECT VARIABLE_VALUE INTO v_ctt1 FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_TABLES';
SELECT VARIABLE_VALUE INTO v_ctd1 FROM performance_schema.session_status WHERE VARIABLE_NAME='CREATED_TMP_DISK_TABLES';
SELECT VARIABLE_VALUE INTO v_hr1 FROM performance_schema.session_status WHERE VARIABLE_NAME='HANDLER_READ_RND_NEXT';
SELECT VARIABLE_VALUE INTO v_sr1 FROM performance_schema.session_status WHERE VARIABLE_NAME='SORT_ROWS';
SELECT VARIABLE_VALUE INTO v_ss1 FROM performance_schema.session_status WHERE VARIABLE_NAME='SORT_SCAN';
INSERT INTO win_demo_measure (label, innodb_rows_read_d, created_tmp_tables_d,
created_tmp_disk_tables_d, handler_read_rnd_next_d, sort_rows_d, sort_scan_d)
VALUES (p_label,
v_irr1-v_irr0, v_ctt1-v_ctt0, v_ctd1-v_ctd0, v_hr1-v_hr0, v_sr1-v_sr0, v_ss1-v_ss0);
END$$
-- 自检登记：把一次断言结果写入 win_demo_result
DROP PROCEDURE IF EXISTS win_v$$
CREATE PROCEDURE win_v(IN p_name VARCHAR(40), IN p_pass TINYINT, IN p_detail VARCHAR(400))
BEGIN
INSERT INTO win_demo_result (name, passed, detail) VALUES (p_name, p_pass, p_detail);
END$$
-- 错误探针：故意执行一条“应当报错”的 SQL，捕获错误码验证反模式
DROP PROCEDURE IF EXISTS win_err_probe$$
CREATE PROCEDURE win_err_probe(IN p_sql TEXT, IN p_expect_err INT)
BEGIN
DECLARE v_err INT DEFAULT 0;
DECLARE v_msg VARCHAR(400) DEFAULT '';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 v_err = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;
END;
SET @w_sql = p_sql;
PREPARE s1 FROM @w_sql;
IF v_err = 0 THEN
EXECUTE s1;
END IF;
DEALLOCATE PREPARE s1;
INSERT INTO win_demo_probe (label, errno, msg, expect_err, ok)
VALUES (LEFT(p_sql,60), v_err, v_msg, p_expect_err, IF(v_err = p_expect_err, 1, 0));
END$$
DELIMITER ;
-- =====================================================================
-- §02 实验室建表（win_lab_build / win_lab_reset）
-- 复用第 17 主题已建好的 agg_* 表；仅当缺失/为空时按相同口径重建。
-- 始终重建派生表 win_daily_sales（按 国家+本地日 预聚合，约 1 万行，快）。
-- =====================================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS win_lab_reset$$
CREATE PROCEDURE win_lab_reset()
BEGIN
DROP TABLE IF EXISTS win_daily_sales;
DROP TABLE IF EXISTS win_gl_tmp;
END$$
DROP PROCEDURE IF EXISTS win_lab_build$$
CREATE PROCEDURE win_lab_build()
BEGIN
-- ---------- 维度表（缺则建，空则填） ----------
CREATE TABLE IF NOT EXISTS agg_dim_country (
country_code CHAR(2) NOT NULL,
name_en VARCHAR(40) DEFAULT NULL,
region VARCHAR(20) DEFAULT NULL,
offset_min INT DEFAULT NULL,
currency_code CHAR(3) DEFAULT NULL,
PRIMARY KEY (country_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
IF (SELECT COUNT(*) FROM agg_dim_country) = 0 THEN
INSERT INTO agg_dim_country 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');
END IF;
CREATE TABLE IF NOT EXISTS agg_dim_currency (
currency_code CHAR(3) NOT NULL,
name_en VARCHAR(40) DEFAULT NULL,
usd_rate DECIMAL(12,6) DEFAULT NULL,
PRIMARY KEY (currency_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
IF (SELECT COUNT(*) FROM agg_dim_currency) = 0 THEN
INSERT INTO agg_dim_currency VALUES
('AED','UAE Dirham',0.270000),('AUD','Australian Dollar',0.660000),
('BRL','Brazilian Real',0.180000),('CNY','Chinese Yuan',0.140000),
('EUR','Euro',1.080000),('GBP','British Pound',1.270000),
('INR','Indian Rupee',0.012000),('JPY','Japanese Yen',0.006700),
('USD','US Dollar',1.000000);
END IF;
-- ---------- 事实表（缺则建，空则造 100 万行；数据在则跳过） ----------
CREATE TABLE IF NOT EXISTS agg_gl_trade_order (
order_id BIGINT NOT NULL,
country_code CHAR(2) DEFAULT NULL,
currency_code CHAR(3) DEFAULT NULL,
category VARCHAR(20) DEFAULT NULL,
status VARCHAR(20) DEFAULT NULL,
order_ts DATETIME DEFAULT NULL,
amount_orig DECIMAL(18,2) DEFAULT NULL,
rate DECIMAL(12,6) DEFAULT NULL,
amount_usd DECIMAL(18,2) DEFAULT NULL,
rate_date DATE DEFAULT NULL,
customer_id INT DEFAULT NULL,
channel VARCHAR(20) DEFAULT NULL,
PRIMARY KEY (order_id),
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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
IF (SELECT COUNT(*) FROM agg_gl_trade_order) = 0 THEN
-- 真实 100 万行生成（WITH RECURSIVE 2000 x 500），日期模数 1093 与 10 互质以避免维度相关
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;
-- ---------- 六域小表（缺则建，空则填；数据在则跳过） ----------
CREATE TABLE IF NOT EXISTS agg_oa_approval (
approval_id INT NOT NULL, dept VARCHAR(30) DEFAULT NULL, applicant VARCHAR(30) DEFAULT NULL,
status VARCHAR(20) DEFAULT NULL, submitted_ts DATETIME DEFAULT NULL,
approved_ts DATETIME DEFAULT NULL, hours_to_approve DECIMAL(6,2) DEFAULT NULL,
PRIMARY KEY (approval_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
IF (SELECT COUNT(*) FROM agg_oa_approval) = 0 THEN
INSERT INTO agg_oa_approval (approval_id, dept, applicant, status, submitted_ts, approved_ts, hours_to_approve)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<500)
SELECT n,
ELT(1 + n%6, 'Finance','HR','IT','Legal','Ops','Sales'),
CONCAT('user', n),
ELT(1 + n%3, 'approved','pending','rejected'),
TIMESTAMPADD(HOUR, n%2000, '2025-01-01 08:00:00'),
TIMESTAMPADD(HOUR, n%2000 + (n%48), '2025-01-01 08:00:00'),
ROUND((n%48)+0.5, 2)
FROM s;
END IF;
CREATE TABLE IF NOT EXISTS agg_hr_employee (
emp_id INT NOT NULL, dept VARCHAR(30) DEFAULT NULL, location VARCHAR(30) DEFAULT NULL,
salary_usd DECIMAL(12,2) DEFAULT NULL, hire_date DATE DEFAULT NULL,
PRIMARY KEY (emp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
IF (SELECT COUNT(*) FROM agg_hr_employee) = 0 THEN
INSERT INTO agg_hr_employee (emp_id, dept, location, salary_usd, hire_date)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<800)
SELECT n,
ELT(1 + n%6, 'Finance','HR','IT','Legal','Ops','Sales'),
ELT(1 + n%5, 'HK','CN','SG','UK','US'),
ROUND(3000 + (MOD(n*40503, 17000)), 2),
DATE(TIMESTAMPADD(DAY, n%4000, '2015-01-01'))
FROM s;
END IF;
CREATE TABLE IF NOT EXISTS agg_mes_work_order (
wo_id INT NOT NULL, line VARCHAR(20) DEFAULT NULL, shift VARCHAR(20) DEFAULT NULL,
qty INT DEFAULT NULL, defect_qty INT DEFAULT NULL, prod_ts DATETIME DEFAULT NULL,
PRIMARY KEY (wo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
IF (SELECT COUNT(*) FROM agg_mes_work_order) = 0 THEN
INSERT INTO agg_mes_work_order (wo_id, line, shift, qty, defect_qty, prod_ts)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<1500)
SELECT n,
ELT(1 + n%4, 'L1','L2','L3','L4'),
ELT(1 + n%3, 'Day','Night','Swing'),
100 + (n%900),
(n%37),
TIMESTAMPADD(HOUR, n%8000, '2025-01-01 00:00:00')
FROM s;
END IF;
CREATE TABLE IF NOT EXISTS agg_scm_shipment (
ship_id INT NOT NULL, carrier VARCHAR(30) DEFAULT NULL, status VARCHAR(20) DEFAULT NULL,
weight_kg DECIMAL(10,2) DEFAULT NULL, ship_ts DATETIME DEFAULT NULL,
PRIMARY KEY (ship_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
IF (SELECT COUNT(*) FROM agg_scm_shipment) = 0 THEN
INSERT INTO agg_scm_shipment (ship_id, carrier, status, weight_kg, ship_ts)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<1200)
SELECT n,
ELT(1 + n%5, 'DHL','FedEx','UPS','SF','EMS'),
ELT(1 + n%3, 'delivered','in_transit','customs'),
ROUND(0.5 + (MOD(n*7919, 5000))/10.0, 2),
TIMESTAMPADD(HOUR, n%6000, '2025-01-01 00:00:00')
FROM s;
END IF;
CREATE TABLE IF NOT EXISTS agg_erp_product (
product_id INT NOT NULL, category VARCHAR(20) DEFAULT NULL, warehouse VARCHAR(20) DEFAULT NULL,
stock_qty INT DEFAULT NULL, unit_cost_usd DECIMAL(12,2) DEFAULT NULL, material VARCHAR(20) DEFAULT NULL,
PRIMARY KEY (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
IF (SELECT COUNT(*) FROM agg_erp_product) = 0 THEN
INSERT INTO agg_erp_product (product_id, category, warehouse, stock_qty, unit_cost_usd, material)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<1000)
SELECT n,
ELT(1 + n%5, 'Ring','Necklace','Bracelet','Earring','Pendant'),
ELT(1 + n%3, 'WH-HK','WH-SG','WH-UK'),
50 + (n%950),
ROUND(20 + (MOD(n*104729, 980)), 2),
ELT(1 + n%4, 'Gold','Platinum','Silver','Diamond')
FROM s;
END IF;
CREATE TABLE IF NOT EXISTS agg_crm_order (
cust_id INT DEFAULT NULL, region VARCHAR(20) DEFAULT NULL, channel VARCHAR(20) DEFAULT NULL,
order_ts DATETIME DEFAULT NULL, amount_usd DECIMAL(18,2) DEFAULT NULL, status VARCHAR(20) DEFAULT NULL,
KEY ix_region_channel (region,channel,amount_usd)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
IF (SELECT COUNT(*) FROM agg_crm_order) = 0 THEN
INSERT INTO agg_crm_order (cust_id, region, channel, order_ts, amount_usd, status)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<2000)
SELECT 1 + (n % 500),
ELT(1 + n%5, 'NA','EMEA','APAC','LATAM','MEA'),
ELT(1 + n%3, 'web','app','store'),
TIMESTAMPADD(HOUR, n%4000, '2025-01-01 00:00:00'),
ROUND(10 + (MOD(n*5651, 4990)), 2),
ELT(1 + n%3, 'paid','shipped','refunded')
FROM s;
END IF;
-- ---------- 派生表：按 国家 + 本地日 预聚合（窗口演示的高并发替代方案） ----------
DROP TABLE IF EXISTS win_daily_sales;
CREATE TABLE win_daily_sales (
country_code CHAR(2) NOT NULL,
local_day DATE NOT NULL,
orders INT NOT NULL,
amount_usd DECIMAL(18,2) NOT NULL,
PRIMARY KEY (country_code, local_day),
KEY ix_local_day (local_day, country_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO win_daily_sales (country_code, local_day, orders, amount_usd)
SELECT o.country_code,
DATE(TIMESTAMPADD(MINUTE, dc.offset_min, o.order_ts)) AS local_day,
COUNT(*) AS orders,
SUM(o.amount_usd) AS amount_usd
FROM agg_gl_trade_order o
JOIN agg_dim_country dc ON dc.country_code = o.country_code
GROUP BY o.country_code, local_day;
END$$
DELIMITER ;
-- =====================================================================
-- §03 排名函数：ROW_NUMBER / RANK / DENSE_RANK
-- =====================================================================
-- §3.1 ROW_NUMBER：每个国家按金额降序编号，取“金额最高 3 单”
-- 注意：要精确取每组前 N，必须用 ROW_NUMBER()（RANK 遇到并列会多取）。
SELECT country_code, order_id, amount_usd, rn
FROM (
SELECT country_code, order_id, amount_usd,
ROW_NUMBER() OVER (PARTITION BY country_code ORDER BY amount_usd DESC) AS rn
FROM agg_gl_trade_order
) t
WHERE rn <= 3
ORDER BY country_code, rn
LIMIT 40;
-- §3.2 RANK vs DENSE_RANK：并列时 RANK 留空位、DENSE_RANK 不留空位（反模式对比）
SELECT v,
RANK() OVER (ORDER BY v) AS rnk,
DENSE_RANK() OVER (ORDER BY v) AS drnk
FROM (SELECT 10 AS v UNION ALL SELECT 10 UNION ALL SELECT 20
UNION ALL SELECT 20 UNION ALL SELECT 30) t
ORDER BY v;
-- §3.3 反模式演示：用 RANK()<=3 取“前三”会因并列多取（探针，仅展示不报错）
-- 下面这个查询在并列多时返回行数 > 3*国家数，正是 RANK 的坑。
SELECT country_code, COUNT(*) AS cnt_top3_rank
FROM (
SELECT country_code,
RANK() OVER (PARTITION BY country_code ORDER BY amount_usd DESC) AS rk
FROM agg_gl_trade_order
) t
WHERE rk <= 3
GROUP BY country_code
ORDER BY country_code
LIMIT 12;
-- §3.4 每个客户给自己订单排序（第几单）
SELECT customer_id, order_id, amount_usd, order_ts, nth_order
FROM (
SELECT customer_id, order_id, amount_usd, order_ts,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts) AS nth_order
FROM agg_gl_trade_order
WHERE customer_id IN (1,2,3)
) t
WHERE nth_order <= 5
ORDER BY customer_id, nth_order
LIMIT 20;
-- =====================================================================
-- §04 累计计算：SUM(...) OVER(...) 实现“运行总计 / 累计”
-- =====================================================================
-- §4.1 每个国家按本地日累计销售额（在预聚合表 win_daily_sales 上开窗，干净清晰）
SELECT country_code, local_day, amount_usd,
SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY local_day
ROWS UNBOUNDED PRECEDING) AS cum_amount
FROM win_daily_sales
WHERE country_code IN ('CN','US')
ORDER BY country_code, local_day
LIMIT 24;
-- §4.2 每个国家按本地日累计订单数（COUNT 窗口）
SELECT country_code, local_day, orders,
COUNT(*) OVER (PARTITION BY country_code ORDER BY local_day
ROWS UNBOUNDED PRECEDING) AS cum_orders
FROM win_daily_sales
WHERE country_code = 'CN'
ORDER BY local_day
LIMIT 15;
-- §4.3 在原始事实表上：某国家按订单时间累计金额（抽样前 10 行示意）
SELECT country_code, order_ts, amount_usd,
SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY order_ts
ROWS UNBOUNDED PRECEDING) AS cum_amount
FROM agg_gl_trade_order
WHERE country_code = 'CN'
ORDER BY order_ts
LIMIT 10;
-- =====================================================================
-- §05 窗口帧（Window Frame）：ROWS vs RANGE，及移动平均
-- =====================================================================
-- §5.1 默认帧陷阱：有 ORDER BY 无显式帧 → 默认 RANGE UNBOUNDED PRECEDING，
-- 会把“同 ORDER BY 值”的所有并列行一并计入（不是逐行）。对比显式 ROWS。
SELECT v,
SUM(v) OVER (ORDER BY v) AS def_range, -- 默认 RANGE：并列同日全算
SUM(v) OVER (ORDER BY v ROWS UNBOUNDED PRECEDING) AS all_rows, -- 显式 ROWS：逐行累计
AVG(v) OVER (ORDER BY v ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS trailing2
FROM (SELECT 1 AS v UNION ALL SELECT 1 UNION ALL SELECT 2
UNION ALL SELECT 2 UNION ALL SELECT 3) t
ORDER BY v;
-- §5.2 “近 7 行”移动平均（ROWS BETWEEN 6 PRECEDING AND CURRENT ROW）
-- 注意：必须用 ROWS，用 RANGE 会变成“近 7 个同日”，语义不同。
SELECT country_code, local_day, amount_usd,
ROUND(AVG(amount_usd) OVER (PARTITION BY country_code ORDER BY local_day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7_rows
FROM win_daily_sales
WHERE country_code = 'CN'
ORDER BY local_day
LIMIT 15;
-- §5.3 “近 7 行”移动求和（库存/产值场景常用）
SELECT country_code, local_day, amount_usd,
SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY local_day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sum7_rows
FROM win_daily_sales
WHERE country_code = 'CN'
ORDER BY local_day
LIMIT 15;
-- §5.4 命名窗口 WINDOW 复用：同一帧在多个聚合间共享，可读且不易写错
SELECT country_code, local_day, amount_usd,
AVG(amount_usd) OVER w AS ma7,
MIN(amount_usd) OVER w AS min7,
MAX(amount_usd) OVER w AS max7
FROM win_daily_sales
WHERE country_code = 'CN'
WINDOW w AS (PARTITION BY country_code ORDER BY local_day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
ORDER BY local_day
LIMIT 12;
-- =====================================================================
-- §06 偏移/取值类：LAG / LEAD / FIRST_VALUE / LAST_VALUE / NTH_VALUE
-- =====================================================================
-- §6.1 每个客户：上一张订单金额(LAG)、下一张(LEAD)、与上一单间隔天数
SELECT customer_id, order_ts, amount_usd,
LAG(amount_usd) OVER (PARTITION BY customer_id ORDER BY order_ts) AS prev_amt,
LEAD(amount_usd) OVER (PARTITION BY customer_id ORDER BY order_ts) AS next_amt,
TIMESTAMPDIFF(DAY,
LAG(order_ts) OVER (PARTITION BY customer_id ORDER BY order_ts), order_ts) AS gap_days
FROM agg_gl_trade_order
WHERE customer_id IN (1,2)
ORDER BY customer_id, order_ts
LIMIT 20;
-- §6.2 FIRST_VALUE / LAST_VALUE：LAST_VALUE 默认帧=RANGE 当前行，取到的是“自己”；
-- 要取真正“分区最后一行”必须显式 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
SELECT country_code, order_ts, amount_usd,
FIRST_VALUE(amount_usd) OVER (PARTITION BY country_code ORDER BY order_ts) AS first_amt,
LAST_VALUE(amount_usd) OVER (PARTITION BY country_code ORDER BY order_ts
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS true_last_amt,
LAST_VALUE(amount_usd) OVER (PARTITION BY country_code ORDER BY order_ts) AS wrong_last_amt
FROM agg_gl_trade_order
WHERE country_code = 'CN'
ORDER BY order_ts
LIMIT 10;
-- §6.3 NTH_VALUE：取分区内“第 3 行”的金额（同样需显式全帧）
SELECT country_code, order_ts, amount_usd,
NTH_VALUE(amount_usd, 3) OVER (PARTITION BY country_code ORDER BY order_ts
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_amt
FROM agg_gl_trade_order
WHERE country_code = 'CN'
ORDER BY order_ts
LIMIT 10;
-- =====================================================================
-- §07 百分位 / 分位数：NTILE / PERCENT_RANK / CUME_DIST
-- =====================================================================
-- §7.1 NTILE(4)：把客户按累计消费切成四个等分位（quartile）
WITH cust_spend AS (
SELECT customer_id, SUM(amount_usd) AS total_usd
FROM agg_gl_trade_order
GROUP BY customer_id
)
SELECT customer_id, ROUND(total_usd,2) AS total_usd,
NTILE(4) OVER (ORDER BY total_usd) AS quartile
FROM cust_spend
ORDER BY total_usd DESC
LIMIT 15;
-- §7.1b NTILE 分位人数分布（应大致均衡，最多相差 1）
-- 注意：窗口函数在 GROUP BY 之后计算，不能“GROUP BY 窗口别名”，须先算 NTILE 再外层分组。
SELECT quartile, COUNT(*) AS cnt
FROM (
WITH cust_spend AS (
SELECT customer_id, SUM(amount_usd) AS total_usd
FROM agg_gl_trade_order GROUP BY customer_id
)
SELECT NTILE(4) OVER (ORDER BY total_usd) AS quartile FROM cust_spend
) t
GROUP BY quartile
ORDER BY quartile;
-- §7.2 PERCENT_RANK：部门内按薪资排名的百分位（0~1，最小为 0，最大为 1）
SELECT dept, emp_id, salary_usd,
ROUND(PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary_usd), 4) AS pct_rank
FROM agg_hr_employee
ORDER BY dept, salary_usd
LIMIT 20;
-- §7.3 CUME_DIST：部门内“≤当前薪资”的累积分布比例（单调不减，最大为 1）
SELECT dept, emp_id, salary_usd,
ROUND(CUME_DIST() OVER (PARTITION BY dept ORDER BY salary_usd), 4) AS cume
FROM agg_hr_employee
ORDER BY dept, salary_usd
LIMIT 20;
-- =====================================================================
-- §08 万亿级高并发（诚实实测版）
-- =====================================================================
-- 关键实测结论：MySQL 9.0 的窗口函数【无论有无索引都会做 filesort】，
-- EXPLAIN 显示 “Using index; Using filesort”。覆盖索引的作用是省掉“回表取度量列”，
-- 并不能去掉排序。真正降低窗口成本的两大杠杆是：① 预聚合减少参与窗口的行数；
-- ② 分区裁剪 / 分库分表缩小单分区规模。
-- ---------------------------------------------------------------------
-- §8.1 EXPLAIN：窗口 PARTITION BY country_code ORDER BY order_ts
-- 即便有索引 ix_country_ts(country_code, order_ts) 仍会 “Using filesort”（窗口必排序）
EXPLAIN
SELECT country_code, order_ts,
SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY order_ts
ROWS UNBOUNDED PRECEDING) AS cum
FROM agg_gl_trade_order\G
-- §8.2 建“覆盖索引”（分区列+排序列+度量列）：仅省回表，不去掉排序
-- MySQL 8/9 不支持 DROP/CREATE INDEX ... IF EXISTS，故用 information_schema 判存在后条件创建。
SET @idx_exists = (SELECT COUNT(*) FROM information_schema.statistics
WHERE table_schema='jewelryanalytics' AND table_name='agg_gl_trade_order'
AND index_name='ix_cover_cty_ts_amt');
SET @idx_sql = IF(@idx_exists=0,
'CREATE INDEX ix_cover_cty_ts_amt ON agg_gl_trade_order (country_code, order_ts, amount_usd)',
'SELECT 1');
PREPARE s_idx FROM @idx_sql;
EXECUTE s_idx;
DEALLOCATE PREPARE s_idx;
-- §8.3 再 EXPLAIN：仍 “Using index; Using filesort”，但多了 Using index（覆盖，免回表）
EXPLAIN
SELECT country_code, order_ts, amount_usd,
SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY order_ts
ROWS UNBOUNDED PRECEDING) AS cum
FROM agg_gl_trade_order\G
-- §8.4 度量：覆盖索引 vs 非覆盖（强制走 ix_country_ts 须回表取 amount_usd）
-- 覆盖索引的 innodb_rows_read 显著低于非覆盖（省掉百万次回表）。
CALL win_measure('win_cover',
'SELECT COUNT(*) FROM (SELECT SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY order_ts ROWS UNBOUNDED PRECEDING) FROM agg_gl_trade_order) t');
CALL win_measure('win_noncover',
'SELECT COUNT(*) FROM (SELECT SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY order_ts ROWS UNBOUNDED PRECEDING) FROM agg_gl_trade_order USE INDEX (ix_country_ts)) t');
-- §8.5 度量：原始 100 万行开窗 vs 预聚合 1 万行开窗 → 行数/扫描差巨大（真正的降成本杠杆）
CALL win_measure('win_raw_1M',
'SELECT COUNT(*) FROM (SELECT country_code, SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY order_ts ROWS UNBOUNDED PRECEDING) FROM agg_gl_trade_order) t');
CALL win_measure('win_preagg',
'SELECT COUNT(*) FROM (SELECT country_code, SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY local_day ROWS UNBOUNDED PRECEDING) FROM win_daily_sales) t');
-- §8.6 查看度量结果
SELECT label, sort_rows_d, innodb_rows_read_d, created_tmp_tables_d
FROM win_demo_measure
WHERE label IN ('win_cover','win_noncover','win_raw_1M','win_preagg')
ORDER BY label;
-- §8.7 结论（文字）
-- ① 窗口函数无法靠索引去掉排序（MySQL 9.0 实测必 filesort）；覆盖索引仅省回表 I/O。
-- ② 真正降成本：先把事实表按维度预聚合（win_daily_sales）再开窗，参与行数降 ~100 倍。
-- ③ 架构层：分区裁剪 / 分库分表 / 读写分离 / CQRS，把窗口计算下沉到预计算汇总层。
-- =====================================================================
-- §09 六域窗口模板：OA / ERP / CRM / HR / MES / SCM
-- =====================================================================
-- ---- OA：审批流 ----
-- 9.1 每个部门按提交时间累计审批单数（运行计数）
SELECT dept, submitted_ts, approval_id,
COUNT(*) OVER (PARTITION BY dept ORDER BY submitted_ts ROWS UNBOUNDED PRECEDING) AS dept_seq
FROM agg_oa_approval
ORDER BY dept, submitted_ts
LIMIT 15;
-- 9.2 部门按平均审批时长排名（耗时越长越靠后）
SELECT dept, ROUND(AVG(hours_to_approve),2) AS avg_h,
RANK() OVER (ORDER BY AVG(hours_to_approve)) AS rnk
FROM agg_oa_approval
GROUP BY dept
ORDER BY rnk;
-- ---- ERP：库存 ----
-- 9.3 商品按“类别内库存金额”排名（金额=库存量×单位成本）
SELECT category, product_id, stock_qty, unit_cost_usd,
stock_qty*unit_cost_usd AS stock_value,
RANK() OVER (PARTITION BY category ORDER BY stock_qty*unit_cost_usd DESC) AS rnk_in_cat
FROM agg_erp_product
ORDER BY category, rnk_in_cat
LIMIT 20;
-- ---- CRM：客户 ----
-- 9.4 每个区域把客户按 LTV 切成四分位
SELECT region, cust_id, ROUND(SUM(amount_usd),2) AS ltv,
NTILE(4) OVER (PARTITION BY region ORDER BY SUM(amount_usd)) AS quartile
FROM agg_crm_order
GROUP BY region, cust_id
ORDER BY region, ltv DESC
LIMIT 20;
-- ---- HR：人力 ----
-- 9.5 部门内高薪排名（ROW_NUMBER 精确前 N）
SELECT dept, emp_id, salary_usd,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary_usd DESC) AS rich_rank
FROM agg_hr_employee
ORDER BY dept, rich_rank
LIMIT 20;
-- 9.6 部门内按入职日期累计人数（运行计数）
SELECT dept, hire_date, emp_id,
COUNT(*) OVER (PARTITION BY dept ORDER BY hire_date ROWS UNBOUNDED PRECEDING) AS cum_headcount
FROM agg_hr_employee
ORDER BY dept, hire_date
LIMIT 15;
-- ---- MES：制造 ----
-- 9.7 每条产线近 10 单良率移动平均（yield = (qty-defect)/qty）
SELECT line, prod_ts, qty, defect_qty,
ROUND((qty-defect_qty)/qty,4) AS yield,
ROUND(AVG((qty-defect_qty)/qty) OVER (PARTITION BY line ORDER BY prod_ts
ROWS BETWEEN 9 PRECEDING AND CURRENT ROW),4) AS ma10_yield
FROM agg_mes_work_order
ORDER BY line, prod_ts
LIMIT 20;
-- 9.8 同产线内各班次总产量排名
SELECT line, shift, SUM(qty) AS total_qty,
RANK() OVER (PARTITION BY line ORDER BY SUM(qty) DESC) AS shift_rank
FROM agg_mes_work_order
GROUP BY line, shift
ORDER BY line, shift_rank
LIMIT 20;
-- ---- SCM：供应链 ----
-- 9.9 承运商按平均重量排名
SELECT carrier, ROUND(AVG(weight_kg),2) AS avg_w,
RANK() OVER (ORDER BY AVG(weight_kg) DESC) AS rnk
FROM agg_scm_shipment
GROUP BY carrier
ORDER BY rnk;
-- 9.10 每个承运商按发货时间累计重量（运行求和）
SELECT carrier, ship_ts, weight_kg,
SUM(weight_kg) OVER (PARTITION BY carrier ORDER BY ship_ts ROWS UNBOUNDED PRECEDING) AS cum_w
FROM agg_scm_shipment
ORDER BY carrier, ship_ts
LIMIT 15;
-- =====================================================================
-- §10 国际时区时间差 + 国际各种货币结算
-- =====================================================================
-- 注意：本机 mysql.time_zone_name 为空 → CONVERT_TZ() 恒 NULL；
-- 故统一用 TIMESTAMPADD(MINUTE, 固定偏移, ts) 硬编码换算本地日（偏移来自 agg_dim_country.offset_min）。
-- §10.1 UTC 日 vs 本地日：时区偏移把“同一时刻”归到不同自然日（如 BR -180 提前到前一天）
SELECT o.country_code, order_ts,
DATE(order_ts) AS utc_day,
DATE(TIMESTAMPADD(MINUTE, dc.offset_min, o.order_ts)) AS local_day
FROM agg_gl_trade_order o
JOIN agg_dim_country dc ON dc.country_code = o.country_code
WHERE o.country_code IN ('CN','BR','US')
ORDER BY order_ts
LIMIT 12;
-- §10.2 本地日维度累计销售额（窗口按本地日，而非 UTC 日，符合各地营业日口径）
SELECT country_code, local_day, amount_usd,
SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY local_day
ROWS UNBOUNDED PRECEDING) AS cum_local
FROM win_daily_sales
WHERE country_code = 'CN'
ORDER BY local_day
LIMIT 12;
-- §10.3 多币种结算铁律：只能对 amount_usd 归一结算；跨币种 SUM(amount_orig) 无意义
-- 下面按币种分别看：sum_orig 是“混合币种数值相加”，绝对不能用；sum_usd 才是真美元口径。
SELECT currency_code,
SUM(amount_orig) AS sum_orig_mixed, -- 跨币种相加，量级不可比，错误示范
SUM(amount_usd) AS sum_usd -- 归一美元，正确口径
FROM agg_gl_trade_order
GROUP BY currency_code
ORDER BY currency_code;
-- §10.4 国家内按美元金额做百分位排名（结算单位统一为 USD）
SELECT country_code, order_id, amount_usd,
ROUND(PERCENT_RANK() OVER (PARTITION BY country_code ORDER BY amount_usd), 4) AS pct_usd
FROM agg_gl_trade_order
WHERE country_code = 'CN'
ORDER BY pct_usd DESC
LIMIT 10;
-- =====================================================================
-- §11 自检验证 V1–V12（全部通过才算交付）
-- =====================================================================
-- V1：ROW_NUMBER 取每组前 3，10 国 × 3 = 30 行
SET @v1 = (SELECT COUNT(*) FROM (
SELECT country_code, ROW_NUMBER() OVER (PARTITION BY country_code ORDER BY amount_usd DESC) AS rn
FROM agg_gl_trade_order
) t WHERE rn <= 3);
CALL win_v('V1_row_number_top3', IF(@v1=30,1,0), CONCAT('top3_rows=',@v1,' expect=30'));
-- V2：RANK 留空位 / DENSE_RANK 不留（v=20→rnk=3,drnk=2；v=30→rnk=5,drnk=3）
SET @v2 = (SELECT COUNT(*) FROM (
SELECT v, RANK() OVER (ORDER BY v) rnk, DENSE_RANK() OVER (ORDER BY v) drnk
FROM (SELECT 10 v UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 20 UNION ALL SELECT 30) t
) x WHERE (rnk=3 AND drnk=2) OR (rnk=5 AND drnk=3));
CALL win_v('V2_rank_vs_dense', IF(@v2=3,1,0), CONCAT('match_rows=',@v2,' expect=3'));
-- V3：每个国家累计列最后一行 = 该国总额（运行总计正确性）
SET @v3 = (SELECT COUNT(*) FROM (
SELECT country_code, MAX(cum) AS last_cum, SUM(amount_usd) AS tot
FROM (SELECT country_code, amount_usd,
SUM(amount_usd) OVER (PARTITION BY country_code ORDER BY order_ts ROWS UNBOUNDED PRECEDING) AS cum
FROM agg_gl_trade_order) t
GROUP BY country_code
) g WHERE last_cum <> tot);
CALL win_v('V3_running_total', IF(@v3=0,1,0), CONCAT('mismatch_countries=',@v3,' expect=0'));
-- V4：窗口帧 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW（受控 4 行，预期已知）
SET @v4 = (SELECT COUNT(*) FROM (
SELECT x,
ROUND(AVG(x) OVER (ORDER BY x ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),4) AS ma3,
CASE ROW_NUMBER() OVER (ORDER BY x)
WHEN 1 THEN x
WHEN 2 THEN (x + LAG(x) OVER (ORDER BY x))/2
ELSE (x + LAG(x) OVER (ORDER BY x) + LAG(x,2) OVER (ORDER BY x))/3 END AS exp
FROM (SELECT 10 x UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40) t
) z WHERE ABS(ma3 - exp) > 0.0001);
CALL win_v('V4_window_frame', IF(@v4=0,1,0), CONCAT('mismatch=',@v4,' expect=0'));
-- V5：LAG/LEAD 方向正确（首行 prev=NULL/nxt=下一行；末行 nxt=NULL）
SET @v5 = (SELECT COUNT(*) FROM (
SELECT x, LAG(x) OVER (ORDER BY x) AS prev, LEAD(x) OVER (ORDER BY x) AS nxt
FROM (SELECT 10 x UNION ALL SELECT 20 UNION ALL SELECT 30) t
) z WHERE (x=10 AND prev IS NULL AND nxt=20)
OR (x=20 AND prev=10 AND nxt=30)
OR (x=30 AND prev=20 AND nxt IS NULL));
CALL win_v('V5_lag_lead', IF(@v5=3,1,0), CONCAT('match_rows=',@v5,' expect=3'));
-- V6：FIRST_VALUE=首行、LAST_VALUE(全帧)=末行（证明默认帧取不到真正末行）
SET @v6 = (SELECT COUNT(*) FROM (
SELECT x, FIRST_VALUE(x) OVER (ORDER BY x) AS fv,
LAST_VALUE(x) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT 10 x UNION ALL SELECT 20 UNION ALL SELECT 30) t
) z WHERE fv=10 AND lv=30);
CALL win_v('V6_first_last_value', IF(@v6=3,1,0), CONCAT('match_rows=',@v6,' expect=3'));
-- V7：NTILE(4) 在 20 万客户上四等分，每桶恰 50000
SET @v7 = (SELECT COUNT(*) FROM (
SELECT quartile, COUNT(*) c FROM (
WITH cust_spend AS (SELECT customer_id, SUM(amount_usd) s FROM agg_gl_trade_order GROUP BY customer_id)
SELECT NTILE(4) OVER (ORDER BY s) AS quartile FROM cust_spend
) t GROUP BY quartile
) g WHERE c <> 50000);
CALL win_v('V7_ntile_balanced', IF(@v7=0,1,0), CONCAT('bad_buckets=',@v7,' expect=0'));
-- V8：PERCENT_RANK 全部落在 [0,1]
SET @v8 = (SELECT COUNT(*) FROM (
SELECT PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary_usd) AS p FROM agg_hr_employee
) t WHERE p < 0 OR p > 1);
CALL win_v('V8_percent_rank_range', IF(@v8=0,1,0), CONCAT('violations=',@v8,' expect=0'));
-- V9：CUME_DIST 全部落在 (0,1]
SET @v9 = (SELECT COUNT(*) FROM (
SELECT CUME_DIST() OVER (PARTITION BY dept ORDER BY salary_usd) AS c FROM agg_hr_employee
) t WHERE c <= 0 OR c > 1);
CALL win_v('V9_cume_dist_range', IF(@v9=0,1,0), CONCAT('violations=',@v9,' expect=0'));
-- V10：时区重分桶后各国总额不变（本地日合计 = 事实表合计）
SET @v10 = (SELECT COUNT(*) FROM (
SELECT a1.country_code,
(SELECT SUM(amount_usd) FROM agg_gl_trade_order a2 WHERE a2.country_code=a1.country_code) AS total_fact,
SUM(a1.amount_usd) AS total_daily
FROM win_daily_sales a1 GROUP BY a1.country_code
) t WHERE ABS(total_fact - total_daily) > 0.01);
CALL win_v('V10_tz_total_invariant', IF(@v10=0,1,0), CONCAT('mismatch_countries=',@v10,' expect=0'));
-- V11：预聚合显著降低窗口参与行数（原始 100 万行 vs 预聚合 1 万行，扫描行数差百倍）
-- 诚实注：MySQL 9.0 窗口函数无论有无索引都 filesort，覆盖索引只省回表 I/O；
-- 真正降成本靠“预聚合减行数”，故此处断言预聚合读取行数远低于原始。
SET @raw_read = (SELECT COALESCE(innodb_rows_read_d,0) FROM win_demo_measure WHERE label='win_raw_1M');
SET @pre_read = (SELECT COALESCE(innodb_rows_read_d,0) FROM win_demo_measure WHERE label='win_preagg');
CALL win_v('V11_preagg_reduces_rows', IF(@pre_read < @raw_read AND @raw_read > 100000,1,0),
CONCAT('raw_read=',@raw_read,' preagg_read=',@pre_read));
-- V12：win_daily_sales 行数 = 事实表去重 (国家,本地日) 数
SET @v12a = (SELECT COUNT(*) FROM win_daily_sales);
SET @v12b = (SELECT COUNT(*) FROM (
SELECT DISTINCT o.country_code, DATE(TIMESTAMPADD(MINUTE, dc.offset_min, o.order_ts)) AS ld
FROM agg_gl_trade_order o JOIN agg_dim_country dc ON dc.country_code = o.country_code
) x);
CALL win_v('V12_preagg_rowcount', IF(@v12a=@v12b,1,0), CONCAT('daily_rows=',@v12a,' expect=',@v12b));
-- 汇总自检结果
SELECT name, passed, detail FROM win_demo_result ORDER BY name;
SELECT CONCAT('PASS=', SUM(passed), ' / TOTAL=', COUNT(*), ' / FAIL=', SUM(1-passed))
AS verdict FROM win_demo_result;
-- =====================================================================
-- §12 总结：窗口函数对照表 / 度量 / 结论 / 方法清单 / 脚本结构
-- =====================================================================
-- §12.1 窗口函数对照表（首行为表头；CAST 防截断）
SELECT CAST('窗口函数' AS CHAR(22)) AS fn,
CAST('语义' AS CHAR(34)) AS meaning,
CAST('默认帧/易错点' AS CHAR(34)) AS frame_note,
CAST('珠宝行业场景' AS CHAR(30)) AS use_case
UNION ALL
SELECT 'ROW_NUMBER','逐行连续编号，并列也占不同号','无','每组精确前N（TOP-N 用这个，别用 RANK）'
UNION ALL
SELECT 'RANK','并列同号，后续留空位','无','并列榜单（同金额同名次，但会多取）'
UNION ALL
SELECT 'DENSE_RANK','并列同号，不留空位','无','并列榜单且不希望缺号'
UNION ALL
SELECT 'NTILE(n)','把有序行切成 n 个等分桶','无','客户/LTV 四分位、RFM 分层'
UNION ALL
SELECT 'PERCENT_RANK','(排名-1)/(总行-1)，0~1','无','部门内薪资百分位'
UNION ALL
SELECT 'CUME_DIST','≤当前值的累积比例，0~1','无','累积分布（如达标率）'
UNION ALL
SELECT 'LAG/LEAD','取前/后第 N 行','无','环比、订单间隔天数'
UNION ALL
SELECT 'FIRST/LAST_VALUE','分区首/末行','LAST_VALUE 默认帧=当前行，须显式全帧','首单/末单金额'
UNION ALL
SELECT 'NTH_VALUE','分区第 N 行','同样需显式全帧','第 3 单金额'
UNION ALL
SELECT 'SUM/AVG..OVER','聚合+窗口，可带帧','默认 RANGE(同值并算)','运行总计、移动平均'
UNION ALL
SELECT 'ROWS BETWEEN','按“行数”界定帧','近N行必须用 ROWS，非 RANGE','7行移动平均'
UNION ALL
SELECT 'RANGE BETWEEN','按“ORDER BY 值区间”界定帧','同值多行一并纳入','按金额区间聚合';
-- §12.2 高并发度量（来自 win_measure；SORT_ROWS 越低越好）
SELECT label, sort_rows_d, innodb_rows_read_d, created_tmp_tables_d
FROM win_demo_measure
ORDER BY label;
-- §12.3 三条结论
-- ① 窗口函数“先 WHERE/GROUP BY，后开窗”；不能在 WHERE/HAVING 直接引用窗口结果，须套子查询。
-- ② 移动平均/运行总计默认是 RANGE 帧（同值并算），要“近 N 行”必须写 ROWS BETWEEN n PRECEDING AND CURRENT ROW；
-- LAST_VALUE/NTH_VALUE 要取真正末/第N行，必须显式 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
-- ③ 万亿级（诚实实测）：MySQL 9.0 窗口函数【无论有无索引都会 filesort】，覆盖索引只省回表 I/O、不去掉排序；
-- 真正降成本靠“先预聚合再开窗”（参与行数降百倍）+ 分区裁剪/分库分表；架构层物化汇总表 + 读写分离/CQRS 下沉计算。
-- §12.4 八条方法清单
-- 1) 分组排名 → ROW_NUMBER/RANK/DENSE_RANK
-- 2) 运行总计/计数 → SUM/COUNT OVER(... ROWS UNBOUNDED PRECEDING)
-- 3) 移动平均/求和 → AVG/SUM OVER(... ROWS BETWEEN n PRECEDING AND CURRENT ROW)
-- 4) 同比/环比 → LAG/LEAD
-- 5) 分位数/百分位 → NTILE/PERCENT_RANK/CUME_DIST
-- 6) 取首末行 → FIRST_VALUE/LAST_VALUE（记得显式全帧）
-- 7) 高并发 → 覆盖索引(省回表 I/O) + 预聚合减行数 + 物化汇总下沉计算
-- 8) 国际化 → TIMESTAMPADD 固定偏移换本地日；只 SUM(amount_usd) 结算
-- §12.5 脚本结构
-- _w00 头块/运行必读/窗口支持矩阵/反模式速查
-- _w01 度量框架（win_* 表 + win_snap/measure/v/err_probe）
-- _w02 实验室建表（win_lab_build 复用 agg_*，建 win_daily_sales）
-- _w03 排名 _w04 运行总计 _w05 窗口帧/移动平均 _w06 偏移取值
-- _w07 百分位 _w08 高并发 _w09 六域模板 _w10 国际时区/多币种
-- _w11 自检验证 V1–V12 _w12 本总结
SELECT 'MySQL9_WindowFunctions 演示完成：V1–V12 见 win_demo_result，全部 PASS 即交付。' AS done;
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号