如何在Snowflake中用OBJECT_CONSTRUCT实现分组计数嵌套对象？

针对你需要的总行数统计、字段非空值计数、去重值数组及值计数嵌套对象的需求，可通过拆分CTE（公共表表达式）分步计算，再用OBJECT_CONSTRUCT和OBJECT_AGG组合结果的方式实现，避开直接嵌套SELECT的限制。

示例实现（以sales_data表为例，字段为region、product） #

WITH total_rows AS (
    -- 统计总行数
    SELECT COUNT(*) AS total FROM sales_data
),
region_stats AS (
    -- 统计region字段的非空数、去重数组、值计数对象
    SELECT
        COUNT(region) AS non_null_count,
        ARRAY_AGG(DISTINCT region) AS distinct_values,
        OBJECT_AGG(region, count) AS value_counts
    FROM (
        -- 先按region值分组计数
        SELECT region, COUNT(*) AS count
        FROM sales_data
        WHERE region IS NOT NULL
        GROUP BY region
    )
),
product_stats AS (
    -- 统计product字段的对应指标（复制此结构可扩展到更多字段）
    SELECT
        COUNT(product) AS non_null_count,
        ARRAY_AGG(DISTINCT product) AS distinct_values,
        OBJECT_AGG(product, count) AS value_counts
    FROM (
        SELECT product, COUNT(*) AS count
        FROM sales_data
        WHERE product IS NOT NULL
        GROUP BY product
    )
)
-- 组合所有统计结果为嵌套对象
SELECT
    OBJECT_CONSTRUCT(
        'total_rows', (SELECT total FROM total_rows),
        'region', OBJECT_CONSTRUCT(
            'non_null_count', (SELECT non_null_count FROM region_stats),
            'distinct_values', (SELECT distinct_values FROM region_stats),
            'value_counts', (SELECT value_counts FROM region_stats)
        ),
        'product', OBJECT_CONSTRUCT(
            'non_null_count', (SELECT non_null_count FROM product_stats),
            'distinct_values', (SELECT distinct_values FROM product_stats),
            'value_counts', (SELECT value_counts FROM product_stats)
        )
    ) AS summary_stats
FROM dual;

关键逻辑说明 #

1. 总行数统计：单独用CTE计算，避免与字段统计逻辑耦合
2. 字段指标拆分：每个字段单独用CTE处理，先通过分组聚合得到值的计数，再用OBJECT_AGG将「值-计数」转换为键值对对象，同时用COUNT()和ARRAY_AGG(DISTINCT)得到非空数和去重数组
3. 结果组合：通过OBJECT_CONSTRUCT将各部分结果嵌套为最终的统计对象，子查询仅引用单值/单对象，符合Snowflake语法限制

示例输出（对应测试数据） #

假设sales_data表数据如下：

最终summary_stats输出为：

{
  "total_rows": 5,
  "region": {
    "non_null_count": 4,
    "distinct_values": ["North", "South", "East"],
    "value_counts": {
      "North": 2,
      "South": 2,
      "East": 1
    }
  },
  "product": {
    "non_null_count": 4,
    "distinct_values": ["Laptop", "Phone"],
    "value_counts": {
      "Laptop": 3,
      "Phone": 1
    }
  }
}

注意事项 #

• 若需统计NULL值的数量，可在分组时将NULL替换为字符串（如COALESCE(region, 'NULL')），再纳入OBJECT_AGG
• 扩展更多字段时，只需复制对应字段的_stats CTE，并在最终OBJECT_CONSTRUCT中添加字段节点即可
• OBJECT_AGG会自动忽略键为NULL的条目，因此分组前过滤NULL可避免无效键值对

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