oracle SQL中合并连续赋值语句获取最小和最大赋值日期

c90pui9n 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(114)

我正试图从Oracle数据库中提取一些信息。我有一个select语句，目前拉取以下信息：

ACCOUNT_ID：每个帐户的唯一标识符。该帐户应始终分配给某人（STRT_DT和END_DT中没有间隙）。
STAFF_ID：分配给该账户的工作人员的唯一标识符。工作人员将同时被分配到多个账户，并可能在一段时间内停止处理一个账户，然后再被分配回该账户。
STRT_DT：员工在帐户上开始分配的日期
END_DT：员工在帐户上的任务结束日期。如果帐户已关闭，则该帐户可能具有最终结束日期，如果帐户仍处于打开状态，则该值将为NULL
RANK：根据开始日期，指示帐户的每个分配发生的顺序。

工作人员有多种类型的任务，这导致同一工作人员经常有背靠背的任务，如下所示：
| 账户ID|个人ID| STRT_DT|结束_DT|秩|
| --|--|--|--|--|
| 223 |一|23年1月1日|2019 - 01 - 10| 1 |
| 223 |一|2019 - 01 - 10| 2019 - 01 - 14| 2 |
| 223 |一|2019 - 01 - 14| 2019 - 01 - 16| 3 |
| 223 |B| 2019 - 01 - 16| 2019年02月02日| 4 |
| 223 |C| 2019年02月02日|2019 - 02 - 13| 5 |
| 223 |C| 2019 - 02 - 13| 2019 - 02 - 19| 6 |
| 223 |一|2019 - 02 - 19| NULL| 7 |
| 461 |一|2019 - 01 - 10| 2019 - 01 - 23| 1 |
| 461 |C| 2019 - 01 - 23| 2019 - 02 - 14| 2 |
| 461 |一|2019 - 02 - 14| 2019 - 03 - 11| 3 |
| 461 |B| 2019 - 03 - 11| 03/29/23| 4 |
工作人员在一个账户上有两个或两个以上不连续的时间段是可能的。这是指有另一个人被分配到帐户之间的排名。这在账户223中得到了证明，其中人员A在1/16结束其分配，有其他工作人员被分配到该案例，然后他们在2/19重新开始。
我需要将相同ACCOUNT_ID上有相同STAFF_ID的行连续压缩，以便每行指示工作人员首次开始使用该帐户的时间以及最终结束的时间。输出应该如下所示：
| ROW_ID|账户ID| STAFF_ID| STRT_DT|结束_DT|
| --|--|--|--|--|
| 1 | 223 |一|23年1月1日|2019 - 01 - 16|
| 2 | 223 |B| 2019 - 01 - 16| 2019年02月02日|
| 3 | 223 |C| 2019年02月02日|2019 - 02 - 19|
| 4 | 223 |一|2019 - 02 - 19| NULL|
| 5 | 461 |一|2019 - 01 - 10| 2019 - 01 - 23|
| 6 | 461 |C| 2019 - 01 - 23| 2019 - 02 - 14|
| 7 | 461 |一|2019 - 02 - 14| 2019 - 03 - 11|
| 8 | 461 |B| 2019 - 03 - 11| 03/29/23|
除了数据库中已经存在的表之外，我不能访问临时表--如果需要的话，可以使用临时表，并且根据需要为每个数据类型提供尽可能多的列。我主要使用select语句来拉取数据，所以我对插入表和更新表不太有经验。
我假设我需要使用某种循环语句，但我以前没有使用过它们。任何关于如何构建它的指导，资源链接或示例代码都将非常有帮助。我真的不知道从何说起。
编辑：当我一直在做这件事的时候，我想也可以使用LAG（）/LEAD（）或FIRST_VALUE进行一些分区，但我也很难把它们放在一起。我会更新，如果我有任何进展。

oracle

来源：https://stackoverflow.com/questions/77047170/merging-consecutive-assignment-rows-to-get-min-and-max-date-of-assignment-in-sql

1条答案

按热度按时间

jei2mxaa1#

在Oracle 12中，您可以使用MATCH_RECOGNIZE进行逐行模式匹配：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY account_id
  ORDER     BY strt_dt
  MEASURES
    FIRST(person_id) AS person_id,
    FIRST(strt_dt)   AS strt_dt,
    LAST(end_dt)     AS end_dt
  PATTERN (person same_person*)
  DEFINE
    same_person AS PREV(person_id) = person_id
                AND PREV(end_dt) = strt_dt
);

或者，在早期版本中，您可以使用：用途：

SELECT account_id,
       MIN(person_id) AS person_id,
       MIN(strt_dt) KEEP (DENSE_RANK FIRST ORDER BY strt_dt) AS strt_dt,
       MAX(end_dt) KEEP (DENSE_RANK LAST ORDER BY strt_dt) AS end_dt
FROM   (
  SELECT t.*,
         SUM(has_changed_grp) OVER (PARTITION BY account_id ORDER BY strt_dt)
           AS grp
  FROM   (
    SELECT t.*,
           CASE
           WHEN person_id = LAG(person_id)
                              OVER (PARTITION BY account_id ORDER BY strt_dt)
           THEN 0
           ELSE 1
           END AS has_changed_grp
    FROM   table_name t
  ) t
)
GROUP BY
       account_id,
       grp

其中，对于样本数据：

CREATE TABLE table_name (ACCOUNT_ID, PERSON_ID, STRT_DT, END_DT, RANK) AS
SELECT 223, 'A', DATE '2023-01-01', DATE '2023-01-10', 1 FROM DUAL UNION ALL
SELECT 223, 'A', DATE '2023-01-10', DATE '2023-01-14', 2 FROM DUAL UNION ALL
SELECT 223, 'A', DATE '2023-01-14', DATE '2023-01-16', 3 FROM DUAL UNION ALL
SELECT 223, 'B', DATE '2023-01-16', DATE '2023-02-02', 4 FROM DUAL UNION ALL
SELECT 223, 'C', DATE '2023-02-02', DATE '2023-02-13', 5 FROM DUAL UNION ALL
SELECT 223, 'C', DATE '2023-02-13', DATE '2023-02-19', 6 FROM DUAL UNION ALL
SELECT 223, 'A', DATE '2023-02-19', NULL,              7 FROM DUAL UNION ALL
SELECT 461, 'A', DATE '2023-01-10', DATE '2023-01-23', 1 FROM DUAL UNION ALL
SELECT 461, 'C', DATE '2023-01-23', DATE '2023-02-14', 2 FROM DUAL UNION ALL
SELECT 461, 'A', DATE '2023-02-14', DATE '2023-03-11', 3 FROM DUAL UNION ALL
SELECT 461, 'B', DATE '2023-03-11', DATE '2023-03-29', 4 FROM DUAL;

两个输出：
| 账户ID|个人ID| STRT_DT|结束_DT|
| --|--|--|--|
| 223 |一|2019 -01- 21 00：00：00| 2023-01-16 00：00：00|
| 223 |B| 2023-01-16 00：00：00| 2023-02-02 00：00：00：00|
| 223 |C| 2023-02-02 00：00：00：00| 2023-02-19 00：00：00|
| 223 |一|2023-02-19 00：00：00| * 空 *|
| 461 |一|2023-01-10 00：00：00| 2023-01-23 00：00：00|
| 461 |C| 2023-01-23 00：00：00| 2019 -02-14 00：00：00|
| 461 |一|2019 -02-14 00：00：00| 2023-03-11 00：00：00|
| 461 |B| 2023-03-11 00：00：00| 2019 -03-29 00：00：00|
fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

oracle SQL中合并连续赋值语句获取最小和最大赋值日期

1条答案

相关问题

热门标签

最新问答