Как заполнить разреженные данные предыдущим непустым значением в SQL
Следующее является очень распространенной проблемой во всех технологиях, связанных с данными, и мы рассмотрим два очень простых решения на основе SQL:
Как заполнить ячейки разреженного набора данных «предыдущим непустым значением»?
Эта проблема
Приведенный выше набор данных содержит несколько интересных точек данных, которые не равны нулю, а некоторые пробелы моделируются значением ноль. В других примерах мы могли бы заменить ноль на NULL , но это все равно будет той же проблемой. Желаемый результат следующий:
Обратите внимание, что все сгенерированные значения выделены красным, и они соответствуют самому последнему значению синего цвета.
Решение с использованием оконных функций
Это решение, которое вы должны искать, и в связанном вопросе переполнения стека есть два ответа, которые оба используют оконные функции:
Оба решения примерно эквивалентны. Вот как они работают (используя синтаксис Oracle):
Это просто простой способ получения значений NULL когда в нашем наборе данных есть допустимое «пустое» значение. Таким образом, вместо нулей мы просто получаем NULL . Применяя эту функцию к нашим данным, мы получаем:
Мы делаем это потому, что теперь мы можем использовать полезное предложение IGNORE NULLS , которое доступно некоторым функциям ранжирования, в частности, LAST_VALUE() или LAG() . Теперь мы можем написать:
Где мы берем последнее NULL значение, которое предшествует текущей строке при упорядочении строк по col1 :
- Если текущая строка содержит NULL значение, мы принимаем это значение.
- Если текущая строка содержит значение NULL , мы будем идти вверх, пока не достигнем значения, отличного от NULL
- Если мы идем «вверх» и не достигли значения, отличного от NULL , мы получим NULL
Обратите внимание, что в большинстве оконных функций после указания предложения ORDER BY в качестве значения по умолчанию принимается следующее предложение frame:
Это много ключевых слов, но их смысл не так уж и неясен, когда вы освоите оконные функции. Мы рекомендуем прочитать следующие сообщения в блоге, чтобы узнать больше о них:
Наконец, поскольку мы не хотим, чтобы эти значения NULL оставались в наших результатах, мы просто удаляем их, используя NVL() (или COALESCE() в других базах данных):
Легко, не правда ли? Обратите внимание, что в этом конкретном случае LAG() и LAST_VALUE() будут иметь одинаковый эффект.
Решение с использованием предложения MODEL
Всякий раз, когда у вас возникает проблема в (Oracle) SQL, которую становится трудно решить с помощью оконных функций, предложение Oracle MODEL может предложить «простое» решение. Я использую кавычки на «легкий», потому что синтаксис немного сложен для запоминания, но суть его на самом деле не так сложна.
Предложение MODEL — не что иное, как специфический для Oracle диалект для реализации логики, подобной электронной таблице, в базе данных. Я настоятельно рекомендую прочитать соответствующую Белую книгу от Oracle, которая очень хорошо объясняет функциональность:
Вот как вы можете решить проблему с 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 .
Как в excel сделать пустые ячейки а не 0?
Замечание: Если Вы вдруг забыли сочетание клавиш, откройте вкладку Home (Главная) и в разделе Editing (Редактирование) из выпадающего меню Find & Select (Найти и выделить) выберите команду Go To Special (Выделить группу ячеек). На экране появится то же диалоговое окно.
[expert_bq id=»1570″]Если требуется проверить сразу несколько ячеек, можно использовать функцию в качестве формулы массива выделить требуемое количество пустых ячеек, ввести формулу ЕПУСТО и в качестве аргумента передать диапазон исследуемых ячеек, для выполнения использовать комбинацию клавиш Ctrl Shift Enter. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Выделите строки отдельно или используйте клавишу Ctrl, чтобы выбрать их. Не надо перетащите, чтобы выбрать их, потому что это будет включать скрытые данные. Затем используйте любой из вышеперечисленных способов, чтобы удалить их.
Найти пустые строки в Excel
Подобно функции «Найти», функция «Перейти к специальному» помогает находить пробелы. Преимущество этой опции в том, что она выделяет все пробелы одновременно. Таким образом, вы можете быстро отметить их, а затем удалить строки.
