如何高效获取多列的前N个不同值？

我有如下数据表：

WITH tbl AS (
    SELECT 1 AS id, "Phone" AS product, 105 AS cost UNION ALL
    SELECT 2 AS id, "Camera" AS product, 82 AS cost UNION ALL
    SELECT 3 AS id, "Cup" AS product, 103 AS cost
) SELECT * FROM tbl

需求：获取每列的N个不同值，类似Power BI右下角的「值分布」功能，不需要统计值的出现次数，只需提取最多10个代表性样本值，且希望一次性获取所有列的样本，无需为每列单独执行查询。

尝试方案1：数组聚合（效率低下） #

该方案可行但效率极低，本质是为每列单独查询：

WITH tbl AS (
    SELECT 1 AS id, 'Phone' AS product, 105 AS cost UNION ALL
    SELECT 2 AS id, 'Camera' AS product, 82 AS cost UNION ALL
    SELECT 3 AS id, 'Cup' AS product, 103 AS cost
) 
SELECT 
    ARRAY_AGG(DISTINCT id LIMIT 2),
    ARRAY_AGG(DISTINCT product LIMIT 2),
    ARRAY_AGG(DISTINCT cost LIMIT 2)
FROM tbl

尝试方案2：跨环境通用但仍不理想（适用于非BigQuery环境） #

该方案能在多数SQL环境运行，但同样存在多次扫描表的问题：

WITH tbl AS (
    SELECT 1 AS id, 'Phone' AS product, 105 AS cost UNION ALL
    SELECT 2 AS id, 'Camera' AS product, 82 AS cost UNION ALL
    SELECT 3 AS id, 'Cup' AS product, 103 AS cost
)  
select 'id' as field, array(select distinct cast(id as string) from tbl limit 2) as values union all
select 'product', array(select distinct cast(product as string) from tbl limit 2) union all
select 'cost', array(select distinct cast(cost as string) from tbl limit 2);

优化方案（基于BigQuery ML功能） #

借助BigQuery的ML.DESCRIBE_DATA函数，只需扫描一次表即可批量提取所有列的代表性样本，自动区分字段类型：数值型字段返回设定数量的分位数，离散型字段返回设定数量的高频值：

WITH tbl AS (
    SELECT 1 AS id, "Phone" AS product, 105 AS cost, true as is_big, date '2014-01-01' as d UNION ALL
    SELECT 2 AS id, "Camera" AS product, 82 AS cost, false as is_big, date '2017-01-01' as d UNION ALL
    SELECT 3 AS id, "Cup" AS product, 103 AS cost, false as is_big, date '2015-01-01' as d union all
    SELECT 7 AS id, "Several" AS product, 103 AS cost, true as is_big, date '2016-01-01' as d
) 
SELECT 
  name, 
  IF(
    array_length(quantiles) is not null, 
    ARRAY(SELECT CAST(tmp AS STRING) FROM UNNEST(quantiles) tmp), 
    ARRAY(SELECT value FROM t.top_values)
  ) values
FROM ML.DESCRIBE_DATA(
  (SELECT * FROM tbl), STRUCT(3 AS num_quantiles, 4 AS top_k)
) t;

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