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

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

我想要一个查询，该查询必须仅检查之前的连续天数并获取分配为“相同” 的计数。

我想按日期选择，例如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 Last day 不是必需的:

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';

说明:

派生表用于计算每个日期和分配的不同“分区”部分:

  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 生成的具体日期无关紧要。这只是分配中每个“组”日期都相同的某个日期。然后，您可以使用 count(*) over(...) 窗口函数计算 (allocation, part) 的相同值的数量:

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
);