时间戳

首页 > 解决方案 > Oracle查询按连续值分组并获取开始日期和结束日期

oracle - Oracle查询按连续值分组并获取开始日期和结束日期

问题描述

我有一个这样的表（实际上是大型查询的结果）：

id   |  date_measured        |  out_of_range
-----+-----------------------+--------------
3147 |  09/08/2019 20.00:00  |  1
3147 |  09/08/2019 21.00:00  |  0
3147 |  09/08/2019 22.00:00  |  0
3147 |  09/08/2019 23.00:00  |  1
3147 |  10/08/2019 00.00:00  |  1
3147 |  10/08/2019 01.00:00  |  1
3147 |  10/08/2019 02.00:00  |  0
3125 |  09/08/2019 20.00:00  |  0
3125 |  09/08/2019 21.00:00  |  1
3125 |  09/08/2019 22.00:00  |  1
3125 |  09/08/2019 23.00:00  |  0
3125 |  10/08/2019 00.00:00  |  1
3125 |  10/08/2019 01.00:00  |  1
3125 |  10/08/2019 02.00:00  |  1

我需要这个结果：

id   |  date_measured_start  |  date_measured_end    |  consecutive_out_of_range
-----+-----------------------+-----------------------+--------------------------
3147 |  09/08/2019 20.00:00  |  09/08/2019 20.00:00  |  1
3147 |  09/08/2019 23.00:00  |  10/08/2019 01.00:00  |  3
3125 |  09/08/2019 21.00:00  |  09/08/2019 22.00:00  |  2
3125 |  10/08/2019 00.00:00  |  10/08/2019 02.00:00  |  3

这是值out_of_range = 1和相对开始和结束日期的连续重复。

我尝试使用此解决方案，但我不能只有连续的1用于out_of_range。价值。

标签： oracledatedatetimegroup-by

解决方案

ROW_NUMBER如果给每一行两个递增的数值，则使用分析函数 - 每对一个id，另一个每id对out_of_range。如果您从另一个中减去一个，则结果数字将在具有相同id/out_of_range值的一组连续行中保持不变，您可以使用它来GROUP BY：

查询：

SELECT id,
       MIN( date_measured ) AS date_measured_start,
       MAX( date_measured ) AS date_measured_end,
       COUNT( * ) AS consecutive_out_of_range
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY id ORDER BY date_measured )
           - ROW_NUMBER() OVER ( PARTITION BY id, out_of_range ORDER BY date_measured )
           AS rn
  FROM   table_name t
)
WHERE out_of_range = 1
GROUP BY id, rn

输出：

  身份证 | DATE_MEASURED_START | DATE_MEASURED_END | CONSECUTIVE_OUT_OF_RANGE
---: | :----------------- | :----------------- | ----------------------:
第3147章 2019-08-09 20:00:00 | 2019-08-09 20:00:00 | 1
第3147章 2019-08-09 23:00:00 | 2019-08-10 01:00:00 | 3
3125 | 2019-08-10 00:00:00 | 2019-08-10 02:00:00 | 3
3125 | 2019-08-09 21:00:00 | 2019-08-09 22:00:00 | 2

db<>在这里摆弄

推荐阅读