SQL多查询在Bigquery中使用with -

Question

我正在尝试弄清楚如何发出包含多个查询的单个请求，以便生成一个包含所有查询结果的视图。

下面是示例：

WITH Query_1 AS
(
SELECT
dimension1,
dimension2,
dimension3,
dimension4,
dimension5,

CASE WHEN STARTS_WITH (dimension1, "N_") THEN '1' ELSE '0' END AS qadimension1,
CASE WHEN dimension2 IS NOT NULL THEN dimension2 ELSE '0' END AS qadimension2,
CASE WHEN dimension3 IS NOT NULL THEN dimension3 ELSE '0' END AS qadimension3,
CASE WHEN dimension4 IS NOT NULL THEN dimension4 ELSE '0' END AS qadimension4,
FROM `XXXXX.XXXXX.first_view`
WHERE dimension5='2021' AND dimension1 IS NOT NULL),

Query_2 AS (
SELECT
dimension1,
qadimension1,
dimension6,

CASE WHEN qadimension1 = dimension6 THEN '1' ELSE '0' END AS QA_check1,
FROM `XXXXX.XXXXX.first_view` , `XXXXX.XXXXX.second_view`
WHERE qadimension1 != '0'),


Query_3 AS (
SELECT
dimension1,
qadimension2,
dimension7,

CASE WHEN qadimension2 = dimension7 THEN '1' ELSE '0' END AS QA_check2,
FROM `XXXXX.XXXXX.first_view` , `XXXXX.XXXXX.third_view`
WHERE qadimension2 != '0')



SELECT 
dimension1,
dimension2,
dimension3,
dimension4,
dimension5,
dimension6,
dimension7,

qadimension1,
qadimension2,
qadimension3,
qadimension4,

QA_check1,
QA_check2,

FROM
Query_1,
Query_2,
Query_3

WHERE FULL JOIN dimension1;

仍然有一个错误:语法错误:意外的关键字FULL at 242:7

非常感谢您的帮助

Answer 1

下面是针对BigQuery标准SQL的说明

尝试下面-注意所有CTE没有更改，所以下面只有最后一条select语句

SELECT 
  dimension1,
  dimension2,
  dimension3,
  dimension4,
  dimension5,
  dimension6,
  dimension7,

  q2.qadimension1,
  q3.qadimension2,
  qadimension3,
  qadimension4,

  QA_check1,
  QA_check2

FROM Query_1
LEFT JOIN Query_2 q2 USING(dimension1)
LEFT JOIN Query_3 q3 USING(dimension1)