Статьи

Заполнение разреженных данных предыдущим непустым значением в SQL

Сентябрь 30, 2017

Как заполнить ячейки разреженного набора данных «предыдущим непустым значением»?

Это очень распространенная проблема во всех технологиях, связанных с данными, и мы рассмотрим два очень простых решения на основе SQL.

Эта проблема

Проблема действительно проста, и я повторно использую пример, предоставленный пользователем Stack Overflow aljassi, чтобы ответить на этот вопрос :

У нас есть таблица, содержащая «разреженные» данные:

Col1  Col2  Col3  Col4
----------------------
A     0     1     5
B     0     4     0
C     2     0     0
D     0     0     0
E     3     5     0
F     0     3     0
G     0     3     1
H     0     1     5
I     3     5     0

Приведенный выше набор данных содержит несколько интересных точек данных, которые не равны нулю, а некоторые пробелы моделируются значением ноль. В других примерах мы могли бы заменить ноль на NULL, но это все равно будет той же проблемой. Желаемый результат следующий:

Col1  Col2  Col3  Col4
----------------------
A     0     1     5
B     0     4     5
C     2     4     5
D     2     4     5
E     3     5     5
F     3     3     5
G     3     3     1
H     3     1     5
I     3     5     5

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

Как это сделать с SQL? Мы рассмотрим два решения …

Решение с использованием оконных функций

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

Оба решения примерно эквивалентны. Вот как они работают (используя синтаксис Oracle):

WITH t(col1, col2, col3, col4) AS (
  SELECT 'A', 0, 1, 5 FROM DUAL UNION ALL
  SELECT 'B', 0, 4, 0 FROM DUAL UNION ALL
  SELECT 'C', 2, 0, 0 FROM DUAL UNION ALL
  SELECT 'D', 0, 0, 0 FROM DUAL UNION ALL
  SELECT 'E', 3, 5, 0 FROM DUAL UNION ALL
  SELECT 'F', 0, 3, 0 FROM DUAL UNION ALL
  SELECT 'G', 0, 3, 1 FROM DUAL UNION ALL
  SELECT 'H', 0, 1, 5 FROM DUAL UNION ALL
  SELECT 'I', 3, 5, 0 FROM DUAL
)
SELECT
  col1,

  nvl(last_value(nullif(col2, 0)) 
      IGNORE NULLS OVER (ORDER BY col1), 0) col2,

  nvl(last_value(nullif(col3, 0)) 
      IGNORE NULLS OVER (ORDER BY col1), 0) col3,

  nvl(last_value(nullif(col4, 0)) 
      IGNORE NULLS OVER (ORDER BY col1), 0) col4
FROM t

Теперь давайте разберем эти оконные функции:

NULLIF (colx, 0)

Это просто простой способ получения NULLзначений всякий раз, когда мы имеем то, что является допустимым «пустым» значением в нашем наборе данных. Так что вместо нулей мы просто получаем NULL. Применяя эту функцию к нашим данным, мы получаем:

Col1  Col2  Col3  Col4
----------------------
A     NULL  1     5
B     NULL  4     NULL
C     2     NULL  NULL
D     NULL  NULL  NULL
E     3     5     NULL
F     NULL  3     NULL
G     NULL  3     1
H     NULL  1     5
I     3     5     NULL

Мы делаем это, потому что теперь мы можем использовать полезное IGNORE NULLSпредложение, которое доступно для некоторых функций ранжирования, в частности LAST_VALUE(), или LAG(). Теперь мы можем написать:

last_value(...) IGNORE NULLS OVER (ORDER BY col1)

Где мы берем последнее ненулевое NULLзначение, которое предшествует текущей строке при упорядочении строк col1:

  • Если текущая строка содержит ненулевое NULLзначение, мы принимаем это значение.
  • Если текущая строка содержит NULLзначение, мы будем идти вверх, пока не достигнем не NULLзначения.
  • Если мы идем «вверх», и мы не достигли какой-либо бесполезной NULL , ну, мы получаем NULL.

Это приводит к следующему результату:

Col1  Col2  Col3  Col4
----------------------
A     NULL  1     5
B     NULL  4     5
C     2     4     5
D     2     4     5
E     3     5     5
F     3     3     5
G     3     3     1
H     3     1     5
I     3     5     5

Обратите внимание, что в большинстве оконных функций после указания ORDER BYпредложения по умолчанию используется следующее предложение frame:

last_value(...) IGNORE NULLS OVER (
  ORDER BY col1
  ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW
)

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

Наконец, поскольку мы не хотим, чтобы эти NULLзначения оставались в наших результатах, мы просто удаляем их, используя NVL()(или COALESCE()в других базах данных):

nvl(last_value(...) IGNORE NULLS OVER (...), 0)

Легко, не правда ли? Обратите внимание, что в данном конкретном случае LAG()и LAST_VALUE()будет иметь тот же эффект.

Решение с использованием предложения MODEL

Всякий раз, когда у вас возникает проблема в (Oracle) SQL, которую трудно решить с помощью оконных функций, предложение Oracle MODELможет предложить «простое» решение. Я использую кавычки на «легкий», потому что синтаксис немного сложен для запоминания, но суть его на самом деле не так сложна.

Это MODELпредложение — не что иное, как специфический для Oracle диалект для реализации логики, подобной электронной таблице, в базе данных. Я настоятельно рекомендую прочитать соответствующую Белую книгу от Oracle, которая очень хорошо объясняет функциональность:

http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf

Вот как вы могли бы решить эту проблему MODEL(и терпеть меня):

WITH t(col1, col2, col3, col4) AS (
  SELECT 'A', 0, 1, 5 FROM DUAL UNION ALL
  SELECT 'B', 0, 4, 0 FROM DUAL UNION ALL
  SELECT 'C', 2, 0, 0 FROM DUAL UNION ALL
  SELECT 'D', 0, 0, 0 FROM DUAL UNION ALL
  SELECT 'E', 3, 5, 0 FROM DUAL UNION ALL
  SELECT 'F', 0, 3, 0 FROM DUAL UNION ALL
  SELECT 'G', 0, 3, 1 FROM DUAL UNION ALL
  SELECT 'H', 0, 1, 5 FROM DUAL UNION ALL
  SELECT 'I', 3, 5, 0 FROM DUAL
)
SELECT * FROM t
MODEL
  DIMENSION BY (row_number() OVER (ORDER BY col1) rn)
  MEASURES (col1, col2, col3, col4)
  RULES (
    col2[any] = DECODE(col2[cv(rn)], 0, NVL(col2[cv(rn) - 1], 0), col2[cv(rn)]),
    col3[any] = DECODE(col3[cv(rn)], 0, NVL(col3[cv(rn) - 1], 0), col3[cv(rn)]),
    col4[any] = DECODE(col4[cv(rn)], 0, NVL(col4[cv(rn) - 1], 0), col4[cv(rn)])
  )

Здесь есть три пункта, которые представляют интерес:

ОПИСАНИЕ ИЗМЕРЕНИЯ ПО

Как и в электронной таблице Microsoft Excel,  DIMENSIONсоответствует последовательному, индивидуальному индексу каждой ячейки электронной таблицы, по которому мы хотим получить доступ к этой ячейке. В Excel всегда есть два измерения (одно написано буквами A..Z, AA..ZZ,…), а другое — цифрами (1..infinity).

Используя MODEL, вы можете указать столько размеров, сколько хотите. В нашем примере мы будем использовать только одну, номер строки каждой строки, упорядоченный по col1(другой вариант использования для оконной функции).

Положение MEASURES

Предложение MEASURESопределяет отдельные значения ячейки для каждой «ячейки». В Microsoft Excel ячейка может иметь только одно значение. В предложении Oracle MODELмы можем оперировать многими значениями одновременно в «ячейке».

В этом случае мы просто сделаем все столбцы нашими ячейками.

ПРАВИЛА

Это действительно интересная часть в MODELпункте. Здесь мы указываем, по каким правилам мы хотим вычислить значения каждой отдельной ячейки. Синтаксис прост:

RULES (
  <rule 1>,
  <rule 2>,
  ...,
  <rule N>
)

Каждое отдельное правило может реализовывать присваивание формы:

RULES (
  cell[dimension(s)] = rule
)

В нашем случае мы повторим то же правило для ячеек col2, col3и col4, и для любого значения измерения rn(для номера строки). Итак, левая часть задания:

RULES (
  col2[any] = rule,
  col3[any] = rule,
  col4[any] = rule,
)

Правая часть — это тривиальное (хотя и не очень тривиальное) выражение:

1 DECODE(col2[cv(rn)], 0, NVL(col2[cv(rn) - 1], 0), col2[cv(rn)])

Давайте снова разложим.

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

DECODE простая и полезная функция Oracle, которая принимает первый аргумент, сравнивает его с аргументом 2 и, если они совпадают, возвращает аргумент 3, в противном случае — аргумент 4. Она работает как a  CASE, что немного более многословно:

1 2 3 4 5 DECODE(A, B, C, D)   -- The same as:   CASE A WHEN B THEN C ELSE D END

резюме (гп)

cv() является  MODEL определенной «функцией», которая означает «текущее значение». В левой части присваивания мы использовали  "any" в качестве спецификатора измерения, поэтому мы применяем это правило для любого значения  rn. Чтобы получить доступ к определенному  rnзначению, мы просто напишем  cv(rn)или «текущее значение rn».

Рекурсивность

RULES Из  MODEL пункта разрешено занимать рекурсивное дерево (хотя и не график, так что никаких циклов не допускается), где каждая ячейка может быть определена на основе предыдущей ячейки, которая вновь определяется на основе его предшественник. Мы делаем это через  col2[cv(rn) - 1], где  cv(rn) - 1означает «текущий номер строки минус один».

Легко, правда? Предоставляется. Синтаксис не прост, и мы только царапаем поверхность того, что возможно  MODEL.

Вывод

SQL предоставляет отличные способы реализации управляемых данными декларативных спецификаций того, какими должны быть ваши данные. Предложение  MODEL немного жуткое, но в то же время чрезвычайно мощное. Гораздо проще, а также немного быстрее — оконные функции, инструмент, который должен быть в цепочке инструментов каждого разработчика, работающего с SQL.

В этой статье мы показали, как заполнить пробелы в разреженных данных с помощью оконных функций или  MODEL. Подобный вариант использования — промежуточные итоги. Если эта статья вызвала у вас интерес, я предлагаю прочитать о  различных подходах к вычислению промежуточного итога в SQL .

42
Share: