Is there an aggregate function AND / OR for boolean types in Snowflake SQL?

Question

Given that I have a table like this

ID  X
1   TRUE
1   FALSE
2   FALSE
2   FALSE

I would like to achieve the following result:

ID  BOOL_OR(X)
1   TRUE
2   FALSE

That is , I want to GROUP BY ID and aggregate the boolean values of X using the boolean OR operator.

PostgreSQL has bool_or() that I can use like this

with t1 as
         (
             select *
             from (values (1, true)
                        , (1, false)
                        , (2, false)
                        , (2, false)) AS t1 (id, x)
         )
select id, bool_or(x)
from t1
group by id;
-- results
ID  BOOL_OR(X)
1   TRUE
2   FALSE

The same query in Snowflake SQL gives SQL compilation error: Invalid identifier BOOL_OR, which is no surprise since the Snowflake documentation for Aggregate functions does not list BOOL_OR.

So my question is there some other alternative way to get the same effects as PostgreSQL's bool_or and bool_and in Snowflake SQL?

Answer 1

UPDATE 2019-11-04

Snowflake recently introduced BOOLOR_AGG and BOOLAND_AGG functions that should provide the desired functionality.

Original answer

MIN and MAX functions in Snowflake seem to do what you would expect for booleans, with MIN working like BOOL_AND / AND_AGG and with MAX working like BOOL_OR / OR_AGG.

See this example:

create or replace table x(col1 boolean, col2 boolean, col3 boolean);
insert into x values(true, true, false),(true,false,false);
select * from x;
------+-------+-------+
 COL1 | COL2  | COL3  |
------+-------+-------+
 TRUE | TRUE  | FALSE |
 TRUE | FALSE | FALSE |
------+-------+-------+

select min(col1),max(col1),min(col2),max(col2),min(col3),max(col3) from x;
-----------+-----------+-----------+-----------+-----------+-----------+
 MIN(COL1) | MAX(COL1) | MIN(COL2) | MAX(COL2) | MIN(COL3) | MAX(COL3) |
-----------+-----------+-----------+-----------+-----------+-----------+
 TRUE      | TRUE      | FALSE     | TRUE      | FALSE     | FALSE     |
-----------+-----------+-----------+-----------+-----------+-----------+

Note, this does not seem to be officially documented though, so use at your own risk :)

Answer 2

There is a boolor() function in Snowflake, but it's not the same thing. What you want to use is the Snowflake bitor_agg() function, which does the same logic, but on a bit value. The function is a little awkward because it uses the integer equivalent of a boolean, rather than a boolean directly. So, in your example:

with t1 as
         (
             select $1 as id, $2 as x
             from (values (1, true)
                        , (1, false)
                        , (2, false)
                        , (2, false)) AS t1
         )
select id, bitor_agg(x::integer)::boolean
from t1
group by id;

The conversion inside the function makes the values integer for the bitor_agg() function, and then the boolean conversion outside brings it back to boolean for you.

https://docs.snowflake.net/manuals/sql-reference/functions/bitor_agg.html

Answer 3

I'm going to propose something much simpler: Use MAX for AND and use MIN for OR. Yes, you may have to cast the argument to ::integer but that's a small price to pay.

select   min(value::integer)::boolean,
         max(value::integer)::boolean,
         key
from     mytable
group by key;