CREATE AGGREGATION POLICY

Creates a new aggregation policy in the current/specified schema or replaces an existing aggregation policy.

After creating an aggregation policy, assign the aggregation policy to a table using an ALTER TABLE command or a view using an ALTER VIEW command.

See also:

Aggregation policy DDL reference

语法

CREATE [ OR REPLACE ] AGGREGATION POLICY [ IF NOT EXISTS ] <name>
  AS () RETURNS AGGREGATION_CONSTRAINT -> <body>
  [ COMMENT = '<string_literal>' ]

参数

name

聚合策略的标识符；对于架构必须是唯一的。

The identifier value must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

body

SQL expression that determines the restrictions of an aggregation policy.

To define the constraints of the aggregation policy, use the SQL expression to call one or more of the following functions:

NO_AGGREGATION_CONSTRAINT

When the policy body returns a value from this function, queries can return data from an aggregation-constrained table or view without restriction. For example, the body of the policy could call this function when an administrator needs to obtain unaggregated results from the aggregation-constrained table or view.

Call NO_AGGREGATION_CONSTRAINT without an argument.

AGGREGATION_CONSTRAINT

When the policy body returns a value from this function, queries must aggregate data in order to return results. Use the MIN_GROUP_SIZE argument to specify how many records must be included in each aggregation group.

The syntax of the AGGREGATION_CONSTRAINT function is:

AGGREGATION_CONSTRAINT ( MIN_GROUP_SIZE => <integer_expression> )

Where:

MIN_GROUP_SIZE => integer_expression

Specifies how many rows or entities must be included in the groups returned by a query against the aggregation-constrained table or view.

There is a difference between passing a 1 and a 0 as the argument to the function. Both require results to be aggregated.

  • Passing a 1 also requires that each aggregation group contain at least one record from the aggregation-constrained table. So for outer joins, at least one record from the aggregation-constrained table must match a record from an unprotected table.
  • Passing a 0 allows the query to return groups that consist entirely of records from another table. So for outer joins between an aggregation-constrained table and an unprotected table, a group could consist of records from the unprotected table that do not match any records in the aggregation-constrained table.

The body of a policy cannot reference user-defined functions, tables, or views.

COMMENT = 'string_literal'

为聚合策略添加注释或覆盖现有注释。

访问控制要求

A role used to execute this operation must have the following privileges at a minimum:

权限对象备注
CREATE AGGREGATION POLICY架构

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

For additional details on aggregation policy DDL and privileges, see Privileges and commands.

使用说明

  • If you want to update an existing aggregation policy and need to see the current body of the policy, run the DESCRIBE AGGREGATION POLICY command or GET_DDL function.

  • 关于元数据：

    Attention

    Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

  • The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.

  • CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

示例

创建一个聚合策略，该策略要求查询返回包含五行或更多行的组：

CREATE AGGREGATION POLICY my_policy AS ()
  RETURNS AGGREGATION_CONSTRAINT ->
  AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

Create an aggregation policy that allows a user with role admin to return unaggregated results while requiring all other queries to return groups of five or more rows:

CREATE AGGREGATION POLICY my_policy AS ()
  RETURNS AGGREGATION_CONSTRAINT ->
    CASE
      WHEN CURRENT_ROLE() = 'ADMIN'
        THEN NO_AGGREGATION_CONSTRAINT()
      ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5)
    END;