一、数仓分层

1.1为什么要分层？

把复杂问题简单化。将复杂的任务分解成多层来完成，每一层只处理简单的任务，方便定位问题。
减少重复开发。规范数据分层，通过的中间层数据，能够减少极大的重复计算，增加一次计算结果的复用性。
隔离原始数据。不论是数据的异常还是数据的敏感性，使真实数据与统计数据解耦开。

数仓层级结构图：

1.2 OSD 操作数据层解析

ODS (Operational Data Store) 操作数据层

作用：ODS 层用于存储来自不同操作系统或业务系统的原始数据。通常，ODS 存储的数据是未经过多处理的原始数据，主要用于实时或准实时的查询。

举例：假设你有三个数据源，分别是：

数据源1：销售订单系统
数据源2：库存管理系统
数据源3：客户管理系统

在 ODS 层，你会将这些系统的原始数据集中存储在一个数据库中。例如，销售订单数据、库存数据和客户数据都可能存储在 ODS 中。

1.3 DWD 明细数据层解析

DWD (Data Warehouse Detail) 明细数据层

作用：DWD 层是对 ODS 层数据的处理结果，数据在此层经过清洗、转换和去重等操作。这里的数据通常是更细粒度的、结构化的数据，并且已经准备好供进一步分析使用。

举例：你可以在 DWD 层将 ODS 层中的销售订单数据进行清洗，去除无效记录，并按照商品类别、地区等维度进行细化。这样便可以生成具有更多细节的表，便于后续分析。

1.4 DWS 数据汇总层解析

作用：DWS 层是对 DWD 层数据进行汇总、聚合后的结果，主要用于对业务进行更高层次的分析。

举例：例如，在 DWS 层，你可能会将销售订单数据按月汇总，生成各个月份的销售总额、订单数量、客户数量等数据，方便进行月度报表分析。

1.5 ADS 应用数据层解析

作用：ADS 层是最终用于支持业务应用的层，通常是根据业务需求来定制的报表、应用程序等使用的数据。

举例：在 ADS 层，基于 DWS 层的汇总数据，生成具体业务需求的报告和仪表盘。例如，按月份展示产品销售趋势、每个地区的市场占有率等。

1.6 DIM 维表数据解析

作用：DIM 层存储与业务维度相关的元数据。例如，时间维度、地区维度、产品维度等，这些数据可以帮助在其他层次上对数据进行过滤和聚合。

举例：你可能会有一个包含地区信息的维度表，记录不同地区的名称、代码等。这个表可以在 DWD 或 DWS 层中与其他数据进行关联，帮助进行区域性分析。

二、数仓命名规范

2.1 表命名

ODS层典型的命名规则格式：<层级>_<系统名>_<原表名>_<同步方式>_<同步频率>

其他层典型的命名规则格式：<层级>_<主题表名>_<同步方式>_<同步频率>

2.1.1. ODS层命名

ODS 层存储的是从各个源系统同步过来的原始数据。假设我们从销售订单系统同步数据，并且是按小时进行同步。

命名示例：ods_crm_sales_orders_full_sync_hourly

ods：层级
crm：系统名
sales_orders：原表名（销售订单）
full_sync：同步方式（全量同步）
hourly：同步频率（每小时同步一次）

2.1.2 DWD层命名

DWD 层的数据已经经过清洗和转换。例如，我们对销售订单数据进行了去重和清洗，并准备好了按客户的销售明细。

命名示例：dwd_sales_order_cleaned_hourly

dwd：层级
sales_order：原始表名
cleaned：表示数据已清洗
hourly：同步频率（每小时更新）

2.1.3 DWS层命名

DWS 层存储的是汇总数据，例如按月汇总的销售数据。

命名示例：dws_monthly_sales_summary_daily

dws：层级
monthly_sales_summary：汇总的数据（按月销售汇总）
daily：每天更新

2.1.4 ADS层命名

ADS 层是为最终应用提供的数据，通常是经过高度聚合和优化的结果数据。

命名示例：ads_customer_spending_report_monthly

ads：层级
customer_spending_report：报告类型（客户消费报告）
monthly：每月更新

2.1.5 DIM层命名

DIM 层存储各种维度数据。例如，存储产品的基本信息。

命名示例：dim_product_info_hourly

dim：层级
product：报告类型（产品信息）
hourly：每月更新

2.1.6 更新策略

full_sync：全量同步
incremental_sync：增量同步
hourly：每小时同步
daily：每天同步
real_time：实时同步

三、数据例子

3.1、创建Schema

CREATE SCHEMA resource;
CREATE SCHEMA ods;
CREATE SCHEMA dwd;
CREATE SCHEMA dws;
CREATE SCHEMA ads;
CREATE SCHEMA dim;

3.2 模拟创建来源表并插入数据

CREATE TABLE resource.sales_orders (
    order_id SERIAL PRIMARY KEY,  -- 订单 ID
    customer_id INT,              -- 客户 ID
    product_id INT,               -- 产品 ID
    order_date TIMESTAMP,         -- 订单时间
    quantity INT,                 -- 购买数量
    unit_price DECIMAL(10, 2),    -- 单价
    total_amount DECIMAL(10, 2)   -- 总金额 (quantity * unit_price)
);

INSERT INTO resource.sales_orders (order_id, customer_id, product_id, order_date, quantity, unit_price, total_amount)
VALUES
    (1, 101, 2001, '2025-08-01 10:00', 2, 150.00, 300.00),
    (2, 102, 2002, '2025-08-02 11:00', 1, 200.00, 200.00),
    (3, 103, 2003, '2025-08-03 12:00', 5, 100.00, 500.00),
    (4, 104, 2001, '2025-08-03 13:30', 1, 150.00, 150.00),
    (5, 105, 2004, '2025-08-04 09:00', 3, 120.00, 360.00);

3.3 创建ODS层

3.3.1 创建ODS表

CREATE TABLE ods.crm_sales_orders_incremental_sync_hourly (
    order_id SERIAL PRIMARY KEY,  -- 订单 ID
    customer_id INT,              -- 客户 ID
    product_id INT,               -- 产品 ID
    order_date TIMESTAMP,         -- 订单时间
    quantity INT,                 -- 购买数量
    unit_price DECIMAL(10, 2),    -- 单价
    total_amount DECIMAL(10, 2)   -- 总金额 (quantity * unit_price)
);

3.3.2 增量同步sql语句，有则更新，无则插入

MERGE INTO ods.sales_orders_incremental_sync_hourly AS target
USING resource.sales_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED AND source.order_date > '2025-07-02 00:00' THEN
    UPDATE SET
        customer_id = source.customer_id,
        product_id = source.product_id,
        order_date = source.order_date,
        quantity = source.quantity,
        unit_price = source.unit_price,
        total_amount = source.total_amount
WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, product_id, order_date, quantity, unit_price, total_amount)
    VALUES (source.order_id, source.customer_id, source.product_id, source.order_date, source.quantity, source.unit_price, source.total_amount);

3.4 创建DWD层

3.4.1 创建DWD表

CREATE TABLE dwd.sales_orders_incremental_sync_hourly (
    order_id SERIAL PRIMARY KEY,  -- 订单 ID
    customer_id INT,              -- 客户 ID
    product_id INT,               -- 产品 ID
    order_date TIMESTAMP,         -- 订单时间
    quantity INT,                 -- 购买数量
    unit_price DECIMAL(10, 2),    -- 单价
    total_amount DECIMAL(10, 2)   -- 总金额 (quantity * unit_price)
);

3.4.2 增量同步清洗后插入DWD

MERGE INTO ods.sales_orders_incremental_sync_hourly AS target
USING resource.sales_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED AND source.order_date > '2025-07-02 00:00' THEN
    UPDATE SET
        customer_id = source.customer_id,
        product_id = source.product_id,
        order_date = source.order_date,
        quantity = source.quantity,
        unit_price = source.unit_price,
        total_amount = source.total_amount
WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, product_id, order_date, quantity, unit_price, total_amount)
    VALUES (source.order_id, source.customer_id, source.product_id, source.order_date, source.quantity, source.unit_price, source.total_amount);

3.5 创建DWS层

3.5.1 创建DWS表

例如按月汇总的销售数据。

CREATE TABLE dws.monthly_sales_summary_daily (
    sales_month DATE,
    total_sales DECIMAL(10, 2),
    total_orders INT,
    total_customers INT
);

3.6 创建ADS层

3.6.1我们为客户消费情况创建一个表

CREATE TABLE ads.customer_spending_report_monthly (
    customer_id INT PRIMARY KEY,
    total_spent DECIMAL(10, 2)
);

3.6.2 同步更新信息

MERGE INTO ads.customer_spending_report_monthly AS target
USING (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM dwd.sales_orders_cleaned_hourly
    GROUP BY customer_id
) AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
    UPDATE SET total_spent = source.total_spent
WHEN NOT MATCHED THEN
    INSERT (customer_id, total_spent)
    VALUES (source.customer_id, source.total_spent);

打赏赞