如何用Athena SQL实现与numpy.percentile(higher插值)完全一致的结果？

要在Athena SQL中得到和Python代码完全相同的分位数结果，需要手动模拟numpy中interpolation='higher'的计算逻辑——即对于给定的百分位，返回大于等于该百分位对应位置的最小元素。

核心逻辑拆解 #

numpy的np.percentile(a, p, interpolation='higher')计算逻辑：

1. 对数组排序（默认已完成）。
2. 计算百分位p对应的位置：pos = (n-1) * p / 100，其中n是数组元素个数（此处为10）。
3. 取大于等于pos的最小整数索引，返回该索引对应的元素。

对应的Athena SQL实现 #

WITH dataset (val) AS (
    VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
),
-- 生成带0-based索引的排序数据集，匹配numpy数组的索引规则
sorted_data AS (
    SELECT 
        val,
        ROW_NUMBER() OVER(ORDER BY val) - 1 AS idx
    FROM dataset
),
-- 生成0到100的所有百分位值
percentiles AS (
    SELECT CAST(generate_series(0, 100) AS DOUBLE) AS p
)
SELECT 
    ARRAY_AGG(val ORDER BY p) AS numpy_style_percentiles
FROM (
    SELECT 
        p,
        -- 计算numpy higher插值对应的索引，匹配sorted_data中的元素
        (SELECT val FROM sorted_data WHERE idx = CEIL((9 * p)/100)) AS val
    FROM percentiles
)

结果验证 #

执行上述SQL后，得到的数组将与Python代码输出完全一致：

[1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10]

为什么之前的approx_percentile结果有差异 #

approx_percentile是近似分位函数，默认的插值逻辑与numpy的higher不一致，它会将每个数值均匀分配到连续的百分位区间中，而不是严格遵循“取大于等于位置的最小元素”规则，因此无法直接得到匹配结果。

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