Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/booland_agg below:

Website Navigation

• Reference
• Function and stored procedure reference
• Aggregate

BOOLAND_AGG | Snowflake Documentation

Categories:

Aggregate functions (Boolean) , Window functions , Conditional expression functions

Returns TRUE if all non-NULL Boolean records in a group evaluate to TRUE.

If all records in the group are NULL, or if the group is empty, the function returns NULL.

See also:

BOOLAND , BOOLOR_AGG , BOOLXOR_AGG

Aggregate function

Window function

BOOLAND_AGG( <expr> )  OVER ( [ PARTITION BY <partition_expr> ] )

Copy

expr

The input expression must be an expression that can be evaluated to a boolean or converted to a boolean.

partition_expr

This column or expression specifies how to separate the input into partitions (sub-windows).

The data type of the returned value is BOOLEAN.

• Numeric values, Decimal, and floating point values are converted to TRUE if they are different from zero.

• Character/text types are not supported as they cannot be converted to Boolean.

• When this function is called as a window function, it does not support:

  • An ORDER BY clause within the OVER clause.

  • Explicit window frames.

Aggregate function

The following example shows that booland_agg returns true when all of the input values are true.

Create and load the table:

create or replace table test_boolean_agg(
    id integer,
    c1 boolean, 
    c2 boolean,
    c3 boolean,
    c4 boolean
    );

insert into test_boolean_agg (id, c1, c2, c3, c4) values 
    (1, true, true,  true,  false),
    (2, true, false, false, false),
    (3, true, true,  false, false),
    (4, true, false, false, false);

Copy

Display the data:

select * from test_boolean_agg;
+----+------+-------+-------+-------+
| ID | C1   | C2    | C3    | C4    |
|----+------+-------+-------+-------|
|  1 | True | True  | True  | False |
|  2 | True | False | False | False |
|  3 | True | True  | False | False |
|  4 | True | False | False | False |
+----+------+-------+-------+-------+

Copy

Query the data:

select booland_agg(c1), booland_agg(c2), booland_agg(c3), booland_agg(c4)
    from test_boolean_agg;
+-----------------+-----------------+-----------------+-----------------+
| BOOLAND_AGG(C1) | BOOLAND_AGG(C2) | BOOLAND_AGG(C3) | BOOLAND_AGG(C4) |
|-----------------+-----------------+-----------------+-----------------|
| True            | False           | False           | False           |
+-----------------+-----------------+-----------------+-----------------+

Copy

Window function

This example is similar to the previous example, but it shows usage as a window function, with the input rows split into two partitions (one for IDs greater than 0 and one for IDs less than or equal to 0). Additional data was added to the table.

Add rows to the table:

insert into test_boolean_agg (id, c1, c2, c3, c4) values
    (-4, false, false, false, true),
    (-3, false, true,  true,  true),
    (-2, false, false, true,  true),
    (-1, false, true,  true,  true);

Copy

Display the data:

select * 
    from test_boolean_agg
    order by id;
+----+-------+-------+-------+-------+
| ID | C1    | C2    | C3    | C4    |
|----+-------+-------+-------+-------|
| -4 | False | False | False | True  |
| -3 | False | True  | True  | True  |
| -2 | False | False | True  | True  |
| -1 | False | True  | True  | True  |
|  1 | True  | True  | True  | False |
|  2 | True  | False | False | False |
|  3 | True  | True  | False | False |
|  4 | True  | False | False | False |
+----+-------+-------+-------+-------+

Copy

Query the data:

select 
      id,
      booland_agg(c1) OVER (PARTITION BY (id > 0)),
      booland_agg(c2) OVER (PARTITION BY (id > 0)),
      booland_agg(c3) OVER (PARTITION BY (id > 0)),
      booland_agg(c4) OVER (PARTITION BY (id > 0))
    from test_boolean_agg
    order by id;
+----+----------------------------------------------+----------------------------------------------+----------------------------------------------+----------------------------------------------+
| ID | BOOLAND_AGG(C1) OVER (PARTITION BY (ID > 0)) | BOOLAND_AGG(C2) OVER (PARTITION BY (ID > 0)) | BOOLAND_AGG(C3) OVER (PARTITION BY (ID > 0)) | BOOLAND_AGG(C4) OVER (PARTITION BY (ID > 0)) |
|----+----------------------------------------------+----------------------------------------------+----------------------------------------------+----------------------------------------------|
| -4 | False                                        | False                                        | False                                        | True                                         |
| -3 | False                                        | False                                        | False                                        | True                                         |
| -2 | False                                        | False                                        | False                                        | True                                         |
| -1 | False                                        | False                                        | False                                        | True                                         |
|  1 | True                                         | False                                        | False                                        | False                                        |
|  2 | True                                         | False                                        | False                                        | False                                        |
|  3 | True                                         | False                                        | False                                        | False                                        |
|  4 | True                                         | False                                        | False                                        | False                                        |
+----+----------------------------------------------+----------------------------------------------+----------------------------------------------+----------------------------------------------+

Copy

Error example

If this function is passed strings that cannot be converted to Boolean, the function will give an error:

select booland_agg('invalid type');

100037 (22018): Boolean value 'invalid_type' is not recognized

Copy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4