Блог

Допустим, у меня есть следующая схема:

 SENSOR
--------------
ID (numeric)
READ_DATE (date)
VALUE (numeric)
  

Я хочу найти всплески в данных, которые длятся не менее X нескольких дней. Мы получаем 1 считывание с датчика только один раз в день, поэтому ID и READ_DATE в значительной степени взаимозаменяемы с точки зрения уникальности.

Например, у меня есть следующие записи:

 1, 2019-01-01, 100
2, 2019-01-02, 1000
3, 2019-01-03, 1500
4, 2019-01-04, 1100
5, 2019-01-05, 500
6, 2019-01-06, 700
7, 2019-01-07, 1500
8, 2019-01-08, 2000
  

В этом примере для X = 2 with VALUE >= 1000 я хочу получить строки 3, 4, 8, потому что (2, 3), (3, 4), (7, 8) последовательно >= до 1000.

Я не уверен, как подойти к этому. Я думал о выполнении COUNT оконной функции, но не знаю, как проверить, есть ли X записей> = 1000.

Это примерно настолько общее, насколько я думаю, это может быть.

Сначала я создаю некоторые данные, используя табличную переменную, но это может быть временная / физическая таблица:

 DECLARE @table TABLE (id INT, [date] DATE, [value] INT);
INSERT INTO @table SELECT 1, '20190101', 100;
INSERT INTO @table SELECT 2, '20190102', 1000;
INSERT INTO @table SELECT 3, '20190103', 1500;
INSERT INTO @table SELECT 4, '20190104', 1100;
INSERT INTO @table SELECT 5, '20190105', 500;
INSERT INTO @table SELECT 6, '20190106', 700;
INSERT INTO @table SELECT 7, '20190107', 1500;
INSERT INTO @table SELECT 8, '20190108', 2000;
  

Затем я использую CTE (который можно заменить на менее эффективный подзапрос):

 WITH x AS (
    SELECT 
        *,
        CASE WHEN [value] >= 1000 THEN 1 END AS spike
    FROM 
        @table)
SELECT
    x2.id,
    x2.[date],
    x2.[value]
FROM
    x x1
    INNER JOIN x x2 ON x2.id = x1.id   1
WHERE
    x1.spike = 1
    AND x2.spike = 1;
  

Это предполагает, что ваши идентификаторы являются последовательными, если это не так, вам нужно будет присоединиться к дате, что сложнее.

Результаты:

 id  date        value
3   2019-01-03  1500
4   2019-01-04  1100
8   2019-01-08  2000
  

Хорошо, это не Postgres, и он не очень общий (рекурсивный CTE), но, похоже, он работает??

 DECLARE @spike_length INT = 3;

WITH x AS (
    SELECT 
        *,
        CASE WHEN [value] >= 1000 THEN 1 ELSE 0 END AS spike
    FROM 
        @table),
y AS (
    SELECT
        x.id,
        x.[date],
        x.[value],
        x.spike AS spike_length
    FROM
        x
    WHERE
        id = 1
    UNION ALL
    SELECT
        x.id,
        x.[date],
        x.[value],
        CASE WHEN x.spike = 0 THEN 0 ELSE y.spike_length   1 END
    FROM
        y
        INNER JOIN x ON x.id = y.id   1)
SELECT * FROM y WHERE spike_length >= @spike_length;
  

Результаты:

 id  date        value   spike_length
4   2019-01-04  1100    3
  

Вы можете подойти к этому как к проблеме пробелов и островов — нахождение последовательных значений выше порогового значения. Ниже приводится первая дата таких последовательностей:

 select s.read_date
from (select s.*,
             row_number() over (order by date) as seqnum
      from sensor s
      where value >= 1000
     ) s
group by (date - seqnum * interval '1 day')
having count(*) >= 2;
  

Наблюдение здесь заключается в том, что оно (date - seqnum * interval '1 day') является постоянным для смежных строк.

Вы можете получить исходные строки с помощью еще одного уровня подзапросов:

 select s.*
from (select s.*, count(*) over (partition by (date - seqnum * interval '1 day') as cnt
      from (select s.*,
                   row_number() over (order by date) as seqnum
            from sensor s
            where value >= 1000
           ) s
     ) s
where cnt >= 2;
  

В итоге я получил следующее:

 -- this parts helps filtering values < 1000 later on
with a as (
    select *,
    case when value >= 1000 then 1 else 0 end as indicator
    from sensor),
-- using the indicator, create a window that calculates the length of the spike
b as (
    select *,
    sum(indicator) over (order by id asc rows between 2 preceding and current row) as spike
    from a)
-- now filter out all spikes < 3
-- (because the window has a size of 3, it can never be larger than 3, so = 3 is okay)
select id, value from b where spike = 3;
  

Это дополняет ответ @ Gordon Linoff, но я нашел его слишком сложным.

Если вы можете использовать аналитические функции, то вы должны быть в состоянии сделать что-то подобное, чтобы получить то, что вам нужно (я изменил ваш лимит 1000 на 1500, иначе он вернул бы все строки, которые последовательно составляют 1000 и выше)

     CREATE TABLE test1 (
    id number,
    value number
 );

 insert all
    into test1 (id, value) values (1, 100)
    into test1 (id, value) values (2, 1000)
    into test1 (id, value) values (3, 1500)
    into test1 (id, value) values (4, 1100)
    into test1 (id, value) values (5, 500)
    into test1 (id, value) values (6, 700)
    into test1 (id, value) values (7, 1500)
    into test1 (id, value) values (8, 2000)
select * from dual;
  

РЕДАКТИРОВАТЬ — После повторного чтения еще раз — и из комментария — переделали, чтобы ответить на актуальный вопрос! Использование 2 лагов — один, чтобы убедиться, что предыдущий день был 1000 или больше, а другой, чтобы подсчитать, сколько раз произошло для X фильтрации.

 SELECT * FROM 
(
    SELECT id,
        value, 
        spike, 
        CASE WHEN spike = 0 THEN 0 ELSE (spike   LAG(spike, 1, 0) OVER (ORDER BY id)   1) END as SPIKE_LENGTH
    FROM (
        select id,
            value, 
            CASE WHEN LAG(value, 1, 0) OVER (ORDER BY id) >= 1000 AND value >= 1000 THEN 1 ELSE 0 END AS SPIKE
        from test1
        )
)
WHERE spike_length >= 2;
  

Который возвращает

 ID  Value  spike    spike_length
3   1500    1   2
4   1100    1   3
8   2000    1   2
  

Если вы увеличите фильтр длины всплеска до >= 3 — получите только идентификатор 4, который является единственным идентификатором с 3 более 1000 подряд.