Bigquery SQL失败，带有引用其他表的相关子查询，除非可以将其解开。

huangapple
go评论95阅读模式
英文:

Bigquery SQL fails with Correlated subqueries that reference other tables are not supported unless they can be de-correlated

问题

以下是您要翻译的内容：

  1. BigQuery fails on my query with the following error, and I'm not sure how to mitigate this.
  3. > Query error: Correlated subqueries that reference other tables are not
  4. > supported unless they can be de-correlated, such as by transforming
  5. > them into an efficient JOIN. at [31:1]
  7. #### Standalone SQL Query (and temporary tables) to reproduce:
  9. create temporary table records (
  10.   ID int64,
  11.   Events array<struct<
  12.     Tag string,
  13.     Info string,
  14.     Citations array<struct<
  15.       Tag string,
  16.       SourceID int64>>>>);
  18. insert into records values
  19. (1, [
  20.     ('A', 'C', [('AA', 1000), ('AB', 1001)]),
  21.     ('A', 'C', [('AA', 1000), ('AB', 1001)]),
  22.     ('B', 'D', [('BA', 1000), ('BB', 1001)])]);
  24. create temporary table sources (
  25.   ID int64,
  26.   Title string);
  28. insert into sources values
  29. (1000, "ABCD"),
  30. (1001, "EFGH");
  32. select 
  33.   Record.ID,
  34.   array(
  35.     select as struct
  36.       Event.Tag,
  37.       Event.Info,
  38.       array(
  39.         select as struct
  40.           Citation.SourceID,
  41.           Citation.Tag,
  42.           Source.Title
  43.         from unnest(Event.Citations) as Citation
  44.         left join sources as Source on Citation.SourceID = Source.ID
  45.       ) as Citations
  46.     from unnest(Record.Events) as Event) as Events
  47. from records as Record;

The events table looks like (in json):

[{
"ID": "1",
"Events": [{
"Tag": "A",
"Info": "C",
"Citations": [{
"Tag": "AA",
"SourceID": "1000"
}, {
"Tag": "AB",
"SourceID": "1001"
}]
}, {
"Tag": "A",
"Info": "C",
"Citations": [{
"Tag": "AA",
"SourceID": "1000"
}, {
"Tag": "AB",
"SourceID": "1001"
}]
}, {
"Tag": "B",
"Info": "D",
"Citations": [{
"Tag": "BA",
"SourceID": "1000"
}, {
"Tag": "BB",
"SourceID": "1001"
}]
}]
}]

The sources table looks like:
[{
"ID": "1000",
"Title": "ABCD"
}, {
"ID": "1001",
"Title": "EFGH"
}]

  2. I'd like the output to look like:
  4. [{
  5.   "ID": "1",
  6.   "Events": [{
  7.     "Tag": "A",
  8.     "Citations": [{
  9.       "Tag": "AA",
  10.       "SourceID": "1000",
  11.       "Title": "ABCD"
  12.     }, {
  13.       "Tag": "AB",
  14.       "SourceID": "1001",
  15.       "Title": "EFGH"
  16.     }]
  17.   }, {
  18.     "Tag": "B",
  19.     "Citations": [{
  20.       "Tag": "BA",
  21.       "SourceID": "1000",
  22.       "Title": "ABCD"
  23.     }, {
  24.       "Tag": "BB",
  25.       "SourceID": "1001",
  26.       "Title": "EFGH"
  27.     }]
  28. }]
英文:

BigQuery fails on my query with the following error, and I'm not sure how to mitigate this.

> Query error: Correlated subqueries that reference other tables are not
> supported unless they can be de-correlated, such as by transforming
> them into an efficient JOIN. at [31:1]

Standalone SQL Query (and temporary tables) to reproduce:

  1. create temporary table records (
  2.   ID int64,
  3.   Events array&lt;struct&lt;
  4.     Tag string,
  5.     Info string,
  6.     Citations array&lt;struct&lt;
  7.       Tag string,
  8.       SourceID int64&gt;&gt;&gt;&gt;);
  10. insert into records values
  11. (1, [
  12.     (&#39;A&#39;, &#39;C&#39;, [(&#39;AA&#39;, 1000), (&#39;AB&#39;, 1001)]),
  13.     (&#39;A&#39;, &#39;C&#39;, [(&#39;AA&#39;, 1000), (&#39;AB&#39;, 1001)]),
  14.     (&#39;B&#39;, &#39;D&#39;, [(&#39;BA&#39;, 1000), (&#39;BB&#39;, 1001)])]);
  16. create temporary table sources (
  17.   ID int64,
  18.   Title string);
  20. insert into sources values
  21. (1000, &quot;ABCD&quot;),
  22. (1001, &quot;EFGH&quot;);
  24. select 
  25.   Record.ID,
  26.   array(
  27.     select as struct
  28.       Event.Tag,
  29.       Event.Info,
  30.       array(
  31.         select as struct
  32.           Citation.SourceID,
  33.           Citation.Tag,
  34.           Source.Title
  35.         from unnest(Event.Citations) as Citation
  36.         left join sources as Source on Citation.SourceID = Source.ID
  37.       ) as Citations
  38.     from unnest(Record.Events) as Event) as Events
  39. from records as Record;

The events table looks like (in json):

  1. [{
  2.   &quot;ID&quot;: &quot;1&quot;,
  3.   &quot;Events&quot;: [{
  4.     &quot;Tag&quot;: &quot;A&quot;,
  5.     &quot;Info&quot;: &quot;C&quot;, 
  6.     &quot;Citations&quot;: [{
  7.       &quot;Tag&quot;: &quot;AA&quot;,
  8.       &quot;SourceID&quot;: &quot;1000&quot;
  9.     }, {
  10.       &quot;Tag&quot;: &quot;AB&quot;,
  11.       &quot;SourceID&quot;: &quot;1001&quot;
  12.     }]
  13.   }, {
  14.     &quot;Tag&quot;: &quot;A&quot;,
  15.     &quot;Info&quot;: &quot;C&quot;, 
  16.     &quot;Citations&quot;: [{
  17.       &quot;Tag&quot;: &quot;AA&quot;,
  18.       &quot;SourceID&quot;: &quot;1000&quot;
  19.     }, {
  20.       &quot;Tag&quot;: &quot;AB&quot;,
  21.       &quot;SourceID&quot;: &quot;1001&quot;
  22.     }]
  23.   }, {
  24.     &quot;Tag&quot;: &quot;B&quot;,
  25.     &quot;Info&quot;: &quot;D&quot;,
  26.     &quot;Citations&quot;: [{
  27.       &quot;Tag&quot;: &quot;BA&quot;,
  28.       &quot;SourceID&quot;: &quot;1000&quot;
  29.     }, {
  30.       &quot;Tag&quot;: &quot;BB&quot;,
  31.       &quot;SourceID&quot;: &quot;1001&quot;
  32.     }]
  33.   }]
  34. }]

The sources table looks like:

  1. [{
  2.   &quot;ID&quot;: &quot;1000&quot;,
  3.   &quot;Title&quot;: &quot;ABCD&quot;
  4. }, {
  5.   &quot;ID&quot;: &quot;1001&quot;,
  6.   &quot;Title&quot;: &quot;EFGH&quot;
  7. }]

I'd like the output to look like:

  1. [{
  2.   &quot;ID&quot;: &quot;1&quot;,
  3.   &quot;Events&quot;: [{
  4.     &quot;Tag&quot;: &quot;A&quot;,
  5.     &quot;Citations&quot;: [{
  6.       &quot;Tag&quot;: &quot;AA&quot;,
  7.       &quot;SourceID&quot;: &quot;1000&quot;,
  8.       &quot;Title&quot;: &quot;ABCD&quot;
  9.     }, {
  10.       &quot;Tag&quot;: &quot;AB&quot;,
  11.       &quot;SourceID&quot;: &quot;1001&quot;,
  12.       &quot;Title&quot;: &quot;EFGH&quot;
  13.     }]
  14.   }, {
  15.     &quot;Tag&quot;: &quot;B&quot;,
  16.     &quot;Citations&quot;: [{
  17.       &quot;Tag&quot;: &quot;BA&quot;,
  18.       &quot;SourceID&quot;: &quot;1000&quot;,
  19.       &quot;Title&quot;: &quot;ABCD&quot;
  20.     }, {
  21.       &quot;Tag&quot;: &quot;BB&quot;,
  22.       &quot;SourceID&quot;: &quot;1001&quot;,
  23.       &quot;Title&quot;: &quot;EFGH&quot;
  24.     }]
  25.   }]
  26. }]

答案1

得分: 1

你可以尝试以下的代码：

  1. SELECT
  2.   ID,
  3.   ARRAY_AGG((SELECT AS STRUCT Event.* EXCEPT(Citations), Citations)) AS Events
  4. FROM (
  5.   SELECT
  6.     r.ID,
  7.     ANY_VALUE(e) Event, --&gt; will discard *e.Citations* in outer query whatever it is
  8.     ARRAY_AGG((SELECT AS STRUCT c.*, s.Title)) Citations
  9.   FROM records r
  10.   CROSS JOIN UNNEST(r.events) e WITH offset
  11.   CROSS JOIN UNNEST(e.citations) c
  12.   LEFT JOIN sources s ON s.ID = c.SourceID
  13.   GROUP BY r.ID, offset
  14. ) GROUP BY ID;

查询结果

Bigquery SQL失败，带有引用其他表的相关子查询，除非可以将其解开。

英文:

You can try below instead.

  1. SELECT
  2.   ID,
  3.   ARRAY_AGG((SELECT AS STRUCT Event.* EXCEPT(Citations), Citations)) AS Events
  4. FROM (
  5.   SELECT
  6.     r.ID,
  7.     ANY_VALUE(e) Event, --&gt; will discard *e.Citations* in outer query whatever it is
  8.     ARRAY_AGG((SELECT AS STRUCT c.*, s.Title)) Citations
  9.   FROM records r
  10.   CROSS JOIN UNNEST(r.events) e WITH offset
  11.   CROSS JOIN UNNEST(e.citations) c
  12.   LEFT JOIN sources s ON s.ID = c.SourceID
  13.   GROUP BY r.ID, offset
  14. ) GROUP BY ID;

Query results

Bigquery SQL失败，带有引用其他表的相关子查询，除非可以将其解开。

huangapple
  • 本文由 发表于 2023年3月1日 14:04:35
  • 转载请务必保留本文链接：https://go.coder-hub.com/75600081.html
  • google-bigquery
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定