时间戳

首页 > 解决方案 > 从 bigquery 数组中获取所有长度为 3 的窗口

google-bigquery - 从 bigquery 数组中获取所有长度为 3 的窗口

问题描述

如果我有一张这样的桌子：

value1 | value2 | value3
-------|--------|-------
  a    |   1    |.  z
  a    |   2    |.  x
  a.   |.  3    |.  z
  a.   |.  4    |.  x
  b.   |.  1    |.  x
  b.   |.  2    |.  z
  c.   |.  1    |.  z
  c.   |.  2    |.  x
  c.   |.  3    |.  z

我想从中获取三个值的所有有序窗口，例如：

(a, [(1, z), (2, x), (3, z)]), (a, [(2, z), (3, z), (4, x)]), (c, [(1, z), (2, x), (3, z)])

使用 BigQuery，我将如何在跨多个唯一 value1 键的聚合中执行此操作并确保它们按 value2 排序。

所以我的查询是这样的：

SELECT
    val1,
    ARRAY_AGG(STRUCT(value2, value3)) vals
FROM my_table

然后我需要一个查询来覆盖它以获取三个值的所有分组。我想我可以使用 ROW_NUMBER() 或其他东西，但我不确定是否有一种简单的方法来解决这个问题。

标签： google-bigquery

解决方案

以下是 BigQuery Standrad SQL

版本 1： 

#standardSQL
SELECT
  value1, [arr[OFFSET(pos)], arr[OFFSET(pos + 1)], arr[OFFSET(pos + 2)]] arr
FROM (
  SELECT value1, ARRAY_AGG(STRUCT(value2, value3)) arr
  FROM `project.dataset.table`
  GROUP BY value1
  HAVING ARRAY_LENGTH(arr) > 2
), UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(arr) - 3)) pos

版本 2： 

#standardSQL
SELECT
  value1, ARRAY(SELECT s FROM UNNEST(arr) s WITH OFFSET ord WHERE ord >= pos ORDER BY ord LIMIT 3) arr
FROM (
  SELECT value1, ARRAY_AGG(STRUCT(value2, value3)) arr
  FROM `project.dataset.table`
  GROUP BY value1
  HAVING ARRAY_LENGTH(arr) > 2
), UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(arr) - 3)) pos

如果使用以下 CTE 将两个版本应用于您的问题中的虚拟数据 

WITH `project.dataset.table` AS (
  SELECT 'a' value1, 1 value2, 'z' value3 UNION ALL
  SELECT 'a', 2, 'x' UNION ALL
  SELECT 'a', 3, 'z' UNION ALL
  SELECT 'a', 4, 'x' UNION ALL
  SELECT 'b', 1, 'x' UNION ALL
  SELECT 'b', 2, 'z' UNION ALL
  SELECT 'c', 1, 'z' UNION ALL
  SELECT 'c', 2, 'x' UNION ALL
  SELECT 'c', 3, 'z' 
)

结果是 

Row value1  arr.value2  arr.value3   
1   a       1           z    
            2           x    
            3           z    
2   a       2           x    
            3           z    
            4           x    
3   c       1           z    
            2           x    
            3           z

显然，你可以用任何你需要的方式打包输出

推荐阅读