SQL百分比计算运行速度非常缓慢 - 需要帮助加快速度

Question

我有一张大表（现在大约有8.5亿行），并且需要在插入新数据时每周计算百分位数值，静态数据会变得很脏。但是，该过程非常慢（使用我的硬件/当前查询5-6小时）。

如何更改我的查询以加快查询速度？

现在，我的查询基本上是这样的：

SELECT DISTINCT [ident1] 
    ,[ident2] 
    ,[ident3] 
    ,[ident4] 
    ,percentile_cont(0.05) 
     WITHIN GROUP (
      ORDER BY [value] ASC 
     ) OVER (
      PARTITION BY [ident1] 
       ,[ident2] 
       ,[ident3] 
       ,[ident4] 
     ) AS [percentile_5] 
    ,percentile_cont(0.10) 
     WITHIN GROUP (
      ORDER BY [value] ASC 
     ) OVER (
      PARTITION BY [ident1] 
       ,[ident2] 
       ,[ident3] 
       ,[ident4] 
     ) AS [percentile_10] 
    ,percentile_cont(0.25) 
     WITHIN GROUP (
      ORDER BY [value] ASC 
     ) OVER (
      PARTITION BY [ident1] 
       ,[ident2] 
       ,[ident3] 
       ,[ident4] 
     ) AS [percentile_25] 
    ,percentile_cont(0.50) 
     WITHIN GROUP (
      ORDER BY [value] ASC 
     ) OVER (
      PARTITION BY [ident1] 
       ,[ident2] 
       ,[ident3] 
       ,[ident4] 
     ) AS [percentile_50] 
    ,percentile_cont(0.75) 
     WITHIN GROUP (
      ORDER BY [value] ASC 
     ) OVER (
      PARTITION BY [ident1] 
       ,[ident2] 
       ,[ident3] 
       ,[ident4] 
     ) AS [percentile_75] 
    ,percentile_cont(0.90) 
     WITHIN GROUP (
      ORDER BY [value] ASC 
     ) OVER (
      PARTITION BY [ident1] 
       ,[ident2] 
       ,[ident3] 
       ,[ident4] 
     ) AS [percentile_90] 
    ,percentile_cont(0.95) 
     WITHIN GROUP (
      ORDER BY [value] ASC 
     ) OVER (
      PARTITION BY [ident1] 
       ,[ident2] 
       ,[ident3] 
       ,[ident4] 
     ) AS [percentile_95] 
FROM dataTable 

我认为这个问题的一部分是用出来的DISTINCT选择我得到一个排在DB的每个值。 SQL是否足够聪明，可以为每个组计算一次百分位数？或者是为每个值重复计算？

任何帮助将不胜感激。

Answer 1

把不同的子查询：

select . . . 
from (select distinct . . .) s; 

distinct应该发生后所有select列已计算。