时间戳

首页 > 解决方案 > BigQuery：获取窗口中的第一个非空值？

google-bigquery - BigQuery：获取窗口中的第一个非空值？

问题描述

我想在值列表中获取第一个非空、非“未定义”值作为窗口的一部分。

最小的例子：

给定以下代码：

SELECT
    FIRST_VALUE(
        CASE WHEN val = "undefined" THEN NULL ELSE val END
        IGNORE NULLS
    )
    OVER (ORDER BY order_key)
    AS res
FROM (
    SELECT 1 AS order_key, CAST(NULL AS STRING) AS val
    UNION ALL
    SELECT 2 AS order_key, "undefined" AS val
    UNION ALL
    SELECT 3 AS order_key, "value" AS val
) base

我期待

res
value
value
value

作为结果集。然而，上面给出的结果如下：

res
null
null
value

该文档指出以下内容：

FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

返回当前窗口框架中第一行的 value_expression 的值。

此函数在计算中包含 NULL 值，除非存在 IGNORE NULLS。如果存在 IGNORE NULLS，该函数会从计算中排除 NULL 值。

然而，在这种情况下，似乎 value_expression 不是针对 NULL 测试的。似乎 FIRST_VALUE 针对源字段而不是 CASE 语句（实际上是上面的value_expression）检查 NULL。

虽然问题可以通过将案例作为子查询的一部分轻松解决，但我想更好地理解为什么这是一个问题。为什么不FIRST_VALUE忽略通过 CASE 语句提供的 NULL？

标签： google-bigquery

解决方案

替代上述逻辑：

如果您愿意改造您的查询，而不是使用窗口函数 ( FIRST_VALUE)，可以通过以下方式实现相同的效果ARRAY_AGG(expr IGNORE NULLS ORDER BY ordering)[OFFSET(0)]：

SELECT
    id,
    ARRAY_AGG(
        CASE WHEN val = 'undefined' THEN NULL ELSE val END
        IGNORE NULLS
        ORDER BY order_key
    )[OFFSET(0)]
    AS res
FROM (
    SELECT 1 AS id, 1 AS order_key, CAST(NULL AS STRING) AS val
    UNION ALL
    SELECT 1 AS id, 2 AS order_key, 'undefined' AS val
    UNION ALL
    SELECT 1 AS id, 3 AS order_key, "value" AS val
    UNION ALL
    SELECT 2 AS id, 1 AS order_key, CAST(NULL AS STRING) AS val
    UNION ALL
    SELECT 2 AS id, 2 AS order_key, 'undefined' AS val
    UNION ALL
    SELECT 2 AS id, 3 AS order_key, "value" AS val
) base
GROUP BY id
  1. 给定组的空记录集，ARRAY_AGG(...)[OFFSET(0)]将返回 NULL
  2. 给定组的非空记录集，ARRAY_AGG(...)[OFFSET(0)]将返回 value_expression 的第一个非 NULL 结果，按提供的 ORDER BY 子句排序。

唯一的缺点（除了性能之外？）是您需要使用此逻辑创建一个公共表表达式，然后将其与使用窗口函数的表连接起来。

推荐阅读