如何在单个Teradata查询中输出不同的25％，50％，75％? [英] How to output different 25th, 50th, 75th percentiles in single Teradata query?

问题描述

我花了几个小时来回想类似的事情，并弄出了一个不太混乱的代码，可以在一个Teradata查询中输出25、50、75％百分位.可以进一步扩展以生成" 5分摘要".根据您的人口估算值获取最小和最大变化静态值.

I had got stuck few hours back on around something similar and worked out a less messy code for outputting 25th, 50th, 75th percentiles in a single Teradata query. Can be further extended to produce a "5 point summary". For minimum and maximum change static values according to your population estimate.

某处有人要求一种优雅的方法.分享我的.

Somewhere someone had asked for an elegant approach. Sharing mine.

代码如下:

SELECT MAX(PER_MIN) AS PER_MIN,
       MAX(PER_25) AS PER_25,
       MAX(PER_50)  AS PER_50,
       MAX(PER_75)  AS PER_75,
       MAX(PER_MAX) AS PER_MAX
FROM (SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY DURATION_MACRO_CURR ASC) = CAST(COUNT(*) OVER() * 0.01 AS INT) THEN DURATION_MACRO_CURR END AS PER_MIN,
             CASE WHEN ROW_NUMBER() OVER(ORDER BY DURATION_MACRO_CURR ASC) = CAST(COUNT(*) OVER() * 0.25 AS INT) THEN DURATION_MACRO_CURR END AS PER_25,
             CASE WHEN ROW_NUMBER() OVER(ORDER BY DURATION_MACRO_CURR ASC) = CAST(COUNT(*) OVER() * 0.50 AS INT) THEN DURATION_MACRO_CURR END AS PER_50
             CASE WHEN ROW_NUMBER() OVER(ORDER BY DURATION_MACRO_CURR ASC) = CAST(COUNT(*) OVER() * 0.75 AS INT) THEN DURATION_MACRO_CURR END AS PER_75
             CASE WHEN ROW_NUMBER() OVER(ORDER BY DURATION_MACRO_CURR ASC) = CAST(COUNT(*) OVER() * 0.99 AS INT) THEN DURATION_MACRO_CURR END AS PER_MAX
      FROM PROD_EXP_DL_CVM.PROD_CVM
      WHERE PW_END_DATE =  '2016-10-18'
    ) BASE

这是所需的输出:

推荐答案

我将使用条件聚合来做到这一点:

I would do this using conditional aggregation:

select min(DURATION_MACRO_CURR) as min_val,
       min(case when seqnum / 0.25 >= cnt then DURATION_MACRO_CURR end) as 25_percentile,
       min(case when seqnum / 0.50 >= cnt then DURATION_MACRO_CURR end) as 50_percentile,
       min(case when seqnum / 0.75 >= cnt then DURATION_MACRO_CURR end) as 75_percentile,
       max(DURATION_MACRO_CURR) as max_val
from (select pc.*,
             row_number() over (order by DURATION_MACRO_CURR) as seqnum,
             count(*) over () as cnt
      from PROD_EXP_DL_CVM.PROD_CVM pc
      where pc.PW_END_DATE =  '2016-10-18'
     ) pc;

这篇关于如何在单个Teradata查询中输出不同的25％，50％，75％?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！