KES 数据仓库与OLAP应用实战：数据分析、聚合查询与性能优化
前言
数据仓库是企业数据分析的核心基础设施，为决策提供数据支撑。KES作为企业级关系型数据库，不仅擅长OLTP（联机事务处理），在OLAP（联机分析处理）方面也有出色表现。
本篇内容深入讲解KES在数据仓库和OLAP场景中的应用，详细讲解数据建模、聚合查询、分析函数以及性能优化技巧。全文以实际操作为主，结合大量真实案例。如果你需要构建数据分析系统，或者希望提升分析查询性能，相信这篇内容对你会有帮助。
一、数据仓库基础
理解数据仓库的基本概念是构建分析系统的前提。
-- OLTP vs OLAP对比
-- OLTP：面向事务，数据量小，查询简单，响应快
-- OLAP：面向分析，数据量大，查询复杂，响应慢
-- 创建数据仓库事实表
CREATE TABLE sales_fact (
sale_id BIGSERIAL,
date_key DATE NOT NULL,
product_key BIGINT NOT NULL,
customer_key BIGINT NOT NULL,
store_key BIGINT NOT NULL,
quantity INT NOT NULL,
amount NUMERIC(12,2) NOT NULL,
cost NUMERIC(12,2),
profit NUMERIC(12,2),
created_at TIMESTAMP DEFAULT now()
);
-- 创建维度表
CREATE TABLE date_dim (
date_key DATE PRIMARY KEY,
year INT,
quarter INT,
month INT,
week INT,
day_of_week INT,
is_holiday BOOLEAN
);
CREATE TABLE product_dim (
product_key BIGINT PRIMARY KEY,
product_name VARCHAR(200),
category VARCHAR(100),
brand VARCHAR(100),
price NUMERIC(10,2)
);
CREATE TABLE customer_dim (
customer_key BIGINT PRIMARY KEY,
customer_name VARCHAR(100),
gender VARCHAR(10),
age INT,
city VARCHAR(100),
vip_level INT
);
星型模型
-- 星型模型查询示例
SELECT
d.year,
d.quarter,
p.category,
SUM(f.amount) AS total_sales,
SUM(f.profit) AS total_profit
FROM sales_fact f
JOIN date_dim d ON f.date_key = d.date_key
JOIN product_dim p ON f.product_key = p.product_key
WHERE d.year = 2026
GROUP BY d.year, d.quarter, p.category
ORDER BY d.year, d.quarter, p.category;
雪花模型
-- 雪花模型：维度表进一步规范化
CREATE TABLE category_dim (
category_key BIGINT PRIMARY KEY,
category_name VARCHAR(100),
parent_category BIGINT
);
CREATE TABLE product_dim_v2 (
product_key BIGINT PRIMARY KEY,
product_name VARCHAR(200),
category_key BIGINT REFERENCES category_dim(category_key),
brand VARCHAR(100)
);
二、聚合查询与分组分析
聚合查询是OLAP的核心功能，KES提供了丰富的聚合函数和分组方式。
基础聚合函数
-- 销售统计
SELECT
date_trunc('month', date_key) AS month,
COUNT(*) AS order_count,
SUM(quantity) AS total_quantity,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount,
MIN(amount) AS min_amount,
MAX(amount) AS max_amount
FROM sales_fact
WHERE date_key >= '2026-01-01'
GROUP BY date_trunc('month', date_key)
ORDER BY month;
高级聚合函数
-- 百分位数
SELECT
percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount,
percentile_cont(0.9) WITHIN GROUP (ORDER BY amount) AS p90_amount,
percentile_cont(0.95) WITHIN GROUP (ORDER BY amount) AS p95_amount
FROM sales_fact
WHERE date_key >= '2026-01-01';
-- 标准差和方差
SELECT
STDDEV(amount) AS stddev_amount,
VARIANCE(amount) AS variance_amount
FROM sales_fact;
-- 字符串聚合
SELECT
category,
STRING_AGG(product_name, ', ') AS products
FROM product_dim
GROUP BY category;
GROUPING SETS
-- 多维度聚合
SELECT
year,
quarter,
category,
SUM(amount) AS total_sales
FROM sales_fact s
JOIN date_dim d ON s.date_key = d.date_key
JOIN product_dim p ON s.product_key = p.product_key
WHERE year = 2026
GROUP BY GROUPING SETS (
(year, quarter, category), -- 年+季度+类别
(year, quarter), -- 年+季度
(year, category), -- 年+类别
() -- 总计
)
ORDER BY year, quarter, category;
ROLLUP和CUBE
-- ROLLUP：层次化聚合
SELECT
year,
quarter,
month,
SUM(amount) AS total_sales
FROM sales_fact s
JOIN date_dim d ON s.date_key = d.date_key
WHERE year = 2026
GROUP BY ROLLUP (year, quarter, month)
ORDER BY year, quarter, month;
-- CUBE：全维度组合
SELECT
year,
category,
brand,
SUM(amount) AS total_sales
FROM sales_fact s
JOIN date_dim d ON s.date_key = d.date_key
JOIN product_dim p ON s.product_key = p.product_key
WHERE year = 2026
GROUP BY CUBE (year, category, brand)
ORDER BY year, category, brand;
三、分析函数与窗口函数
分析函数是OLAP查询的利器，可以实现复杂的排名、累计、移动平均等计算。
排名函数
-- 销售排名
SELECT
product_name,
category,
total_sales,
RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS category_rank,
DENSE_RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS dense_rank,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) AS row_num
FROM (
SELECT
p.product_name,
p.category,
SUM(f.amount) AS total_sales
FROM sales_fact f
JOIN product_dim p ON f.product_key = p.product_key
GROUP BY p.product_name, p.category
) t;
累计与移动平均
-- 累计销售额
SELECT
date_trunc('month', date_key) AS month,
SUM(amount) AS monthly_sales,
SUM(SUM(amount)) OVER (ORDER BY date_trunc('month', date_key)) AS cumulative_sales
FROM sales_fact
WHERE date_key >= '2026-01-01'
GROUP BY date_trunc('month', date_key)
ORDER BY month;
-- 3个月移动平均
SELECT
date_trunc('month', date_key) AS month,
SUM(amount) AS monthly_sales,
AVG(SUM(amount)) OVER (
ORDER BY date_trunc('month', date_key)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3m
FROM sales_fact
WHERE date_key >= '2026-01-01'
GROUP BY date_trunc('month', date_key)
ORDER BY month;
同比环比分析
-- 同比环比计算
WITH monthly_sales AS (
SELECT
date_trunc('month', date_key) AS month,
SUM(amount) AS sales
FROM sales_fact
GROUP BY date_trunc('month', date_key)
)
SELECT
month,
sales,
LAG(sales, 1) OVER (ORDER BY month) AS prev_month,
LAG(sales, 12) OVER (ORDER BY month) AS prev_year,
ROUND((sales - LAG(sales, 1) OVER (ORDER BY month)) * 100.0 /
NULLIF(LAG(sales, 1) OVER (ORDER BY month), 0), 2) AS mom_growth,
ROUND((sales - LAG(sales, 12) OVER (ORDER BY month)) * 100.0 /
NULLIF(LAG(sales, 12) OVER (ORDER BY month), 0), 2) AS yoy_growth
FROM monthly_sales
ORDER BY month;
四、数据仓库性能优化
大规模数据分析查询需要针对性的性能优化。
物化视图
-- 创建物化视图
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
date_trunc('month', date_key) AS month,
category,
brand,
SUM(quantity) AS total_quantity,
SUM(amount) AS total_amount,
SUM(profit) AS total_profit
FROM sales_fact s
JOIN product_dim p ON s.product_key = p.product_key
GROUP BY date_trunc('month', date_key), category, brand;
-- 创建索引
CREATE INDEX idx_mv_monthly_sales_month ON mv_monthly_sales (month);
CREATE INDEX idx_mv_monthly_sales_category ON mv_monthly_sales (category);
-- 刷新物化视图
REFRESH MATERIALIZED VIEW mv_monthly_sales;
-- 并发刷新（不阻塞查询）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;
分区表优化
-- 按月分区销售事实表
CREATE TABLE sales_fact_partitioned (
sale_id BIGSERIAL,
date_key DATE NOT NULL,
product_key BIGINT NOT NULL,
customer_key BIGINT NOT NULL,
amount NUMERIC(12,2) NOT NULL
) PARTITION BY RANGE (date_key);
-- 创建月度分区
CREATE TABLE sales_2026_01 PARTITION OF sales_fact_partitioned
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE sales_2026_02 PARTITION OF sales_fact_partitioned
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- 分区裁剪：查询自动只扫描相关分区
SELECT * FROM sales_fact_partitioned
WHERE date_key >= '2026-01-01' AND date_key < '2026-02-01';
列存储优化
-- 创建列存储表（需要扩展支持）
-- KES支持列存储引擎，适合OLAP场景
-- 压缩策略
-- 对于历史数据，可以使用更高压缩比
ALTER TABLE sales_fact SET (
toast_tuple_target = 128,
toast.autovacuum_enabled = true
);
五、实战案例解析
场景一：销售数据分析仪表盘
构建销售数据分析仪表盘。
-- 销售概览
CREATE OR REPLACE FUNCTION get_sales_dashboard(p_date DATE)
RETURNS TABLE(
metric_name VARCHAR,
metric_value NUMERIC,
prev_value NUMERIC,
change_rate NUMERIC
) AS $$
BEGIN
RETURN QUERY
SELECT * FROM (
VALUES
('今日销售额',
(SELECT SUM(amount) FROM sales_fact WHERE date_key = p_date),
(SELECT SUM(amount) FROM sales_fact WHERE date_key = p_date - INTERVAL '1 day'),
(SELECT ROUND((SUM(amount) - (SELECT SUM(amount) FROM sales_fact WHERE date_key = p_date - INTERVAL '1 day')) * 100.0 /
NULLIF((SELECT SUM(amount) FROM sales_fact WHERE date_key = p_date - INTERVAL '1 day'), 0), 2)
FROM sales_fact WHERE date_key = p_date)),
('本月销售额',
(SELECT SUM(amount) FROM sales_fact WHERE date_trunc('month', date_key) = date_trunc('month', p_date)),
(SELECT SUM(amount) FROM sales_fact WHERE date_trunc('month', date_key) = date_trunc('month', p_date - INTERVAL '1 month')),
(SELECT ROUND((SUM(amount) - (SELECT SUM(amount) FROM sales_fact WHERE date_trunc('month', date_key) = date_trunc('month', p_date - INTERVAL '1 month'))) * 100.0 /
NULLIF((SELECT SUM(amount) FROM sales_fact WHERE date_trunc('month', date_key) = date_trunc('month', p_date - INTERVAL '1 month')), 0), 2)
FROM sales_fact WHERE date_trunc('month', date_key) = date_trunc('month', p_date)))
) t;
END;
$$ LANGUAGE plpgsql;
-- 使用仪表盘函数
SELECT * FROM get_sales_dashboard(CURRENT_DATE);
场景二：客户行为分析
分析客户购买行为。
-- 客户分层分析
WITH customer_stats AS (
SELECT
c.customer_key,
c.customer_name,
COUNT(DISTINCT f.sale_id) AS order_count,
SUM(f.amount) AS total_amount,
AVG(f.amount) AS avg_amount,
MAX(f.date_key) AS last_purchase_date
FROM customer_dim c
LEFT JOIN sales_fact f ON c.customer_key = f.customer_key
GROUP BY c.customer_key, c.customer_name
)
SELECT
CASE
WHEN total_amount >= 100000 THEN 'VIP客户'
WHEN total_amount >= 50000 THEN '重要客户'
WHEN total_amount >= 10000 THEN '普通客户'
ELSE '低价值客户'
END AS customer_level,
COUNT(*) AS customer_count,
SUM(order_count) AS total_orders,
SUM(total_amount) AS total_sales,
ROUND(AVG(total_amount), 2) AS avg_sales_per_customer
FROM customer_stats
GROUP BY
CASE
WHEN total_amount >= 100000 THEN 'VIP客户'
WHEN total_amount >= 50000 THEN '重要客户'
WHEN total_amount >= 10000 THEN '普通客户'
ELSE '低价值客户'
END
ORDER BY total_sales DESC;
场景三：商品销售趋势分析
分析商品销售趋势。
-- 商品销售趋势
WITH product_trend AS (
SELECT
p.product_name,
p.category,
date_trunc('month', f.date_key) AS month,
SUM(f.amount) AS monthly_sales
FROM sales_fact f
JOIN product_dim p ON f.product_key = p.product_key
WHERE f.date_key >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY p.product_name, p.category, date_trunc('month', f.date_key)
)
SELECT
product_name,
category,
month,
monthly_sales,
LAG(monthly_sales, 1) OVER (PARTITION BY product_name ORDER BY month) AS prev_month_sales,
ROUND((monthly_sales - LAG(monthly_sales, 1) OVER (PARTITION BY product_name ORDER BY month)) * 100.0 /
NULLIF(LAG(monthly_sales, 1) OVER (PARTITION BY product_name ORDER BY month), 0), 2) AS mom_growth,
AVG(monthly_sales) OVER (
PARTITION BY product_name
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3m
FROM product_trend
ORDER BY product_name, month;
总结与展望
数据仓库与OLAP是KES的重要应用场景。通过合理的数据建模、高效的聚合查询和性能优化，可以构建强大的数据分析系统。
核心原则：
根据业务需求选择合适的数据建模方式
充分利用聚合函数和分析函数
使用物化视图优化重复查询
合理设计分区策略，提升查询性能
定期分析查询性能，持续优化
KES在OLAP场景表现出色，支持丰富的分析函数和聚合方式。在实际应用中，建议根据数据量和查询特点，选择合适的优化策略，构建高效的分析系统。
期望本篇内容能够帮助你掌握KES数据仓库和OLAP应用的核心技术，为构建数据分析平台提供技术支撑。