如何在红移中实现窗口化的运行中位数？

Question

我正在拉出我的头发，试图按时间顺序创建分区值的运行/累积中位数。基本上我有一个表：

create table "SomeData"
(
    ClientId INT,
    SomeData DECIMAL(10,2),
    SomeDate TIMESTAMP
);

有了一些数据:

INSERT INTO "SomeData" (ClientId, SomeData, SomeDate) VALUES
(1, 1, '1 Jan 2000'),
(1, 2, '2 Jan 2000'),
(1, 3, '3 Jan 2000'),
(1, 4, '4 Jan 2000'),
(2, 100, '1 Jan 2000'),
(2, 100, '2 Jan 2000'),
(2, 100, '3 Jan 2000'),
(2, 200, '4 Jan 2000'),
(2, 200, '5 Jan 2000'),
(2, 200, '6 Jan 2000'),
(2, 200, '7 Jan 2000');

我需要一个由 ClientId 分区的运行中位数，按 SomeDate 排序。

基本上，我需要生产的是这样的：

ClientId    SomeDate      Median of SomeData
1           "2000-01-01"  1.000
1           "2000-01-02"  1.500
1           "2000-01-03"  2.000
1           "2000-01-04"  2.500
2           "2000-01-01"  100.0
2           "2000-01-02"  100.0
2           "2000-01-03"  100.0
2           "2000-01-04"  100.0
2           "2000-01-05"  100.0
2           "2000-01-06"  150.0
2           "2000-01-07"  200.0

在PostgresSql 9.x中，我能够以多种方式使用Aggregate_median函数来做到这一点，但是这在Redshift中被证明是困难的，它只有一个聚合的中位数

SELECT ClientId, SomeDate, median(SomeData) OVER (PARTITION BY ClientId ORDER BY SomeDate)
FROM "SomeData" xout
ORDER BY ClientId, SomeDate;

然而，在Redshift上运行上述内容会出现错误：

错误：窗口规范不应包含框架子句和窗口函数中位数的排序依据

中位数可以用手动关联子查询替换回原始表，但 RedShift 似乎也不支持这些。

错误：由于内部错误，不支持此类型的相关子查询模式

这里有一堆在PostGres中工作的小提琴，在Redshift中都不起作用

在这一点上，我似乎需要将数据拉入内存并在代码中执行此操作，但如果可以直接在Redshift中完成此操作，我将不胜感激。

Answer 1

这是对您要查找的数量的精确计算。

本身并不性感，但它正确地处理了奇数和偶数长度的中间值。

with row_numbers as (
    SELECT d.partitionField -- the field (or fields) you are partitioning the window function by
         , d.orderField  -- your sort field for the window functions
         , d.medianField -- quantity your are computing the median of

         , ROW_NUMBER() 
           OVER (PARTITION BY partitionField ORDER BY orderField) as seqnum

    FROM data d
)

, medians as (    
    SELECT nth_value(medianField, CASE 
                                  WHEN mod(seqnum, 2) = 0 THEN (seqnum/2)::int 
                                  ELSE ((seqnum/2)::int + 1) 
                                  END) 
           OVER (PARTITION BY partitionField ORDER BY orderField ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as median1

         , nth_value(medianField, (seqnum/2)::int + 1) OVER (PARTITION BY partitionField ORDER BY orderField ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as median2

         , mod(seqnum, 2) as mod1
    FROM row_numbers
    ORDER BY partitionField, orderField
)

select CASE
       when mod(mod1,2) = 0
       then ((median1 + median2)/2)::FLOAT
       else median1
       end as median
from medians

Answer 2

我认为@GordonLinoff提出的解决方案是不正确的，因为它没有对具有您试图寻找的中值的行进行排序。正确的方式受到了启发:

移动中位数，T-SQL 中的模式

工作在红移：

WITH CTE
AS
(
SELECT  ClientId,
        ROW_NUMBER() OVER (PARTITION BY ClientId ORDER BY SomeDate ASC) row_num,
        SomeDate,
        SomeData
FROM "SomeData" 
)
SELECT A.SomeDate,
       A.SomeData,
                (SELECT  MEDIAN(B.SomeData)
                FROM CTE B 
                WHERE B.row_num BETWEEN 1 AND A.row_num 
                GROUP BY A.ClientId) AS median
FROM CTE A

Answer 3

我想知道您是否可以使用nth_value（）做到这一点：

SELECT ClientId, SomeDate,
       NTH_VALUE(seqnum / 2) OVER (PARTITION BY ClientId ORDER BY SomeDate)
FROM (SELECT s.*,
             COUNT(*) OVER (PARTITION BY ClientId ORDER BY SomeDate) as seqnum
      FROM SomeData s
     ) s
ORDER BY ClientId, SomeDate;

注意:使用< code>COUNT(*)而不是< code>ROW_NUMBER()需要一些时间来适应。