此查询将产生每一行的计数：
SELECt allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS cFROM ( SELECt allocation, d, d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part FROM t)ORDER BY d;
然后，您可以对其进行过滤以找到给定行的计数：
SELECt cFROM ( SELECt allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS c FROM ( SELECt allocation, d,d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part FROM t ))WHERe d = DATE '2015-01-05';
解释：
派生表用于
part为每个日期和分配计算不同的“分区” ：
SELECt allocation, d, d - row_number() OVER (PARTITION BY allocation ORDER BY d) AS part FROM t
结果是：
allocation dpart--------------------------------Same 01.01.15 31.12.14Good 02.01.15 01.01.15Same 03.01.15 01.01.15Same 04.01.15 01.01.15Same 05.01.15 01.01.15Good 06.01.15 04.01.15
由产生的具体日期
part无关紧要。分配中的每个“组”日期都只是相同的某个日期。然后，您可以
(allocation, part)使用
count(*)over(...)window函数计算相同值的数量：
SELECt allocation, d, count(*) OVER (PARTITION BY allocation, part ORDER BY d) AS cFROM (...)ORDER BY d;
产生想要的结果。
数据
我已将下表用作示例：
CREATE TABLE t AS ( SELECt DATE '2015-01-01' AS d, 'Same' AS allocation FROM dual UNIOn ALL SELECt DATE '2015-01-02' AS d, 'Good' AS allocation FROM dual UNIOn ALL SELECt DATE '2015-01-03' AS d, 'Same' AS allocation FROM dual UNIOn ALL SELECt DATE '2015-01-04' AS d, 'Same' AS allocation FROM dual UNIOn ALL SELECt DATE '2015-01-05' AS d, 'Same' AS allocation FROM dual UNIOn ALL SELECt DATE '2015-01-06' AS d, 'Good' AS allocation FROM dual);
