BigQuery中基于含CTE的查询创建新表报错的解决方案咨询

你好，我看了你的问题，这个是BigQuery中CTAS（Create Table As Select）语法的常见误区，我来帮你梳理下问题和解决方法：

问题原因 #

你之前的写法是把CREATE OR REPLACE TABLE语句插在了CTE定义和主查询之间，这破坏了BigQuery中CTE的语法结构——所有CTE必须以WITH开头形成一个完整的块，不能被其他DDL语句打断，所以系统会报错识别不了CREATE关键字。

正确的语法逻辑 #

在BigQuery中，想要基于带CTE的查询创建表，必须把整个WITH开头的CTE块紧跟在CREATE TABLE ... AS的后面，也就是把CTE作为AS之后的查询主体的一部分。

修正后的完整SQL #

我已经帮你把整个查询调整成了正确的结构，你可以直接使用：

CREATE OR REPLACE TABLE `stage.myassort.company_report` AS
WITH CATALOG_SKU_CTE AS (
    SELECT A.sku
    FROM `catalog-stage.catalog_enterprise_views.sku_hierarchy_view` A,
         `catalog-stage.catalog_enterprise_views.sku_v` B
    WHERE A.sku = B.SKU
      AND UPPER(B.group) = "MERCHANDISING"
      AND UPPER(B.specification) = "NONCONFIGURABLE"
      AND UPPER(B.classification) IN ("LUMBER","NORMAL")
),
-- Get all current stores and their markets
STORE_CTE AS (
    SELECT STR_NBR
    FROM `assrtmgmt-stage.assrtmgmt.STR_CRDB_VIEW`
    WHERE str_typ_ind = "N"
      AND MKT_NBR <> 401
),
-- Create a list of SKU-store combinations for this triangulation
SKU_STORE_CTE AS (
    SELECT A.SKU, B.STR_NBR
    FROM CATALOG_SKU_CTE A,
         STORE_CTE B
),
AC_CTE AS (
    SELECT SKU, ASSORTED, LOCATION, STRATEGY
    FROM `assrtmgmt-stage.AC_SHARED.product_assortment_commons_realtime`
    WHERE UPPER(LOCATION_TYPE) = "STR"
      AND SELLING_CHANNEL = 1
),
POG_PRODUCT_CTE AS (
    SELECT DISTINCT SKU_NBR, DBKEY
    FROM `spaceplanning-stage.SPACE_COMMONS.IX_SPC_PRODUCT_VIEW`
    WHERE ACTV_FLG = true
),
MYASSORT_CTE AS (
    SELECT DISTINCT A.sku_id, B.str_id
    FROM `assrtmgmt-stage.myassort_strategy.stock_set_sku` A,
         `assrtmgmt-stage.myassort_strategy.stock_set_str` B
    WHERE A.set_id = B.set_id
),
-- Get list of SKU-store combinations from POG where the SKU-store is either on live a POG or a Pending POG
POG_VIEW_CTE AS (
    SELECT DISTINCT POG_SKUS.SKU_NBR, POG_STORES.STR_NBR
    FROM POG_PRODUCT_CTE POG_SKUS,
         `spaceplanning-stage.SPACE_COMMONS.IX_SPC_PLANOGRAM_VIEW` POG,
         `spaceplanning-stage.SPACE_COMMONS.IX_SPC_POSITION_VIEW` POG_POS,
         `spaceplanning-stage.SPACE_COMMONS.THD_STORE_POG_ASSIGN_VIEW` POG_STORES
    WHERE POG_SKUS.DBKEY = POG_POS.DBPARENTPRODUCTKEY
      AND POG_POS.ACTV_FLG = true
      AND POG_POS.DBPRNTPLANOGRAMKEY = POG.DBKEY
      AND POG.ACTV_FLG = true
      AND POG.DBKEY = POG_STORES.DBPARENTPLANOGRAMKEY
      AND POG_STORES.FINISHEDDATE IS NULL
      AND POG_STORES.ACTV_FLG = true
)
-- MAIN QUERY
SELECT SSC.SKU AS `SKU`,
       SSC.STR_NBR AS `Store`,
       CASE WHEN MA.sku_id IS NULL THEN "N" ELSE "Y" END AS `MyAssortment`,
       CASE WHEN PVC.SKU_NBR IS NULL THEN "N" ELSE "Y" END AS `POG`,
       CASE
           WHEN AC.ASSORTED IS NULL THEN "N"
           WHEN UPPER(AC.ASSORTED) = "OFF" THEN "N"
           WHEN UPPER(AC.STRATEGY) LIKE "%INACTIVE%" THEN "N"
           WHEN UPPER(AC.STRATEGY) LIKE "%DELETE%" THEN "N"
           WHEN UPPER(AC.STRATEGY) = "CLEARANCE" THEN "N"
           ELSE "Y"
       END AS `Assortment_Commons`
FROM SKU_STORE_CTE SSC
LEFT OUTER JOIN MYASSORT_CTE MA ON SSC.SKU = MA.SKU_ID AND SSC.STR_NBR = MA.STR_ID
LEFT OUTER JOIN POG_VIEW_CTE PVC ON SSC.SKU = PVC.SKU_NBR AND SSC.STR_NBR = CAST(PVC.STR_NBR AS INT64)
LEFT OUTER JOIN AC_CTE AC ON SSC.SKU = AC.SKU AND SSC.STR_NBR = AC.LOCATION
ORDER BY SSC.SKU, SSC.STR_NBR;

额外的小调整说明 #

1. 移除了主查询末尾多余的括号，避免潜在语法错误
2. 统一了代码缩进，让结构更易读
3. 保留了你原来的字符串引号风格（双引号），BigQuery中双引号和单引号都可用于字符串，只要保持一致即可

这样调整后，就能正常创建你需要的报表表了。

内容来源于stack exchange