英文:
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
你可以生成一个日历,然后使用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;
输出如下:
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 |
英文:
You can generate a calendar and then use a 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;
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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论