Удалять нулевые значения до тех пор, пока первое значение не будет нулевым

У меня есть ежедневные временные ряды для компаний в моем наборе данных, и я использую PostgreSQL.
Для каждой компании все строки с NULL в столбце 3 должны быть удалены до первой записи NOT NULL в этом столбце для этой компании. Затем все последовательные отсутствующие значения заполняются значением последнего наблюдаемого значения для этой компании, которое НЕ NULL.

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

        date           company        column3
1 2004-01-01             A               5  
2 2004-01-01             B               NULL
3 2004-01-01             C               NULL  
4 2004-01-02             A               NULL
5 2004-01-02             B               7
6 2004-01-02             C               NULL
7 2004-01-03             A               6
8 2004-01-03             B               7
9 2004-01-03             C               9    
10 2004-01-04            A               NULL
11 2004-01-04            B               NULL
12 2004-01-04            C               NULL

Было бы здорово, если бы мне удалось написать запрос, который доставляет

        date           company        column3
1 2004-01-01             A               5  
2 2004-01-02             A               5
3 2004-01-02             B               7
4 2004-01-03             A               6
5 2004-01-03             B               7
6 2004-01-03             C               9
7 2004-01-04             A               6
8 2004-01-04             B               7
9 2004-01-04             C               9

Я старался:

SELECT a.date, a.company, COALESCE(a.column3, (SELECT b.column3 FROM mytable b 
WHERE b.company=a.company AND b.colmun3 IS NOT NULL ORDER BY b.company=a.company 
DESC LIMIT 1)) FROM mytable a;

Есть две проблемы с кодом:

  1. Он не удаляет все записи со значениями NULL до первого значения NOT NULL, а
    заполняет все пропущенные значения.
  2. ...с первым наблюдением в столбце, а не с последним наблюдением перед
    отсутствующим значением.

postgresql null sql window-functions
person user3319629    schedule 17.02.2014    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Я предлагаю использовать два уровня подзапросов с функциями окна вместо коррелированные подзапросы:

SELECT *
FROM  (
   SELECT the_date, company, max(col3) OVER (PARTITION BY company, grp) AS col3
   FROM (
      SELECT *, count(col3) OVER (PARTITION BY company ORDER BY the_date) AS grp
      FROM   tbl
      ) sub1
   ) sub2
WHERE  col3 IS NOT NULL
ORDER  BY the_date, company;

Производит запрошенный результат.

-> SQLfiddle

Это предполагает уникальные записи на (company, the_date). Должно быть намного быстрее для таблиц, содержащих более нескольких строк. Индекс (уникальный, чтобы обеспечить уникальность?!) значительно повысил бы производительность:

CREATE INDEX tbl_company_date_idx ON tbl (company, the_date);

Как?

Агрегатная функция count() игнорирует значения NULL при подсчете. Используемая в качестве функции агрегатного окна, она вычисляет текущий счетчик столбца в соответствии с определением окна по умолчанию, которое равно RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это приводит к тому, что счетчик «застревает» для строк со значениями NULL, тем самым образуя группу одноранговых узлов, которые имеют одно и то же (ненулевое) значение.

Во второй оконной функции единственное ненулевое значение для группы легко извлекается с помощью max(). Группа перед первым ненулевым значением сохраняет NULL, что легко устраняется в последнем SELECT.

Дополнительные пояснения в этом тесно связанном ответе:
Получить последнее известное значение для каждого столбца строки

person Erwin Brandstetter    schedule 18.02.2014

arrow_upward
1
arrow_downward

Пытаться:

SELECT *
FROM (
  SELECT id,
         date,
         company,
         case when column3 is not null
              then column3
              else (
                     SELECT column3
                     FROM mytable t1
                     WHERE t1.company = t.company
                       AND t1.date < t.date
                       AND t1.column3 IS NOT NULL
                     ORDER BY t1.date DESC LIMIT 1
                    )
          end column3
  FROM mytable T
) AS subq
WHERE column3 IS NOT NULL;

демо: http://sqlfiddle.com/#!15/0cdce/12

person krokodilko    schedule 17.02.2014