时间戳

首页 > 解决方案 > 在 BigQuery 中声明数组常量

sql - 在 BigQuery 中声明数组常量

问题描述

我正在尝试重构 SQL 查询，而当前查询是

WITH first_result AS (
    select table.a,table.b,table.c from table where table.a NOT IN 
    ('x', 'y', 'z',.......1000 more entries)),
second_result AS (....),
SELECT * FROM second_result

如何将['x', 'y', 'z',.......1000 more entries]分成常数？我想让查询更具可读性

标签： sqlgoogle-bigquery

解决方案

最初的问题是关于修复以下查询 - 

with first_result as (
    select table.a,table.b,table.c from table where table.a not in 
    ['x', 'y', 'z']),
second_result as (....),
select * from second_result

有了答案

您应该使用WHERE a NOT IN UNNEST(['x', 'y', 'z'])如下示例（BigQuery 标准 SQL） 

with first_result as (
    select table.a,table.b,table.c from table where table.a not in 
    UNNEST(['x', 'y', 'z'])  ),
second_result as (....),
select * from second_result

推荐阅读