数据仓库之命名规范

数据仓库命名规范旨在确保数据仓库中的各种对象（如数据库、表、列、索引、视图等）具有一致、清晰且有意义的名称。这些规范有助于提高数据的可读性、可维护性和可理解性。以下是一些常见的数据仓库命名规范，涵盖了数据库对象的各个方面。

一般命名规则

1. 清晰和有意义

名称应该清晰明了，能够反映其实际用途或内容。

-- 不好的命名
SELECT * FROM tbl1;

-- 好的命名
SELECT * FROM customer_orders;

2. 避免使用保留字

避免使用SQL或其他数据库系统的保留字作为名称。

-- 不好的命名
SELECT * FROM user;

-- 好的命名
SELECT * FROM customer;

3. 使用小写字母

使用小写字母，避免使用大写或混合大小写，以提高一致性。

-- 不好的命名
SELECT * FROM CustomerData;

-- 好的命名
SELECT * FROM customer_data;

4. 单词间使用下划线分隔

使用下划线（_）分隔单词，提高名称的可读性。

-- 不好的命名
SELECT * FROM customerdetails;

-- 好的命名
SELECT * FROM customer_details;

数据库对象命名

1. 数据库

数据库名称应反映其用途或所属的业务领域。

-- 示例
CREATE DATABASE sales_data;

2. 表

表名应为复数形式，反映表中存储的数据类型。

-- 示例
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

3. 列

列名应为单数形式，反映列中存储的数据内容。

-- 示例
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

4. 索引

索引名称应包含表名和列名，并以idx为后缀。

-- 示例
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

5. 视图

视图名称应以vw_为前缀，反映其数据来源或用途。

-- 示例
CREATE VIEW vw_customer_orders AS
SELECT c.customer_id, c.first_name, c.last_name, o.order_id, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

6. 存储过程和函数

存储过程和函数名称应以sp_或fn_为前缀，反映其功能。

-- 示例
CREATE PROCEDURE sp_get_customer_orders (IN customer_id INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = customer_id;
END;

CREATE FUNCTION fn_calculate_discount (amount DECIMAL(10, 2), discount_rate DECIMAL(5, 2))
RETURNS DECIMAL(10, 2)
BEGIN
    RETURN amount * (1 - discount_rate);
END;

命名约定示例

-- 数据库
CREATE DATABASE sales_data;

-- 表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- 索引
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- 视图
CREATE VIEW vw_customer_orders AS
SELECT c.customer_id, c.first_name, c.last_name, o.order_id, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

-- 存储过程
CREATE PROCEDURE sp_get_customer_orders (IN customer_id INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = customer_id;
END;

-- 函数
CREATE FUNCTION fn_calculate_discount (amount DECIMAL(10, 2), discount_rate DECIMAL(5, 2))
RETURNS DECIMAL(10, 2)
BEGIN
    RETURN amount * (1 - discount_rate);
END;

数据仓库分层概述

1. 原始数据层（Raw Data Layer）

   • 保存从源系统提取的原始数据，未经处理或仅经过基本清洗。
   • 命名规范：通常以raw_或stg_作为前缀。

2. 数据集成层（Data Integration Layer）

   • 保存经过清洗、转换和集成的数据，通常用于进一步分析和处理。
   • 命名规范：通常以int_或etl_作为前缀。

3. 数据汇总层（Data Aggregation Layer）

   • 保存聚合和汇总的数据，通常用于生成报表和仪表盘。
   • 命名规范：通常以agg_或sum_作为前缀。

4. 数据服务层（Data Service Layer）

   • 保存最终提供给用户或应用程序的数据，通常以视图或数据集形式存在。
   • 命名规范：通常以svc_或vw_作为前缀。

分层命名规范详细介绍

原始数据层（Raw Data Layer）

原始数据层中的表和对象通常使用raw_或stg_作为前缀，后面跟随数据源名称和表名。

-- 示例
CREATE TABLE raw_sales_data (
    sale_id INT,
    product_id INT,
    sale_date DATE,
    quantity INT,
    price DECIMAL(10, 2)
);

CREATE TABLE stg_customer_data (
    customer_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

数据集成层（Data Integration Layer）

数据集成层中的表和对象通常使用int_或etl_作为前缀，后面跟随数据处理阶段和表名。

-- 示例
CREATE TABLE int_sales_data_cleaned (
    sale_id INT,
    product_id INT,
    sale_date DATE,
    quantity INT,
    price DECIMAL(10, 2),
    cleaned_flag BOOLEAN
);

CREATE TABLE etl_customer_data_transformed (
    customer_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    full_name VARCHAR(100)
);

数据汇总层（Data Aggregation Layer）

数据汇总层中的表和对象通常使用agg_或sum_作为前缀，后面跟随汇总维度和表名。

-- 示例
CREATE TABLE agg_sales_monthly (
    month DATE,
    total_sales DECIMAL(10, 2),
    total_quantity INT
);

CREATE TABLE sum_customer_purchases (
    customer_id INT,
    total_purchases DECIMAL(10, 2),
    total_items INT
);

数据服务层（Data Service Layer）

数据服务层中的视图和对象通常使用svc_或vw_作为前缀，后面跟随数据服务对象的名称。

-- 示例
CREATE VIEW svc_sales_summary AS
SELECT 
    sale_date,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_sales
FROM 
    int_sales_data_cleaned
GROUP BY 
    sale_date;

CREATE VIEW vw_customer_summary AS
SELECT 
    customer_id,
    full_name,
    email,
    total_purchases,
    total_items
FROM 
    sum_customer_purchases
JOIN 
    etl_customer_data_transformed USING (customer_id);

实践中的分层命名示例

假设我们有一个电商平台的数据仓库，包含订单数据和客户数据：

原始数据层

CREATE TABLE raw_orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2)
);

CREATE TABLE raw_customers (
    customer_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

数据集成层

CREATE TABLE int_orders_cleaned (
    order_id INT,
    customer_id INT,
    order_date DATE,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    cleaned_flag BOOLEAN
);

CREATE TABLE int_customers_transformed (
    customer_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    full_name VARCHAR(100)
);

数据汇总层

CREATE TABLE agg_orders_monthly (
    month DATE,
    total_sales DECIMAL(10, 2),
    total_quantity INT
);

CREATE TABLE sum_customers_purchases (
    customer_id INT,
    total_purchases DECIMAL(10, 2),
    total_items INT
);

数据服务层

CREATE VIEW svc_order_summary AS
SELECT 
    order_date,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_sales
FROM 
    int_orders_cleaned
GROUP BY 
    order_date;

CREATE VIEW vw_customer_summary AS
SELECT 
    customer_id,
    full_name,
    email,
    total_purchases,
    total_items
FROM 
    sum_customers_purchases
JOIN 
    int_customers_transformed USING (customer_id);

通过遵循这些命名规范，可以显著提高数据仓库的可读性和可维护性，使得数据管理和使用更加高效。