— ============================================================================
\n— PostgreSQL 18 \u2014 SQL \u805a\u5408\u51fd\u6570\u4e0e\u5206\u7ec4\u96c6 (Aggregate Functions & Grouping Sets)
\n— \u573a\u666f: \u4e07\u4ebf\u6570\u636e\u9ad8\u5e76\u53d1 \/ \u56fd\u9645\u65f6\u533a\u65f6\u95f4\u5dee \/ \u56fd\u9645\u591a\u79cd\u8d27\u5e01\u7ed3\u7b97
\n— \u672c\u6587\u4ef6\u3010\u81ea\u5305\u542b\u00b7\u5b8c\u5168\u5e42\u7b49\u3011: \u91cd\u590d\u8fd0\u884c\u7ed3\u679c\u9010\u5b57\u8282\u4e00\u81f4 (setseed \u4fdd\u8bc1\u9020\u6570\u4e00\u81f4)
\n— \u8fde\u63a5\u65b9\u5f0f: PGPASSWORD=<your_password> psql -U postgres -h localhost -p 5433 -d jewelry_pg -f \u672c\u6587\u4ef6
\n— ============================================================================<\/p>\n
\\\\timing off
\nSET client_min_messages = warning; — \u6291\u5236 DROP\/CREATE\/VACUUM \u7684 NOTICE, \u4fdd\u8bc1\u91cd\u8dd1\u5e42\u7b49
\nSET max_parallel_workers_per_gather = 0; — \u5173\u5e76\u884c, \u4f7f\u5757\u7edf\u8ba1\u7a33\u5b9a(\u4e07\u4ebf\u7ea7\u53ef\u636e\u9700\u5f00\u542f)
\nSET random_page_cost = 1.1;
\nSET seq_page_cost = 1.0;
\nSET effective_cache_size = '4GB';<\/p>\n
— —————————————————————————-
\n— 0. \u5ea6\u91cf\u6846\u67b6 (DROP + CREATE, \u5e42\u7b49)
\n— pg_plan_stats: \u6293\u771f\u5b9e\u6267\u884c\u8ba1\u5212 (Actual Rows \/ \u5171\u4eab\u5757 \/ \u626b\u63cf\u8282\u70b9\u7c7b\u578b)
\n— pg_find_scan : \u4e0b\u94bb\u5230\u771f\u6b63\u7684\u626b\u63cf\u8282\u70b9 (\u9876\u5c42\u5e38\u662f Aggregate\/Finalize Aggregate)
\n— pg_demo_v : \u81ea\u68c0\u767b\u8bb0
\n— pg_demo_err_probe : \u6355\u83b7\u771f\u5b9e\u62a5\u9519
\n— —————————————————————————-
\nDROP FUNCTION IF EXISTS pg_plan_stats(text) CASCADE;
\nCREATE OR REPLACE FUNCTION pg_plan_stats(
\n p_sql text,
\n OUT actual_rows bigint,
\n OUT shared_blks bigint,
\n OUT node_type text)
\nRETURNS record LANGUAGE plpgsql AS $$
\nDECLARE j json;
\nBEGIN
\n EXECUTE 'EXPLAIN (ANALYZE, TIMING OFF, FORMAT JSON) ' || p_sql INTO j;
\n actual_rows := (j->0->'Plan'->>'Actual Rows')::numeric::bigint;
\n shared_blks := COALESCE((j->0->'Plan'->>'Shared Hit Blocks')::numeric::bigint,0)
\n + COALESCE((j->0->'Plan'->>'Shared Read Blocks')::numeric::bigint,0);
\n node_type := pg_find_scan(j->0->'Plan');
\nEND $$;<\/p>\n
DROP FUNCTION IF EXISTS pg_find_scan(json) CASCADE;
\nCREATE OR REPLACE FUNCTION pg_find_scan(cur json) RETURNS text LANGUAGE plpgsql AS $$
\nDECLARE nt text;
\nBEGIN
\n nt := cur->>'Node Type';
\n IF nt LIKE '%Scan' THEN RETURN nt; END IF;
\n IF cur->'Plans' IS NOT NULL AND json_array_length(cur->'Plans') > 0 THEN
\n RETURN pg_find_scan(cur->'Plans'->0);
\n END IF;
\n RETURN nt;
\nEND $$;<\/p>\n
DROP TABLE IF EXISTS pg_demo_check;
\nCREATE TABLE pg_demo_check (
\n name text PRIMARY KEY,
\n pass int,
\n detail text
\n);
\nDROP FUNCTION IF EXISTS pg_demo_v(text,int,text) CASCADE;
\nCREATE OR REPLACE FUNCTION pg_demo_v(p_name text, p_pass int, p_detail text)
\nRETURNS void LANGUAGE plpgsql AS $$
\nBEGIN
\n INSERT INTO pg_demo_check(name,pass,detail) VALUES (p_name,p_pass,p_detail)
\n ON CONFLICT (name) DO UPDATE SET pass=EXCLUDED.pass, detail=EXCLUDED.detail;
\nEND $$;<\/p>\n
DROP TABLE IF EXISTS pg_demo_probe;
\nCREATE TABLE pg_demo_probe (
\n label text PRIMARY KEY,
\n sqlstate text,
\n msg text
\n);
\nDROP FUNCTION IF EXISTS pg_demo_err_probe(text,text) CASCADE;
\nCREATE OR REPLACE FUNCTION pg_demo_err_probe(p_label text, p_sql text)
\nRETURNS void LANGUAGE plpgsql AS $$
\nDECLARE e text; s text;
\nBEGIN
\n BEGIN
\n EXECUTE p_sql;
\n INSERT INTO pg_demo_probe(label,sqlstate,msg) VALUES (p_label,'OK','\u6267\u884c\u6210\u529f')
\n ON CONFLICT (label) DO UPDATE SET sqlstate='OK', msg='\u6267\u884c\u6210\u529f';
\n EXCEPTION WHEN OTHERS THEN
\n GET STACKED DIAGNOSTICS s=RETURNED_SQLSTATE, e=MESSAGE_TEXT;
\n INSERT INTO pg_demo_probe(label,sqlstate,msg) VALUES (p_label,s,e)
\n ON CONFLICT (label) DO UPDATE SET sqlstate=EXCLUDED.sqlstate, msg=EXCLUDED.msg;
\n END;
\nEND $$;<\/p>\n
— —————————————————————————-
\n— 1. \u7ef4\u5ea6\u8868 (\u516b\u57df\u4e2d\u53f0\u914d\u5957\u7ef4\u5ea6) \u2014\u2014 DROP + CREATE \u4fdd\u8bc1\u5e42\u7b49
\n— —————————————————————————-
\nDROP TABLE IF EXISTS pg_txn CASCADE;
\nDROP TABLE IF EXISTS pg_customer CASCADE;
\nDROP TABLE IF EXISTS pg_product CASCADE;
\nDROP TABLE IF EXISTS pg_supplier CASCADE;
\nDROP TABLE IF EXISTS pg_warehouse CASCADE;
\nDROP TABLE IF EXISTS pg_employee CASCADE;<\/p>\n
— \u7ef4\u5ea6: \u5ba2\u6237 (\u542b\u65f6\u533a \/ VIP \u7b49\u7ea7)
\nCREATE TABLE pg_customer (
\n customer_id int PRIMARY KEY,
\n cust_name text,
\n country_code text,
\n tz text,
\n vip_level int,
\n email text
\n);
\nINSERT INTO pg_customer
\nSELECT n,
\n (ARRAY['Acme','Globex','Initech','Umbrella','Soylent','Hooli','Stark','Wayne','Wonka','Cyberdyne'])[1+(n%10)] || ' ' || chr(65+(n%26)) || n,
\n (ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+(n%10)],
\n (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)],
\n 1+(n%5),
\n 'cust'||n||'@example.com'
\nFROM generate_series(1,10000) n;<\/p>\n
— \u7ef4\u5ea6: \u5546\u54c1 KMS (\u591a\u8bed\u8a00\u540d\u79f0\/\u63cf\u8ff0 + jsonb \u89c4\u683c + \u751f\u6210\u5f0f tsvector)
\nCREATE TABLE pg_product (
\n product_id int PRIMARY KEY,
\n sku text,
\n name_en text,
\n name_cn text,
\n desc_en text,
\n desc_cn text,
\n specs jsonb,
\n search_vec tsvector GENERATED ALWAYS AS (
\n to_tsvector('english', coalesce(desc_en,'')||' '||coalesce(name_en,''))) STORED
\n);
\nINSERT INTO pg_product (product_id, sku, name_en, name_cn, desc_en, desc_cn, specs)
\nSELECT n,
\n 'SKU-'||lpad(n::text,6,'0'),
\n (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 '\u73e0\u5b9d'||n,
\n 'A fine '||(ARRAY['diamond','gold','pearl','ruby','sapphire','emerald','platinum','tanzanite','jade','onyx'])[1+(n%10)]||' piece crafted for global markets.',
\n '\u7cbe\u81f4'||(ARRAY['\u94bb\u77f3','\u9ec4\u91d1','\u73cd\u73e0','\u7ea2\u5b9d\u77f3','\u84dd\u5b9d\u77f3','\u7956\u6bcd\u7eff','\u94c2\u91d1','\u5766\u6851\u77f3','\u7fe1\u7fe0','\u7f1f\u739b\u7459'])[1+(n%10)]||'\u9970\u54c1\u3002',
\n jsonb_build_object('carat', round((0.5+(n%50)\/10.0)::numeric,2),
\n 'color', (ARRAY['D','E','F','G','H'])[1+(n%5)],
\n 'clarity',(ARRAY['VVS1','VVS2','VS1','VS2','SI1'])[1+(n%5)])
\nFROM generate_series(1,2000) n;<\/p>\n
— \u7ef4\u5ea6: \u4f9b\u5e94\u5546 \/ \u4ed3\u5e93 \/ \u5458\u5de5
\nCREATE TABLE pg_supplier (supplier_id int PRIMARY KEY, supplier_name text, country_code text);
\nINSERT INTO pg_supplier
\nSELECT n, 'Supplier-'||n, (ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+(n%10)]
\nFROM generate_series(1,200) n;<\/p>\n
CREATE TABLE pg_warehouse (warehouse_id int PRIMARY KEY, warehouse_name text, country_code text);
\nINSERT INTO pg_warehouse
\nSELECT n, 'WH-'||n, (ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+(n%10)]
\nFROM generate_series(1,50) n;<\/p>\n
CREATE TABLE pg_employee (emp_id int PRIMARY KEY, emp_name text, dept text, country_code text);
\nINSERT INTO pg_employee
\nSELECT n, 'Emp-'||n, (ARRAY['OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'])[1+(n%8)],
\n (ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+(n%10)]
\nFROM generate_series(1,500) n;<\/p>\n
— —————————————————————————-
\n— 2. \u4e8b\u5b9e\u8868 pg_txn (10 \u4e07\u884c\u4ea4\u6613, \u516b\u57df + \u65f6\u533a\u751f\u6210\u5217 + \u672c\u5730\u65e5\u751f\u6210\u5217 + \u591a\u5e01\u79cd)
\n— —————————————————————————-
\nCREATE TABLE pg_txn (
\n txn_id bigint PRIMARY KEY,
\n system_code text NOT NULL,
\n customer_id int,
\n product_id int,
\n supplier_id int,
\n warehouse_id int,
\n emp_id int,
\n country_code text,
\n currency_code text,
\n amount_orig numeric(14,2),
\n amount_usd numeric(14,4),
\n created_utc timestamptz,
\n tz text GENERATED ALWAYS AS (
\n CASE country_code
\n WHEN 'CN' THEN 'Asia\/Shanghai' WHEN 'HK' THEN 'Asia\/Hong_Kong'
\n WHEN 'US' THEN 'America\/New_York' WHEN 'JP' THEN 'Asia\/Tokyo'
\n WHEN 'DE' THEN 'Europe\/Berlin' WHEN 'FR' THEN 'Europe\/Paris'
\n WHEN 'GB' THEN 'Europe\/London' WHEN 'SG' THEN 'Asia\/Singapore'
\n WHEN 'AU' THEN 'Australia\/Sydney' WHEN 'IN' THEN 'Asia\/Kolkata'
\n ELSE 'UTC' END) STORED,
\n create_local_date date GENERATED ALWAYS AS (
\n (created_utc AT TIME ZONE (
\n CASE country_code
\n WHEN 'CN' THEN 'Asia\/Shanghai' WHEN 'HK' THEN 'Asia\/Hong_Kong'
\n WHEN 'US' THEN 'America\/New_York' WHEN 'JP' THEN 'Asia\/Tokyo'
\n WHEN 'DE' THEN 'Europe\/Berlin' WHEN 'FR' THEN 'Europe\/Paris'
\n WHEN 'GB' THEN 'Europe\/London' WHEN 'SG' THEN 'Asia\/Singapore'
\n WHEN 'AU' THEN 'Australia\/Sydney' WHEN 'IN' THEN 'Asia\/Kolkata'
\n ELSE 'UTC' END))::date) STORED,
\n reference_no text UNIQUE,
\n note text,
\n rand_val double precision
\n);<\/p>\n
— \u9020\u6570: setseed \u4fdd\u8bc1\u6bcf\u6b21\u8fd0\u884c\u6570\u636e\u5b8c\u5168\u4e00\u81f4 -> \u5e42\u7b49
\nDO $$
\nBEGIN
\n PERFORM setseed(0.20261018);
\n INSERT INTO pg_txn (txn_id, system_code, customer_id, product_id, supplier_id, warehouse_id,
\n emp_id, country_code, currency_code, amount_orig, amount_usd,
\n created_utc, reference_no, note, rand_val)
\n SELECT n,
\n (ARRAY['OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'])[1+(n%8)],
\n 1+((n+n\/8)%10000), — \u5ba2\u6237: \u89e3\u8026 note \u7684 n%12, \u907f\u514d\u8de8\u57df\u6a21\u8026\u5408
\n 1+(n%2000),
\n 1+(n%200),
\n 1+(n%50),
\n 1+(n%500),
\n (ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+((n\/8)%10)],
\n (ARRAY['CNY','HKD','USD','JPY','EUR','EUR','GBP','SGD','AUD','INR'])[1+((n\/8)%10)],
\n round((100 + random()*90000)::numeric,2),
\n round(((100 + random()*90000) \/
\n (CASE (ARRAY['CNY','HKD','USD','JPY','EUR','EUR','GBP','SGD','AUD','INR'])[1+(n%10)]
\n WHEN 'CNY' THEN 7.10 WHEN 'HKD' THEN 7.80 WHEN 'USD' THEN 1.00
\n WHEN 'JPY' THEN 150.0 WHEN 'EUR' THEN 0.92 WHEN 'GBP' THEN 0.79
\n WHEN 'SGD' THEN 1.35 WHEN 'AUD' THEN 1.52 WHEN 'INR' THEN 83.0 END))::numeric,4),
\n '2025-01-01 00:00:00+00'::timestamptz + ((random()*300*24*3600)::integer * interval '1 second'),
\n 'REF-'||lpad(n::text,8,'0'),
\n (ARRAY['Diamond','Gold','Pearl','Ruby','Sapphire','Emerald','Platinum','Tanzanite','Jade','Onyx','Zirconia','Topaz'])[1+(n%12)] || ' order for VIP client',
\n random()
\n FROM generate_series(1,100000) n;
\nEND $$;<\/p>\n
— \u7d22\u5f15 (\u8986\u76d6\u7d22\u5f15\u7528\u4e8e\u514d\u56de\u8868\u805a\u5408)
\nCREATE INDEX idx_txn_cover ON pg_txn (system_code, amount_usd);
\nCREATE INDEX idx_txn_created ON pg_txn (created_utc);
\nCREATE INDEX idx_txn_localday ON pg_txn (create_local_date);
\nCREATE INDEX idx_txn_cust ON pg_txn (customer_id);
\nCREATE INDEX idx_txn_sys_ctry ON pg_txn (system_code, country_code);
\nCREATE INDEX brin_txn_created ON pg_txn USING brin (created_utc);<\/p>\n
VACUUM ANALYZE pg_txn; — \u4f7f\u8986\u76d6\u7d22\u5f15 Index Only Scan \u771f\u6b63\u514d\u56de\u8868<\/p>\n
— ============================================================================
\n— \u00a71. \u57fa\u672c\u805a\u5408\u51fd\u6570 (COUNT \/ SUM \/ AVG \/ MIN \/ MAX) \u2014\u2014 \u5355\u57df GMV \u770b\u677f
\n— ============================================================================
\n\\\\echo '=== \u00a71 \u57fa\u672c\u805a\u5408: ERP \u57df GMV \u770b\u677f ==='
\nSELECT system_code,
\n count(*) AS txn_cnt,
\n round(sum(amount_usd),2) AS gmv_usd,
\n round(avg(amount_usd),2) AS avg_usd,
\n round(min(amount_usd),2) AS min_usd,
\n round(max(amount_usd),2) AS max_usd
\nFROM pg_txn
\nWHERE system_code = 'ERP'
\nGROUP BY system_code;<\/p>\n
— ============================================================================
\n— \u00a72. \u591a\u5217 GROUP BY \u2014\u2014 \u516b\u57df \u00d7 \u5341\u56fd \u4ea4\u53c9\u6c47\u603b
\n— ============================================================================
\n\\\\echo '=== \u00a72 \u591a\u5217 GROUP BY: \u57df \u00d7 \u56fd\u5bb6 GMV (\u53d6\u524d 12 \u884c) ==='
\nSELECT system_code, country_code,
\n count(*) AS txn_cnt,
\n round(sum(amount_usd),2) AS gmv_usd
\nFROM pg_txn
\nGROUP BY system_code, country_code
\nORDER BY gmv_usd DESC
\nLIMIT 12;<\/p>\n
— ============================================================================
\n— \u00a73. HAVING \u2014\u2014 \u8fc7\u6ee4\u805a\u5408\u7ed3\u679c (GMV \u8d85\u8fc7\u95e8\u69db\u7684 \u57df\u00d7\u56fd\u5bb6 \u7ec4\u5408)
\n— ============================================================================
\n\\\\echo '=== \u00a73 HAVING: \u4ec5\u4fdd\u7559 GMV > 1,000,000 \u7684 \u57df\u00d7\u56fd\u5bb6 \u7ec4\u5408 ==='
\nSELECT system_code, country_code,
\n count(*) AS txn_cnt,
\n round(sum(amount_usd),2) AS gmv_usd
\nFROM pg_txn
\nGROUP BY system_code, country_code
\nHAVING sum(amount_usd) > 1000000
\nORDER BY gmv_usd DESC
\nLIMIT 12;<\/p>\n
— ============================================================================
\n— \u00a74. \u56fd\u9645\u65f6\u533a\u805a\u5408 \u2014\u2014 \u540c\u4e00\u7b14 UTC \u4ea4\u6613\u6309\u672c\u5730\u65e5\u5f52\u5c5e\u53ef\u80fd\u4e0d\u540c\u5929
\n— ============================================================================
\n\\\\echo '=== \u00a74 \u65f6\u533a\u805a\u5408: \u4e2d\u56fd(CN) \u6309 UTC \u65e5 vs \u4e0a\u6d77\u672c\u5730\u65e5 \u7684\u6bcf\u65e5\u7b14\u6570\u5206\u5e03(\u5404\u53d6\u524d 5) ==='
\n\\\\echo '— 4a UTC \u65e5 —'
\nSELECT (created_utc AT TIME ZONE 'UTC')::date AS utc_day, count(*) AS cnt
\nFROM pg_txn WHERE country_code='CN' GROUP BY 1 ORDER BY 1 LIMIT 5;
\n\\\\echo '— 4b \u4e0a\u6d77\u672c\u5730\u65e5 (\u751f\u6210\u5217 create_local_date) —'
\nSELECT create_local_date AS shanghai_day, count(*) AS cnt
\nFROM pg_txn WHERE country_code='CN' GROUP BY 1 ORDER BY 1 LIMIT 5;<\/p>\n
— ============================================================================
\n— \u00a75. \u56fd\u9645\u591a\u5e01\u79cd\u7ed3\u7b97\u53e3\u5f84 \u2014\u2014 \u539f\u5e01\u6df7\u52a0\u65e0\u610f\u4e49, \u987b\u7edf\u4e00 amount_usd
\n— ============================================================================
\n\\\\echo '=== \u00a75 \u591a\u5e01\u79cd: \u6309\u539f\u5e01\u5206\u7ec4\u6c47\u603b vs \u7edf\u4e00\u7f8e\u5143\u53e3\u5f84 ==='
\nSELECT currency_code,
\n round(sum(amount_orig),2) AS gmv_orig,
\n round(sum(amount_usd),2) AS gmv_usd
\nFROM pg_txn
\nGROUP BY currency_code
\nORDER BY gmv_usd DESC;<\/p>\n
— ============================================================================
\n— \u00a76. \u7edf\u8ba1\u5206\u5e03\u805a\u5408 \u2014\u2014 \u5206\u4f4d\u6570 \/ \u4f17\u6570 \/ \u5b57\u7b26\u4e32\u805a\u5408
\n— ============================================================================
\n\\\\echo '=== \u00a76a \u5206\u4f4d\u6570: \u5355\u7b14\u7f8e\u5143\u91d1\u989d \u4e2d\u4f4d\u6570 & 90 \u5206\u4f4d ==='
\nSELECT round(percentile_cont(0.5) WITHIN GROUP (ORDER BY amount_usd)::numeric,2) AS median_usd,
\n round(percentile_cont(0.9) WITHIN GROUP (ORDER BY amount_usd)::numeric,2) AS p90_usd,
\n round(percentile_disc(0.9) WITHIN GROUP (ORDER BY amount_usd)::numeric,2) AS p90_disc_usd
\nFROM pg_txn;<\/p>\n
\\\\echo '=== \u00a76b \u4f17\u6570: \u6700\u9891\u7e41\u7ed3\u7b97\u8d27\u5e01 ==='
\nSELECT mode() WITHIN GROUP (ORDER BY currency_code) AS top_currency,
\n count(*) FILTER (WHERE true) AS total
\nFROM pg_txn;<\/p>\n
\\\\echo '=== \u00a76c \u5b57\u7b26\u4e32\u805a\u5408: \u5ba2\u6237 #1 \u7684\u8ba2\u5355\u53f7\u6e05\u5355 (\u524d 60 \u5b57\u7b26) ==='
\nSELECT left(string_agg(reference_no, ', ' ORDER BY txn_id), 60) AS order_list
\nFROM pg_txn WHERE customer_id = 1;<\/p>\n
— ============================================================================
\n— \u00a77. \u5206\u7ec4\u96c6: GROUPING SETS \/ ROLLUP \/ CUBE + GROUPING() \u6807\u8bb0\u5c0f\u8ba1
\n— ============================================================================
\n\\\\echo '=== \u00a77a GROUPING SETS: \u540c\u65f6\u5f97 (\u57df)\/(\u56fd\u5bb6)\/(\u57df,\u56fd\u5bb6)\/(\u603b\u8ba1) \u56db\u79cd\u7c92\u5ea6 ==='
\nSELECT system_code, country_code,
\n GROUPING(system_code) AS g_sys,
\n GROUPING(country_code) AS g_ctry,
\n count(*) AS txn_cnt,
\n round(sum(amount_usd),2) AS gmv_usd
\nFROM pg_txn
\nGROUP BY GROUPING SETS ((system_code),(country_code),(system_code,country_code),())
\nORDER BY g_sys, g_ctry, system_code, country_code;<\/p>\n
\\\\echo '=== \u00a77b ROLLUP(\u56fd\u5bb6,\u57df): \u5c42\u7ea7\u5c0f\u8ba1 (\u56fd\u5bb6,\u57df)->(\u56fd\u5bb6)->(\u603b\u8ba1) ==='
\nSELECT country_code, system_code,
\n GROUPING(country_code) AS g_c, GROUPING(system_code) AS g_s,
\n count(*) AS txn_cnt
\nFROM pg_txn
\nGROUP BY ROLLUP (country_code, system_code)
\nORDER BY g_c, g_s, country_code, system_code;<\/p>\n
\\\\echo '=== \u00a77c CUBE(\u56fd\u5bb6,\u57df): \u7acb\u65b9\u4f53 (\u542b \u57df \u5355\u72ec\u5c0f\u8ba1) ==='
\nSELECT country_code, system_code,
\n GROUPING(country_code) AS g_c, GROUPING(system_code) AS g_s,
\n count(*) AS txn_cnt
\nFROM pg_txn
\nGROUP BY CUBE (country_code, system_code)
\nORDER BY g_c, g_s, country_code, system_code;<\/p>\n
— ============================================================================
\n— \u00a78. \u4e07\u4ebf\u7ea7\u9884\u6f14 \u2014\u2014 \u7269\u5316\u89c6\u56fe\u9884\u805a\u5408 (\u907f\u514d\u6bcf\u6b21\u5168\u8868\u626b\u63cf)
\n— ============================================================================
\nDROP MATERIALIZED VIEW IF EXISTS mv_gmv CASCADE;
\nCREATE MATERIALIZED VIEW mv_gmv AS
\n SELECT system_code, country_code, create_local_date,
\n SUM(amount_usd) gmv_usd, COUNT(*) cnt
\n FROM pg_txn
\n GROUP BY system_code, country_code, create_local_date;
\nREFRESH MATERIALIZED VIEW mv_gmv;<\/p>\n
\\\\echo '=== \u00a78 \u7269\u5316\u89c6\u56fe\u9884\u805a\u5408: \u6309\u672c\u5730\u65e5\u00d7\u57df\u00d7\u56fd\u5bb6 \u7684 GMV (\u524d 5) ==='
\nSELECT system_code, country_code, create_local_date,
\n round(gmv_usd,2) AS gmv_usd, cnt
\nFROM mv_gmv ORDER BY gmv_usd DESC LIMIT 5;<\/p>\n
— ============================================================================
\n— V1\u2013V18 \u81ea\u68c0 (\u5168\u90e8\u5305\u8fdb DO $$ \u5757; PG \u65e0\u4f1a\u8bdd\u53d8\u91cf, \u7528 SELECT INTO)
\n— ============================================================================
\n\\\\echo '=== SELF-CHECK V1..V18 ==='<\/p>\n
— V1: \u57fa\u672c\u805a\u5408 (\u5355\u57df ERP \u8ba1\u6570\u7ea6 1.25 \u4e07, \u6c47\u603b > 0, \u987a\u5e8f\u6b63\u786e)
\nDO $$
\nDECLARE cnt bigint; sm numeric; av numeric; mn numeric; mx numeric;
\nBEGIN
\n SELECT count(*), sum(amount_usd), avg(amount_usd), min(amount_usd), max(amount_usd)
\n INTO cnt, sm, av, mn, mx FROM pg_txn WHERE system_code='ERP';
\n PERFORM pg_demo_v('V1_basic_aggregate',
\n CASE WHEN cnt BETWEEN 12000 AND 13000 AND sm>0 AND av>=mn AND mx>=av THEN 1 ELSE 0 END,
\n 'erp_cnt='||cnt||' sum_usd='||round(sm,2)||' avg='||round(av,2)||' min='||mn||' max='||mx);
\nEND $$;<\/p>\n
— V2: \u591a\u5217 GROUP BY \u884c\u6570 = 8 \u57df \u00d7 10 \u56fd = 80
\nDO $$
\nDECLARE n int;
\nBEGIN
\n SELECT count(*) INTO n FROM (SELECT 1 FROM pg_txn GROUP BY system_code, country_code) z;
\n PERFORM pg_demo_v('V2_multicol_groupby',
\n CASE WHEN n = 80 THEN 1 ELSE 0 END, 'group_rows='||n||' (8\u57df x 10\u56fd)');
\nEND $$;<\/p>\n
— V3: HAVING \u8fc7\u6ee4\u540e\u5168\u90e8\u6ee1\u8db3\u9608\u503c
\nDO $$
\nDECLARE n int; mn_gmv numeric;
\nBEGIN
\n SELECT count(*), min(gmv) INTO n, mn_gmv FROM (
\n SELECT system_code, country_code, SUM(amount_usd) gmv
\n FROM pg_txn GROUP BY 1,2 HAVING SUM(amount_usd) > 100000) z;
\n PERFORM pg_demo_v('V3_having_filter',
\n CASE WHEN n>0 AND mn_gmv > 100000 THEN 1 ELSE 0 END,
\n 'pass_groups='||n||' min_gmv='||round(mn_gmv,2));
\nEND $$;<\/p>\n
— V4: \u65f6\u533a\u805a\u5408\u53e3\u5f84\u5dee\u5f02 (CN: UTC \u65e5 vs \u4e0a\u6d77\u672c\u5730\u65e5 \u7684\u6bcf\u65e5\u5206\u5e03\u4e0d\u540c)
\nDO $$
\nDECLARE diff int;
\nBEGIN
\n SELECT count(*) INTO diff FROM (
\n SELECT ((created_utc AT TIME ZONE 'UTC')::date)::text d, count(*) c FROM pg_txn WHERE country_code='CN' GROUP BY 1
\n EXCEPT
\n SELECT create_local_date::text, count(*) FROM pg_txn WHERE country_code='CN' GROUP BY 1) z;
\n PERFORM pg_demo_v('V4_timezone_aggregate',
\n CASE WHEN diff > 0 THEN 1 ELSE 0 END,
\n 'CN_utc_vs_localday_diff_days='||diff||' (\u65f6\u533a\u81f4\u65e5\u5f52\u5c5e\u4e0d\u540c)');
\nEND $$;<\/p>\n
— V5: \u591a\u5e01\u79cd\u7ed3\u7b97\u53e3\u5f84 (\u6df7\u5e01\u539f\u5e01\u603b\u989d \u2260 \u7f8e\u5143\u53e3\u5f84; \u6309\u5e01\u79cd\u5206\u7ec4\u53ef\u91cd\u7b97\u56de\u603b\u91cf)
\nDO $$
\nDECLARE orig_sum numeric; usd_sum numeric; recon numeric;
\nBEGIN
\n SELECT sum(amount_orig), sum(amount_usd) INTO orig_sum, usd_sum FROM pg_txn;
\n SELECT sum(s) INTO recon FROM (
\n SELECT sum(amount_orig) s FROM pg_txn GROUP BY currency_code) z;
\n PERFORM pg_demo_v('V5_multicurrency_settle',
\n CASE WHEN orig_sum <> usd_sum AND recon = orig_sum THEN 1 ELSE 0 END,
\n 'mixed_orig='||round(orig_sum,2)||' usd_settle='||round(usd_sum,2)||' recon='||round(recon,2));
\nEND $$;<\/p>\n
— V6: \u5206\u4f4d\u6570 (\u4e2d\u4f4d\u6570 > 0 \u4e14 90 \u5206\u4f4d >= \u4e2d\u4f4d\u6570)
\nDO $$
\nDECLARE med numeric; p90 numeric;
\nBEGIN
\n SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY amount_usd),
\n percentile_cont(0.9) WITHIN GROUP (ORDER BY amount_usd)
\n INTO med, p90 FROM pg_txn;
\n PERFORM pg_demo_v('V6_percentile',
\n CASE WHEN med>0 AND p90>=med THEN 1 ELSE 0 END,
\n 'median_usd='||round(med,2)||' p90_usd='||round(p90,2));
\nEND $$;<\/p>\n
— V7: \u4f17\u6570 (\u6700\u9891\u7e41\u8d27\u5e01\u975e\u7a7a\u4e14\u5728\u679a\u4e3e\u5185)
\nDO $$
\nDECLARE m text;
\nBEGIN
\n SELECT mode() WITHIN GROUP (ORDER BY currency_code) INTO m FROM pg_txn;
\n PERFORM pg_demo_v('V7_mode_currency',
\n CASE WHEN m IS NOT NULL THEN 1 ELSE 0 END, 'most_frequent_currency='||m);
\nEND $$;<\/p>\n
— V8: \u5b57\u7b26\u4e32\u805a\u5408 (\u5ba2\u6237 #1 \u6709\u591a\u7b14, \u5217\u8868\u542b\u9017\u53f7)
\nDO $$
\nDECLARE s text;
\nBEGIN
\n SELECT string_agg(reference_no, ', ' ORDER BY txn_id) INTO s FROM pg_txn WHERE customer_id=1;
\n PERFORM pg_demo_v('V8_string_agg',
\n CASE WHEN s IS NOT NULL AND length(s) > 0 AND position(',' in s) > 0 THEN 1 ELSE 0 END,
\n 'cust1_order_count='||(length(s)-length(replace(s,',',''))+1));
\nEND $$;<\/p>\n
— V9: GROUPING SETS \u884c\u6570 = 8 + 10 + 80 + 1 = 99
\nDO $$
\nDECLARE n int;
\nBEGIN
\n SELECT count(*) INTO n FROM (
\n SELECT system_code, country_code, GROUPING(system_code), GROUPING(country_code)
\n FROM pg_txn GROUP BY GROUPING SETS ((system_code),(country_code),(system_code,country_code),())) z;
\n PERFORM pg_demo_v('V9_grouping_sets',
\n CASE WHEN n = 99 THEN 1 ELSE 0 END,
\n 'distinct_group_rows='||n||' (8+10+80+1=99)');
\nEND $$;<\/p>\n
— V10: ROLLUP(\u56fd\u5bb6,\u57df) \u884c\u6570 = 80 + 10 + 1 = 91
\nDO $$
\nDECLARE n int;
\nBEGIN
\n SELECT count(*) INTO n FROM (
\n SELECT system_code, country_code FROM pg_txn
\n GROUP BY ROLLUP (country_code, system_code)) z;
\n PERFORM pg_demo_v('V10_rollup',
\n CASE WHEN n = 91 THEN 1 ELSE 0 END,
\n 'rollup_rows='||n||' (80+10+1=91)');
\nEND $$;<\/p>\n
— V11: CUBE(\u56fd\u5bb6,\u57df) \u884c\u6570 = 80 + 10 + 8 + 1 = 99 (\u6bd4 ROLLUP \u591a 8 \u884c\u57df\u5c0f\u8ba1)
\nDO $$
\nDECLARE n int;
\nBEGIN
\n SELECT count(*) INTO n FROM (
\n SELECT system_code, country_code FROM pg_txn
\n GROUP BY CUBE (country_code, system_code)) z;
\n PERFORM pg_demo_v('V11_cube',
\n CASE WHEN n = 99 THEN 1 ELSE 0 END,
\n 'cube_rows='||n||' (80+10+8+1=99, \u6bd4ROLLUP\u591a8)');
\nEND $$;<\/p>\n
— V12: GROUPING() \u533a\u5206\u5c0f\u8ba1 (system_code \u88ab\u5c0f\u8ba1\u6389\u7684\u884c = 10\u56fd\u5c0f\u8ba1 + 1\u603b\u8ba1 = 11)
\nDO $$
\nDECLARE n_sub int;
\nBEGIN
\n SELECT count(*) INTO n_sub FROM (
\n SELECT system_code, country_code, GROUPING(system_code) gs, GROUPING(country_code) gc
\n FROM pg_txn GROUP BY GROUPING SETS ((system_code),(country_code),(system_code,country_code),())
\n ) z WHERE gs=1;
\n PERFORM pg_demo_v('V12_grouping_func',
\n CASE WHEN n_sub = 11 THEN 1 ELSE 0 END,
\n 'system_code_subtotal_rows='||n_sub||' (10\u56fd\u5c0f\u8ba1+1\u603b\u8ba1=11; \u7528GROUPING()\u800c\u975eIS NULL\u533a\u5206)');
\nEND $$;<\/p>\n
— V13: \u5168\u8868\u805a\u5408 Seq Scan \u4ee3\u4ef7 (\u672a\u7d22\u5f15\u5217 amount_orig \u5fc5\u5168\u626b, \u4e07\u4ebf\u7ea7\u987b\u5206\u533a\/\u7269\u5316\u89c6\u56fe)
\nDO $$
\nDECLARE r record;
\nBEGIN
\n SELECT * INTO r FROM pg_plan_stats('SELECT SUM(amount_orig) FROM pg_txn');
\n PERFORM pg_demo_v('V13_fullscan_aggregate',
\n CASE WHEN r.node_type='Seq Scan' AND r.shared_blks > 500 THEN 1 ELSE 0 END,
\n 'node='||r.node_type||' shared_blks='||r.shared_blks||' (\u4e07\u4ebf\u7ea7\u987b\u5206\u533a\/\u7269\u5316\u89c6\u56fe)');
\nEND $$;<\/p>\n
— V14: \u8986\u76d6\u7d22\u5f15\u805a\u5408 (\u5e26\u8fc7\u6ee4) \u8d70 Index Only Scan, \u514d\u56de\u8868
\nDO $$
\nDECLARE r record;
\nBEGIN
\n SELECT * INTO r FROM pg_plan_stats(
\n 'SELECT system_code, SUM(amount_usd) FROM pg_txn WHERE system_code IN (''ERP'',''CRM'',''SCM'') GROUP BY system_code');
\n PERFORM pg_demo_v('V14_covering_aggregate',
\n CASE WHEN r.node_type='Index Only Scan' THEN 1 ELSE 0 END,
\n 'node='||r.node_type||' shared_blks='||r.shared_blks);
\nEND $$;<\/p>\n
— V15: \u6700\u7ec8\u805a\u5408\u8f93\u51fa\u884c\u6570 = 8 \u57df (\u5206\u7ec4\u6b63\u786e, \u4e07\u4ebf\u7ea7\u53ef\u5e76\u884c Finalize Aggregate)
\nDO $$
\nDECLARE n int;
\nBEGIN
\n SELECT count(*) INTO n FROM (
\n SELECT system_code, SUM(amount_usd) FROM pg_txn GROUP BY system_code) z;
\n PERFORM pg_demo_v('V15_group_output_rows',
\n CASE WHEN n = 8 THEN 1 ELSE 0 END, 'domain_groups='||n||' (Finalize Aggregate \u8f93\u51fa8\u884c)');
\nEND $$;<\/p>\n
— V16: \u4e07\u4ebf\u9884\u6f14 \u2014 \u7269\u5316\u89c6\u56fe\u9884\u805a\u5408\u5757\u6570 << \u57fa\u8868\u5168\u805a\u5408\u5757\u6570
\nDO $$
\nDECLARE r1 record; r2 record; mvc int;
\nBEGIN
\n SELECT count(*) INTO mvc FROM mv_gmv;
\n SELECT * INTO r1 FROM pg_plan_stats('SELECT SUM(gmv_usd) FROM mv_gmv');
\n SELECT * INTO r2 FROM pg_plan_stats('SELECT SUM(amount_orig) FROM pg_txn');
\n PERFORM pg_demo_v('V16_materialized_summary',
\n CASE WHEN r1.shared_blks < r2.shared_blks THEN 1 ELSE 0 END,
\n 'mv_rows='||mvc||' mv_blks='||r1.shared_blks||' base_blks='||r2.shared_blks);
\nEND $$;<\/p>\n
— V17: \u805a\u5408\u786e\u5b9a\u6027 (\u540c\u67e5\u8be2\u4e24\u6b21\u7ed3\u679c\u4e00\u81f4 -> \u6574\u4f53\u5e42\u7b49)
\nDO $$
\nDECLARE a numeric; b numeric;
\nBEGIN
\n SELECT SUM(amount_usd) INTO a FROM pg_txn;
\n SELECT SUM(amount_usd) INTO b FROM pg_txn;
\n PERFORM pg_demo_v('V17_deterministic',
\n CASE WHEN a IS NOT NULL AND a = b THEN 1 ELSE 0 END, 'sum_usd='||round(a,2));
\nEND $$;<\/p>\n
— V18: \u805a\u5408\u5ffd\u7565 NULL (COUNT(*)=4, COUNT(v)=3, AVG \u4e0d\u542b NULL)
\nDO $$
\nDECLARE c1 int; c2 int; av numeric; su numeric;
\nBEGIN
\n WITH t(v) AS (VALUES (1),(2),(NULL),(3))
\n SELECT COUNT(*), COUNT(v), AVG(v), SUM(v) INTO c1, c2, av, su FROM t;
\n PERFORM pg_demo_v('V18_null_handling',
\n CASE WHEN c1=4 AND c2=3 AND av=2 AND su=6 THEN 1 ELSE 0 END,
\n 'count_all='||c1||' count_nonnull='||c2||' avg_ignores_null='||av||' sum='||su);
\nEND $$;<\/p>\n
— —————————————————————————-
\n— \u81ea\u68c0\u6c47\u603b
\n— —————————————————————————-
\nSELECT name AS check_name, pass AS pass_cnt, detail
\nFROM pg_demo_check ORDER BY name;<\/p>\n
SELECT CASE WHEN min(pass)=1 THEN '\u5168\u90e8\u901a\u8fc7 (ALL PASS)' ELSE '\u672a\u5168\u90e8\u901a\u8fc7 (SOME FAILED)' END AS result
\nFROM pg_demo_check;<\/p>\n
SELECT count(*) AS total, sum(pass) AS passed FROM pg_demo_check;<\/p>\n","protected":false},"excerpt":{"rendered":"
—
\n— PostgreSQL 18 \u2014 SQL \u805a\u5408\u51fd\u6570\u4e0e\u5206\u7ec4\u96c6 (Aggregate Functions & Grouping Sets)
\n— \u573a\u666f: \u4e07\u4ebf\u6570\u636e\u9ad8\u5e76\u53d1 \/ \u56fd\u9645\u65f6\u533a\u65f6\u95f4\u5dee \/ \u56fd\u9645\u591a\u79cd\u8d27\u5e01\u7ed3\u7b97
\n— \u672c\u6587\u4ef6\u3010\u81ea\u5305\u542b\u00b7\u5b8c\u5168\u5e42\u7b49\u3011: \u91cd\u590d\u8fd0\u884c\u7ed3\u679c\u9010\u5b57\u8282\u4e00\u81f4 (setseed \u4fdd\u8bc1\u9020\u6570\u4e00\u81f4)
\n— \u8fde\u63a5\u65b9\u5f0f: PGPASSWORD psql -U postgres -h localhost -p 5433 -d jewelry<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[954,323,2330,100,2032],"topic":[],"class_list":["post-118010","post","type-post","status-publish","format-standard","hentry","category-server","tag-postgresql","tag-323","tag-2330","tag-100","tag-2032"],"yoast_head":"\n