Обнаружение и удаление пробелов во временных рядах

В моем наборе данных есть ежедневные временные ряды для разных компаний, и я работаю с PostgreSQL. Моя цель — исключить компании со слишком неполными временными рядами. Поэтому я хочу исключить все компании, у которых есть 3 или более последовательных пропущенных значения. Кроме того, я хочу исключить все компании, у которых более 50% пропущенных значений между их первой и последней датой в наборе данных.

Мы можем работать со следующими примерами данных:

date             company    value
2012-01-01       A          5
2012-01-01       B          2
2012-01-02       A          NULL
2012-01-02       B          2
2012-01-02       C          4
2012-01-03       A          NULL
2012-01-03       B          NULL
2012-01-03       C          NULL
2012-01-04       A          NULL
2012-01-04       B          NULL
2012-01-04       C          NULL
2012-01-05       A          8
2012-01-05       B          9
2012-01-05       C          3
2012-01-06       A          8
2012-01-06       B          9
2012-01-06       C          NULL

Таким образом, A должен быть исключен, потому что у него есть разрыв в три последовательных пропущенных значения, и C, потому что у него есть более 50% пропущенных значений между его первой и последней датой.

Объединив другие ответы на этом форуме, я составил следующий код:

  1. Добавьте первичный ключ автоинкремента для идентификации каждой строки

    CREATE TABLE test AS SELECT * FROM mytable ORDER BY company, date; 
CREATE SEQUENCE id_seq; ALTER TABLE test ADD id INT UNIQUE; 
ALTER TABLE test ALTER COLUMN id SET DEFAULT NEXTVAL('id_seq'); 
UPDATE test SET id = NEXTVAL('id_seq');

ALTER TABLE test ADD PRIMARY KEY (id);

  2. Обнаружение пробелов во временном ряду

    CREATE TABLE to_del AS WITH count3 AS 
( SELECT *, 
  COUNT(CASE WHEN value IS NULL THEN 1 END) 
     OVER (PARTITION BY company ORDER BY id 
           ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) 
  AS cnt FROM test) 
SELECT company, id FROM count3 WHERE cnt >= 3;

  3. Удалить пробелы из mytable

    DELETE FROM mytable WHERE company in (SELECT DISTINCT company FROM to_del);

Кажется, удалось обнаружить и удалить пробелы из 3 или более последовательных пропущенных значений из временного ряда. Но такой подход очень громоздкий. И я не могу понять, как дополнительно исключить все компании с более чем 50% пропущенных значений.

Можете ли вы придумать более эффективное решение, чем мое (я только учусь работать с PostgreSQL), которое также позволяет исключить компании с более чем 50% пропущенных значений?


postgresql sql gaps-and-islands
person user3319629    schedule 24.02.2014    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Я бы создал только один запрос:

DELETE FROM mytable 
WHERE company in (
  SELECT Company 
  FROM (
    SELECT Company, 
      COUNT(CASE WHEN value IS NULL THEN 1 END) 
         OVER (PARTITION BY company ORDER BY id 
               ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) As cnt,
      COUNT(CASE WHEN value IS NULL THEN 1 END) 
         OVER (PARTITION BY company)
      / 
      COUNT(*) 
         OVER (PARTITION BY company) As p50
  ) alias
  WHERE cnt >= 3 OR p50 > 0.5
)

Составной индекс по столбцам (компания + значение) может помочь получить максимальную скорость этого запроса.

РЕДАКТИРОВАТЬ

Приведенный выше запрос не работает
Я немного его поправил, вот демонстрация: http://sqlfiddle.com/#!15/c9bfe/7
Две вещи были изменены:
- РАЗДЕЛЕНИЕ ПО КОМПАНИИ ЗАКАЗ ПО дате вместо ЗАКАЗ BY id
 — явное приведение к числовому (поскольку целое число усечено до 0):
OVER (PARTITION BY company)::numeric

  SELECT company, cnt, p50
  FROM (
    SELECT company, 
      COUNT(CASE WHEN value IS NULL THEN 1 END) 
         OVER (PARTITION BY company ORDER BY date 
               ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) As cnt,
      SUM(CASE WHEN value IS NULL THEN 1 ELSE 0 END) 
         OVER (PARTITION BY company)::numeric
      / 
      COUNT(*) 
         OVER (PARTITION BY company) As p50
    FROM mytable
  ) alias
--  WHERE cnt >= 3 OR p50 > 0.5

и теперь запрос на удаление должен работать:

DELETE FROM mytable 
WHERE company in (
      SELECT company
      FROM (
        SELECT company, 
          COUNT(CASE WHEN value IS NULL THEN 1 END) 
             OVER (PARTITION BY company ORDER BY date 
                   ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) As cnt,
          SUM(CASE WHEN value IS NULL THEN 1 ELSE 0 END) 
             OVER (PARTITION BY company)::numeric
          / 
          COUNT(*) 
             OVER (PARTITION BY company) As p50
        FROM mytable
      ) alias
    WHERE cnt >= 3 OR p50 > 0.5
)
person krokodilko    schedule 24.02.2014
comment
Спасибо, использование только одного запроса выглядит очень элегантно. Поэтому я пытаюсь следовать вашему подходу, но почему-то он пока не совсем работает. Пока он исключает строки с отсутствующими значениями. Что мне нужно, так это полностью исключить из набора данных все фирмы, которые удовлетворяют одному из двух критериев. @kordirko - person user3319629; 24.02.2014
comment
Я изменил свой ответ, предыдущий запрос был неправильным. - person krokodilko; 24.02.2014
comment
Да, теперь он работает идеально. Спасибо за ваш ответ, а также за демо, которое помогло мне понять, как это работает! @kordirko - person user3319629; 24.02.2014

arrow_upward
1
arrow_downward

Для критерия 50% вы можете выбрать все компании, для которых количество различных дат меньше половины количества дней между минимальной и максимальной датами.

Я не проверял это, но это должно дать вам представление. Я использовал CTE, чтобы его было легче читать.

WITH MinMax AS 
(
    SELECT Company, DATE_PART('day', AGE(MIN(Date), MAX(Date))) AS calendar_days, COUNT(DISTINCT date) AS days FROM table
     GROUP By Company
)
SELECT Company FROM MinMax
 WHERE (calendars_days / 2) > days
person Johann Blais    schedule 24.02.2014