BigQuery：[CTE表名称]缺少数据集，而请求中没有设置默认数据集

Question

我的原始表有：(1) acceptance_rate - string，百分比(2) host_is_superhost - boolean

我想在没有%的情况下将(1) acceptance_rate转换为整数，因此我创建了一个CTE，如下所示：

WITH acceptance_rate_cte AS 
(SELECT 
    CAST(REPLACE(acceptance_rate,'%',"") AS int) AS new_acceptance_rate, 
    host_is_superhost AS new_superhost
FROM table1
WHERE acceptance_rate NOT IN ("N/A","0%")
ORDER BY new_acceptance_rate DESC)

SELECT new_acceptance_rate, new_superhost
FROM acceptance_rate_cte;

新的CTE表如下：

new_acceptance_rate x- new_superhost

100 @ true

90 -真

95假..。

接下来，我想要创建一个表，将所有的new_acceptance_rate分组到20的桶中，然后计算出这些桶中有多少为true或false。所以我做了这个：

SELECT CASE WHEN new_acceptance_rate >0 AND  new_acceptance_rate <= 20 then '1-20'
     WHEN new_acceptance_rate >20 AND new_acceptance_rate <=40 then '21-40'
     WHEN new_acceptance_rate >40 AND new_acceptance_rate<=60 THEN '41-60'
     WHEN new_acceptance_rate >60 AND new_acceptance_rate <=80 THEN '61-80'  
     ELSE 'Above 80'
     END acceptance_range,
     new_superhost,
     count(*) as superhost_count
FROM acceptance_rate_cte

我对结果的期望是：

acceptance_range _ new_superhost _ superhost_count

1-20真. 15

1-20假25

..。

但是，我却收到了一个错误消息，如下所示：

运行查询表名称“

”时出现错误，缺少数据集，而请求中没有设置默认数据集。

Answer 1

我在上面使用一些示例数据运行了您的查询，它似乎大部分是正确的。

with acceptance_rate_cte AS 
(   SELECT 
    CAST(REPLACE(acceptance_rate,'%',"") AS int) AS new_acceptance_rate, 
    host_is_superhost AS new_superhost
    FROM table1
    WHERE acceptance_rate NOT IN ("N/A","0%")
    ORDER BY new_acceptance_rate DESC
)
SELECT CASE WHEN new_acceptance_rate >0 AND  new_acceptance_rate <= 20 then '1-20'
     WHEN new_acceptance_rate >20 AND new_acceptance_rate <=40 then '21-40'
     WHEN new_acceptance_rate >40 AND new_acceptance_rate<=60 THEN '41-60'
     WHEN new_acceptance_rate >60 AND new_acceptance_rate <=80 THEN '61-80'  
     ELSE 'Above 80'
     END acceptance_range,  
     new_superhost,
     count(*) as superhost_count
FROM acceptance_rate_cte
GROUP BY acceptance_range, new_superhost;   

为了使其正确执行，我确实需要添加一个by组。基于您的错误，我猜您还没有在同一个会话中同时运行CTE和query。当您执行时，同时运行两个。