BigQuery中实现PostgreSQL的VALUES构造常量表CTE的等效写法是什么

BigQuery 不支持PostgreSQL风格的VALUES列表构造临时表语法，你可以直接用SELECT + UNION ALL的组合替换原VALUES部分，改写后的完整语句如下：

WITH rates AS (
  SELECT 2017 AS init, 2018 AS end, 0.1 AS rate UNION ALL
  SELECT 2016 AS init, 2017 AS end, 0.1 AS rate UNION ALL
  SELECT 2015 AS init, 2016 AS end, 0.2 AS rate UNION ALL
  SELECT 2014 AS init, 2015 AS end, 0.3 AS rate
)
SELECT * FROM my_bq_table as my
  JOIN rates as r ON my.year = r.init;

该写法与原PostgreSQL版本逻辑完全等价，字段类型会由BigQuery自动推导，执行效率无差异，是构造临时常量表的官方推荐写法。

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