I have a table on BigQuery with some information and need to create another table with aggregated information by name with columns that have values with some condition. Here is an example of a table: …
Tag: google-bigquery
Pivot rows into column Googlesql
I have a data like below ID name timeSpent_in_mins 1 ABC 12 1 XYZ 24 I need something like ID ABC XYZ 1 12 24 Need something similar to Pivot in googlesql/standard sql Thanks In …
BigQuery nested table UPDATE based on condition of non-nested data AND nested data
I am trying to update records in a bigQuery database that looks like this: Using the code below: UPDATE `tottus-chile.espacio.nested_table` SET addresses = ARRAY( SELECT AS STRUCT * REPLACE(‘…
Replace aggregated duplicate value in unique rows
I have a table in which each row represents a different event, with one of the fields providing a value aggregated across all the unique events that occur within that id. For example: +—————…
Add nested column to BigQuery table, joining on value of another nested column in standard SQL
I have a reasonably complex dataset being pulled into BigQuery table via an Airflow DAG which cannot easily be adjusted. This job pulls data into a table with this format: | Line_item_id | Device …
How to redesign the table? Like recursive CTE
Due to the limited support of Bigquery for recursive CTE, I only have to use some silly ways to deal with the problem. My question is that I have a table below: Employee0 Employee1 Employee2 …
How to remove Repeated field in BigQuery schema?
I have a schema that has a repeated field nested into another repeated field like so: person.children.toys. I want to make this inner field not repeated (so child can have only single nullable toy). I …
Count rows that don’t match between two tables within a group
I have two tables, each with a user_id, and group_name columns. E.g. table1: | user_id | group_name1| ———————— | 1 | ‘groupA’ | | 1 | ‘groupB’ | | 2 | ‘…
SQL distinct values per group – how to “group by” and get a list of distinct values per group?
I need to do a bigquery/standard SQL statement (or a series of statements) that does “group by”, and returns a list of distinct values for each group (not count). for example for tables with headers …
CASE to sum column based on boolean
I have: Then I group by to get the counts but how do I sum X when Y is TRUE only? Or get the % of A in relation to true/untrue? Answer how do I sum X when Y is TRUE only?