如何写一个关于空白和孤岛问题的查询? [英] How to write a query for a gaps and islands problem?

问题描述

这是一个空白和孤岛的问题.

This is a gaps and islands problem.

Meter_id |Realtimeclock      |I_Y|I_B|I_X|
201010   |27-09-2018 00:00:00|1.0|2.0|3.0|
201010   |27-09-2018 00:30:00|1.0|2.0|3.0|
201010   |27-09-2018 01:00:00|1.0|2.0|3.0|
201010   |27-09-2018 01:30:00|1.0|2.0|3.0|
201010   |27-09-2018 02:00:00|1.0| 0 |3.0|
201010   |27-09-2018 02:30:00|1.0| 0 |0  |
201010   |27-09-2018 03:00:00|1.0|2.0|3.0|
201010   |27-09-2018 03:30:00|1.0|2.0|3.0|
201011   |27-09-2018 00:00:00|1.0|2.0|3.0|
201011   |27-09-2018 00:30:00|1.0|2.0|3.0|
201010   |28-09-2018 03:00:00|1.0|2.0|3.0|
201010   |28-09-2018 03:30:00|1.0|2.0|3.0|
201011   |28-09-2018 04:00:00|1.0| 0 |0  |
201011   |28-09-2018 00:00:00|1.0|2.0|3.0|
201011   |28-09-2018 00:30:00|1.0|2.0|3.0|

一种方法使用行数差异方法:

One approach uses the difference in row numbers method:

select * from (
WITH cte1 AS (
        SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Meter_id ORDER BY Realtimeclock) rn
        FROM yourTable t
    ),
    cte2 AS (
        SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Meter_id ORDER BY Realtimeclock) rn
        FROM yourTable t
        WHERE I_B <> 0
    ),
    cte3 AS (
        SELECT t1.*,
            t1.rn - t2.rn AS diff
        FROM cte1 t1
        INNER JOIN cte2 t2
            ON t1.Meter_id = t2.Meter_id AND t1.Realtimeclock = t2.Realtimeclock
    )        
    SELECT
        Meter_id,
        MIN(Realtimeclock) AS start_time,
        MAX(Realtimeclock) AS end_time,
        COUNT(I_Y) AS I_Y,
        COUNT(I_B) AS I_B,
        COUNT(I_X) AS I_X,ROW_NUMBER() OVER (PARTITION BY meter_id ORDER BY meter_id ) AS Spell
    FROM cte3
    GROUP BY
        Meter_id,
        diff);

输出应打印为，请告诉我代码中需要进行的任何更改.

The output should print like ,,Please let me know any change required in code.

从上表中，我需要基于条件I_Y，I_B，I_X的日间拼写作为开始时间和结束时间，该条件是可计数的非零值. 在这里，我们看到201010 meter_id的开始时间有两个咒语，因为它们之间存在时间间隔.同样，它必须显示所有咒语以及日期和时间戳.

From the above table I need daywise spells as start time and endtime, based on condition I_Y,I_B,I_X which is non-zero value as countable. Here we see the start time of 201010 meter_id has two spells as there was time gap between them. Likewise it has to show all the spells along with date and timestamp.

Meter_id |start_time         |End_time           |I_Y|I_B|I_X|spell
201010   |27-09-2018 00:00:00|27-09-2018 01:30:00|4  |4  |4  |1
201010   |27-09-2018 03:00:00|27-09-2018 03:30:00|4  |4  |4  |2
201011   |27-09-2018 00:00:00|27-09-2018 00:30:00|2  |2  |2  |1
201010   |28-09-2018 03:00:00|27-09-2018 03:30:00|2  |2  |2  |1
201011   |28-09-2018 00:00:00|28-09-2018 00:30:00|2  |2  |2  |1

投掷运行时错误如下，

[错误]执行(35:22):ORA-01830:日期格式图片在转换整个输入字符串之前结束

[Error] Execution (35: 22): ORA-01830: date format picture ends before converting entire input string

蒂姆，

请仔细研究.这将对我有很大帮助.

Please look into it.It will be a big help to me ever.

在给定trunc(realtimeclock)而不是TO_DATE(realtimeclock)之后，以上内容将清除..

the above is cleared after giving trunc(realtimeclock) instead of TO_DATE(realtimeclock) ..

感谢您的帮助，蒂姆.

推荐答案

您只需对当前方法进行一些修改，即可在日期上添加分区(除了meter_id之外).然后，在最后一个查询中，添加COUNT，它可以计算给定仪表和日期的咒语数量.

You only need a slight modification to your current approach, to add a partition on the date (in addition to the meter_id). Then, in the final query, add a COUNT which tallies the number of spells for a given meter and date.

WITH cte1 AS (
    SELECT t.*,
        ROW_NUMBER() OVER (PARTITION BY Meter_id, TO_DATE(Realtimeclock)
            ORDER BY Realtimeclock) rn
    FROM yourTable t
),
cte2 AS (
    SELECT t.*,
        ROW_NUMBER() OVER (PARTITION BY Meter_id, TO_DATE(Realtimeclock)
            ORDER BY Realtimeclock) rn
    FROM yourTable t
    WHERE I_B <> 0
),
cte3 AS (
    SELECT t1.*,
        t1.rn - t2.rn AS diff
    FROM cte1 t1
    INNER JOIN cte2 t2
        ON t1.Meter_id = t2.Meter_id AND t1.Realtimeclock = t2.Realtimeclock
)

SELECT
    Meter_id,
    MIN(Realtimeclock) AS start_time,
    MAX(Realtimeclock) AS end_time,
    COUNT(I_Y) AS I_Y,
    COUNT(I_B) AS I_B,
    COUNT(I_X) AS I_X,
    COUNT(*) OVER (PARTITION BY TO_DATE(Realtimeclock), Meter_id
        ORDER BY MIN(Realtimeclock)) AS spell
FROM cte3
GROUP BY
    Meter_id,
    TO_DATE(Realtimeclock),
    diff;

演示

请注意，此答案假定从一个日历日到下一个日历日都不会进行轮班.如果可能发生这种情况，并且您需要考虑到这一点，那么您应该给我们提供有关计算此类事件的逻辑.

Note that this answer assumes that shifts never run from one calendar day over to the next. If this could happen, and you need to account for it, then you should give us what the logic is regarding counting such occurrences.

再次在SQL Server中进行演示，尽管上面的查询是Oracle代码，并且应该可以正常运行.

Demo again in SQL Server, though the above query is Oracle code and should run without any issues.

这篇关于如何写一个关于空白和孤岛问题的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！