sql - 如何在 Redshift 中实现窗口运行中位数？

Question

我正在竭尽全力尝试创建一个运行/累积median按时间顺序排列的分区值。基本上我有一张 table :

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 function 以多种方式执行此操作，然而这在 Redshift 中被证明是困难的，因为它只有聚合中位数

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

在 Redshift 上运行上述命令会出现错误:

ERROR: window specification should not contain frame clause and order-by for window function median

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

ERROR: This type of correlated subquery pattern is not supported due to internal error

Here are a bunch of fiddles可以在 PostGres 中使用，但不能在 Redshift 中使用

此时看来我需要将数据拉入内存并 do this in code ，但如果这可以直接在 Redshift 中完成，我们将不胜感激。

Answer 1

我想知道你是否可以使用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;

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