获取满足给定条件的连续天数

Question

我的Oracle数据库中具有以下结构：

Date          Allocation  id
2015-01-01    Same        200
2015-01-02    Good        200
2015-01-03    Same        200
2015-01-04    Same        200
2015-01-05    Same        200
2015-01-06    Good        200

我想查询只需要检查前连续几天并获取“分配”所在的计数的查询"Same"。

我想按日期选择，例如2015-01-05。
示例输出：对于日期2015-01-05，计数为3。

新问题。通过Lukas Eder的查询，计数始终为1或2。但预期是3。为什么？！

Date          Allocation  id
2015-01-01    Same        400
2015-01-02    Good        400
2015-01-03    Same        400
2015-01-04    Same        400
2015-01-05    Same        400
2015-01-06    Good        400

卢卡斯·埃德（Lukas Eder）的代码

 SELECT c
    FROM (
      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';

预期的输出是这样的，First_day end不需要最后一天：

id   count    first_day   Last_Day
200  3        2015-01-03  2015-01-05
400  3        2015-01-03  2015-01-05

Answer 1

此查询将产生每一行的计数：

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
)
ORDER BY d;

然后，您可以对其进行过滤以找到给定行的计数：

SELECT c
FROM (
  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  d           part
--------------------------------
Same        01.01.15    31.12.14
Good        02.01.15    01.01.15
Same        03.01.15    01.01.15
Same        04.01.15    01.01.15
Same        05.01.15    01.01.15
Good        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 c
FROM (...)
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
);