oracle 间隔为1天的SELECT查询，并使用分析函数LEAD()

czq61nw1 于 2023-03-01 发布在 Oracle

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

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

oracle

来源：https://stackoverflow.com/questions/75493497/select-query-over-an-interval-of-1-day-and-use-analytic-functions-lead

1条答案

按热度按时间

d8tt03nd1#

您可以生成日历，然后使用PARTITION ed 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;

其中，输出：
| 开始时间|结束时间|BS_日期|
| - ------|- ------|- ------|
| 2023年2月16日19时18分16.045126000秒|2023年2月17日19时21分30秒|2023年2月17日00时00分|
| * 无效 |2023年2月16日19时18分16.045126000秒|2023年2月16日00时00分|
| 2023年2月14日18时58分40.927273000秒| 无效 *|2023年2月15日00时00分|
| 2023年2月13日21时43分38.832417000秒|2023年2月14日18时58分40.927273000秒|2023年2月14日00时00分|
| 2023年2月12日18时30分：40.595363000|2023年2月13日21时43分38.832417000秒|2023年2月13日00时00分|
| * 无效 *|2023年2月12日18时30分：40.595363000|2023年2月12日00时00分|
fiddle

赞(0）回复(0）举报 2023-03-01

我来回答

oracle 间隔为1天的SELECT查询，并使用分析函数LEAD()

1条答案

相关问题

热门标签

最新问答