所有分钟分组时甲骨文案

Question

我准备了一个SQL，该SQL按每分钟计算交易。但是我想在没有交易的情况下获得分钟。我找不到任何解决方案。

我的SQL：

SELECT SUM(CASE
             WHEN UPPER(situation) = 'OK' THEN
              1
             ELSE
              0
           END) AS OK,
       SUM(CASE
             WHEN UPPER(situation) = 'NOK' THEN
              1
             ELSE
              0
           END) AS NOK,
       TO_CHAR(q1.start_date, 'DD/MM/YYYY HH24:MI') AS StartDate,
       TO_CHAR(q1.start_date, 'YYYY/MM/DD HH24:MI') AS FormatStartDate
  FROM table1 q1
 WHERE q1.start_date >=
       TO_DATE(TO_CHAR((sysdate - 4 / 24), 'dd/mm/yyyy hh24:mi'),
               'dd/mm/yyyy hh24:mi')
   AND q1.start_date <=
       TO_DATE(TO_CHAR((sysdate - 0 / 24), 'dd/mm/yyyy hh24:mi'),
               'dd/mm/yyyy hh24:mi')
   AND q1.type = 'TEST'
 GROUP BY q1.type,
          TO_CHAR(q1.start_date, 'DD/MM/YYYY HH24:MI'),
          TO_CHAR(q1.start_date, 'YYYY/MM/DD HH24:MI')
 ORDER BY FormatStartDate

SQL结果：

OK	Nok	StartDate	FormatStartDate
2	0	26/04/2022 11:51	2022/04/26 11:51
3	0	26/04/2022 11:52	2022/04/04/26 11:52 11:52
1	0	26/26/26/26/26/ 04/2022 11:54	2022/04/26 11:54
2	0	26/04/2022 11:56	2022/04/26 11:56

您可以在结果中看到，线26/04/2022 11:53 26/04/2022 11:55缺少。即使是0，我也想在结果中看到这些行。

Answer 1

在数据仓库中，您有一个日期维度表，每分钟有1个记录（或任何相关间隔的业务案例。或使用通过连接来生成查询的一部分

- 这是虚拟数据，因为您没有提供任何示例数据。

/* generate some dummy data*/
with test_data (id, entry_date) as
(
  SELECT 1, TO_DATE('27-APR-2022 09:52','DD-MON-YYYY HH24:MI') FROM DUAL UNION ALL
  SELECT 2, TO_DATE('27-APR-2022 10:00','DD-MON-YYYY HH24:MI') FROM DUAL UNION ALL
  SELECT 3, TO_DATE('27-APR-2022 10:00','DD-MON-YYYY HH24:MI') FROM DUAL UNION ALL
  SELECT 4, TO_DATE('27-APR-2022 09:58','DD-MON-YYYY HH24:MI') FROM DUAL 
), 
/* determine upper and lower bound for generating the minutes table */
test_data_boundaries (min_dt, max_dt) as
(
  SELECT MIN(entry_date), MAX(entry_date) FROM test_data
),
/* generate rows with 1 minute interval */
all_minutes (dt) as
(
  SELECT min_dt + (LEVEL - 1)/(60 * 24) FROM test_data_boundaries connect by LEVEL <=  ((max_dt - min_dt) * 60 * 24) + 1 
)
/* join test_data to minutes table */

SELECT
  COUNT(t.id),
  TO_CHAR(m.dt,'DD-MON-YYYY HH24:MI')
  FROM
  all_minutes m
  LEFT OUTER JOIN test_data t ON t.entry_date = m.dt
  GROUP BY m.dt
  ORDER BY m.dt;


1   27-APR-2022 09:52
0   27-APR-2022 09:53
0   27-APR-2022 09:54
0   27-APR-2022 09:55
0   27-APR-2022 09:56
0   27-APR-2022 09:57
1   27-APR-2022 09:58
0   27-APR-2022 09:59
2   27-APR-2022 10:00

Answer 2

您可以生成日历，然后使用分区 ed 外部Join 生成所有行：

SELECT q1.type,
       COALESCE(q1.ok, 0) AS ok,
       COALESCE(q1.nok, 0) AS nok,
       TO_CHAR(c.dt, 'YYYY/MM/DD HH24:MI') AS FormatStartDate
FROM   ( SELECT TRUNC(SYSDATE - INTERVAL '4' HOUR, 'MI')
                + (LEVEL - 1) * INTERVAL '1' MINUTE AS dt
         FROM   DUAL
         CONNECT BY LEVEL <= 4 * 60 + 1
       ) c
       LEFT OUTER JOIN (
         SELECT type,
                TRUNC(start_date, 'MI') AS start_date,
                COUNT(CASE UPPER(situation) WHEN 'OK' THEN 1 END) AS ok,
                COUNT(CASE UPPER(situation) WHEN 'NOK' THEN 1 END) AS nok
         FROM   table1
         WHERE  UPPER(situation) IN ('OK', 'NOK')
         AND    TRUNC(start_date, 'MI')
                  BETWEEN TRUNC(SYSDATE - INTERVAL '4' HOUR, 'MI')
                  AND     SYSDATE
         GROUP BY
               type,
               TRUNC(start_date, 'MI')
       ) q1
       PARTITION BY (q1.type)
       ON (c.dt = q1.start_date)
ORDER BY q1.type, c.dt

对于示例数据：

CREATE TABLE table1 (type, start_date, situation) AS
SELECT 'type1', TRUNC(SYSDATE, 'MI') - INTERVAL '04:00' HOUR TO MINUTE, 'OK' FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 'type1', TRUNC(SYSDATE, 'MI') - INTERVAL '03:59' HOUR TO MINUTE, 'OK' FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT 'type1', TRUNC(SYSDATE, 'MI') - INTERVAL '03:57' HOUR TO MINUTE, 'OK' FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 'type1', TRUNC(SYSDATE, 'MI') - INTERVAL '03:55' HOUR TO MINUTE, 'OK' FROM DUAL CONNECT BY LEVEL <= 4;

输出：

类型	确定	nok	格式化
type1	2	0	2022/04/27 05:11
type1	3	0	2022/04/27 05:12
type1	0	0	2022/04/27 05:13
type1	1	0	2022/04/27 05:14
type1	0	0	2022/04/27 05:15
type1	4	0	2022/04/27 05:16
type1	0	0	2022/04/27 05:17
type1	0	0	2022/04/27 05:18
...	...	...	...

​