Как заполнить ячейки разреженного набора данных «предыдущим непустым значением»?
Это очень распространенная проблема во всех технологиях, связанных с данными, и мы рассмотрим два очень простых решения на основе 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? Мы рассмотрим два решения …
Решение с использованием оконных функций
Это решение, которое вы должны искать, и в связанном вопросе переполнения стека есть два ответа, которые оба используют оконные функции:
- Решение пользователя nop77svk, использующее
LAST_VALUE()
- Решение пользователя MT0 с использованием
LAG()
Оба решения примерно эквивалентны. Вот как они работают (используя синтаксис 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
)
Это много ключевых слов, но их смысл не так уж и неясен, когда вы освоите оконные функции. Мы рекомендуем прочитать следующие сообщения в блоге, чтобы узнать больше о них:
- Разница между ROW_NUMBER (), RANK () и DENSE_RANK ()
- Не упустите потрясающую мощь SQL с помощью FIRST_VALUE (), LAST_VALUE (), LEAD () и LAG ()
Наконец, поскольку мы не хотим, чтобы эти 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 .