New in v2.0:计算列通过列定义中包含的标量表达式存储从其他列生成的数据。

为什么要使用计算列?

与分区,JSONB列或二级索引一起使用时,计算列特别有用。

注意事项

计算列:

创建

只能在创建表时添加计算列。 使用以下语法:

column_name <type> AS (<expr>) STORED
Parameter Description
column_name 计算列的名称/标识符。
<type> 计算列的数据类型。
<expr> 用于计算列值的纯scalar表达式。不能使用标记为impure的任何函数,例如now()nextval()
STORED (必需)计算列与其他列一起存储。

示例

使用计算列创建表

在这个例子中,让我们创建一个带有计算列的简单表:

CREATE TABLE names (
    id INT PRIMARY KEY,
    first_name STRING,
    last_name STRING,
    full_name STRING AS (CONCAT(first_name, ' ', last_name)) STORED
  );

然后,插入几行数据:

INSERT INTO names (id, first_name, last_name) VALUES
    (1, 'Lola', 'McDog'),
    (2, 'Carl', 'Kimball'),
    (3, 'Ernie', 'Narayan');
SELECT * FROM names;
+----+------------+-------------+----------------+
| id | first_name |  last_name  |   full_name    |
+----+------------+-------------+----------------+
|  1 | Lola       | McDog       | Lola McDog     |
|  2 | Carl       | Kimball     | Carl Kimball   |
|  3 | Ernie      | Narayan     | Ernie Narayan  |
+----+------------+-------------+----------------+

full_name列是从first_namelast_name列计算的,无需定义视图。

使用地理分区和计算列创建表

地理分区是一项企业功能。 申请企业版license,查阅Enterprise Licensing

在这个例子中,让我们创建一个包含地理分区和计算列的表:

CREATE TABLE user_locations (
    locality STRING AS (CASE
      WHEN country IN ('ca', 'mx', 'us') THEN 'north_america'
      WHEN country IN ('au', 'nz') THEN 'australia'
    END) STORED,
    id SERIAL,
    name STRING,
    country STRING,
    PRIMARY KEY (locality, id))
    PARTITION BY LIST (locality)
    (PARTITION north_america VALUES IN ('north_america'),
    PARTITION australia VALUES IN ('australia'));

然后,插入几行数据:

INSERT INTO user_locations (name, country) VALUES
    ('Leonard McCoy', 'us'),
    ('Uhura', 'nz'),
    ('Spock', 'ca'),
    ('James Kirk', 'us'),
    ('Scotty', 'mx'),
    ('Hikaru Sulu', 'us'),
    ('Pavel Chekov', 'au');
SELECT * FROM user_locations;
+---------------+--------------------+---------------+---------+
|   locality    |         id         |     name      | country |
+---------------+--------------------+---------------+---------+
| australia     | 333153890100609025 | Uhura         | nz      |
| australia     | 333153890100772865 | Pavel Chekov  | au      |
| north_america | 333153890100576257 | Leonard McCoy | us      |
| north_america | 333153890100641793 | Spock         | ca      |
| north_america | 333153890100674561 | James Kirk    | us      |
| north_america | 333153890100707329 | Scotty        | mx      |
| north_america | 333153890100740097 | Hikaru Sulu   | us      |
+---------------+--------------------+---------------+---------+

locality列是从country列计算的。 然后可以将位置值用于地理分区。

使用JSONB列和计算列创建表

在这个例子中,让我们创建一个包含JSONB列和计算列的表:

CREATE TABLE student_profiles (
    id STRING PRIMARY KEY AS (profile->>'id') STORED,
    profile JSONB
);

然后,插入几行数据:

INSERT INTO student_profiles (profile) VALUES
    ('{"id": "d78236", "name": "Arthur Read", "age": "16", "school": "PVPHS", "credits": 120, "sports": "none"}'),
    ('{"name": "Buster Bunny", "age": "15", "id": "f98112", "school": "THS", "credits": 67, "clubs": "MUN"}'),
    ('{"name": "Ernie Narayan", "school" : "Brooklyn Tech", "id": "t63512", "sports": "Track and Field", "clubs": "Chess"}');
SELECT * FROM student_profiles;
+--------+---------------------------------------------------------------------------------------------------------------------+
|   id   |                                                       profile                                                       |
+--------+---------------------------------------------------------------------------------------------------------------------+
| d78236 | {"age": "16", "credits": 120, "id": "d78236", "name": "Arthur Read", "school": "PVPHS", "sports": "none"}           |
| f98112 | {"age": "15", "clubs": "MUN", "credits": 67, "id": "f98112", "name": "Buster Bunny", "school": "THS"}               |
| t63512 | {"clubs": "Chess", "id": "t63512", "name": "Ernie Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} |
+--------+---------------------------------------------------------------------------------------------------------------------+

主键id被计算为profile列中的字段。

在计算列上创建具有二级索引的表

在这个例子中,让我们创建一个包含计算列和该列索引的表:

CREATE TABLE gymnastics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    athlete STRING,
    vault DECIMAL,
    bars DECIMAL,
    beam DECIMAL,
    floor DECIMAL,
    combined_score DECIMAL AS (vault + bars + beam + floor) STORED,
    INDEX total (combined_score DESC)
  );

然后,插入几行数据:

INSERT INTO gymnastics (athlete, vault, bars, beam, floor) VALUES
    ('Simone Biles', 15.933, 14.800, 15.300, 15.800),
    ('Gabby Douglas', 0, 15.766, 0, 0),
    ('Laurie Hernandez', 15.100, 0, 15.233, 14.833),
    ('Madison Kocian', 0, 15.933, 0, 0),
    ('Aly Raisman', 15.833, 0, 15.000, 15.366);
SELECT * FROM gymnastics;
+--------------------------------------+------------------+--------+--------+--------+--------+----------------+
|                  id                  |     athlete      | vault  |  bars  |  beam  | floor  | combined_score |
+--------------------------------------+------------------+--------+--------+--------+--------+----------------+
| 3fe11371-6a6a-49de-bbef-a8dd16560fac | Aly Raisman      | 15.833 |      0 | 15.000 | 15.366 |         46.199 |
| 56055a70-b4c7-4522-909b-8f3674b705e5 | Madison Kocian   |      0 | 15.933 |      0 |      0 |         15.933 |
| 69f73fd1-da34-48bf-aff8-71296ce4c2c7 | Gabby Douglas    |      0 | 15.766 |      0 |      0 |         15.766 |
| 8a7b730b-668d-4845-8d25-48bda25114d6 | Laurie Hernandez | 15.100 |      0 | 15.233 | 14.833 |         45.166 |
| b2c5ca80-21c2-4853-9178-b96ce220ea4d | Simone Biles     | 15.933 | 14.800 | 15.300 | 15.800 |         61.833 |
+--------------------------------------+------------------+--------+--------+--------+--------+----------------+

现在,让我们使用二级索引运行查询:

SELECT athlete, combined_score FROM gymnastics ORDER BY combined_score DESC;
+------------------+----------------+
|     athlete      | combined_score |
+------------------+----------------+
| Simone Biles     |         61.833 |
| Aly Raisman      |         46.199 |
| Laurie Hernandez |         45.166 |
| Madison Kocian   |         15.933 |
| Gabby Douglas    |         15.766 |
+------------------+----------------+

最高综合得分为61.833的运动员是Simone Biles。

See Also