KES 数据仓库与OLAP应用实战：数据分析、聚合查询与性能优化

国内服务器1周前发布 beixibaobao

15 0 0

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应用的核心技术，为构建数据分析平台提供技术支撑。

国内服务器

文章版权归作者所有，未经允许请勿转载。

无zookeeper Kafka 4.1.0 Raft 集群搭建

国内服务器

4个月前

470

以Hadoop为锚点：大数据分布式技术的学习感悟与能力跃迁

国内服务器

1个月前

180

消息队列高可用：Kafka 集群容灾与 Exactly-Once 语义保障

国内服务器

2周前

160

Cloudera CDP/CMP华为鲲鹏版下 Spark应用加速，华为昇腾芯片的实用配置过程

国内服务器

4个月前

430

KES 数据仓库与OLAP应用实战：数据分析、聚合查询与性能优化