Databricks中percentile_disc函数报错排查及替代方案咨询

错误原因 #

你的SQL触发ParseException主要有两个问题：

• 别名引用顺序错误：主查询里，你先定义了sum(TotalQuantity) as TotalQuantity这个别名，后面percentile_disc函数里写ORDER BY TotalQuantity时，SQL解析器会误以为你要引用这个刚定义的聚合后别名，但SELECT子句里的别名不能被同一句子内的其他表达式直接引用，导致解析失败。
• 缺少字段别名：第13、14行的percentile_disc计算结果没有指定别名，不符合SQL语法要求，进一步引发解析异常。

修正后的SQL代码 #

把percentile_disc里的排序字段指定为子查询的原始字段（用表别名DETAL限定），同时给每个百分位计算结果加上别名：

select
        customerid,
        yearid,
        monthid,
        sum(TotalSpendings) as TotalSpendings,
        sum(TotalQuantity) as TotalQuantity,
        count (distinct ticketid) as TotalTickets,
        AVG(AvgIndexesPerTicket) as AvgIndexesPerTicket,
        max (transactiondate) as DateOfLastVisit,
        count(distinct transactiondate) as TotalNumberOfVisits,
        AVG(TotalSpendings) as AverageTicket,
        sum(TotalQuantity)/count(distinct ticketid) as AvgQttyPerTicket,
        sum(TotalDiscount) as TotalDiscount,
        percentile_disc(0.25) WITHIN GROUP (ORDER BY DETAL.TotalQuantity) as PercentileQttyTicket_25, 
        percentile_disc(0.50) WITHIN GROUP (ORDER BY DETAL.TotalQuantity) as PercentileQttyTicket_50,
        percentile_disc(0.75) WITHIN GROUP (ORDER BY DETAL.TotalQuantity) as PercentileQttyTicket_75,
        percentile_disc(0.90) WITHIN GROUP (ORDER BY DETAL.TotalQuantity) as PercentileQttyTicket_90,
        
        percentile_disc(0.25) WITHIN GROUP (ORDER BY DETAL.TotalSpendings) as PercentileSpendingsTicket_25,
        percentile_disc(0.50) WITHIN GROUP (ORDER BY DETAL.TotalSpendings) as PercentileSpendingsTicket_50,
        percentile_disc(0.75) WITHIN GROUP (ORDER BY DETAL.TotalSpendings) as PercentileSpendingsTicket_75,
        percentile_disc(0.90) WITHIN GROUP (ORDER BY DETAL.TotalSpendings) as PercentileSpendingsTicket_90
        
from (

select
        a.customerid,
        a.ticketid,
        a.transactiondate,
        extract(year from a.transactiondate) as yearid,
        extract(month from a.transactiondate) as monthid,
        sum(positionvalue) as TotalSpendings,
        sum(quantity) as TotalQuantity,   
        count(distinct productindex)/count(distinct a.ticketid) as AvgIndexesPerTicket,
        sum(discountvalue) as TotalDiscount
        from default.TICKET_ITEM a
          

        where 1=1
        and a.transactiondate between '2022-10-01' and '2022-10-31'
        and a.transactiontype = 'S'
        and a.transactiontypeheader = 'S'
        and a.customerid in ('94861b2c83c54d03930af4585a3a325a')
        and length(a.customerid) > 10
        group by 1,2,3,4,5) DETAL
        
group by 1,2,3

其他计算百分位的方法 #

除了percentile_disc，Databricks还支持以下几种方式计算百分位：

• percentile_cont：返回连续插值的百分位值，适用于需要平滑结果的场景，语法和percentile_disc完全一致，只是函数名不同。
• approx_percentile：针对大数据量优化的近似计算函数，效率更高，语法为approx_percentile(DETAL.TotalQuantity, 0.25)，不需要WITHIN GROUP子句。
• NTILE窗口函数：将分组内的数据分成指定份数，取对应分组的边界值作为近似百分位，比如计算25分位可以写成：

  max(case when ntile(4) over(partition by customerid, yearid, monthid order by DETAL.TotalQuantity) = 1 then DETAL.TotalQuantity end) as PercentileQttyTicket_25
  

  这种方法是近似值，适合快速估算的场景。

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