sql - 在Teradata中一次通过获取多个百分位(等效于percentile_cont)

我了解我们可以在Teradata中将percentile_cont重写为：

SELECT
  part_col
  ,data_col
   + ((MIN(data_col) OVER (PARTITION BY part_col ORDER BY data_col ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - data_col)
       * (((COUNT(*) OVER (PARTITION BY part_col) - 1) * x) MOD 1)) AS percentile_cont
FROM tab
QUALIFY ROW_NUMBER() OVER (PARTITION BY part_col ORDER BY data_col)
    = CAST((COUNT(*) OVER (PARTITION BY part_col) - 1) * x AS INT) + 1;

有关更多信息，请参见this very helpful discussion。

理解用x替换0.90将返回第90个百分位数，是否有一种优雅的方法来扩展它并在一次通过中返回多个百分位数？

例如，假设我要扩展此示例并一次通过返回第25、50和75个百分位数？这可能吗？好像我需要多个QUALIFY语句？同样，如果我需要多个GROUP BY等效项，这类似于在PARTITION BY中传递更多列吗？

-- SQL:2008 Equivalent pseudo-code
SELECT
  part_col_a
 ,part_col_b
 ,PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY order_col) AS p25
 ,PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY order_col) AS p50
 ,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY order_col) AS p75
FROM tab
GROUP BY
  part_col_a
  ,part_col_b

最佳答案

您应该完整阅读我的博客，最终查询完全是您想要的：-)

SELECT part_col
  ,MIN(pc25) OVER (PARTITION BY part_col) AS quartile_1
  ,MIN(pc50) OVER (PARTITION BY part_col) AS quartile_2
  ,MIN(pc75) OVER (PARTITION BY part_col) AS quartile_3
FROM
 (
  SELECT
    part_col
    ,COUNT(*)      OVER (PARTITION BY part_col) - 1 AS N
    ,ROW_NUMBER()  OVER (PARTITION BY part_col ORDER BY data_col) - 1 AS rowno
    ,MIN(data_col) OVER (PARTITION BY part_col ORDER BY data_col ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - data_col AS diff

    ,CASE
       WHEN rowno = CAST(N * 0.25 AS INT)
       THEN data_col +(((N * 0.25) MOD 1) * diff)
     END AS pc25

    ,CASE
       WHEN rowno = CAST(N * 0.50 AS INT)
       THEN data_col +(((N * 0.50) MOD 1) * diff)
     END AS pc50

    ,CASE
       WHEN rowno = CAST(N * 0.75 AS INT)
       THEN data_col +(((N * 0.75) MOD 1) * diff)
     END AS pc75
  FROM tab
  QUALIFY   rowno = CAST(N * 0.25 AS INT)
       OR   rowno = CAST(N * 0.50 AS INT)
       OR   rowno = CAST(N * 0.75 AS INT)
 ) AS dt
QUALIFY ROW_NUMBER() OVER (PARTITION BY part_col ORDER BY part_col) = 1

关于sql - 在Teradata中一次通过获取多个百分位(等效于percentile_cont)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35733463/