SQL使用WITH生成临时表后执行INNER JOIN报缺失数据集错误如何解决

• WITH语句定义的临时表（CTE）作用域仅包含紧随其后的单条SQL语句：你当前拆分写了3组独立的WITH + SELECT查询，每组查询执行完成后对应的临时表就会被销毁，后续执行JOIN查询时数据库中不存在这三个表，因此会抛出表缺失的报错。
• 关联逻辑存在隐患：三张表的分组维度均为member_casual+start_week_date，如果仅用member_casual作为关联条件，相同用户类型下的不同星期统计值会产生笛卡尔积，输出结果不符合预期。

根据你的实际需求可以选择以下两种实现方式：

方案1：合并三个月的统计结果（按行拼接，更符合常规统计需求） #

如果需要将三个月的同维度统计结果汇总为一个数据集，使用UNION ALL即可：

WITH 
-- 先统一定义所有CTE，作用域覆盖后面的整条合并查询
october_fall10 AS
(SELECT 
   start_station_name,
   end_station_name,
   start_station_id,
   end_station_id,
   EXTRACT (DATE FROM started_at) AS start_date,
   EXTRACT(DAYOFWEEK FROM started_at) AS start_week_date,
   EXTRACT (TIME FROM started_at) AS start_time,    
   EXTRACT (DATE FROM ended_at) AS end_date,
   EXTRACT(DAYOFWEEK FROM ended_at) AS end_week_date,    
   EXTRACT (TIME FROM ended_at) AS end_time,
   DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
   member_casual
FROM 
   `ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysis),
november_fall11 AS
(SELECT
   start_station_name,
   end_station_name,
   start_station_id,
   end_station_id,
   EXTRACT (DATE FROM started_at) AS start_date,
   EXTRACT(DAYOFWEEK FROM started_at) AS start_week_date,
   EXTRACT (TIME FROM started_at) AS start_time,    
   EXTRACT (DATE FROM ended_at) AS end_date,
   EXTRACT(DAYOFWEEK FROM ended_at) AS end_week_date,    
   EXTRACT (TIME FROM ended_at) AS end_time,
   DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
   member_casual
FROM
   `ciclystic.cyclistic_seasonal_analysis.fall_202011` AS fall_analysis11),
december_fall12 AS
(SELECT
   start_station_name,
   end_station_name,
   start_station_id,
   end_station_id,
   EXTRACT (DATE FROM started_at) AS start_date,
   EXTRACT(DAYOFWEEK FROM started_at) AS start_week_date,
   EXTRACT (TIME FROM started_at) AS start_time,    
   EXTRACT (DATE FROM ended_at) AS end_date,
   EXTRACT(DAYOFWEEK FROM ended_at) AS end_week_date,    
   EXTRACT (TIME FROM ended_at) AS end_time,
   DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
   member_casual
FROM
   `ciclystic.cyclistic_seasonal_analysis.fall_202012` AS fall_analysis11),
-- 分别统计各月的指标
oct_stat AS (
SELECT
   '2020-10' as month,
   member_casual,
   start_week_date,
   COUNT (member_casual) AS member_casual_start,
   TIME(
   EXTRACT(hour   FROM AVG(start_time - '0:0:0')), 
   EXTRACT(minute FROM AVG(start_time - '0:0:0')), 
   EXTRACT(second FROM AVG(start_time - '0:0:0'))
   ) AS avg_start_time
FROM 
   october_fall10
GROUP BY
   start_week_date,
   member_casual
),
nov_stat AS (
SELECT
   '2020-11' as month,
   member_casual,
   start_week_date,
   COUNT (member_casual) AS member_casual_start,
   TIME(
   EXTRACT(hour   FROM AVG(start_time - '0:0:0')), 
   EXTRACT(minute FROM AVG(start_time - '0:0:0')), 
   EXTRACT(second FROM AVG(start_time - '0:0:0'))
   ) AS avg_start_time
FROM 
   november_fall11
GROUP BY
   start_week_date,
   member_casual
),
dec_stat AS (
SELECT
   '2020-12' as month,
   member_casual,
   start_week_date,
   COUNT (member_casual) AS member_casual_start,
   TIME(
   EXTRACT(hour   FROM AVG(start_time - '0:0:0')), 
   EXTRACT(minute FROM AVG(start_time - '0:0:0')), 
   EXTRACT(second FROM AVG(start_time - '0:0:0'))
   ) AS avg_start_time
FROM 
   december_fall12
GROUP BY
   start_week_date,
   member_casual
)
-- 合并三个月结果
SELECT * FROM oct_stat
UNION ALL
SELECT * FROM nov_stat
UNION ALL
SELECT * FROM dec_stat
ORDER BY month, start_week_date DESC;

方案2：按列关联三个月的统计结果 #

如果确实需要将同用户类型、同星期的三个月指标放在同一行展示，关联时需要同时使用member_casual和start_week_date作为关联条件：

WITH 
-- 同样先统一定义所有CTE
october_fall10 AS
(SELECT 
   start_station_name,
   end_station_name,
   start_station_id,
   end_station_id,
   EXTRACT (DATE FROM started_at) AS start_date,
   EXTRACT(DAYOFWEEK FROM started_at) AS start_week_date,
   EXTRACT (TIME FROM started_at) AS start_time,    
   EXTRACT (DATE FROM ended_at) AS end_date,
   EXTRACT(DAYOFWEEK FROM ended_at) AS end_week_date,    
   EXTRACT (TIME FROM ended_at) AS end_time,
   DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
   member_casual
FROM 
   `ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysis),
november_fall11 AS
(SELECT
   start_station_name,
   end_station_name,
   start_station_id,
   end_station_id,
   EXTRACT (DATE FROM started_at) AS start_date,
   EXTRACT(DAYOFWEEK FROM started_at) AS start_week_date,
   EXTRACT (TIME FROM started_at) AS start_time,    
   EXTRACT (DATE FROM ended_at) AS end_date,
   EXTRACT(DAYOFWEEK FROM ended_at) AS end_week_date,    
   EXTRACT (TIME FROM ended_at) AS end_time,
   DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
   member_casual
FROM
   `ciclystic.cyclistic_seasonal_analysis.fall_202011` AS fall_analysis11),
december_fall12 AS
(SELECT
   start_station_name,
   end_station_name,
   start_station_id,
   end_station_id,
   EXTRACT (DATE FROM started_at) AS start_date,
   EXTRACT(DAYOFWEEK FROM started_at) AS start_week_date,
   EXTRACT (TIME FROM started_at) AS start_time,    
   EXTRACT (DATE FROM ended_at) AS end_date,
   EXTRACT(DAYOFWEEK FROM ended_at) AS end_week_date,    
   EXTRACT (TIME FROM ended_at) AS end_time,
   DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
   member_casual
FROM
   `ciclystic.cyclistic_seasonal_analysis.fall_202012` AS fall_analysis11),
oct_stat AS (
SELECT
   member_casual,
   start_week_date,
   COUNT (member_casual) AS oct_member_casual_start,
   TIME(
   EXTRACT(hour   FROM AVG(start_time - '0:0:0')), 
   EXTRACT(minute FROM AVG(start_time - '0:0:0')), 
   EXTRACT(second FROM AVG(start_time - '0:0:0'))
   ) AS oct_avg_start_time
FROM 
   october_fall10
GROUP BY
   start_week_date,
   member_casual
),
nov_stat AS (
SELECT
   member_casual,
   start_week_date,
   COUNT (member_casual) AS nov_member_casual_start,
   TIME(
   EXTRACT(hour   FROM AVG(start_time - '0:0:0')), 
   EXTRACT(minute FROM AVG(start_time - '0:0:0')), 
   EXTRACT(second FROM AVG(start_time - '0:0:0'))
   ) AS nov_avg_start_time
FROM 
   november_fall11
GROUP BY
   start_week_date,
   member_casual
),
dec_stat AS (
SELECT
   member_casual,
   start_week_date,
   COUNT (member_casual) AS dec_member_casual_start,
   TIME(
   EXTRACT(hour   FROM AVG(start_time - '0:0:0')), 
   EXTRACT(minute FROM AVG(start_time - '0:0:0')), 
   EXTRACT(second FROM AVG(start_time - '0:0:0'))
   ) AS dec_avg_start_time
FROM 
   december_fall12
GROUP BY
   start_week_date,
   member_casual
)
SELECT *
FROM oct_stat
INNER JOIN nov_stat USING(member_casual, start_week_date)
INNER JOIN dec_stat USING(member_casual, start_week_date)
ORDER BY start_week_date DESC;

内容的提问来源于stack exchange，提问作者Rodrigo