时间戳

首页 > 解决方案 > 如何在标准 SQL 的 WHERE 子句中使用 WITH 子查询作为选项列表

google-bigquery - 如何在标准 SQL 的 WHERE 子句中使用 WITH 子查询作为选项列表

问题描述

有什么问题

我有一个非常大的结果列表。我想根据表中最受欢迎的结果过滤我的查询以仅包含一小部分查询。

当我将过滤子查询放在WITH子句中时，它不起作用。但是，如果我将它直接放在 WHERE 子句中，它确实有效。为什么？

为了清楚起见，我更喜欢第一个，但我无法让它工作。

我正在使用 StandardSQL 在 BigQuery 中工作。

例子

WITH
  most_common AS (
  SELECT
    page
  FROM
    `mydataset`
  LIMIT 
    100
)
SELECT
  *
FROM
   `mydataset`
WHERE
  page IN most_common

在这里，我试图获取所有结果，其中页面位于前 100 行中。

它返回以下错误：-Syntax error: Expected "(" or keyword UNNEST but got identifier "most_common" at [12:12]

但是，如果我将子查询直接放在它可以正常工作的地方。

SELECT
  *
FROM
   `mydataset`
WHERE
  page IN (
  SELECT
    page
  FROM
    `mydataset`
  LIMIT 
    100
)

我的理解有限

它说它想要 unnest，但 unnest 将数组转换为表结果作为子查询，这应该已经是一个表。

标签： google-bigquerysubquery

解决方案

#standardSQL
WITH
  most_common AS (
  SELECT
    page
  FROM
    `mydataset`
  LIMIT 
    100
)
SELECT
  *
FROM
   `mydataset`
WHERE
  page IN (SELECT page FROM most_common)

另外的选择： 

#standardSQL
WITH most_common AS (
  SELECT ARRAY_AGG(page) pages FROM (
    SELECT page
    FROM `mydataset`
    LIMIT 100
  )
)
SELECT *
FROM `mydataset`, most_common
WHERE page IN UNNEST(pages)

或稍微重构的版本 

#standardSQL
WITH most_common AS (
  SELECT ARRAY_AGG(page LIMIT 100) pages 
  FROM `mydataset`
)
SELECT *
FROM `mydataset`, most_common
WHERE page IN UNNEST(pages)

注意：ARRAY_AGG(...)接受 ORDER BY 子句，因此您实际上可以使用此语法选择最常用的ARRAY_AGG(page ORDER BY some metric DESC LIMIT 100)
显然最终版本取决于您的实际用例 - 但不是这个 - 你得到了我希望的想法

推荐阅读