Out of all those non-standard SQL functions in BigQuery, I think I like LOGICAL_AND and LOGICAL_OR the most.
These are aggregation functions I've posted about before (link in my comments), but just wanted to showcase how versatile they can be.
So:
- LOGICAL_OR = at least one value in the grouping bucket is TRUE.
- LOGICAL_AND = all the values in the grouping bucket are TRUE.
Plenty of stuff you can do with it:
- pair them with NOT when needed
- since they're aggregation functions, you compute a result for a bucket with GROUP BY or you can opt for using a window function call OVER (PARTITION BY ...)
- if you opt for GROUP BY, you can opt for filtering output with HAVING; whereas if you go through the window function route, you have QUALIFY for that matter
In the example below, I'm looking to compute three things about customers:
- are all their orders are paid?
- do they have any outstanding orders (i.e. not shipped yet)?
- whether they have ordered olives in the last 3 months
I make use of LOGICAL_AND and LOGICAL_OR for that.
As usual, one can achieve the same results using MIN and MAX, since:
- MIN([TRUE,..., FALSE]) = FALSE AND MAX([TRUE,..., FALSE]) = MAX.
Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.