Как найти всплеск данных с помощью SQL?
Скажем, у меня есть следующая схема:
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 со VALUE >= 1000 я хочу получить строки 3, 4, 8, потому что (2, 3), (3, 4), (7, 8) последовательно >= 1000.
Я не уверен, как к этому подойти. Я думал о выполнении оконной функции COUNT, но не знаю, как проверить, есть ли записи X >= 1000.
@VamsiPrabhala Почему не универсальное решение SQL?
причина, по которой я спрашиваю, - проверить, есть ли возможность использовать аналитические функции.
Оконные функции недоступны в некоторых СУБД (например), то же самое с LEAD и LAG, которые можно было бы использовать здесь
@RichardHansell: сегодня все современные СУБД поддерживают оконные функции
Ответы 4
Это настолько общее, насколько я думаю, это может быть.
Сначала я создаю некоторые данные, используя табличную переменную, но это может быть временная/физическая таблица:
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
Но это при условии, что X = 2. Что, если я хочу, чтобы X = 20? Похоже, для этого потребуется 20 соединений?
Да, я пропустил эту часть ;*
Не то, чтобы идентификаторы с [..] были недопустимыми стандартными SQL (и не будут работать с Postgres).
Ctrl-H "[" заменить на "", то же самое на "]"?
Если вы можете использовать аналитические функции, вы сможете сделать что-то подобное, чтобы получить то, что вам нужно (я изменил ваш лимит 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, получите только ID 4, который является единственным ID с 3 более 1000 подряд.
Ура - не нужно было пытаться уйти в 17:00 - слишком поздно, чтобы что-то понять;) Отредактировано
Вы можете подойти к этому как к проблеме пробелов и островов — найти последовательные значения выше порога. Следующее получает первую дату таких последовательностей:
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, но я нашел его слишком сложным.
отметьте используемые СУБД