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

Следующее является очень распространенной проблемой во всех технологиях, связанных с данными, и мы рассмотрим два очень простых решения на основе SQL:

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

Эта проблема

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

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

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

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

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

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

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

• Решение пользователя nop77svk с использованием LAST_VALUE()
• Решение пользователя MT0 с использованием LAG()

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

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

NULLIF (colx, 0)

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

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

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

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

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

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

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

• Разница между ROW_NUMBER (), RANK () и DENSE_RANK ()
• Не упустите возможности SQL Power с FIRST_VALUE (), LAST_VALUE (), LEAD () и LAG ()

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

Легко, не правда ли? Обратите внимание, что в этом конкретном случае 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 (и потерпите меня):

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

Предложение DIMENSION BY

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

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

Пункт MEASURES

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

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

ПРАВИЛА

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

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

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

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

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

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

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

резюме (гп)

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

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

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

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

Вывод

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

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