amazon-web-services - AWS Athena - 应用过滤器，然后计算百分位数

Question

我正在使用 AWS Athena 来计算一些指标。我有一个这样的数据集:

sessionumber
0
10
-1
10
2
-10
10

我正在尝试计算该值的百分位数，但仅适用于有效值的子集。有效值为 sessionnumber > 1所以我试过了:

with testfun AS 
    (SELECT filter(array_agg(sessionnumber), x -> x >= 1) as validvalues 
     FROM "mydate")

SELECT (percentiles(validvalues, 0.25) FROM testfun

但我收到以下错误:

SYNTAX_ERROR: line 17:10: Unexpected parameters (array(integer), double) for function approx_percentile. Expected: approx_percentile(bigint, double) , approx_percentile(bigint, bigint, double) , approx_percentile(bigint, bigint, double, double) , approx_percentile(bigint, array(double)) , approx_percentile(bigint, bigint, array(double)) , approx_percentile(double, double) , approx_percentile(double, bigint, double, double) , approx_percentile(double, bigint, double) , approx_percentile(double, array(double)) , approx_percentile(double, bigint, array(double)) , approx_percentile(real, double) , approx_percentile(real, bigint, double, double) , approx_percentile(real, bigint, double) , approx_percentile(real, array(double)) , approx_percentile(real, bigint, array(double))

我理解我的错误，但我找不到使用 AWS Athena/PrestoDB 修复的方法。甚至有可能做这样的事情吗？

Answer 1

我找到了解决方法并在这里分享:

WITH validValues AS 
(SELECT approx_percentile(sessionnumber, ARRAY[0.25,0.50,0.75,0.95, 0.99]) as percentiles from (SELECT sessionnumber from "20180407" where sessionnumber >= 1))

SELECT percentiles FROM testfun, validValues