Search code examples
google-bigquerybigquery-udf

BigQuery - Resources exceeded during query execution: Not enough resources for query planning - too many subqueries

I'm trying to pivot a table from

ID DATE DATA
12345 2022-05-01 "Stringvalue.. rank:1"
12345 2022-05-01 "Stringvalue.. rank:2"
67890 2022-05-01 "Stringvalue.. rank:2"
67890 2022-05-01 "Stringvalue.. rank:2"

~

ID DATE DATA
12345 2022-05-01 "Stringvalue.. rank:23"
12345 2022-05-01 "Stringvalue.. rank:24"
67890 2022-05-01 "Stringvalue.. rank:23"
67890 2022-05-01 "Stringvalue.. rank:24"

to

ID DATE rank 1 rank 2 ... rank 24
12345 2022-05-01 "Stringvalue.. rank:1" "Stringvalue.. rank:2" "Stringvalue.. rank:23"
67890 2022-05-01 "Stringvalue.. rank:1" "Stringvalue.. rank:2" "Stringvalue.. rank:24"

referring to this: Dynamic Pivot Needed with Row_Number(), I tried implementing this by

SELECT DISTINCT ID, Date,
  (select ct.Data from cte ct where ct.ID= cte.ID and ct.Date = cte.Date and ct.rank =1) AS Ghour_1,
  (select ct.Data from cte ct where ct.ID = cte.ID and ct.Date= cte.Date and ct.rank =2) AS Ghour_2,
.
.
.

ct.Date = cte.Date and ct.rank =23) AS Ghour_23,
  (select ct.Data from cte ct where ct.ID = cte.ID and ct.Date = cte.Date and ct.rank =24) AS Ghour_24,
   from cte

but it is throwing resources exceeded error, is this because I have too many subquery select statements? Could anyone please suggest how to optimise this query?

Solution

  • Use below as a starting point

    select * from (
  select *, replace(regexp_extract(data, r'\brank:(\d+)$'), ':', '') rank
  from your_table
)
pivot (any_value(data) as rank for rank in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24'))

    if applied to sample data in your question - output is

    enter image description here

    Above is easy to transform to dynamic pivot - see multiple examples here on SO - in particular at least few of my answers dedicated to this