SELECT查询在1天的时间间隔内使用分析函数LEAD()。

huangapple go评论56阅读模式
英文:

SELECT query over an interval of 1 day and use analytic functions LEAD()

问题

我有一个包含每个工作日的时间戳(称为触发器)的表(CTRL_DT)。我需要通过选择前一天触发时间戳和当天触发时间戳之间的行来处理给定工作日期的数据。通过使用LEAD(),我能够获得下面的输出。只有在每天都有触发器的情况下才有效。

假设有一个触发器被错过了。如何在不增加复杂性的情况下重新编写相同的查询以获得期望的输出。

输入数据:(如果您观察,ctrl_dt 2023-02-16被错过了)。

CAPTURE_DT CTRL_DT INST
2023-02-17 19:21:30.612814 2023-02-18 AAA
2023-02-16 19:18:16.045126 2023-02-17 AAA
2023-02-14 18:58:40.927273 2023-02-15 AAA
2023-02-13 21:43:38.832417 2023-02-14 AAA
2023-02-12 18:30:40.595363 2023-02-13 AAA

期望的输出:

STARTTIME ENDTIME BS_DATE
2023-02-16 19:18:16.045126 2023-02-17 19:21:30.612814 2023-02-17
2023-02-16 19:18:16.045126 2023-02-16
2023-02-14 18:58:40.927273 2023-02-15
2023-02-13 21:43:38.832417 2023-02-14 18:58:40.927273 2023-02-14
2023-02-12 18:30:40.595363 2023-02-13 21:43:38.832417 2023-02-13
2023-02-12 18:30:40.595363 2023-02-12

然而,我得到了以下输出:

STARTTIME ENDTIME BS_DATE
2023-02-16 19:18:16.045126 2023-02-17 19:21:30.612814 2023-02-17
2023-02-14 18:58:40.927273 2023-02-16 19:18:16.045126 2023-02-16
2023-02-13 21:43:38.832417 2023-02-14 18:58:40.927273 2023-02
2023-02-12 18:30:40.595363 2023-02-13 21:43:38.832417 2023-02-13
2023-02-12 18:30:40.595363 2023-02-12

使用的查询:

WITH
EVENT_TRIGGER
AS
    (SELECT '2023-02-17 19:21:30.612814' CAPTURE_DT, '2023-02-18' CTRL_DT, 'AAA' INST FROM DUAL
     UNION ALL
     SELECT '2023-02-16 19:18:16.045126' CAPTURE_DT, '2023-02-17' CTRL_DT, 'AAA' INST FROM DUAL
     UNION ALL
     SELECT '2023-02-14 18:58:40.927273' CAPTURE_DT, '2023-02-15' CTRL_DT, 'AAA' INST FROM DUAL
     UNION ALL
     SELECT '2023-02-13 21:43:38.832417' CAPTURE_DT, '2023-02-14' CTRL_DT, 'AAA' INST FROM DUAL
     UNION ALL
     SELECT '2023-02-12 18:30:40.595363' CAPTURE_DT, '2023-02-13' CTRL_DT, 'AAA' INST FROM DUAL)
     
SELECT LEAD (CAPTURE_DT) OVER (PARTITION BY INST ORDER BY CTRL_DT DESC) AS STARTTIME,
      CAPTURE_DT AS ENDTIME,
      TO_DATE (CTRL_DT, 'YYYY-MM-DD') - 1 AS BS_DATE
      FROM EVENT_TRIGGER
      WHERE INST = 'AAA';
英文:

I have a table which contains timestamps (called triggers) per business day (CTRL_DT). I need to process data for a given business date by selecting rows between previous days trigger timestamp and current days trigger. By using LEAD(), I was able to get below output. This only works if there are triggers every day.

Let's say, one trigger is missed. How do I rewrite the same query without much complexity but get the expected output.

Input Data: (if you observe, ctrl_dt 2023-02-16 is missed).

CAPTURE_DT CTRL_DT INST
2023-02-17 19:21:30.612814 2023-02-18 AAA
2023-02-16 19:18:16.045126 2023-02-17 AAA
2023-02-14 18:58:40.927273 2023-02-15 AAA
2023-02-13 21:43:38.832417 2023-02-14 AAA
2023-02-12 18:30:40.595363 2023-02-13 AAA

Expected Output:

STARTTIME ENDTIME BS_DATE
2023-02-16 19:18:16.045126 2023-02-17 19:21:30.612814 2023-02-17
2023-02-16 19:18:16.045126 2023-02-16
2023-02-14 18:58:40.927273 2023-02-15
2023-02-13 21:43:38.832417 2023-02-14 18:58:40.927273 2023-02-14
2023-02-12 18:30:40.595363 2023-02-13 21:43:38.832417 2023-02-13
2023-02-12 18:30:40.595363 2023-02-12

However, I'm getting

STARTTIME ENDTIME BS_DATE
2023-02-16 19:18:16.045126 2023-02-17 19:21:30.612814 2023-02-17
2023-02-14 18:58:40.927273 2023-02-16 19:18:16.045126 2023-02-16
2023-02-13 21:43:38.832417 2023-02-14 18:58:40.927273 2023-02-14
2023-02-12 18:30:40.595363 2023-02-13 21:43:38.832417 2023-02-13
2023-02-12 18:30:40.595363 2023-02-12

Query Used:

    WITH
    EVENT_TRIGGER
    AS
        (SELECT '2023-02-17 19:21:30.612814' CAPTURE_DT, '2023-02-18' CTRL_DT, 'AAA' INST FROM DUAL
         UNION ALL
         SELECT '2023-02-16 19:18:16.045126' CAPTURE_DT, '2023-02-17' CTRL_DT, 'AAA' INST FROM DUAL
         UNION ALL
         SELECT '2023-02-14 18:58:40.927273' CAPTURE_DT, '2023-02-15' CTRL_DT, 'AAA' INST FROM DUAL
         UNION ALL
         SELECT '2023-02-13 21:43:38.832417' CAPTURE_DT, '2023-02-14' CTRL_DT, 'AAA' INST FROM DUAL
         UNION ALL
         SELECT '2023-02-12 18:30:40.595363' CAPTURE_DT, '2023-02-13' CTRL_DT, 'AAA' INST FROM DUAL)
         
    SELECT LEAD (CAPTURE_DT) OVER (PARTITION BY INST ORDER BY CTRL_DT DESC)     AS STARTTIME,
          CAPTURE_DT                                                           AS ENDTIME,
          TO_DATE (CTRL_DT, 'YYYY-MM-DD') - 1                                  AS BS_DATE
          FROM EVENT_TRIGGER
          WHERE INST = 'AAA';

答案1

得分: 2

你可以生成一个日历,然后使用PARTITIONed OUTER JOIN

WITH EVENT_TRIGGER (CAPTURE_DT, CTRL_DT, INST ) AS (
  SELECT TIMESTAMP '2023-02-17 19:21:30.612814', DATE '2023-02-18', 'AAA' FROM DUAL UNION ALL
  SELECT TIMESTAMP '2023-02-16 19:18:16.045126', DATE '2023-02-17', 'AAA' FROM DUAL UNION ALL
  SELECT TIMESTAMP '2023-02-14 18:58:40.927273', DATE '2023-02-15', 'AAA' FROM DUAL UNION ALL
  SELECT TIMESTAMP '2023-02-13 21:43:38.832417', DATE '2023-02-14', 'AAA' FROM DUAL UNION ALL
  SELECT TIMESTAMP '2023-02-12 18:30:40.595363', DATE '2023-02-13', 'AAA' FROM DUAL
),
calendar (day) AS (
  SELECT min_dt + LEVEL - 1 AS day
  FROM   (
    SELECT MIN(TRUNC(capture_dt)) AS min_dt,
           MAX(TRUNC(capture_dt)) AS max_dt
    FROM   event_trigger
  )
  CONNECT BY min_dt + LEVEL - 1 <= max_dt
)
SELECT LAG(e.capture_dt) OVER (PARTITION BY e.inst ORDER BY c.day)
         AS STARTTIME,
       e.CAPTURE_DT AS ENDTIME,
       c.day AS BS_DATE
FROM   calendar c
       LEFT OUTER JOIN EVENT_TRIGGER e
       PARTITION BY (e.inst)
       ON (
           c.day <= e.capture_dt
       AND e.capture_dt < c.day + 1
       )
WHERE  e.inst = 'AAA'
ORDER BY c.day DESC;

输出如下:

STARTTIME ENDTIME BS_DATE
2023-02-16 19:18:16.045126000 2023-02-17 19:21:30.612814000 2023-02-17 00:00:00
null 2023-02-16 19:18:16.045126000 2023-02-16 00:00:00
2023-02-14 18:58:40.927273000 null 2023-02-15 00:00:00
2023-02-13 21:43:38.832417000 2023-02-14 18:58:40.927273000 2023-02-14 00:00:00
2023-02-12 18:30:40.595363000 2023-02-13 21:43:38.832417000 2023-02-13 00:00:00
null 2023-02-12 18:30:40.595363000 2023-02-12 00:00:00

fiddle

英文:

You can generate a calendar and then use a PARTITIONed OUTER JOIN:

WITH EVENT_TRIGGER (CAPTURE_DT, CTRL_DT, INST ) AS (
  SELECT TIMESTAMP &#39;2023-02-17 19:21:30.612814&#39;, DATE &#39;2023-02-18&#39;, &#39;AAA&#39; FROM DUAL UNION ALL
  SELECT TIMESTAMP &#39;2023-02-16 19:18:16.045126&#39;, DATE &#39;2023-02-17&#39;, &#39;AAA&#39; FROM DUAL UNION ALL
  SELECT TIMESTAMP &#39;2023-02-14 18:58:40.927273&#39;, DATE &#39;2023-02-15&#39;, &#39;AAA&#39; FROM DUAL UNION ALL
  SELECT TIMESTAMP &#39;2023-02-13 21:43:38.832417&#39;, DATE &#39;2023-02-14&#39;, &#39;AAA&#39; FROM DUAL UNION ALL
  SELECT TIMESTAMP &#39;2023-02-12 18:30:40.595363&#39;, DATE &#39;2023-02-13&#39;, &#39;AAA&#39; FROM DUAL
),
calendar (day) AS (
  SELECT min_dt + LEVEL - 1 AS day
  FROM   (
    SELECT MIN(TRUNC(capture_dt)) AS min_dt,
           MAX(TRUNC(capture_dt)) AS max_dt
    FROM   event_trigger
  )
  CONNECT BY min_dt + LEVEL - 1 &lt;= max_dt
)
SELECT LAG(e.capture_dt) OVER (PARTITION BY e.inst ORDER BY c.day)
         AS STARTTIME,
       e.CAPTURE_DT AS ENDTIME,
       c.day AS BS_DATE
FROM   calendar c
       LEFT OUTER JOIN EVENT_TRIGGER e
       PARTITION BY (e.inst)
       ON (
           c.day &lt;= e.capture_dt
       AND e.capture_dt &lt; c.day + 1
       )
WHERE  e.inst = &#39;AAA&#39;
ORDER BY c.day DESC;

Which, outputs:

STARTTIME ENDTIME BS_DATE
2023-02-16 19:18:16.045126000 2023-02-17 19:21:30.612814000 2023-02-17 00:00:00
null 2023-02-16 19:18:16.045126000 2023-02-16 00:00:00
2023-02-14 18:58:40.927273000 null 2023-02-15 00:00:00
2023-02-13 21:43:38.832417000 2023-02-14 18:58:40.927273000 2023-02-14 00:00:00
2023-02-12 18:30:40.595363000 2023-02-13 21:43:38.832417000 2023-02-13 00:00:00
null 2023-02-12 18:30:40.595363000 2023-02-12 00:00:00

fiddle

huangapple
  • 本文由 发表于 2023年2月18日 20:58:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/75493497.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定