BigQuery如何将百分位struct展开为含City、Offset、Qant列的表

以下适配你当前使用的BigQuery SQL语法，提供两种实现方式：

方式1：直接基于原表改写（无需额外构造STRUCT） #

如果你不需要保留中间STRUCT结果，可直接在分组查询后对百分位数组做UNNEST，写法更简洁：

SELECT 
  City,
  offset AS Offset,
  quant AS Qant
FROM (
  SELECT 
    City, 
    APPROX_QUANTILES(CAST(Price as INTEGER), 100) AS quants
  FROM T
  WHERE Price > 0
  GROUP BY City
),
UNNEST(quants) quant WITH OFFSET offset;

方式2：基于你已构造的STRUCT展开 #

如果你必须使用已生成的STRUCT结果做展开，写法如下：

WITH struct_result AS (
  -- 你原有构造STRUCT的查询
  SELECT AS STRUCT City, APPROX_QUANTILES(CAST(Price as INTEGER), 100) AS quants
  FROM T
  WHERE Price > 0
  GROUP BY City
)
SELECT 
  s.City,
  offset AS Offset,
  quant AS Qant
FROM struct_result s,
UNNEST(s.quants) quant WITH OFFSET offset;

结果说明 #

• Offset字段从0开始计数，对应0~100的百分位位次，刚好覆盖全部101个百分位值
• 针对你提供的样例数据，Barcelona返回的结果中Offset=0对应值200、Offset=100对应值300；London返回的结果中Offset=0对应值1000、Offset=100对应值2000，中间值为近似百分位计算结果

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