sql: SQL Aggregate Functions and Grouping Sets using PostgreSQL 18

— ============================================================================
— PostgreSQL 18 — SQL 聚合函数与分组集 (Aggregate Functions & Grouping Sets)
— 场景: 万亿数据高并发 / 国际时区时间差 / 国际多种货币结算
— 本文件【自包含·完全幂等】: 重复运行结果逐字节一致 (setseed 保证造数一致)
— 连接方式: PGPASSWORD=<your_password> psql -U postgres -h localhost -p 5433 -d jewelry_pg -f 本文件
— ============================================================================

\\timing off
SET client_min_messages = warning; — 抑制 DROP/CREATE/VACUUM 的 NOTICE, 保证重跑幂等
SET max_parallel_workers_per_gather = 0; — 关并行, 使块统计稳定(万亿级可据需开启)
SET random_page_cost = 1.1;
SET seq_page_cost = 1.0;
SET effective_cache_size = '4GB';

— —————————————————————————-
— 0. 度量框架 (DROP + CREATE, 幂等)
— pg_plan_stats: 抓真实执行计划 (Actual Rows / 共享块 / 扫描节点类型)
— pg_find_scan : 下钻到真正的扫描节点 (顶层常是 Aggregate/Finalize Aggregate)
— pg_demo_v : 自检登记
— pg_demo_err_probe : 捕获真实报错
— —————————————————————————-
DROP FUNCTION IF EXISTS pg_plan_stats(text) CASCADE;
CREATE OR REPLACE FUNCTION pg_plan_stats(
p_sql text,
OUT actual_rows bigint,
OUT shared_blks bigint,
OUT node_type text)
RETURNS record LANGUAGE plpgsql AS $$
DECLARE j json;
BEGIN
EXECUTE 'EXPLAIN (ANALYZE, TIMING OFF, FORMAT JSON) ' || p_sql INTO j;
actual_rows := (j->0->'Plan'->>'Actual Rows')::numeric::bigint;
shared_blks := COALESCE((j->0->'Plan'->>'Shared Hit Blocks')::numeric::bigint,0)
+ COALESCE((j->0->'Plan'->>'Shared Read Blocks')::numeric::bigint,0);
node_type := pg_find_scan(j->0->'Plan');
END $$;

DROP FUNCTION IF EXISTS pg_find_scan(json) CASCADE;
CREATE OR REPLACE FUNCTION pg_find_scan(cur json) RETURNS text LANGUAGE plpgsql AS $$
DECLARE nt text;
BEGIN
nt := cur->>'Node Type';
IF nt LIKE '%Scan' THEN RETURN nt; END IF;
IF cur->'Plans' IS NOT NULL AND json_array_length(cur->'Plans') > 0 THEN
RETURN pg_find_scan(cur->'Plans'->0);
END IF;
RETURN nt;
END $$;

DROP TABLE IF EXISTS pg_demo_check;
CREATE TABLE pg_demo_check (
name text PRIMARY KEY,
pass int,
detail text
);
DROP FUNCTION IF EXISTS pg_demo_v(text,int,text) CASCADE;
CREATE OR REPLACE FUNCTION pg_demo_v(p_name text, p_pass int, p_detail text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO pg_demo_check(name,pass,detail) VALUES (p_name,p_pass,p_detail)
ON CONFLICT (name) DO UPDATE SET pass=EXCLUDED.pass, detail=EXCLUDED.detail;
END $$;

DROP TABLE IF EXISTS pg_demo_probe;
CREATE TABLE pg_demo_probe (
label text PRIMARY KEY,
sqlstate text,
msg text
);
DROP FUNCTION IF EXISTS pg_demo_err_probe(text,text) CASCADE;
CREATE OR REPLACE FUNCTION pg_demo_err_probe(p_label text, p_sql text)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE e text; s text;
BEGIN
BEGIN
EXECUTE p_sql;
INSERT INTO pg_demo_probe(label,sqlstate,msg) VALUES (p_label,'OK','执行成功')
ON CONFLICT (label) DO UPDATE SET sqlstate='OK', msg='执行成功';
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS s=RETURNED_SQLSTATE, e=MESSAGE_TEXT;
INSERT INTO pg_demo_probe(label,sqlstate,msg) VALUES (p_label,s,e)
ON CONFLICT (label) DO UPDATE SET sqlstate=EXCLUDED.sqlstate, msg=EXCLUDED.msg;
END;
END $$;

— —————————————————————————-
— 1. 维度表 (八域中台配套维度) —— DROP + CREATE 保证幂等
— —————————————————————————-
DROP TABLE IF EXISTS pg_txn CASCADE;
DROP TABLE IF EXISTS pg_customer CASCADE;
DROP TABLE IF EXISTS pg_product CASCADE;
DROP TABLE IF EXISTS pg_supplier CASCADE;
DROP TABLE IF EXISTS pg_warehouse CASCADE;
DROP TABLE IF EXISTS pg_employee CASCADE;

— 维度: 客户 (含时区 / VIP 等级)
CREATE TABLE pg_customer (
customer_id int PRIMARY KEY,
cust_name text,
country_code text,
tz text,
vip_level int,
email text
);
INSERT INTO pg_customer
SELECT n,
(ARRAY['Acme','Globex','Initech','Umbrella','Soylent','Hooli','Stark','Wayne','Wonka','Cyberdyne'])[1+(n%10)] || ' ' || chr(65+(n%26)) || n,
(ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+(n%10)],
(ARRAY['Asia/Shanghai','Asia/Hong_Kong','America/New_York','Asia/Tokyo','Europe/Berlin','Europe/Paris','Europe/London','Asia/Singapore','Australia/Sydney','Asia/Kolkata'])[1+(n%10)],
1+(n%5),
'cust'||n||'@example.com'
FROM generate_series(1,10000) n;

— 维度: 商品 KMS (多语言名称/描述 + jsonb 规格 + 生成式 tsvector)
CREATE TABLE pg_product (
product_id int PRIMARY KEY,
sku text,
name_en text,
name_cn text,
desc_en text,
desc_cn text,
specs jsonb,
search_vec tsvector GENERATED ALWAYS AS (
to_tsvector('english', coalesce(desc_en,'')||' '||coalesce(name_en,''))) STORED
);
INSERT INTO pg_product (product_id, sku, name_en, name_cn, desc_en, desc_cn, specs)
SELECT n,
'SKU-'||lpad(n::text,6,'0'),
(ARRAY['Diamond Ring','Gold Necklace','Pearl Earring','Ruby Bracelet','Sapphire Pendant','Emerald Brooch','Platinum Band','Tanzanite Ring','Jade Pendant','Onyx Cufflink'])[1+(n%10)] || ' ' || n,
'珠宝'||n,
'A fine '||(ARRAY['diamond','gold','pearl','ruby','sapphire','emerald','platinum','tanzanite','jade','onyx'])[1+(n%10)]||' piece crafted for global markets.',
'精致'||(ARRAY['钻石','黄金','珍珠','红宝石','蓝宝石','祖母绿','铂金','坦桑石','翡翠','缟玛瑙'])[1+(n%10)]||'饰品。',
jsonb_build_object('carat', round((0.5+(n%50)/10.0)::numeric,2),
'color', (ARRAY['D','E','F','G','H'])[1+(n%5)],
'clarity',(ARRAY['VVS1','VVS2','VS1','VS2','SI1'])[1+(n%5)])
FROM generate_series(1,2000) n;

— 维度: 供应商 / 仓库 / 员工
CREATE TABLE pg_supplier (supplier_id int PRIMARY KEY, supplier_name text, country_code text);
INSERT INTO pg_supplier
SELECT n, 'Supplier-'||n, (ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+(n%10)]
FROM generate_series(1,200) n;

CREATE TABLE pg_warehouse (warehouse_id int PRIMARY KEY, warehouse_name text, country_code text);
INSERT INTO pg_warehouse
SELECT n, 'WH-'||n, (ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+(n%10)]
FROM generate_series(1,50) n;

CREATE TABLE pg_employee (emp_id int PRIMARY KEY, emp_name text, dept text, country_code text);
INSERT INTO pg_employee
SELECT n, 'Emp-'||n, (ARRAY['OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'])[1+(n%8)],
(ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+(n%10)]
FROM generate_series(1,500) n;

— —————————————————————————-
— 2. 事实表 pg_txn (10 万行交易, 八域 + 时区生成列 + 本地日生成列 + 多币种)
— —————————————————————————-
CREATE TABLE pg_txn (
txn_id bigint PRIMARY KEY,
system_code text NOT NULL,
customer_id int,
product_id int,
supplier_id int,
warehouse_id int,
emp_id int,
country_code text,
currency_code text,
amount_orig numeric(14,2),
amount_usd numeric(14,4),
created_utc timestamptz,
tz text GENERATED ALWAYS AS (
CASE country_code
WHEN 'CN' THEN 'Asia/Shanghai' WHEN 'HK' THEN 'Asia/Hong_Kong'
WHEN 'US' THEN 'America/New_York' WHEN 'JP' THEN 'Asia/Tokyo'
WHEN 'DE' THEN 'Europe/Berlin' WHEN 'FR' THEN 'Europe/Paris'
WHEN 'GB' THEN 'Europe/London' WHEN 'SG' THEN 'Asia/Singapore'
WHEN 'AU' THEN 'Australia/Sydney' WHEN 'IN' THEN 'Asia/Kolkata'
ELSE 'UTC' END) STORED,
create_local_date date GENERATED ALWAYS AS (
(created_utc AT TIME ZONE (
CASE country_code
WHEN 'CN' THEN 'Asia/Shanghai' WHEN 'HK' THEN 'Asia/Hong_Kong'
WHEN 'US' THEN 'America/New_York' WHEN 'JP' THEN 'Asia/Tokyo'
WHEN 'DE' THEN 'Europe/Berlin' WHEN 'FR' THEN 'Europe/Paris'
WHEN 'GB' THEN 'Europe/London' WHEN 'SG' THEN 'Asia/Singapore'
WHEN 'AU' THEN 'Australia/Sydney' WHEN 'IN' THEN 'Asia/Kolkata'
ELSE 'UTC' END))::date) STORED,
reference_no text UNIQUE,
note text,
rand_val double precision
);

— 造数: setseed 保证每次运行数据完全一致 -> 幂等
DO $$
BEGIN
PERFORM setseed(0.20261018);
INSERT INTO pg_txn (txn_id, system_code, customer_id, product_id, supplier_id, warehouse_id,
emp_id, country_code, currency_code, amount_orig, amount_usd,
created_utc, reference_no, note, rand_val)
SELECT n,
(ARRAY['OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'])[1+(n%8)],
1+((n+n/8)%10000), — 客户: 解耦 note 的 n%12, 避免跨域模耦合
1+(n%2000),
1+(n%200),
1+(n%50),
1+(n%500),
(ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+((n/8)%10)],
(ARRAY['CNY','HKD','USD','JPY','EUR','EUR','GBP','SGD','AUD','INR'])[1+((n/8)%10)],
round((100 + random()*90000)::numeric,2),
round(((100 + random()*90000) /
(CASE (ARRAY['CNY','HKD','USD','JPY','EUR','EUR','GBP','SGD','AUD','INR'])[1+(n%10)]
WHEN 'CNY' THEN 7.10 WHEN 'HKD' THEN 7.80 WHEN 'USD' THEN 1.00
WHEN 'JPY' THEN 150.0 WHEN 'EUR' THEN 0.92 WHEN 'GBP' THEN 0.79
WHEN 'SGD' THEN 1.35 WHEN 'AUD' THEN 1.52 WHEN 'INR' THEN 83.0 END))::numeric,4),
'2025-01-01 00:00:00+00'::timestamptz + ((random()*300*24*3600)::integer * interval '1 second'),
'REF-'||lpad(n::text,8,'0'),
(ARRAY['Diamond','Gold','Pearl','Ruby','Sapphire','Emerald','Platinum','Tanzanite','Jade','Onyx','Zirconia','Topaz'])[1+(n%12)] || ' order for VIP client',
random()
FROM generate_series(1,100000) n;
END $$;

— 索引 (覆盖索引用于免回表聚合)
CREATE INDEX idx_txn_cover ON pg_txn (system_code, amount_usd);
CREATE INDEX idx_txn_created ON pg_txn (created_utc);
CREATE INDEX idx_txn_localday ON pg_txn (create_local_date);
CREATE INDEX idx_txn_cust ON pg_txn (customer_id);
CREATE INDEX idx_txn_sys_ctry ON pg_txn (system_code, country_code);
CREATE INDEX brin_txn_created ON pg_txn USING brin (created_utc);

VACUUM ANALYZE pg_txn; — 使覆盖索引 Index Only Scan 真正免回表

— ============================================================================
— §1. 基本聚合函数 (COUNT / SUM / AVG / MIN / MAX) —— 单域 GMV 看板
— ============================================================================
\\echo '=== §1 基本聚合: ERP 域 GMV 看板 ==='
SELECT system_code,
count(*) AS txn_cnt,
round(sum(amount_usd),2) AS gmv_usd,
round(avg(amount_usd),2) AS avg_usd,
round(min(amount_usd),2) AS min_usd,
round(max(amount_usd),2) AS max_usd
FROM pg_txn
WHERE system_code = 'ERP'
GROUP BY system_code;

— ============================================================================
— §2. 多列 GROUP BY —— 八域 × 十国 交叉汇总
— ============================================================================
\\echo '=== §2 多列 GROUP BY: 域 × 国家 GMV (取前 12 行) ==='
SELECT system_code, country_code,
count(*) AS txn_cnt,
round(sum(amount_usd),2) AS gmv_usd
FROM pg_txn
GROUP BY system_code, country_code
ORDER BY gmv_usd DESC
LIMIT 12;

— ============================================================================
— §3. HAVING —— 过滤聚合结果 (GMV 超过门槛的 域×国家 组合)
— ============================================================================
\\echo '=== §3 HAVING: 仅保留 GMV > 1,000,000 的 域×国家 组合 ==='
SELECT system_code, country_code,
count(*) AS txn_cnt,
round(sum(amount_usd),2) AS gmv_usd
FROM pg_txn
GROUP BY system_code, country_code
HAVING sum(amount_usd) > 1000000
ORDER BY gmv_usd DESC
LIMIT 12;

— ============================================================================
— §4. 国际时区聚合 —— 同一笔 UTC 交易按本地日归属可能不同天
— ============================================================================
\\echo '=== §4 时区聚合: 中国(CN) 按 UTC 日 vs 上海本地日 的每日笔数分布(各取前 5) ==='
\\echo '— 4a UTC 日 —'
SELECT (created_utc AT TIME ZONE 'UTC')::date AS utc_day, count(*) AS cnt
FROM pg_txn WHERE country_code='CN' GROUP BY 1 ORDER BY 1 LIMIT 5;
\\echo '— 4b 上海本地日 (生成列 create_local_date) —'
SELECT create_local_date AS shanghai_day, count(*) AS cnt
FROM pg_txn WHERE country_code='CN' GROUP BY 1 ORDER BY 1 LIMIT 5;

— ============================================================================
— §5. 国际多币种结算口径 —— 原币混加无意义, 须统一 amount_usd
— ============================================================================
\\echo '=== §5 多币种: 按原币分组汇总 vs 统一美元口径 ==='
SELECT currency_code,
round(sum(amount_orig),2) AS gmv_orig,
round(sum(amount_usd),2) AS gmv_usd
FROM pg_txn
GROUP BY currency_code
ORDER BY gmv_usd DESC;

— ============================================================================
— §6. 统计分布聚合 —— 分位数 / 众数 / 字符串聚合
— ============================================================================
\\echo '=== §6a 分位数: 单笔美元金额 中位数 & 90 分位 ==='
SELECT round(percentile_cont(0.5) WITHIN GROUP (ORDER BY amount_usd)::numeric,2) AS median_usd,
round(percentile_cont(0.9) WITHIN GROUP (ORDER BY amount_usd)::numeric,2) AS p90_usd,
round(percentile_disc(0.9) WITHIN GROUP (ORDER BY amount_usd)::numeric,2) AS p90_disc_usd
FROM pg_txn;

\\echo '=== §6b 众数: 最频繁结算货币 ==='
SELECT mode() WITHIN GROUP (ORDER BY currency_code) AS top_currency,
count(*) FILTER (WHERE true) AS total
FROM pg_txn;

\\echo '=== §6c 字符串聚合: 客户 #1 的订单号清单 (前 60 字符) ==='
SELECT left(string_agg(reference_no, ', ' ORDER BY txn_id), 60) AS order_list
FROM pg_txn WHERE customer_id = 1;

— ============================================================================
— §7. 分组集: GROUPING SETS / ROLLUP / CUBE + GROUPING() 标记小计
— ============================================================================
\\echo '=== §7a GROUPING SETS: 同时得 (域)/(国家)/(域,国家)/(总计) 四种粒度 ==='
SELECT system_code, country_code,
GROUPING(system_code) AS g_sys,
GROUPING(country_code) AS g_ctry,
count(*) AS txn_cnt,
round(sum(amount_usd),2) AS gmv_usd
FROM pg_txn
GROUP BY GROUPING SETS ((system_code),(country_code),(system_code,country_code),())
ORDER BY g_sys, g_ctry, system_code, country_code;

\\echo '=== §7b ROLLUP(国家,域): 层级小计 (国家,域)->(国家)->(总计) ==='
SELECT country_code, system_code,
GROUPING(country_code) AS g_c, GROUPING(system_code) AS g_s,
count(*) AS txn_cnt
FROM pg_txn
GROUP BY ROLLUP (country_code, system_code)
ORDER BY g_c, g_s, country_code, system_code;

\\echo '=== §7c CUBE(国家,域): 立方体 (含 域 单独小计) ==='
SELECT country_code, system_code,
GROUPING(country_code) AS g_c, GROUPING(system_code) AS g_s,
count(*) AS txn_cnt
FROM pg_txn
GROUP BY CUBE (country_code, system_code)
ORDER BY g_c, g_s, country_code, system_code;

— ============================================================================
— §8. 万亿级预演 —— 物化视图预聚合 (避免每次全表扫描)
— ============================================================================
DROP MATERIALIZED VIEW IF EXISTS mv_gmv CASCADE;
CREATE MATERIALIZED VIEW mv_gmv AS
SELECT system_code, country_code, create_local_date,
SUM(amount_usd) gmv_usd, COUNT(*) cnt
FROM pg_txn
GROUP BY system_code, country_code, create_local_date;
REFRESH MATERIALIZED VIEW mv_gmv;

\\echo '=== §8 物化视图预聚合: 按本地日×域×国家 的 GMV (前 5) ==='
SELECT system_code, country_code, create_local_date,
round(gmv_usd,2) AS gmv_usd, cnt
FROM mv_gmv ORDER BY gmv_usd DESC LIMIT 5;

— ============================================================================
— V1–V18 自检 (全部包进 DO $$ 块; PG 无会话变量, 用 SELECT INTO)
— ============================================================================
\\echo '=== SELF-CHECK V1..V18 ==='

— V1: 基本聚合 (单域 ERP 计数约 1.25 万, 汇总 > 0, 顺序正确)
DO $$
DECLARE cnt bigint; sm numeric; av numeric; mn numeric; mx numeric;
BEGIN
SELECT count(*), sum(amount_usd), avg(amount_usd), min(amount_usd), max(amount_usd)
INTO cnt, sm, av, mn, mx FROM pg_txn WHERE system_code='ERP';
PERFORM pg_demo_v('V1_basic_aggregate',
CASE WHEN cnt BETWEEN 12000 AND 13000 AND sm>0 AND av>=mn AND mx>=av THEN 1 ELSE 0 END,
'erp_cnt='||cnt||' sum_usd='||round(sm,2)||' avg='||round(av,2)||' min='||mn||' max='||mx);
END $$;

— V2: 多列 GROUP BY 行数 = 8 域 × 10 国 = 80
DO $$
DECLARE n int;
BEGIN
SELECT count(*) INTO n FROM (SELECT 1 FROM pg_txn GROUP BY system_code, country_code) z;
PERFORM pg_demo_v('V2_multicol_groupby',
CASE WHEN n = 80 THEN 1 ELSE 0 END, 'group_rows='||n||' (8域 x 10国)');
END $$;

— V3: HAVING 过滤后全部满足阈值
DO $$
DECLARE n int; mn_gmv numeric;
BEGIN
SELECT count(*), min(gmv) INTO n, mn_gmv FROM (
SELECT system_code, country_code, SUM(amount_usd) gmv
FROM pg_txn GROUP BY 1,2 HAVING SUM(amount_usd) > 100000) z;
PERFORM pg_demo_v('V3_having_filter',
CASE WHEN n>0 AND mn_gmv > 100000 THEN 1 ELSE 0 END,
'pass_groups='||n||' min_gmv='||round(mn_gmv,2));
END $$;

— V4: 时区聚合口径差异 (CN: UTC 日 vs 上海本地日 的每日分布不同)
DO $$
DECLARE diff int;
BEGIN
SELECT count(*) INTO diff FROM (
SELECT ((created_utc AT TIME ZONE 'UTC')::date)::text d, count(*) c FROM pg_txn WHERE country_code='CN' GROUP BY 1
EXCEPT
SELECT create_local_date::text, count(*) FROM pg_txn WHERE country_code='CN' GROUP BY 1) z;
PERFORM pg_demo_v('V4_timezone_aggregate',
CASE WHEN diff > 0 THEN 1 ELSE 0 END,
'CN_utc_vs_localday_diff_days='||diff||' (时区致日归属不同)');
END $$;

— V5: 多币种结算口径 (混币原币总额 ≠ 美元口径; 按币种分组可重算回总量)
DO $$
DECLARE orig_sum numeric; usd_sum numeric; recon numeric;
BEGIN
SELECT sum(amount_orig), sum(amount_usd) INTO orig_sum, usd_sum FROM pg_txn;
SELECT sum(s) INTO recon FROM (
SELECT sum(amount_orig) s FROM pg_txn GROUP BY currency_code) z;
PERFORM pg_demo_v('V5_multicurrency_settle',
CASE WHEN orig_sum <> usd_sum AND recon = orig_sum THEN 1 ELSE 0 END,
'mixed_orig='||round(orig_sum,2)||' usd_settle='||round(usd_sum,2)||' recon='||round(recon,2));
END $$;

— V6: 分位数 (中位数 > 0 且 90 分位 >= 中位数)
DO $$
DECLARE med numeric; p90 numeric;
BEGIN
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY amount_usd),
percentile_cont(0.9) WITHIN GROUP (ORDER BY amount_usd)
INTO med, p90 FROM pg_txn;
PERFORM pg_demo_v('V6_percentile',
CASE WHEN med>0 AND p90>=med THEN 1 ELSE 0 END,
'median_usd='||round(med,2)||' p90_usd='||round(p90,2));
END $$;

— V7: 众数 (最频繁货币非空且在枚举内)
DO $$
DECLARE m text;
BEGIN
SELECT mode() WITHIN GROUP (ORDER BY currency_code) INTO m FROM pg_txn;
PERFORM pg_demo_v('V7_mode_currency',
CASE WHEN m IS NOT NULL THEN 1 ELSE 0 END, 'most_frequent_currency='||m);
END $$;

— V8: 字符串聚合 (客户 #1 有多笔, 列表含逗号)
DO $$
DECLARE s text;
BEGIN
SELECT string_agg(reference_no, ', ' ORDER BY txn_id) INTO s FROM pg_txn WHERE customer_id=1;
PERFORM pg_demo_v('V8_string_agg',
CASE WHEN s IS NOT NULL AND length(s) > 0 AND position(',' in s) > 0 THEN 1 ELSE 0 END,
'cust1_order_count='||(length(s)-length(replace(s,',',''))+1));
END $$;

— V9: GROUPING SETS 行数 = 8 + 10 + 80 + 1 = 99
DO $$
DECLARE n int;
BEGIN
SELECT count(*) INTO n FROM (
SELECT system_code, country_code, GROUPING(system_code), GROUPING(country_code)
FROM pg_txn GROUP BY GROUPING SETS ((system_code),(country_code),(system_code,country_code),())) z;
PERFORM pg_demo_v('V9_grouping_sets',
CASE WHEN n = 99 THEN 1 ELSE 0 END,
'distinct_group_rows='||n||' (8+10+80+1=99)');
END $$;

— V10: ROLLUP(国家,域) 行数 = 80 + 10 + 1 = 91
DO $$
DECLARE n int;
BEGIN
SELECT count(*) INTO n FROM (
SELECT system_code, country_code FROM pg_txn
GROUP BY ROLLUP (country_code, system_code)) z;
PERFORM pg_demo_v('V10_rollup',
CASE WHEN n = 91 THEN 1 ELSE 0 END,
'rollup_rows='||n||' (80+10+1=91)');
END $$;

— V11: CUBE(国家,域) 行数 = 80 + 10 + 8 + 1 = 99 (比 ROLLUP 多 8 行域小计)
DO $$
DECLARE n int;
BEGIN
SELECT count(*) INTO n FROM (
SELECT system_code, country_code FROM pg_txn
GROUP BY CUBE (country_code, system_code)) z;
PERFORM pg_demo_v('V11_cube',
CASE WHEN n = 99 THEN 1 ELSE 0 END,
'cube_rows='||n||' (80+10+8+1=99, 比ROLLUP多8)');
END $$;

— V12: GROUPING() 区分小计 (system_code 被小计掉的行 = 10国小计 + 1总计 = 11)
DO $$
DECLARE n_sub int;
BEGIN
SELECT count(*) INTO n_sub FROM (
SELECT system_code, country_code, GROUPING(system_code) gs, GROUPING(country_code) gc
FROM pg_txn GROUP BY GROUPING SETS ((system_code),(country_code),(system_code,country_code),())
) z WHERE gs=1;
PERFORM pg_demo_v('V12_grouping_func',
CASE WHEN n_sub = 11 THEN 1 ELSE 0 END,
'system_code_subtotal_rows='||n_sub||' (10国小计+1总计=11; 用GROUPING()而非IS NULL区分)');
END $$;

— V13: 全表聚合 Seq Scan 代价 (未索引列 amount_orig 必全扫, 万亿级须分区/物化视图)
DO $$
DECLARE r record;
BEGIN
SELECT * INTO r FROM pg_plan_stats('SELECT SUM(amount_orig) FROM pg_txn');
PERFORM pg_demo_v('V13_fullscan_aggregate',
CASE WHEN r.node_type='Seq Scan' AND r.shared_blks > 500 THEN 1 ELSE 0 END,
'node='||r.node_type||' shared_blks='||r.shared_blks||' (万亿级须分区/物化视图)');
END $$;

— V14: 覆盖索引聚合 (带过滤) 走 Index Only Scan, 免回表
DO $$
DECLARE r record;
BEGIN
SELECT * INTO r FROM pg_plan_stats(
'SELECT system_code, SUM(amount_usd) FROM pg_txn WHERE system_code IN (''ERP'',''CRM'',''SCM'') GROUP BY system_code');
PERFORM pg_demo_v('V14_covering_aggregate',
CASE WHEN r.node_type='Index Only Scan' THEN 1 ELSE 0 END,
'node='||r.node_type||' shared_blks='||r.shared_blks);
END $$;

— V15: 最终聚合输出行数 = 8 域 (分组正确, 万亿级可并行 Finalize Aggregate)
DO $$
DECLARE n int;
BEGIN
SELECT count(*) INTO n FROM (
SELECT system_code, SUM(amount_usd) FROM pg_txn GROUP BY system_code) z;
PERFORM pg_demo_v('V15_group_output_rows',
CASE WHEN n = 8 THEN 1 ELSE 0 END, 'domain_groups='||n||' (Finalize Aggregate 输出8行)');
END $$;

— V16: 万亿预演 — 物化视图预聚合块数 << 基表全聚合块数
DO $$
DECLARE r1 record; r2 record; mvc int;
BEGIN
SELECT count(*) INTO mvc FROM mv_gmv;
SELECT * INTO r1 FROM pg_plan_stats('SELECT SUM(gmv_usd) FROM mv_gmv');
SELECT * INTO r2 FROM pg_plan_stats('SELECT SUM(amount_orig) FROM pg_txn');
PERFORM pg_demo_v('V16_materialized_summary',
CASE WHEN r1.shared_blks < r2.shared_blks THEN 1 ELSE 0 END,
'mv_rows='||mvc||' mv_blks='||r1.shared_blks||' base_blks='||r2.shared_blks);
END $$;

— V17: 聚合确定性 (同查询两次结果一致 -> 整体幂等)
DO $$
DECLARE a numeric; b numeric;
BEGIN
SELECT SUM(amount_usd) INTO a FROM pg_txn;
SELECT SUM(amount_usd) INTO b FROM pg_txn;
PERFORM pg_demo_v('V17_deterministic',
CASE WHEN a IS NOT NULL AND a = b THEN 1 ELSE 0 END, 'sum_usd='||round(a,2));
END $$;

— V18: 聚合忽略 NULL (COUNT(*)=4, COUNT(v)=3, AVG 不含 NULL)
DO $$
DECLARE c1 int; c2 int; av numeric; su numeric;
BEGIN
WITH t(v) AS (VALUES (1),(2),(NULL),(3))
SELECT COUNT(*), COUNT(v), AVG(v), SUM(v) INTO c1, c2, av, su FROM t;
PERFORM pg_demo_v('V18_null_handling',
CASE WHEN c1=4 AND c2=3 AND av=2 AND su=6 THEN 1 ELSE 0 END,
'count_all='||c1||' count_nonnull='||c2||' avg_ignores_null='||av||' sum='||su);
END $$;

— —————————————————————————-
— 自检汇总
— —————————————————————————-
SELECT name AS check_name, pass AS pass_cnt, detail
FROM pg_demo_check ORDER BY name;

SELECT CASE WHEN min(pass)=1 THEN '全部通过 (ALL PASS)' ELSE '未全部通过 (SOME FAILED)' END AS result
FROM pg_demo_check;

SELECT count(*) AS total, sum(pass) AS passed FROM pg_demo_check;