Базы данных
Временны́е данные
Время в SQL
Содержание
Георгий КорнеевБазы данных / Временны́е данные2
Типы данных
- Время
- date — дата
- time — время внутри суток
- datetime — дата + время внутри суток
- timestamp(n) — внутреннее время, с точностью n знаков после секунды
- Интервал времени
- interval — промежуток времени между событиями
Георгий КорнеевБазы данных / Временны́е данные3
Часовые пояса
- Стандарт
- UTC
- Факт
- Локальное время
- Интерпретация дат и времени зависит от часового пояса
- И летнего/зимнего времени
- Дублирования и разрывы
Георгий КорнеевБазы данных / Временны́е данные4
Операции
- время − время = интервал
- время + интервал = время
- время − интервал = время
- интервал * число = интервал
- интервал / число = интервал
Георгий КорнеевБазы данных / Временны́е данные5
Литералы
- Время
date 'yyyy-mm-dd'
time 'hh:mm:ss.zzz'
datetime 'yyyy-mm-dd hh:mm:ss.zzz'
cast (days.fraction as datetime)
current_date, current_time, current_timestamp(n)
- Интервал
interval 'PyYmMdDThHmMsS'
cast(days.fraction as interval)
Георгий КорнеевБазы данных / Временны́е данные6
Что такое временны́е данные
Содержание
Георгий КорнеевБазы данных / Временны́е данные7
Временны́е данные
- Временны́е (темпоральные) данные
- Зависят от времени
- Типы зависимости
- Информация о транзакциях (изменениях)
- Информация о правильности
- Бивременные данные
Георгий КорнеевБазы данных / Временны́е данные8
Информация об изменениях
- Статус поступающих
Событие Дата Статус Записался в вуз 15.07 Абитуриент Принёс документы 28.07 Абитуриент с документами Приказ о зачислении 06.08 Поступивший Выдача документов 01.09 Первокурсник
Георгий КорнеевБазы данных / Временны́е данные9
Информация о правильности
- Высота Эвереста
Событие Год Высота Геодезические измерения 1856 8840 Топографические измерения 1950 8848 Китайские измерения 1975 8848.13 Измерение по GPS 1998 8850 Повторные китайские измерения 2005 8844.43 Непальские измерения 2019 > 8844 Совместные измерения 2020 8848.86
Георгий КорнеевБазы данных / Временны́е данные10
Бивременны́е данные (1)
- События жизни
Событие Факт Документы Родился ребёнок Рождение — Регистрация рождения — Свидетельство о рождении Изготовление паспорта — Паспорт Наступило XX лет Начало действия —
Георгий КорнеевБазы данных / Временны́е данные11
Бивременны́е данные (2)
- В декабре появились сведения, что начальник поднял зарплату с сентября
- Последствия
- Какая зарплата была в ноябре?
- Работник: новая
- Банк: старая
- Бухгалтерия: новая (но надо доплатить)
- Выплаты зарплаты в сентябре-ноябре?
- Не изменились
- Начисленная зарплата в сентябре-ноябре?
- Увеличилась
- Выплаты в декабре?
- Доплата за сентябрь-ноябрь
- Какая зарплата была в ноябре?
Георгий КорнеевБазы данных / Временны́е данные12
Временны́е атрибуты
- Значение зависит от времени
- Хранение меток времени
- Метка на запись
- Простота запросов
- Компактность
- Проблема с отслеживанием изменений
- Метка на атрибут
- Более сложные запросы
- Бо́льшая гибкость
- Отсутствие дублирования
Георгий КорнеевБазы данных / Временны́е данные13
Операции
- Внесение изменений
- Обычные запросы
- На заданный момент времени
- Запросы изменений
- Как менялись атрибуты в указанный период времени
- Статистические запросы
- С усреднением по времени
Георгий КорнеевБазы данных / Временны́е данные14
Согласованность временны́х данных
- Для любого момента времени
- Известно значение
- Ровно одно значение
- Должна поддерживаться
Георгий КорнеевБазы данных / Временны́е данные15
Полувременны́е базы данных
Содержание
Георгий КорнеевБазы данных / Временны́е данные16
Полувременны́е базы данных
- Текущие данные
- Хранимые данные
- Текущее значение
- Отметка времени, с которой данные действительны
- Представление данных
- Каждому атрибуту — по метке времени!
Георгий КорнеевБазы данных / Временны́е данные17
Пример
- Статус поступающих
create table entrants ( id int not null primary key, firstName varchar(100) not null, lastName varchar(100) not null, status varchar(100), statusTimestamp timestamp )
- Почему метка времени только для status?
- Остальные данные не зависят от времени
Георгий КорнеевБазы данных / Временны́е данные18
Операции
- Внесение изменений
- Изменение данных и меток времени
- Обычные запросы
- С какого момента действителен результат?
- Максимум по временным меткам
- С какого момента действителен результат?
- Запросы изменений
- Не поддерживаются
- Статистические запросы
- Не поддерживаются
Георгий КорнеевБазы данных / Временны́е данные19
Временны́е базы данных
Содержание
Георгий КорнеевБазы данных / Временны́е данные20
Временна́я реляционная алгебра
- Распакованное представление
- Состояние на каждый момент времени
- Не практично
- Требуется другое представление
- Все операции исполняются так, как если бы они исполнялись на распакованных данных
- Соединения
- Статистика
- Отслеживание изменений
Георгий КорнеевБазы данных / Временны́е данные21
Модель интервалов
Содержание
Георгий КорнеевБазы данных / Временны́е данные22
Представление данных
- В месте с атрибутом хранится интервал действительности
- Полуоткрытый
- Возможны противоречия в данных
Георгий КорнеевБазы данных / Временны́е данные23
Пример
- Информация о студенте
create table Students ( id int not null primary key, firstName varchar(100) not null, lastName varchar(100) not null, ) create table StudentGroups ( studentId int not null references students(id), groupId int not null references groups(id), bg date not null, nd date not null )
Георгий КорнеевБазы данных / Временны́е данные24
Вопросы
- Почему две таблицы?
- Чтобы избежать дублирования
- По таблице на каждый темпоральный атрибут
- Что является ключом StudentGroups?
- (studentId, groupId, bg)
- (studentId, groupId, nd)
- Как хранить данные, верные до текущего момента времени?
- Полувременная база данных
- Отметка ≪бесконечность≫ (inf = date '9999-12-31')
Георгий КорнеевБазы данных / Временны́е данные25
Ограничения целостности
- Интервалы не пересекаются
check (not exists ( select * from StudentGroups s1 inner join StudentGroups s2 on s1.studentId = s2.studentId where greatest(s1.bg, s2.bg) < least(s1.nd, s2.nd) ))
- Что делать для нескольких временны́х атрибутах?
- Отдельные ограничения
Георгий КорнеевБазы данных / Временны́е данные26
Пример соединения
- Студенты, учащиеся в одной группе
- Не SQL:
StudentGroups g1 inner join StudentGroups g2 on g1.groupId = g2.groupId using g1 (bg, nd) and g2 (bg, nd)
- SQL:
select g1.*, g2.*, greatest(g1.bg, g2.bg) as bg, least(g1.nd, g2.nd) as nd from StudentGroups g1 inner join StudentGroups g2 on g1.groupId = g2.groupId and greatest(g1.bg, g2.bg) < least(g1.nd, g2.nd)
- Не SQL:
Георгий КорнеевБазы данных / Временны́е данные27
Соединения
- Общий случай
- Не SQL:
t1 inner join t2 on condition using t1 (t1.bg, t1.nd) and t2 (t2.bg, t2.nd)
- SQL:
select t1.*, t2.*, greatest(t1.bg, t2.bg) as bg, least(t1.nd, t2.nd) as nd from t1 inner join t2 on condition and greatest(t1.bg, t2.bg) < least(t1.nd, t2.nd)
- Не SQL:
Георгий КорнеевБазы данных / Временны́е данные28
Операции над множествами
- Пересечение?
- Естественное соединение
- Объединение?
select t1.*, greatest(t1.bg, t2.bg) as bg, least(t1.nd, t2.nd) as nd from t1 inner join t2 on greatest(t1.bg, t2.bg) < least(t1.nd, t2.nd) union all select t2.*, greatest(t1.bg, t2.bg) as bg, least(t1.nd, t2.nd) as nd from t1 inner join t2 on t1.value <> t2.value and greatest(t1.bg, t2.bg) < least(t1.nd, t2.nd)
- Разность?
Георгий КорнеевБазы данных / Временны́е данные29
Запросы
- На текущий момент
select * from StudentGroups where nd = inf
- На заданный момент
select * from StudentGroups where bg <= :time and :time < nd
- Изменений
select * from StudentGroups
- Статистика
select sum(cast(nd - bg as float) * price) / sum(cast(nd - bg as float)) from prices
Георгий КорнеевБазы данных / Временны́е данные30
Изменение текущих значений
- Вставка
insert into StudentGroups(studentId, groupId, bg, nd) values (:studentId, :groupId, current_date, inf)
- Изменение
update StudentGroups set nd = current_date where studentId = :studentId and nd = inf insert into StudentGroups(studentId, groupId, bg, nd) values (:studentId, :groupId, current_date, inf)
- Удаление
update StudentGroups set nd = current_date where studentId = :studentId and nd = inf
Георгий КорнеевБазы данных / Временны́е данные31
Изменение значений в прошлом
- Случай нескольких интервалов
- Случай одного интервала
Георгий КорнеевБазы данных / Временны́е данные32
Случай нескольких интервалов
- Укоротить первый
update StudentGroups set nd = :bg where studentId = :sid and bg < :bg and :bg < nd
- Укоротить последний
update StudentGroups set :bg = :nd where studentId = :sid and bg < :nd and :nd < bg
- Удалить промежуточные
delete from StudentGroups where studentId = :sid and :bg <= bg and nd <= :nd
- Вставить новый
insert into StudentGroups(studentId, groupId, bg, nd) values (:sid, :groupId, :bg, :nd)
Георгий КорнеевБазы данных / Временны́е данные33
Случай одного интервала
- Продублировать интервал
insert into StudentGroups select sid, groupId, :nd, nd from StudentGroups where studentId = :sid and bg < :bg and :nd < nd
- Действовать как в случае нескольких интервалов
Георгий КорнеевБазы данных / Временны́е данные34
Модель событий
Содержание
Георгий КорнеевБазы данных / Временны́е данные35
Представление данных
- В месте с атрибутом хранится метка времени
- Нет противоречий
- Случайное изменение истории
- Нет удалений
Георгий КорнеевБазы данных / Временны́е данные36
Пример
- Информация о студенте
create table students ( id int not null primary key, firstName varchar(100) not null, lastName varchar(100) not null, ) create table StudentGroups ( studentId int not null references students(id), groupId int not null references groups(id), when date, primary key (studentId, groupId, when) )
Георгий КорнеевБазы данных / Временны́е данные37
Запросы
- На текущий момент?
- argmax с условием
- На заданный момент?
- argmax с условием
- Соединения?
- Построить интервальное представление
- Множественные операции?
- Построить интервальное представление
Георгий КорнеевБазы данных / Временны́е данные38
Изменения
- Текущих значений
insert into StudentGroups(studentId, groupId, when) values (:studentId, :groupId, current_date)
- Изменение значений в прошлом
- Семантика не ясна
- Изменение значений на интервале
- Аналогично интервальной модели
Георгий КорнеевБазы данных / Временны́е данные39
Выводы
- Плюсы
- Небольшая экономия памяти
- Интуитивно понятна
- Минусы
- Случайное изменение истории
- Нет удалений
- Очень сложные запросы
- Низкая производительность запросов
Георгий КорнеевБазы данных / Временны́е данные40
Литература
Содержание
Георгий КорнеевБазы данных / Временны́е данные41
Основная литература
- Дейт К. Введение в системы баз данных (глава 23)
- Celko J. SQL for Smarties (глава 4)
- Gao D., Jensen C., Snodgrass R., Soo M. Join operations in temporal databases
Георгий КорнеевБазы данных / Временны́е данные42