I came across this Linkedin post from a Google engineer, on a new (in preview) and very interesting BigQuery syntax: GROUP BY ALL. This will save time when writing and specially modifying complex SQL queries on BigQuery.

The GROUP BY ALL clause groups rows by inferring grouping keys from the SELECT items. It will exclude expressions with aggregate and window functions, constants, and query parameters for a smart GROUP BY. So instead of GROUP BY name, city, device, browser, date or GROUP BY 1, 2, 3, 4, 5 you would use GROUP BY ALL.

An example from documentation :

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
  SUM(PointsScored) AS total_points,
  FirstName AS first_name,
  LastName AS last_name
FROM PlayerStats

 | total_points | first_name | last_name |
 | 7            | Noam       | Adams     |
 | 13           | Jie        | Buchanan  |
 | 1            | Kiran      | Coolidge  |

Another example from the Linkedin post: GROUP BY ALL in BigQuery


Comment? Reply via Email, Mastodon or Twitter.