产品指南
表设计
生成列

生成列

生成列（Generated Columns）是 ProtonBase 中的一项强大特性，允许你定义列值由其他列或表达式自动计算得出。计算值将被存储在表中，从而避免在查询时重复计算。

语法

column_name data_type GENERATED ALWAYS AS (expression) STORED

核心特性

  • 自动计算：生成列的值基于其他列自动计算得出
  • 存储优化：计算结果被存储在表中，避免重复计算
  • 查询加速：可以作为索引的一部分，显著提高查询性能
  • 表达式限制：表达式不能包含易变函数（如 RANDOM()、NOW()）或子查询

基本示例

以下示例展示了如何使用生成列计算商品的最终价格：

CREATE TABLE products (
    product_id INT,
    name TEXT, 
    price NUMERIC,
    tax NUMERIC,
    final_price NUMERIC GENERATED ALWAYS AS (price * (1 + tax)) STORED
);

应用场景

时间字段预处理

生成列在处理时间字段时非常有用，可以预计算常用的日期派生信息，提高查询效率。

提取日期部分

CREATE TABLE orders (
    order_id INT,
    order_date TIMESTAMPTZ,
    order_year INT GENERATED ALWAYS AS (EXTRACT(YEAR FROM order_date)) STORED,
    order_month INT GENERATED ALWAYS AS (EXTRACT(MONTH FROM order_date)) STORED,
    order_day INT GENERATED ALWAYS AS (EXTRACT(DAY FROM order_date)) STORED,
    order_week INT GENERATED ALWAYS AS (EXTRACT(WEEK FROM order_date)) STORED
);

计算时间差

CREATE TABLE user_sessions (
    session_id UUID,
    login_time TIMESTAMPTZ,
    logout_time TIMESTAMPTZ,
    session_duration INTERVAL GENERATED ALWAYS AS (logout_time - login_time) STORED
);

时区转换

CREATE TABLE events (
    event_id INT,
    event_time TIMESTAMPTZ,
    event_time_local TIMESTAMP GENERATED ALWAYS AS (event_time AT TIME ZONE 'Asia/Shanghai') STORED
);

日期范围分类

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    is_weekend BOOLEAN GENERATED ALWAYS AS (EXTRACT(DOW FROM sale_date) IN (0, 6)) STORED,
    is_month_end BOOLEAN GENERATED ALWAYS AS (sale_date = (sale_date + INTERVAL '1 month - 1 day')::DATE) STORED
);

文本处理

生成列也常用于文本处理，可以预计算文本的派生属性。

大小写转换

CREATE TABLE users (
    user_id INT,
    email TEXT,
    email_upper TEXT GENERATED ALWAYS AS (UPPER(email)) STORED
);

文本长度计算

CREATE TABLE posts (
    post_id INT,
    title TEXT,
    content TEXT,
    title_length INT GENERATED ALWAYS AS (LENGTH(title)) STORED,
    content_length INT GENERATED ALWAYS AS (LENGTH(content)) STORED
);

JSON 字段提取

CREATE TABLE user_profiles (
    user_id INT,
    profile JSONB,
    user_name TEXT GENERATED ALWAYS AS (profile->>'name') STORED,
    user_age INT GENERATED ALWAYS AS (CAST(profile->>'age' AS INT)) STORED
);

数值计算

生成列在数值计算中也非常有用，可以预计算复杂的表达式。

分类计算

CREATE TABLE products (
    product_id INT,
    name TEXT,
    price NUMERIC,
    category TEXT,
    discount_rate NUMERIC GENERATED ALWAYS AS (
        CASE 
            WHEN category = 'electronics' THEN 0.1
            WHEN category = 'clothing' THEN 0.15
            WHEN category = 'books' THEN 0.05
            ELSE 0.0
        END
    ) STORED,
    discounted_price NUMERIC GENERATED ALWAYS AS (price * (1 - discount_rate)) STORED
);

累计计算

CREATE TABLE transactions (
    transaction_id INT,
    account_id INT,
    amount NUMERIC,
    transaction_date DATE,
    running_balance NUMERIC GENERATED ALWAYS AS (
        SUM(amount) OVER (PARTITION BY account_id ORDER BY transaction_date)
    ) STORED
);

使用注意事项

  1. 性能考虑：生成列的值在插入或更新时计算，会增加写操作的开销，但可以显著提高查询性能。
  2. 表达式限制：生成列的表达式不能包含易变函数（如 RANDOM()、NOW() 等）或子查询。
  3. 数据类型匹配：生成列的数据类型必须与表达式结果的数据类型兼容。
  4. 索引支持：生成列可以被索引，这在复杂表达式查询中特别有用。

最佳实践

  1. 合理使用：只在确实需要预计算并存储派生值时使用生成列，避免不必要的存储开销。
  2. 索引优化：对经常用于查询条件的生成列创建索引，以提高查询性能。
  3. 表达式简化：尽量简化生成列的表达式，避免过于复杂的计算影响写入性能。
  4. 文档说明：在表结构文档中清楚说明生成列的计算逻辑，便于其他开发者理解。

总之，Generated Columns 通过自动计算派生列，简化了 SQL 操作，是 ProtonBase 的一项强大特性。合理使用生成列可以显著提高查询性能，特别是在处理时间字段预处理、文本处理和复杂数值计算等场景中。

更多使用可以参考 Generated Columns。

约束分区表