sql - 在sql server中查找表中每个日期的中位数

Question

我使用下面的查询来查找每个扇区的中位数

SELECT DISTINCT Sector,
    PERCENTILE_DISC(0.5) WITHIN
GROUP (ORDER BY Value) OVER (PARTITION BY sector) AS Median
FROM TABLE

表格格式如下

    Sector  Date    Value
    A   2014-08-01  1
    B   2014-08-01  5
    C   2014-08-01  7
    A   2014-08-02  6
    B   2014-08-02  5
    C   2014-08-02  4
    A   2014-08-03  3
    B   2014-08-03  9
    C   2014-08-03  6
    A   2014-08-04  5
    B   2014-08-04  8
    C   2014-08-04  9
    A   2014-08-05  5
    B   2014-08-05  7
    C   2014-08-05  2   

所以我得到的预期结果如下

    Sector  Median
    A   5
    B   7
    C   6

现在我需要更改流程，以便在仅考虑给定日期之前的记录的情况下计算中位数。所以新的结果是

    Sector  Date    Value
    A   2014-08-01  1
    B   2014-08-01  5
    C   2014-08-01  7 (Only 1 record each was considered for A, B and C) 

    A   2014-08-02  3.5
    B   2014-08-02  5
    C   2014-08-02  5.5 (2 records each was considered for A, B and C)

    A   2014-08-03  3
    B   2014-08-03  5
    C   2014-08-03  6 (3 records each was considered for A, B and C)

    A   2014-08-04  4
    B   2014-08-04  6.5
    C   2014-08-04  6.5 (4 records each was considered for A, B and C)

    A   2014-08-05  5
    B   2014-08-05  7
    C   2014-08-05  6 (All 5 records each was considered for A, B and C) 

所以这将是一种累积中位数。有人可以告诉我如何实现这一目标吗？我的表有大约 230 万条记录，每条记录大约有 1100 个记录，对应大约 1100 个日期。

如果您需要任何信息，请告诉我。

Answer 1

另一种方法是创建一个三角形JOIN来获取每天的所有过去值并将其用作数据

;With T AS (
  SELECT t2.Sector, t2.[Date], t1.[Value]
  FROM   Table1 t1
         LEFT  JOIN Table1 t2 ON t1.Sector = t2.Sector and t1.[Date] <= t2.[Date]
)
SELECT DISTINCT Sector
     , [Date]
     , PERCENTILE_CONT(0.5) 
         WITHIN GROUP (ORDER BY [Value]) 
         OVER (PARTITION BY sector, [Date]) AS Median 
FROM   T
ORDER BY [Date], Sector;

SQLFiddle demo

在查询中，我将 PERCENTILE_DISC 更改为 PERCENTILE_CONT，以便在出现偶数个值时(例如第二天)获得正确的中位数。