Athena UNPIVOT查询报错：mismatched input 'INCLUDE'问题求助

原始表结构 #

出错的查询语句 #

select * from source.table UNPIVOT INCLUDE NULLS
(value for type in ("oil", "gas", "petroleum"))

期望输出 #

报错信息 #

line 1:52: mismatched input 'INCLUDE'. Expecting: '(', ',', 'CROSS', 'EXCEPT', 'FULL', 'GROUP', 'HAVING', 'INNER', 'INTERSECT', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'OFFSET', 'ORDER', 'RIGHT', 'TABLESAMPLE', 'UNION', 'WHERE', <EOF>

解决方法 #

报错核心是INCLUDE NULLS的语法位置不符合你所用SQL引擎的规范，不同引擎的UNPIVOT语法细节有差异，以下是两种主流的正确写法：

写法1（Spark SQL、Trino等引擎适用） #

把INCLUDE NULLS直接放在UNPIVOT关键字后，跟着括号内的定义，同时注意列名不需要加双引号（除非列名含特殊字符）：

SELECT * 
FROM source.table
UNPIVOT INCLUDE NULLS (
    value FOR type IN (oil, gas, petroleum)
)

写法2（BigQuery适用） #

BigQuery要求INCLUDE NULLS放在UNPIVOT定义的括号外面：

SELECT * 
FROM source.table
UNPIVOT (
    value FOR type IN (oil, gas, petroleum)
)
INCLUDE NULLS

如果你的表中没有NULL值，也可以去掉INCLUDE NULLS，默认会过滤NULL；但保留它能保证后续数据出现NULL时也能被正常展开。

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