Пробелы и острова, или Gaps and islands. Часть II

Это продолжение поста Пробелы и острова, или Gaps and islands. Часть I

Напомню, что задачи вида "пробелы и острова", или "gaps and islands", возникают, когда в некоторой последовательности нужно найти участки (диапазоны, интервалы) непрерывных данных – "острова" – и/или участки отсутствия данных – "пробелы".

Часто последовательность данных – это хронологическая последовательность, то есть, развернутая во времени, например, по датам. Тогда "острова" – это непрерывные периоды, когда некое событие происходит ежедневно, а "пробелы" – это непрерывные периоды, в течение которых это событие не происходит.

Рассмотрим решение задачи "пробелы и острова" на хронологической последовательности данных.

Пробелы и острова, или Gaps and islands. Часть I

Задачи вида "пробелы и острова", или "gaps and islands", возникают, когда в некоторой последовательности нужно найти участки (диапазоны, интервалы) непрерывных данных – "острова" – и/или участки отсутствия данных – "пробелы".

С наступленим эры оконных функций (они же аналитические, они же over-функции) задачи вида "пробелы и острова" решаются с помощью этих функций.

Примеры ниже с таблицей t1 взяты мной из статьи Solving Gaps and Islands with Enhanced Window Functions by Itzik Ben-Gan и адаптированы для выполнения на PostgreSQL.

После примеров с таблицей t1 я разберу более прикладной оригинальный пример с таблицей ежедневных продаж daily_sales.

Десять лет спустя, или Установка PostgreSQL на Windows

10 лет назад я написал первый пост в мой блог – про установку Oracle XE 11gR2 на Windows. Сколько воды утекло...

В 2022 комания Oracle удалила мой аккаунт, которым я пользовался лет двадцать, после того как Россия попала под экспортные ограничения. (Как в том анекдоте, где опытный кадровик помогает молодому коллеге справиться к горой резюме от кандидатов, которые тому нужно просмотреть. Половину резюме – сразу в мусорную корзину со словами "Лузеры нам не нужны!") Но жизнь продолжается.

Неизвестное vs Отсутствующее, или Двуликий NULL

Когда-то я уже писал о "многоликом NULL", см. Часть I и Часть II. Сегодняшний пост в дополнение и в развитие этой темы.

Я неоднократно встречал утверждение, что NULL в SQL нужно понимать как "неизвестно что". С этим согласуется поведение операторов сравнения при сравнении двух NULL (команды выполнены в PostgreSQL 15):

select 1
where null = null
   or null != null
   or null < null
   or null <= null
   or null >= null
   or null > null
;

no rows

dbang! Утилиты для работы с БД

Написанные на Python, утилиты dbang позволяют существенно сэкономить усилия при решении типичных задач, связанных с БД:

• ddiff.py - выполняет запросы к двум БД и формирует отчет о расхождениях;
• dtest.py - выполняет запросы к БД и формирует отчет о найденных проблемах;
• dget.py - выгружает данные из БД в файлы csv, xlsx или html;
• dput.py - загружает данные из файлов csv или xlsx в таблицы БД;
• hedwig.py - на основе файлов создает и отправляет е-мейл.

Утилиты могут работать с СУБД Oracle, PostgreSQL, SQLite и MySQL.

delta revisited. Работа с изменениями первичного ключа

Ранее я описал механизм delta для регистрации изменений в таблицах БД Oracle, позволяющий обрабатывать изменения независимо нескольким клиентам.

Долгое время все решения на базе механизма delta делались для базовых таблиц с неизменными первичными ключами.

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

Попробуем разобраться, как же корректно обрабатывать изменения первичного ключа базовой таблицы, используя механизм delta.

delta revisited. Работа с обычной таблицей изменений

Ранее я описал механизм delta для регистрации изменений в таблицах БД Oracle, позволяющий обрабатывать изменения независимо нескольким клиентам.

Работа описанного механизма опирается на таблицу изменений с rowdependencies. Что делает его зависимым от СУБД Oracle. А в один прекрасный день возникает желание использовать хорошо зарекомендовавший себя механизм в СУБД PostgreSQL или другой СУБД с триггерами.

Можно ли использовать более универсальный подход при реализации таблицы изменений вместо таблицы с rowdependencies?

Кейс: изменение значения первичного ключа

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

Вопрос об изменении значения первичного ключа может также возникнуть в контексте интеграции систем.

Например, система А посылает в шину данных сообщения об изменениях бизнес-сущности Товар. И в этих сообщениях каждый отдельный экземпляр сущности – индивидуальный товар – идентифицируется бизнес-ключом, значение которого может быть изменено пользователем системы А (см. мой пост Кейс: изменение значения бизнес-ключа). А в системе Б, которая получает сообщения из шины данных и на их основании актуализирует собственную копию бизнес-сущности Товар, бизнес-ключ является первичным ключом таблицы в БД. И на него ссылаются внешние ключи из других таблиц.

Сможет ли система Б обновить первичный ключ, если она получит сообщение об изменении бизнес-ключа?

Какого ограничения целостности не хватает в SQL

Константного - когда значение в столбце, присвоенное insert'ом, нельзя изменить update'ом.

Столбцов, которые выиграют от такого ограничения, достаточно много - это, конечно, первичные ключи, и другие атрибуты, описывающие неотъемлемые неизменные свойства объектов, без которых объект утрачивает свою "природу" и перестает быть собой, например, тип товара, вид заказа и подобные. В приложении, с которым работают пользователи, такие поля доступны для заполнения при создании новой записи и недоступны для изменения при редактировании уже существующей.

Можно, конечно, создать триггер для update, который запретит изменение, если новое значение такого столбца отличается от старого.

Периодические таблицы (не химия)

Время от времени приходится работать с таблицами БД, где записи имеют период действия, заданный датой начала и датой конца (последнего дня) периода. Это может быть таблица с ценами товаров, где в разные периоды времени цена одного и того же товара разная. Или таблица с конфигурационными параметрами, где значения параметров разные в разные периоды времени. При этом в каждую конкретную дату у товара может быть только одна действующая цена, а у конфигурационного параметра только одно значение параметра. То есть, периоды действия записей с одним и тем же товаром или параметром не должны пересекаться.

В связи с этим при вставке или изменении записи нужно проверять, не приведет ли эта операция к появлению в таблице записей с пересекающимися периодами.

Функции c within group в СУБД Oracle

Функции c within group бывают агрегатные и аналитические, а объединяет их то, что для вычисления результата они нуждаются в упорядоченной последовательности входных значений. Именно упорядочивание входных значений и задается с помощью within group (order by ...).

Захват и обработка изменений в БД Oracle (at_delta)

Ранее я рассказывал о том, как получить изменения данных в исходных таблицах за период с помощью операции MINUS.

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

Библиотека atop-plsql для разработки в СУБД Oracle

Недавно я оформил и выложил на GitHub "труд долгих лет" atop-plsql - набор PL/SQL пакетов и сопутствующих им типов, таблиц и некоторых других объектов схемы БД.

Эти пакеты развивались и использовались в течение нескольких лет для разработки на их основе конечных решений в СУБД Oracle 11gR2.

Статистические функции в Oracle 11g, часть II

В части I было показано, как с помощью функций Oracle 11g получить статистические характеристики выборки, а именно:

• центральные тенденции: среднее арифметическое, медиану, моду и середину диапазона,
• характеристики разброса: дисперсию, среднеквадратичное отклонение, межквартильный диапазон и диапазон выборки.

Также с помощью запросов SQL были построены столбцовая диаграмма и гистограмма для номинальных и числовых данных.

Сегодня с помощью функций Oracle поиграем с нормальным распределением.

Типы данных TIMESTAMP и INTERVAL в СУБД Oracle

Сегодня в СУБД Oracle есть несколько типов данных для хранения дат и времени. Самый старый из них - тип date - совершенно точно был еще в Oracle 7 (с более ранними версиями СУБД я не работал). Некоторые интересные вещи про тип date я уже рассказывал. В версии Oracle 9 появились новые типы для дат и времени timestamp, timestamp with local time zone и timestamp with time zone, а также интервальные типы interval year to month и interval day to second, работающие вместе с новыми типами и типом date.

Типы timestamp, timestamp with local time zone и timestamp with time zone привнесли два новшества, по сравнению с типом date:

• возможность работать со временем с точностью до наносекунд,
• возможность работать с часовыми поясами (time zones).

Ниже мы поработаем с типами timestamp и interval, обращая внимание на задание значений этих типов с помощью литералов и на их арифметику. Затем обратимся к различиям между типами timestamp, timestamp with local time zone и timestamp with time zone, и рассмотрим, какой из них предпочтительно использовать в какой ситуации.

Комбинаторика в СУБД Oracle 11g

По запросу "oracle factorial" с ходу нагугливаются два подхода к вычислению факториала в СУБД Oracle:

• самоочевидный: в цикле получить произведение натуральных чисел от 1 до n

  create or replace
  function fac1(n pls_integer) return number
  is
      f number := 1;
  begin
      if n in (0, 1) then
          return 1;
      end if;
      for i in 2..n loop
          f := f * i;
      end loop;
      return f;
  end fac1;
  /
  

• забавный: вместо произведения натуральных чисел от 1 до n найти сумму их логарифмов, а затем возвести основание логарифма в найденную степень

  create or replace 
  function fac2(n pls_integer) return number
  is
      f number;
  begin
      select round(exp(sum(ln(level))))
      into f
      from dual
      connect by level <= n;
      return f;
  end fac2;
  /
  

Нулевой год, юлианские дни и наследие папы Григория XIII

Сегодня в СУБД Oracle есть несколько типов данных для хранения дат и времени. Самый старый из них - тип DATE - совершенно точно был еще в Oracle 7 (с более ранними версиями СУБД я не работал). Тогда ввести значение типа DATE можно было только с помощью функции to_date. В версии 9 появился литерал для значений типа DATE (а также новые типы для дат и времени TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE и TIMESTAMP WITH TIME ZONE). Рассмотрим сегодня некоторые особенности типа DATE.

Статистические функции в Oracle 11g

Рассмотрим на примерах возможности статистического анализа и статистические функции в Oracle 11g.

Передача изменений между БД: подход в духе KISS

В БД SOURCE в таблице itemz ведется некий справочник. Время от времени в справочник добавляются новые строки и изменяются существующие. Возможно, строки даже иногда удаляются. В данных этого справочника нуждается БД DEST, причем контракт состоит в передаче из БД SOURCE всех изменений справочника. И как же организована передача изменений БД SOURCE в БД DEST? А вот так.

Про отношения с таблицами. C примерами на SQL

Отношение есть подмножество декартова произведения множеств, элементы которого - кортежи, составленные из элементов перемножаемых множеств. N-арное отношение есть подмножество декартова произведения N множеств, состоящее из кортежей длины N. В частности, бинарное отношение есть множество пар, или кортежей длины 2, где первый элемент принадлежит первому, а второй - второму из двух перемножаемых множеств.

Например, для двух множеств

A = {1, 2, 3}
B = {'Стакан', 'Лимон'}

декартово произведение есть

A x B = {
    (1, 'Стакан'), 
    (1, 'Лимон'),
    (2, 'Стакан'),
    (2, 'Лимон'),
    (3, 'Стакан'),
    (3, 'Лимон')
}