3.103.173.75.2.15.4.1

计算列

计算列通过列定义中包含的表达式从其他列生成的数据。

存储计算列在插入或更新行时进行计算，并将结果值存储在主索引中，类似于非计算列。

使用计算列意义

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

  • 可以在计算列上创建二级索引，这在经常对表进行排序时特别有用，以下有示例说明。

  • JSONB列用于存储半结构化JSONB数据。计算列允许以下用例：一个包含一个列和一个JSONB列的表payload，其主键是根据该payload列的一个字段计算得出的，以下有示例说明。

说明事项

计算列：

  • 不能用于生成其他计算列。
  • 行为与任何其他列一样，除了它们不能直接写入。

虚拟计算列：

  • 不存储在表的主索引中。
  • 当表达式中的列数据更改时重新计算。
  • 不能用作FAMILY定义、CHECK约束或FOREIGN KEY约束的一部分。
  • 不能是外键引用。
  • 不能存储在索引中。
  • 可以是索引列。

定义计算列

要定义存储计算列，请使用以下语法：

column_name <type> as (<expr>) stored

要定义虚拟计算列，请使用以下语法：

column_name <type> as (<expr>) virtual
范围描述
{index_name}计算列的名称。
<type>计算列的数据类型。
<expr>用于计算列值的不可变表达式。
stored计算列与其他列一起存储。
virtual计算列是虚拟的，这意味着列数据不存储在表的主索引中。

例子

创建一个包含存储计算列的表

create table cust_info (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        tell_no string,
        first_name string,
        last_name string,
        all_name string AS (concat(first_name, ' ', last_name)) stored,
        card_no string
);

insert into cust_info (first_name, last_name) values
    ('liu', 'dehua'),
    ('zhang', 'zijian'),
    ('ren', 'xianqi');

table cust_info;

                   id                  | tell_no | first_name | last_name |   all_name   | card_no
---------------------------------------+---------+------------+-----------+--------------+----------
  1ff89b14-d27e-4ad8-b6e7-1d14f6a2dad6 | NULL    | liu        | dehua     | liu dehua    | NULL
  a4413c62-3f99-4bf3-b0a2-2f9770887304 | NULL    | zhang      | zijian    | zhang zijian | NULL
  b17080f0-4a73-4364-966f-89e29937a3fc | NULL    | ren        | xianqi    | ren xianqi   | NULL

该all_name列是根据first_name和last_name列计算的

创建一个表，其中包含一个JSONB列和一个存储的计算列

创建一个包含一JSONB列和一个存储的计算列的表：

此示例显示如何添加具有强制类型的存储计算列：


create table json_test (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    jsoninfor JSONB
);

insert into json_test (jsoninfor) VALUES ('{"aml": "111.11"}');

alter table json_test ADD COLUMN aml DECIMAL AS ((jsoninfor->>'aml')::DECIMAL) stored;


select * from json_test;
                   id                  |     jsoninfor     |  aml
---------------------------------------+-------------------+---------
  8d3968da-ed51-42f0-9446-13f447da4d53 | {"aml": "111.11"} | 111.11

使用JSONB数据创建虚拟计算列

在此示例中，创建一个包含JSONB列和虚拟计算列的表：

create table custinfo (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    testdata jsonb,
    all_name string AS (concat_ws(' ',testdata->>'firstName', testdata->>'lastName')) VIRTUAL
);
insert into custinfo (testdata) VALUES
    ('{"id": "d11111", "firstName": "Arthur", "lastName": "Read", "school": "PVPHS", "credits": 100, "sports": "none"}'),
    ('{"firstName": "Buster", "lastName": "Bunny",  "id": "f92222", "school": "THS", "credits": 24, "clubs": "MUN"}'),
    ('{"firstName": "Ernie", "lastName": "Narayan", "school" : "Brooklyn Tech", "id": "t00074", "sports": "Track and Field", "clubs": "Chess"}');
select * from custinfo;
                   id                  |                                                                testdata                                                                 |   all_name
---------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+----------------
  308b8137-a20f-43f8-94a8-a66a79dea124 | {"clubs": "MUN", "credits": 24, "firstName": "Buster", "id": "f92222", "lastName": "Bunny", "school": "THS"}                            | Buster Bunny
  613ce51c-b25b-4dec-8958-317d56425a72 | {"credits": 100, "firstName": "Arthur", "id": "d11111", "lastName": "Read", "school": "PVPHS", "sports": "none"}                        | Arthur Read
  e1d9c1e0-0e89-410f-b46e-9ed265466db5 | {"clubs": "Chess", "firstName": "Ernie", "id": "t00074", "lastName": "Narayan", "school": "Brooklyn Tech", "sports": "Track and Field"} | Ernie Narayan

虚拟列all_name的计算来自testdata列数据的字段

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

创建一个表，其中包含虚拟计算列和该列的索引：

create table totalby (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tname STRING,
    a decimal,
    b decimal,
    c decimal,
    d decimal,
    t_score DECIMAL as (a + b + c + d) VIRTUAL,
    index total ( t_score desc)
  );

然后，插入几行数据：

insert into totalby (tname, a , b , c , d) values
    ('zahngsan', 15.111, 14.823, 25.600, 12.400),
    ('lisi', 0, 15.766, 0, 0),
    ('wangwu', 14.500, 0, 16.243, 14.563),
    ('liuliu', 0, 15.453, 0, 0),
    ('cluo', 11.233, 0, 12.440, 14.116);

现在，使用二级索引运行查询：

select tname,t_score from totalby  order by t_score desc;
   tname   | t_score
-----------+----------
  zahngsan |  67.934
  wangwu   |  45.306
  cluo     |  37.789
  lisi     |  15.766
  liuliu   |  15.453

将计算列添加到现有表

创建一个表：

create table abc (
    a INT NULL,
    b INT NULL AS (a * 3) stored,
    c INT NULL AS (a + 5) stored,
    FAMILY "primary" (a, b, rowid, c)
  );

然后，插入一行数据：

insert into abc values (2);
table abc;
  a | b | c
----+---+----
  2 | 6 | 7
  2 | 6 | 7

现在向表中添加另一个虚拟计算列：

alter table  abc add column d int AS (a // 2) VIRTUAL;
table abc;
  a | b | c | d
----+---+---+----
  2 | 6 | 7 | 1
  2 | 6 | 7 | 1

更改计算列的公式

数据准备：

create table abc (
    a INT NULL,
    b INT NULL AS (a * 3) stored,
    c INT NULL AS (a + 5) stored,
    FAMILY "primary" (a, b, rowid, c)
  );
alter table  abc add column d int AS (a // 2) stored;

要更改计算列的公式，必须DROP列，并用ADD语句重新定义列：

set sql_safe_updates = false;
alter table abc drop column d;
alter table abc add  column d int as (a // 4) stored;
set sql_safe_updates = true;

将计算列转换为常规列

可以使用ALTER TABLE语句将存储的计算列转换为常规列。

创建一个带有计算列的表：

create table zoos (
    id INT PRIMARY KEY,
    first_name string,
    last_name string,
    all_name string AS (CONCAT(first_name, ' ', last_name)) stored
  );

表中数据准备

insert into zoos (id, first_name, last_name) values
    (10, 'pa', 'hawei'),
    (20, 'ma', 'li'),
    (30, 'da', 'xinging');
select * from zoos;
  id | first_name | last_name |  all_name
-----+------------+-----------+-------------
  10 | pa         | hawei     | pa hawei
  20 | ma         | li        | ma li
  30 | da         | xinging   | da xinging

该all_name列是根据first_name和last_name列计算得来的

show columns from zoos;
  column_name | data_type | is_nullable | column_default |       generation_expression        |  indices  | is_hidden
--------------+-----------+-------------+----------------+------------------------------------+-----------+------------
  id          | INT8      |    false    | NULL           |                                    | {primary} |   false
  first_name  | STRING    |    true     | NULL           |                                    | {}        |   false
  last_name   | STRING    |    true     | NULL           |                                    | {}        |   false
  all_name    | STRING    |    true     | NULL           | concat(first_name, ' ', last_name) | {}        |   false

将计算列all_name转换为常规列：

alter table zoos alter column all_name drop stored;

检查是否转换成功

show columns from zoos;
  column_name | data_type | is_nullable | column_default |       generation_expression        |  indices  | is_hidden
--------------+-----------+-------------+----------------+------------------------------------+-----------+------------
  id          | INT8      |    false    | NULL           |                                    | {primary} |   false
  first_name  | STRING    |    true     | NULL           |                                    | {}        |   false
  last_name   | STRING    |    true     | NULL           |                                    | {}        |   false
  all_name    | STRING    |    true     | NULL           |                                    | {}        |   false

重新准备数据

insert into zoos (id, first_name, last_name,all_name) values (40, 'paper', 'tiger', 'This is null');

再次展示数据

select * from zoos;
  id | first_name | last_name |   all_name
-----+------------+-----------+---------------
  10 | pa         | hawei     | pa hawei
  20 | ma         | li        | ma li
  30 | da         | xinging   | da xinging
  40 | paper      | tiger     | This is null
Copyright © 2024 天云融创数据科技（北京）有限公司京ICP备15014755号-3