Athena获取百分位数边界：寻求更优SQL查询方案

数据背景 #

现有按用户维度存储的模型时序预测数据，表结构如下：

需求 #

筛选每个用户**最新（按timestamp）**的预测值，基于结果计算100个百分位数的边界值。

原实现查询 #

WITH preds AS (
    SELECT user_id, last_prediction
    FROM (
        SELECT 
            user_id, 
            ROUND(prediction[1],5) last_prediction, 
            timestamp curr_ts, 
            MAX(timestamp) OVER (PARTITION BY user_id) max_ts
        FROM "my-schema"."my-table"
        WHERE DATE(timestamp) BETWEEN DATE('2022-08-08') AND DATE('2022-08-09')
          AND version = '1' 
          AND model_id = 'model_a'
    )
    WHERE curr_ts = max_ts
),
with_ntiles AS (
    SELECT *, NTILE(100) OVER(ORDER BY last_prediction) calculated_ntile
    FROM preds
)
SELECT calculated_ntile, MIN(last_prediction) min_pred, MAX(last_prediction) max_pred
FROM with_ntiles
GROUP BY 1 
ORDER BY 1 

原查询结果示例：

优化方案 #

1. 简化最新记录筛选逻辑 #

用ROW_NUMBER()直接标记用户最新行，替代原查询中先计算最大时间再过滤的冗余逻辑，减少窗口计算开销。

2. 合并层级减少中间数据 #

压缩CTE层级，避免生成不必要的临时数据集，降低内存占用。

3. 引擎原生函数替代分组聚合（可选） #

如果使用Snowflake、BigQuery等支持百分位数原生函数的引擎，直接用PERCENTILE_DISC或PERCENTILE_CONT计算边界，无需NTILE分组后再取最值。

优化后基础版SQL #

WITH user_latest_preds AS (
    SELECT 
        ROUND(prediction[1],5) AS last_prediction
    FROM (
        SELECT 
            prediction,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) AS rn
        FROM "my-schema"."my-table"
        WHERE DATE(timestamp) BETWEEN DATE('2022-08-08') AND DATE('2022-08-09')
          AND version = '1' 
          AND model_id = 'model_a'
    )
    WHERE rn = 1
)
SELECT 
    ntile_group AS calculated_ntile,
    MIN(last_prediction) AS min_pred,
    MAX(last_prediction) AS max_pred
FROM (
    SELECT 
        last_prediction,
        NTILE(100) OVER (ORDER BY last_prediction) AS ntile_group
    FROM user_latest_preds
)
GROUP BY ntile_group
ORDER BY ntile_group;

原生百分位数函数版SQL（推荐） #

WITH user_latest_preds AS (
    SELECT 
        ROUND(prediction[1],5) AS last_prediction
    FROM (
        SELECT 
            prediction,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) AS rn
        FROM "my-schema"."my-table"
        WHERE DATE(timestamp) BETWEEN DATE('2022-08-08') AND DATE('2022-08-09')
          AND version = '1' 
          AND model_id = 'model_a'
    )
    WHERE rn = 1
)
SELECT 
    pct AS calculated_ntile,
    PERCENTILE_DISC(pct/100) WITHIN GROUP (ORDER BY last_prediction) AS pred_bound
FROM (
    SELECT GENERATE_SERIES(1,100) AS pct
) pcts
CROSS JOIN user_latest_preds
GROUP BY pct
ORDER BY pct;

额外性能建议 #

确保表上创建联合索引：(model_id, version, user_id, timestamp DESC)，加速WHERE过滤和窗口函数的分区排序计算。

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