Snowflake：对半结构化数据执行行级MIN_BY/MAX_BY（无需展开）

我们需要从Snowflake表的数组类型字段中，提取每个id对应的最新日期的metric值，且不需要先通过FLATTEN展开数组。

示例表创建语句 #

CREATE OR REPLACE TABLE tab (id INT, col VARIANT)
AS
SELECT 1, [{'date':'2024-01-01', 'metric':1}, {'date':'2024-01-02', 'metric':2}] UNION ALL
SELECT 2, [{'date':'2024-01-12', 'metric':3}, {'date':'2024-01-11', 'metric':4}] UNION ALL
SELECT 3, [{'date':'2024-01-05', 'metric':5}, {'date':'2024-01-04', 'metric':6}, {'date':'2024-01-07', 'metric':7}];

已实现的FLATTEN展开方法 #

此前通过FLATTEN展开数组后使用MAX_BY的实现代码如下：

WITH cte AS (
    SELECT tab.id, tab.col,s.value:metric::INT AS metric, s.value:date::DATE AS date
    FROM tab
    ,TABLE(FLATTEN(tab.col)) AS s(val)
)
SELECT id, col, MAX_BY(metric, date) AS col_newest_metric
FROM cte
GROUP BY id, col
ORDER BY id;

无需展开数组的实现方法 #

方法1：ARRAY_SORT排序后取首元素 #

通过ARRAY_SORT函数将数组按日期降序排序，直接提取排序后第一个元素的metric值：

SELECT
  id,
  col,
  ARRAY_SORT(col, (a, b) => CASE WHEN a:date::DATE > b:date::DATE THEN -1 ELSE 1 END)[0]:metric::INT AS col_newest_metric
FROM tab
ORDER BY id;

方法2：直接用MAX_BY筛选数组元素 #

利用Snowflake的MAX_BY函数直接对数组元素进行筛选，定位到date最大的元素后提取metric：

SELECT
  id,
  col,
  MAX_BY(col, (element) => element:date::DATE):metric::INT AS col_newest_metric
FROM tab
ORDER BY id;

以上两种方法均无需展开数组，直接在原数组上完成计算，在数组元素较多的场景下能提升查询效率。

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