在oracle10g、11g、mysql、sqlserver中如何将掉电和开机两个数据行合并为一个数据行(不支持匹配识别)

x8diyxa7 于 2021-06-19 发布在 Mysql

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

https://drive.google.com/file/d/1yakmtd5m2meindbeg2ckutb3vw7ypfto/view?usp=sharing
大家好，
我已经说过，给定的问题与链接和被解决的sql模式匹配解决方案是支持或oracle 12c由wernfried domscheit的信息：

CREATE TABLE ALERT_EVENT (MEASUREMENT_POINT VARCHAR2(10), TIME_ALERT DATE, STATUS VARCHAR2(10));

INSERT INTO ALERT_EVENT VALUES('PE01', to_date('04/10/2018 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER ON');
INSERT INTO ALERT_EVENT VALUES('PE01', to_date('03/10/2018 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER OFF');
INSERT INTO ALERT_EVENT VALUES('PE01', to_date('02/10/2018 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER ON');
INSERT INTO ALERT_EVENT VALUES('PE01', to_date('01/10/2018 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER OFF');
INSERT INTO ALERT_EVENT VALUES('PE02',  to_date('03/10/2018 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER OFF'); 
INSERT INTO ALERT_EVENT VALUES('PE02',  to_date('02/10/2018 23:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER ON');
INSERT INTO ALERT_EVENT VALUES('PE02',  to_date('02/10/2018 22:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER OFF');
INSERT INTO ALERT_EVENT VALUES('PE03', to_date('04/10/2018 10:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER OFF');
INSERT INTO ALERT_EVENT VALUES('PE03',  to_date('02/10/2018 23:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER ON');
INSERT INTO ALERT_EVENT VALUES('PE03',  to_date('02/10/2018 22:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER OFF');
INSERT INTO ALERT_EVENT VALUES('PE03',  to_date('01/10/2018 00:00:00','dd/mm/yyyy hh24:mi:ss'), 'POWER ON');

查询结果使用oracle匹配模式您非常接近，请尝试以下方法：

SELECT * 
FROM ALERT_EVENT ml
MATCH_RECOGNIZE (
     PARTITION BY MEASUREMENT_POINT
     ORDER BY TIME_ALERT
     MEASURES  STRT.TIME_ALERT AS start_tstamp,
               LAST(END.TIME_ALERT) AS end_tstamp
     PATTERN (STRT END*)
     DEFINE
        STRT AS STRT.STATUS LIKE '%OFF%' ,
        END AS END.STATUS LIKE '%ON%' 
     ) MR
ORDER BY MEASUREMENT_POINT ,start_tstamp;

MEASUREMENT_POINT   START_TSTAMP           END_TSTAMP
==================  =====================  ====================
PE01                2018-10-01 00:00:00    2018-10-02 00:00:00
PE01                2018-10-03 00:00:00    2018-10-04 00:00:00
PE02                2018-10-02 22:00:00    2018-10-02 23:00:00
PE02                2018-10-03 00:00:00 
PE03                2018-10-02 22:00:00    2018-10-02 23:00:00
PE03                2018-10-04 10:00:00

问题：但执行到oracle旧版本或mysql或sql server时，此函数不支持用于模式匹配的sql。对于不使用sql进行模式匹配的给定问题，要查询常规的解决方案是什么？
非常感谢！

mysql sql-server oracle oracle11g oracle10g

来源：https://stackoverflow.com/questions/52884688/how-to-merge-two-data-row-warning-off-power-and-on-power-into-one-in-oracle-10g

1条答案

按热度按时间

deikduxw1#

在oracle 12c之前的版本中，您可以通过创建一个新列来进行分组，然后根据测量点和新的分组列查找相关状态的最小/最大时间警告。
分组列是通过计算运行和并在每次遇到电源关闭状态时将其增加1来生成的。
这给了我们一个大致的问题：

select measurement_point,
       min(case when status = 'POWER OFF' then time_alert end) start_tstamp,
       max(case when status = 'POWER ON' then time_alert end) end_tstamp
from   (select measurement_point,
               time_alert,
               status,
               sum(case when status = 'POWER OFF' then 1 else 0 end) over (partition by measurement_point order by time_alert) grp
        from   alert_event)
group by measurement_point, grp
having min(case when status = 'POWER OFF' then time_alert end) is not null
order by measurement_point, min(time_alert);

MEASUREMENT_POINT START_TSTAMP        END_TSTAMP
----------------- ------------------- -------------------
PE01              01/10/2018 00:00:00 02/10/2018 00:00:00
PE01              03/10/2018 00:00:00 04/10/2018 00:00:00
PE02              02/10/2018 22:00:00 02/10/2018 23:00:00
PE02              03/10/2018 00:00:00 
PE03              02/10/2018 22:00:00 02/10/2018 23:00:00
PE03              04/10/2018 10:00:00

请注意，这将把状态为“开机”的连续行分组为一组；如果您不希望生成grp列，那么您必须仔细研究它是如何生成的。

赞(0）回复(0）举报 2021-06-20

我来回答

在oracle10g、11g、mysql、sqlserver中如何将掉电和开机两个数据行合并为一个数据行(不支持匹配识别)

1条答案

相关问题

热门标签

最新问答