精华内容
下载资源
问答
  • 在 SQL 数据库中,生成列(Generated Column)是指由表中其他字段计算得到的列,因此也称为计算列(Computed Column)。 本文介绍各种主流数据库对于生成列/计算列的实现,包括 Oracle、MySQL、SQL Server、...

    生成列

    什么是生成列?

    在 SQL 数据库中,生成列(Generated Column)是指由表中其他字段计算得到的列,因此也称为计算列(Computed Column)

    生成列存在两种类型:存储(stored)生成列和虚拟(virtual)生成列。存储生成列和普通列类似,在插入或者更新数据时自动计算并且存储该列的值,需要占用存储空间;虚拟生成列不需要占用存储空间,只在读取时进行计算。因此,虚拟生成列就像是一个视图(字段的视图),而存储生成列就像是一个物化视图(实时更新)。

    ⚠️我们无法直接插入或者更新生成列的值,它的值由数据库自动生成和更新。

    生成列的常见用途包括:

    • 虚拟生成列可以用来简化和统一查询。我们可以将复杂的查询条件定义成一个生成列,然后在查询该表时使用,从而确保所有的查询都使用相同的判断条件。
    • 存储生成列可以作为查询条件的物化缓存(materialized cache),减少查询时的计算成本。
    • 生成列可以模拟函数索引:定义一个基于函数表达式的生成列并且创建索引。对于存储型的生成列,这种方式需要占用更多的存储。

    各种主流 SQL 数据库对于生成列/计算列的支持如下:

    生成列OracleMySQLSQL ServerPostgreSQLSQLite
    存储生成列✔️✔️✔️✔️
    虚拟生成列✔️✔️✔️✔️
    约束支持主键约束
    NOT NULL
    UNIQUE
    CHECK
    外键约束
    主键约束
    NOT NULL
    UNIQUE
    CHECK
    外键约束
    主键约束*
    NOT NULL*
    UNIQUE*
    CHECK*
    外键约束*
    主键约束
    NOT NULL
    UNIQUE
    CHECK
    外键约束

    NOT NULL
    UNIQUE
    CHECK
    外键约束
    索引支持✔️✔️*✔️*✔️✔️

    * 参考下文中的具体讨论。

    Oracle 中的虚拟列

    Oracle 11g 开始支持虚拟的生成列,简称虚拟列。定义虚拟列的语法如下:

    column [ datatype [ COLLATE column_collation_name ] ]
      [ GENERATED ALWAYS ] AS (column_expression) [ VIRTUAL ]
    

    如果省略了 datatype,虚拟列的类型由表达式 column_expression 决定;GENERATED ALWAYS AS表示定义生成列;表达式只能包含当前表中的字段、常量以及确定性的函数;VIRTUAL表示虚拟列,可以省略。以下是一个创建虚拟列的示例:

    CREATE TABLE t_circle(
       id INTEGER PRIMARY KEY,
       x NUMERIC(20,10) NOT NULL,
       y NUMERIC(20,10) NOT NULL,
       radius NUMERIC(20,10) NOT NULL,
       perimeter NUMERIC(20,10) GENERATED ALWAYS AS (2 * 3.14159265 * radius) VIRTUAL
    );
    
    ALTER TABLE t_circle ADD (area AS (3.14159265 * radius * radius));
    

    首先,使用CREATE TABLE语句为表 t_circle 创建了一个虚拟列 perimeter;然后使用ALTER TABLE语句为其增加了一个虚拟列 area。

    接下来我们插入一些数据:

    INSERT INTO t_circle(id, x, y, radius) VALUES (1, 2, 2, 5);
    SELECT * FROM t_circle;
    ID|X|Y|RADIUS|PERIMETER |AREA       |
    --|-|-|------|----------|-----------|
     1|2|2|     5|31.4159265|78.53981625|
    
    INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
    SQL Error [54013] [99999]: ORA-54013: INSERT operation disallowed on virtual columns
    

    第一个插入语句没有指定虚拟列的值,在查询时由数据库自动计算;第二个插入语句指定了虚拟列的值,执行失败。

    Oracle 中的虚拟列支持索引,我们为 t_circle 中的虚拟列创建两个索引:

    CREATE UNIQUE INDEX idx11 ON t_circle(perimeter);
    CREATE INDEX idx12 ON t_circle(area);
    

    除了支持索引之外,虚拟列还支持NOT NULLUNIQUE、主键、CHECK以及外键约束,但是不支持DEFAULT默认值。

    使用 Oracle 虚拟列需要注意以下事项:

    • 如果表达式 column_expression 引用了具有列级安全的字段,虚拟列不会继承基础列上的安全规则。此时,用户需要自己确保虚拟列数据的安全,可以为虚拟列再设置一个列级安全策略或者使用函数对数据进行模糊处理。例如,信用卡号通常会使用一个列级安全策略进行包含,允许客服中心的员工查看最后四位数字以便验证信息。此时,可以定义一个虚拟列保存信用卡号的后四位子串。
    • 基于虚拟列创建的索引等价于函数索引。
    • 不能直接更新虚拟列。因此,不能在UPDATE语句的SET子句中设置虚拟列。不过,可以在UPDATE语句的WHERE子句中使用虚拟列。同理,可以在DELETE语句的WHERE子句中使用虚拟列。
    • FROM子句中使用包含虚拟列的表的查询语句可以缓存结果,具体参考Oracle 官方文档
    • 表达式 column_expression 可以引用明确指定了 DETERMINISTIC 属性的 PL/SQL 函数。但是,如果随后替换了该函数的定义,基于虚拟列的对象不会失效。此时如果表中包含数据,而且虚拟列用于了约束、索引、物化视图或者查询结果缓存,访问虚拟列的查询可能返回不正确的结果。因此,为了替换虚拟列中的确定性函数:
      • 禁用然后再启用虚拟列上的约束。
      • 重建虚拟列上的索引。
      • 完全刷新基于虚拟列的物化视图。
      • 刷新访问了该虚拟列的查询结果缓存。
      • 重新收集该表的统计信息。
    • 虚拟列可以是 INVISIBLE 列,虚拟列的表达式中可以包含 INVISIBLE 列。

    Oracle 中的虚拟列存在以下限制:

    • 只能为关系型的堆表创建虚拟列,索引组织表、外部表、对象表、聚簇表以及临时表不支持虚拟列。
    • 虚拟列表达式 column_expression 存在以下限制:
      • 不能引用其他虚拟列。
      • 只能引用当前表中的列。
      • 可以引用确定性的自定义函数,但此时该虚拟列不能作为分区字段。
      • 表达式的结果必须是一个标量值。
    • 虚拟列不支持 Oracle 提供的数据类型、用户定义类型以及 LOB 和 LONG RAW 类型。
    • 虚拟列可以作为分区字段,但是作为分区字段的虚拟列中不能包含 PL/SQL 函数。

    参考文档:Oracle 官方文档

    MySQL 中的生成列

    MySQL 5.7 引入了生成列,支持虚拟和存储两种类型的生成列。定义生成列的语法如下:

    col_name data_type [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
    

    其中,GENERATED ALWAYS可以省略,AS定义了生成列的表达式;VIRTUAL表示创建虚拟生成列,虚拟列的值不会存储,而是在读取时BEFORE触发器之后立即计算;STORED表示存储生成列;默认创建的是VIRTUAL生成列。

    我们创建一个表 t_circle:

    CREATE TABLE t_circle(
       id INTEGER PRIMARY KEY,
       x NUMERIC(20,10) NOT NULL,
       y NUMERIC(20,10) NOT NULL,
       radius NUMERIC(20,10) NOT NULL,
       perimeter NUMERIC(20,10) AS (2 * 3.14159265 * radius)
    );
    
    ALTER TABLE t_circle ADD area NUMERIC(20,10) AS (3.14159265 * radius * radius) STORED;
    

    其中,perimeter 是一个虚拟的生成列;area 是一个存储的生成列。MySQL 生成列还支持NOT NULLUNIQUE、主键、CHECK以及外键约束,但是不支持DEFAULT默认值。

    MySQL 生成列的表达式必须遵循以下规则:

    • 允许使用常量、确定性的内置函数以及运算符。确定性函数意味着对于表中的相同数据,多次调用返回相同的结果,与当前用户无关。非确定性的函数包括 CONNECTION_ID()、CURRENT_USER()、NOW() 等。
    • 不允许使用存储函数和自定义函数。
    • 不允许使用存储过程和函数的参数。
    • 不允许使用变量(系统变量、自定义变量或者存储程序中的本地变量)。
    • 不允许子查询。
    • 允许引用表中已经定义的其他生成列;允许引用任何其他非生成列,无论这些列出现的位置在前面还是后面。
    • 不允许使用 AUTO_INCREMENT 属性。
    • 不允许使用 AUTO_INCREMENT 字段作为生成列的基础列。
    • 如果表达式的运算导致截断或者为函数提供了不正确的输入,CREATE TABLE 将会失败。

    另外,如果表达式的结果类型与字段定义中的数据类型不同,将会执行隐式的类型转换。

    接下来我们运行一些数据测试:

    INSERT INTO t_circle(id, x, y, radius) VALUES (1, 2, 2, 5);
    SELECT * FROM t_circle;
    id|x           |y           |radius      |perimeter    |area         |
    --|------------|------------|------------|-------------|-------------|
     1|2.0000000000|2.0000000000|5.0000000000|31.4159265000|78.5398162500|
    
    INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
    SQL Error [3105] [HY000]: The value specified for generated column 'perimeter' in table 't_circle' is not allowed.
    

    第一个插入语句没有指定生成列的值,由数据库自动计算;第二个插入语句为 perimeter 提供了数据,执行失败;不过可以使用DEFAULT关键字。

    MySQL 支持存储生成列的索引,InnoDB 还支持虚拟生成列的二级索引,具体参考 MySQL 官方文档 。我们使用以下语句为 t_circle 表的两个生成列分别创建两个索引:

    CREATE UNIQUE INDEX idx1 ON t_circle(perimeter);
    CREATE INDEX idx2 ON t_circle(area);
    

    另外,使用 MySQL 生成列还需要注意以下事项:

    • 对于CREATE TABLE ... LIKE语句,创建的新表将会保留原表中的生成列信息。
    • 对于CREATE TABLE ... SELECT语句,创建的新表不会保留查询语句的原表中的生成列信息;并且SELECT语句不能为目标表中的生成列赋值。
    • 允许基于生成列的分区,具体参考 MySQL 官方文档
    • 存储生成列上的外键约束不能为ON UPDATE操作指定CASCADESET NULL或者SET DEFAULT选项,也不能为ON DELETE操作指定SET NULL 或者SET DEFAULT选项。
    • 存储生成列的基础列上的外键约束也不能为ON UPDATE或者ON DELETE操作指定CASCADESET NULL或者SET DEFAULT选项。
    • 外键约束中的被引用字段不能是虚拟的生成列。
    • 触发器不能通过 NEW.col_name 或者 OLD.col_name 引用生成列。
    • 对于INSERTREPLACE以及UPDATE,如果要明确指定生成列的值,只能使用DEFAULT。视图中的生成列属于可更新列,但是也只能使用DEFAULT显式更新。

    参考文档:MySQL 官方文档

    SQL Server 中的计算列

    SQL Server 2005 增加了生成列的支持,称为计算列。计算列的完整定义如下:

    <computed_column_definition> ::=
    column_name AS computed_column_expression
    [ PERSISTED [ NOT NULL ] ]
    [
        [ CONSTRAINT constraint_name ]
        { PRIMARY KEY | UNIQUE }
            [ CLUSTERED | NONCLUSTERED ]
            [
                WITH FILLFACTOR = fillfactor
              | WITH ( <index_option> [ , ...n ] )
            ]
            [ ON { partition_scheme_name ( partition_column_name )
            | filegroup | "default" } ]
      
        | [ FOREIGN KEY ]
            REFERENCES referenced_table_name [ ( ref_column ) ]
            [ ON DELETE { NO ACTION | CASCADE } ]
            [ ON UPDATE { NO ACTION } ]
            [ NOT FOR REPLICATION ]
      
        | CHECK [ NOT FOR REPLICATION ] ( logical_expression )
    ]
    

    其中,AS表示定义一个计算列;PERSISTED表示需要存储该列的值,即存储型的计算列; 计算列的表达式可以使用其他非计算列、常量、函数、变量,但是不能使用子查询或别名数据类型。SQL Server 中的计算列支持主键、UNIQUE约束,存储计算列还支持NOT NULL、外键以及CHECK约束;但是计算列不支持DEFAULT默认值,也不能作为外键中的被引用字段。

    我们创建一个表 t_circle:

    CREATE TABLE t_circle(
       id INTEGER PRIMARY KEY,
       x NUMERIC NOT NULL,
       y NUMERIC NOT NULL,
       radius NUMERIC NOT NULL,
       perimeter NUMERIC AS (2 * 3.14159265 * radius),
       area NUMERIC AS (3.14159265 * radius * radius) PERSISTED
    );
    
    ALTER TABLE t_circle ADD dt AS GETDATE();
    

    其中,perimeter 是一个虚拟的计算列;area 是一个存储的计算列;ALTER TABLE语句增加了第三个计算列,使用了一个不确定性函数 GETDATE()。

    我们测试一下数据插入和查询:

    INSERT INTO t_circle VALUES (1, 2, 2, 5);
    SELECT * FROM t_circle;
    id|x|y|radius|perimeter  |area     |dt                 |
    --|-|-|------|-----------|---------|-------------------|
     1|2|2|     5|31.41592650|78.539816|2020-02-03 19:02:30|
    
    INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
    SQL Error [271] [S0001]: The column "perimeter" cannot be modified because it is either a computed column or is the result of a UNION operator.
    

    第一个插入语句没有指定生成列的值,由数据库自动计算;查询返回了所有的字段,多次运行的话 dt 字段将会返回不同的日期;第二个插入语句为 perimeter 提供了数据,执行失败;INSERTUPDATE语句不能为生成列指定值。

    SQL Server 支持基于计算列的索引,但是需要满足一定的条件:

    create unique index idx1 on t_circle(perimeter);
    create index idx2 on t_circle(area);
    
    create index idx3 on t_circle(dt);
    SQL Error [2729] [S0001]: Column 'dt' in table 't_circle' cannot be used in an index or statistics or as a partition key because it is non-deterministic.
    

    前两个计算列不包含不确定性的函数,可以创建索引,或者 PRIMARY KEY 和 UNIQUE 约束;但是 dt 列不支持索引,因为它包含了不确定性的函数,每次调用时它的值可能发生变化。

    参考文档:SQL Server 官方文档

    PostgreSQL 中的存储生成列

    PostgreSQL 12 提供了生成列,目前只支持存储型的生成列。通过在CREATE TABLE或者ALTER TABLE语句中指定字段的GENERATED ALWAYS AS约束来创建一个生成列:

    column_name data_type [ COLLATE collation ]
    [ CONSTRAINT constraint_name ]
    GENERATED ALWAYS AS ( generation_expr ) STORED
    

    其中,GENERATED ALWAYS AS表示创建生成列;generation_expr 指定了生成列的表达式;STORED意味着需要存储该列的值。例如以下语句:

    CREATE TABLE t_circle(
       id INTEGER PRIMARY KEY,
       x NUMERIC NOT NULL,
       y NUMERIC NOT NULL,
       radius NUMERIC NOT NULL,
       perimeter NUMERIC GENERATED ALWAYS AS (2 * 3.14159265 * radius) STORED
    );
    
    ALTER TABLE t_circle ADD area NUMERIC GENERATED ALWAYS AS (3.14159265 * radius * radius) STORED;
    

    首先,CREATE TABLE语句为表 t_circle 定义了一个生成列 perimeter,表示圆的周长。然后,使用ALTER TABLE语句增加一个生成列 area ,表示圆的面积。

    接下来我们插入一些数据:

    INSERT INTO t_circle VALUES (1, 2, 2, 5);
    SELECT * FROM t_circle;
    id|x|y|radius|perimeter  |area       |
    --|-|-|------|-----------|-----------|
     1|2|2|     5|31.41592650|78.53981625|
    
    INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
    SQL Error [42601]: ERROR: cannot insert into column "perimeter"
      Detail: Column "perimeter" is a generated column.
    

    第一个插入语句没有指定生成列的值,由数据库自动计算;第二个插入语句为 perimeter 提供了数据,执行失败;INSERTUPDATE语句不能为生成列指定值,不过可以使用DEFAULT关键字。

    PostgreSQL 中的生成列支持索引,我们使用以下语句为 t_circle 表的两个生成列分别创建两个索引:

    create unique index idx1 on t_circle(perimeter);
    create index idx2 on t_circle(area);
    

    第一个索引 idx1 是唯一索引,第二个索引 idx2 是普通索引。

    除了支持索引之外,PostgreSQL 生成列还支持NOT NULLUNIQUE、主键、CHECK以及外键约束,但是不支持DEFAULT默认值。另外,在 PostgreSQL 中使用生成列存在一些限制:

    • 生成列的表达式只能使用不可变(IMMUTABLE)函数,不能使用子查询或者引用非当前数据行的任何其他数据。
    • 生成列的表达式不能引用其他的生成列。
    • 生成列的表达式不能引用除了 tableoid 之外的其他系统字段。
    • 生成列不能指定默认值或者标识列。
    • 生成列不能作为分区键的一部分。
    • 外部表可以支持生成列,参考 CREATE FOREIGN TABLE

    使用生成列时还需要注意以下事项:

    • 生成列的访问权限控制与其表达式中引用的基础列无关。因此,一个用户可能无法读取基础列中的数据,但是可以读取生成列的数据,实现特定的数据安全访问。
    • 从逻辑上讲,生成列的值在 BEFORE 触发器之后进行更新。在 BEFORE 触发器中对基础列的修改会同步到生成列中;但是反过来,在 BEFORE 触发器中不能访问生成列的值。

    参考文档:PostgreSQL 生成列

    SQLite 中的生成列

    SQLite 3.31.0 开始支持生成列,语法上通过“GENERATED ALWAYS”字段约束实现:

    SQlite
    其中的GENERATED ALWAYS可以省略;STORED表示存储型的生成列,VIRTUAL表示虚拟型的生成列,省略的话默认为后者。例如以下示例表:

    CREATE TABLE t_circle(
       id INTEGER PRIMARY KEY,
       x NUMERIC NOT NULL,
       y NUMERIC NOT NULL,
       radius NUMERIC NOT NULL,
       perimeter NUMERIC GENERATED ALWAYS AS (2 * 3.14159265 * radius) VIRTUAL,
       area NUMERIC GENERATED ALWAYS AS (3.14159265 * radius * radius) STORED
    );
    

    其中 radius 表示圆的半径;perimeter 是一个虚拟生成列,表示圆的周长;area 是一个存储生成列,表示圆的面积。

    ⚠️SQLite 中的ALTER TABLE ADD COLUMN命令只能增加VIRTUAL生成列,不支持STORED生成列。

    接下来我们插入一些数据:

    sqlite> INSERT INTO t_circle VALUES (1, 2, 2, 5);
    sqlite> SELECT * FROM t_circle;
    1|2|2|5|31.4159265|78.53981625
    
    sqlite> INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
    Error: cannot INSERT into generated column "perimeter"
    

    第一个插入语句执行成功,查询返回了两个生成列的值;第二个插入语句尝试指定生成列的值,返回了错误。

    SQLite 中的生成列支持以下功能:

    1. 生成列可以指定数据类型。SQLite 使用与普通字段相同的类型亲和性将表达式的结构转换为该类型。
    2. 生成列可以像普通字段一样指定 NOT NULL、CHECK 以及 UNIQUE 约束和外键约束。
    3. 生成列可以像普通字段一样支持索引。
    4. 生成列的表达式可以引用表中的任何列,包括其他的生成列,只要该表达式不会直接或者间接引用自身。
    5. 生成列可以出现在表定义中的任何位置。生成列可以出现在普通列的中间,不一定需要位于字段列表的最后。

    我们可以使用以下语句为 t_circle 表的两个生成列分别创建两个索引:

    sqlite> create unique index idx1 on t_circle(perimeter);
    sqlite> create index idx2 on t_circle(area);
    

    另一方面,SQLite 中的生成列目前还存在一些限制:

    1. 生成列不能指定默认值(DEFAULT)。生成列的值总是由 AS 关键字后的表达式决定。
    2. 生成列不能作为 PRIMARY KEY 的一部分。将来的 SQLite 可能会支持基于 STORED 生成列的主键。
    3. 生成列的表达式只能引用常量字面值和其他字段,或者确定性的标量函数。表达式中不能使用子查询、聚合函数、窗口函数或者表函数。
    4. 生成列的表达式可以引用其他的生成列,但是不能引用直接或者间接依赖于它自身的其他生成列。
    5. 生成列的表达式不能直接引用 ROWID,但是可以引用 INTEGER PRIMARY KEY 列,虽然两者效果相同。
    6. 每个表至少需要包含一个非生成的普通列。
    7. ALTER TABLE ADD COLUMN 语句不支持 STORED 生成列,但是可以添加 VIRTUAL 生成列。
    8. 生成列的数据类型和字符排序顺序由字段定义中的数据类型和 COLLATE 子句决定,与 GENERATED
      ALWAYS AS 表达式的数据类型和字符排序顺序无关。

    参考文档:SQLite 官方文档

    欢迎关注❤️、点赞👍、转发📣!

    展开全文
  • MySQL中的Generated Column列(计算列

    千次阅读 2020-08-05 08:55:28
    MySQL中的Generated Column列(计算列) MySQL的Generated Column又称为虚拟列或计算列。Generated Column列的值是在列定义时包含了一个计算表达式计算得到的。 一、定义Generated Column列的语法格式 定义Generated...

    MySQL中的Generated Column列(计算列)

    MySQL的Generated Column又称为虚拟列或计算列。Generated Column列的值是在列定义时包含了一个计算表达式计算得到的。

    一、定义Generated Column列的语法格式

    定义Generated column列的语法如下:

    列名 类型
    [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
    [NOT NULL | NULL] 
    [UNIQUE [KEY]] 
    [[PRIMARY] KEY]
    [COMMENT 'string']
    

    说明:
    (1)AS(expr)用于生成计算列值的表达式。
    (2)VIRTUAL或STORED关键字表示是否存储计算列的值:
    VIRTUAL:列值不存储,虚拟列不占用存储空间,默认设置为VIRTUAL。
    STORED:在添加或更新行时计算并存储列值。存储列需要存储空间,并且可以创建索引。

    二、计算列表达式的要求

    Generated column表达式必须遵循以下规则。如果表达式包含不允许的定义方式,则会发生错误。

    (1)允许使用文本、内置函数和运算符,但不能使用返回值不确定的函数,比如NOW()。
    (2)不允许使用存储函数和用户定义函数。
    (3)不允许使用存储过程和函数参数。
    (4)不允许使用变量(系统变量、用户定义变量和存储程序的局部变量)。
    (5)不允许子查询。
    (6)计算列在定义时可以引用其他的计算列,但只能引用表定义中较早出现的列。
    (7)可以在计算列上创建索引,但不能在VIRTUAL类型的计算列上创建聚集索引。

    三、计算列举例

    1、表的定义

    mysql> create table sales(
        -> goods_id int primary key,
        -> goods_name char(20),
        -> unit_price int,
        -> quantity int,
        -> amount int generated always as (unit_price*quantity));
    Query OK, 0 rows affected (0.02 sec)
    

    2、插入数据

    mysql> insert into sales(goods_id,goods_name,unit_price,quantity) values(100101,'Apple',2,4);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from sales;
    +----------+------------+------------+----------+--------+
    | goods_id | goods_name | unit_price | quantity | amount |
    +----------+------------+------------+----------+--------+
    |   100101 | Apple      |          2 |        4 |      8 |
    +----------+------------+------------+----------+--------+
    1 row in set (0.00 sec)
    

    3、查看创建表的语句

    可见,计算列的默认类型为VIRTUAL。

    mysql> show create table sales\G
    *************************** 1. row ***************************
           Table: sales
    Create Table: CREATE TABLE `sales` (
      `goods_id` int(11) NOT NULL,
      `goods_name` char(20) DEFAULT NULL,
      `unit_price` int(11) DEFAULT NULL,
      `quantity` int(11) DEFAULT NULL,
      `amount` int(11) GENERATED ALWAYS AS ((`unit_price` * `quantity`)) VIRTUAL,
      PRIMARY KEY (`goods_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    

    去式

    create table json_tab
    (
      id        int unsigned primary key auto_increment comment '主键',
      json_info json comment 'json数据',
      json_id   int generated always as (json_info -> '$.id') comment 'json数据的虚拟字段',
      index json_info_id_idx (json_id)
    )
    
    展开全文
  • SQL SERVER跨表计算列

    千次阅读 2018-12-17 09:17:06
    我们的表字段有时候要用到计算列,有时候计算列需要跨表计算,但是直接加入查询语句报错,我们可以使用函数来实现跨表计算列功能。  测试数据: --测试数据 if not object_id(N'user') is null drop table ...

           我们的表字段有时候要用到计算列,有时候计算列需要跨表计算,但是直接加入查询语句报错,我们可以使用函数来实现跨表计算列功能。

           测试数据:

    --测试数据
    if not object_id(N'user') is null
    	drop table [user]
    Go
    Create table [user]([uid] int)
    Insert [user]
    select 1001 union all
    select 1002 union all
    select 1003 union all
    select 1004 union all
    select 1005
    GO
    if not object_id(N'adver') is null
    	drop table adver
    Go
    Create table adver([aid] int,[uid] int)
    Insert adver
    select 101,1001 union all
    select 102,1001 union all
    select 103,1002 union all
    select 104,1001 union all
    select 105,1003 union all
    select 106,1005 union all
    select 107,1003 union all
    select 108,1003 union all
    select 109,1005 union all
    select 110,1003 union all
    select 111,1004 union all
    select 112,1004
    Go
    --测试数据结束

           如果我们这样加计算列,会报错:

    ALTER table [user]
    add sumclo AS (SELECT COUNT(1) FROM dbo.adver WHERE dbo.adver.uid = dbo.[user].uid)

           我们可以换个角度,用函数实现:

    CREATE FUNCTION dbo.Get_RowCount (@uid INT)
    RETURNS INT
    AS
    BEGIN
        DECLARE @rowcount INT;
        SELECT @rowcount = COUNT(1)
        FROM adver
        WHERE adver.uid = @uid;
        RETURN @rowcount;
    END;
    GO
    

           添加计算列:

    ALTER table [user]
    add sumclo AS dbo.Get_RowCount(uid)

           测试结果:

    Select * from [user]

           这样我们就是实现了跨表的计算列。

    展开全文
  • 但是,你会创建定义表值的数据分析表达式 (DAX) 公式,而非从数据源中查询值,并将值加载到新表的中。在 Power BI Desktop 中,计算表是通过使用报表视图或数据视图中的“新建表”功能创建的。 优势:大多情况下...

    计算表

    借助计算表,可以将新表添加到模型中。 但是,你会创建定义表值的数据分析表达式 (DAX) 公式,而非从数据源中查询值,并将值加载到新表的列中。 在 Power BI Desktop 中,计算表是通过使用报表视图或数据视图中的“新建表”功能创建的。

     优势:大多情况下的数据都是由外部添加到模型中,但是如果某些表的数据你想用来查询而非计算的时候,就能很大的体现就算表的好处,典型的例子就是时间维表。

    当然与其他添加的表一样计算表可以跟其他表建立表关系,计算表中的列具有数据类型、格式设置,并能归属于数据类别。 你可以随意对列进行命名,并将其像其他字段一样添加到报表可视化效果。 如果计算表从其中提取数据的任何表以任何形式进行了刷新或更新,则将重新计算计算表。

    如图计算表达到了匹配订单表的订单日期,如果要赛选某个时间段下的数据,只需把时间表对应的时间拖入即可。

     计算列

    凭借计算列,你可以将新数据添加到模型中已存在的表。 但请勿从数据源查询并将值加载到新列中,而是创建用于定义列值的数据分析表达式 (DAX) 公式。 在 Power BI Desktop 中,使用“报表”视图中的“新建列”功能创建计算列。

    与使用查询编辑器中的“添加自定义列”创建为查询的一部分的自定义列不同,在报表视图或数据视图中创建的计算列以你已加载到模型中的数据为基础。 例如,可以选择连接两个不同但相关的表中的值、执行添加或提取子字符串。

    像任何其他字段一样,你创建的计算列将显示在“字段”列表中,但它们将带有特殊图标,显示其值为公式的结果。 你可以随意对列进行命名,并将其像其他字段一样添加到报表可视化效果。

    我们来看一个简单的例子

    通过右键字段或者表选择新建计算列,然后写入地区&城市 = '订单'[地区]&","&'订单'[城市],这里解释一下‘表名’[列名]这是规范的字段定位标准。

    匹配的结果如图

     

    度量值

    使用 Power BI Desktop,只需点几下鼠标,即可创建数据见解。 但有时候,这些数据并不包含解决某些重要问题所需的全部内容。度量值可以帮助你解决该问题。

    度量值用于一些最常见的数据分析。 例如,求和、平均值、最小值或最大值、计数,或自己使用 DAX 公式创建的更高级的计算。 度量值的计算结果也始终随着你与的报表的交互而改变,以便进行快速和动态的临时数据浏览。 让我们仔细了解下。

    如上两图一个计算逻辑Total Sales 可以根据唯独的不同自定义group by的道理一般去计算,这可比计算列有趣多了,可以更具不同的维度做相同的计算。

    计算列和度量值的对比,在我的项目经验中能用度量值解决的尽量用度量值解决,因为它不占内存空间存储的只是存其计算逻辑,计算列的存在一般是用于表关系关联,或者用该计算列做筛选条件的时候才会建立,因为它的存在占内存空间会让整个报表变得沉重和慢速。

     

     

    展开全文
  • 目标表达式>[,<目标表达式>] … FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句) [AS]<别名> [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表...
  • Flink 1.10.0 SQL DDL中如何定义watermark和计算列

    千次阅读 热门讨论 2020-02-26 00:33:24
    随着Flink1.10.0版本的发布,在SQL上一个重大的优化是支持了watermark语义的计算,在之前的Flink1.9.x版本中是不支持的,当时只能用SQL DDL进行proces...
  • SQL Server中的计算列

    千次阅读 2018-08-18 09:36:06
    零、码仙励志 人最大的对手,往往不是别人,而是自己的懒惰。别指望撞大运,运气不可能永远在你身上,任何时候都要靠本事吃饭。你必须拼尽全力,才有资格说自己的运气不好 一、建库和建表 create database scort...
  • Power bi 计算列和度量值的区别

    千次阅读 2019-11-13 16:36:18
    计算列 指标 计算方式 基于当前行 没有行的概念,不会基于行进行计算 名字 Table[column] [measure] 范围 一定...
  • Power BI 计算列、度量值、新建表

    千次阅读 2020-09-01 16:04:38
    计算列中,其上下文是明确的,就是当前行,并且可以根据需要缩小为当前行的某一列。计算列写好代码,数据自动计算出来,并且不受外部筛选器的影响。 度量值,编写度量值时,并没有明确的上下文,度量值写好之后,...
  • SqlServer中计算列详解

    千次阅读 2016-10-31 23:12:00
    计算列区别于需要我们手动或者程序给予赋值的列,它的值来源于该表中其它列的计算值。比如,一个表中包含有数量列Number与单价列Price,我们就可以创建计算列金额Amount来表示数量*单价的结果值,创建Amount列后,在...
  • 院系列为计算列(取学号列的第 3 和第 4 个字符) ,并且院系值参照院系表的编号值(院 系表是被参照表,主关键字是编号;参照表是学生表,外部关键字是院系) ,此约束说明 一名学生一定属于某个院系; ? ? ? 姓...
  • 若要使用具有唯一字段的数据创建关系,例如,当数据中存在“区号”和“本地电话”列时,你可以通过将两者的值合并创建新的“完整电话号码”计算列计算列是用于快速创建模型和可视化效果的有用工具。若要创建计算列...
  • SQL Server计算列

    千次阅读 2017-03-14 15:38:16
    计算列由可以使用同一表中的其他列的表达式计算得来。表达式可以是非计算列的列名、常量、函数,也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。  例如,在 AdventureWorks 示例...
  • SQL SERVER 创建计算列

    千次阅读 2014-08-27 10:23:19
    计算列以创建表或修改表的时候定义的表达式为基础,除非使用了PERSISTED关键字,否则计算列不会物理保存在表中。 在这个技巧中,我会给出创建计算列的演示,并且介绍使用SQL Server 2005的PERSISTED选项的方法...
  • SQL数据库——计算列

    千次阅读 2015-05-26 11:47:40
    SQL Server提供了计算列可以帮助我们解决这个问题。 由于存储计算列需要额外的空间并且函数复杂的话需要CPU资源,所以大家在使用的时候需要先做测试。 接下来说一下什么是计算列:(我这种菜鸟居然连这都不...
  • Sql Server 2008——查询(1)——计算列

    千次阅读 热门讨论 2016-06-04 18:54:41
    所谓的计算列就是对表中的字段进行计算从而达到想要的效果。 查询所有信息: select * from Employees --*表示所有信息 --from Employees:表示在Employees中查询 查询效果如下: 查询固定列: 比如:我们查询上个...
  • Total是自定义的,字段值是根据OrderPrice*OrderCount计算得道的,在点击Total表头时,会显示Expression Editor,用它重新定义字段的计算规则 第一步:添加一个GridControl,设置在父容器停靠,设置数据源;...
  • Bootstrap-table 表格计算列的和----总计

    万次阅读 2017-12-13 15:02:47
    Bootstrap-table 计算表格的和—-总计 html中 < th data -field = "purchaseQuantity" data -align = "center" data -footer -formatter = "sumQuantityFormatterModal" 注意 :table要设置...
  • 前言:在理解计算列上的索引之前,先了解计算列的基本知识。计算列由可以使用同一表中的其他列的表达式计算得来。表达式可以是非计算列的列名、常量、函数,也可以是用一个或多个运算符连接的上述元素的任意组合。...
  • 我的电脑系统:Windows 10 64位 SQL Server 软件版本: SQL Server 2014 Express 本篇博客里面使用了 scott 库,如何你现在还没有添加这个库到...计算列select * from emp; -- * 表示所有的 -- * from emp 表示从emp
  • powerbuilder datawindow中插入计算列

    千次阅读 2012-12-06 16:23:37
    在pb中创建一个gird类型的datawindow,设置好数据源...此时可以在columns中选择要进行计算的列,并在左侧选择运算符,OK后,dw会在最后一列生产一个计算列。如图: 此例中计算ljs和ckl两列的乘积,因此在最右侧
  • MATLAB计算列联表的卡方统计量 如题,最近在备考数据分析,不知道手算的对不对,就写了个简单的MATLAB程序来验证答案. 真不是手懒 真不是懒得打开SPSS,我写完了我才想起来能用SPSS MATLAB代码如下: %% 计算交叉列联...
  • 作用:计算列之间的相关性,不包括缺省值 参数说明: method:可选值为{‘pearson’, ‘kendall’, ‘spearman’} pearson:Pearson相关系数来衡量两个数据集合是否在一条线上面,即针对线性数据的相关系数计算,...
  • PB在计算列中获取上一行数据

    千次阅读 2014-02-09 11:48:23
    1、获取上一行计算列: Describe( "evaluate( '计算列名', "+string(getrow()-1)+ ") ") 2、获取上一行字段值:字段名[-1]
  • 1.计算列(Computed Column)定义  计算列由可以使用同一表中的其他列的表达式计算得来。表达式可以是非计算列的列名、常量、函数,也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。 2...
  • datagridview 计算列成绩之和

    千次阅读 2010-11-29 22:15:00
    datagridview 计算列成绩之和
  • df_grouped = df.groupby(‘Month_of_Year’)[‘CallsPresented’].mean()
  • 计算总和的sql

    千次阅读 2019-12-15 21:51:54
    select SUM(字段名称) from 表名

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,135,082
精华内容 454,032
关键字:

计算列