BigQuery中SQL计算衍生列后求和及数据留存问题咨询

一、直接完成你的求和需求（无需提前存储） #

你不需要先存储中间结果，用CTE（公共表表达式）或者子查询就能一步到位。比如用CTE的写法：

WITH cleaned_data AS (
  SELECT (ended_at - started_at) AS duration, customer_type 
  FROM `my-project-81752.loyal_customer.jan22`
)
SELECT SUM(duration) AS total_duration
FROM cleaned_data
WHERE customer_type = 'X';

或者子查询的更简洁写法：

SELECT SUM(duration) AS total_duration
FROM (
  SELECT (ended_at - started_at) AS duration, customer_type 
  FROM `my-project-81752.loyal_customer.jan22`
)
WHERE customer_type = 'X';

二、留存清洗/过滤后数据的常用方式 #

如果需要多次复用清洗后的结果，有几种实用方法：

• CTE（WITH子句）：适合单查询内的临时复用，BigQuery会自动优化执行计划，不会额外存储数据，写完就能在后续查询块里直接调用。
• 临时表：如果需要跨会话（比如不同查询窗口）复用，可以创建会话级临时表，会话结束后自动删除：

  CREATE TEMP TABLE cleaned_data AS
  SELECT (ended_at - started_at) AS duration, customer_type 
  FROM `my-project-81752.loyal_customer.jan22`;
  
  -- 后续查询直接使用该临时表
  SELECT SUM(duration) FROM cleaned_data WHERE customer_type = 'X';
  

• 永久表：如果数据需要长期留存、多人共享，就创建永久表并指定数据集存储：

  CREATE OR REPLACE TABLE `my-project-81752.loyal_customer.cleaned_jan22` AS
  SELECT (ended_at - started_at) AS duration, customer_type 
  FROM `my-project-81752.loyal_customer.jan22`;
  

三、BigQuery与其他SQL数据库的核心差异 #

1. 多SELECT语句限制：传统SQL数据库（如MySQL、PostgreSQL）允许在同一脚本里写多个独立SELECT（分号分隔），但BigQuery Web UI默认不支持——不过可以通过开启“脚本模式”或分标签页执行来解决。
2. CTE优化逻辑：BigQuery会将CTE逻辑直接合并到主查询中，不会像部分数据库那样先物化CTE，性能更优。
3. 临时表生命周期：BigQuery的临时表是会话级的，关闭会话自动删除；部分数据库的临时表需手动删除或基于连接生命周期存在。
4. 大规模数据适配：BigQuery是云原生数据仓库，专为PB级数据设计，语法上和传统SQL一致，但底层采用分布式执行引擎，处理超大数据集的效率远高于传统数据库。

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