Введение в базы данных
Временны́е данные
Время в SQL
Содержание
Георгий КорнеевВведение в базы данных / Временны́е данные2
Типы данных
- Время
- date — дата
- time — время внутри суток
- datetime — дата + время внутри суток
- timestamp(n) — внутреннее время, с точностью n знаков после секунды
- Интервал времени
- interval — промежуток времени между событиями
Георгий КорнеевВведение в базы данных / Временны́е данные3
Часовые пояса
- Стандарт
- UTC
- Факт
- Локальное время
- Интерпретация дат и времени зависит от часового пояса
- И летнего/зимнего времени
- Дублирования и разрывы
Георгий КорнеевВведение в базы данных / Временны́е данные4
Операции
- время − время = интервал
- время + интервал = время
- время − интервал = время
- интервал * число = интервал
- интервал / число = интервал
Георгий КорнеевВведение в базы данных / Временны́е данные5
Литералы
- Время
date 'yyyy-mm-dd'
time 'hh:mm:ss.zzz'
date 'yyyy-mm-dd'
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
Георгий КорнеевВведение в базы данных / Временны́е данные10
Бивременные данные
- События жизни
Событие Факт Документы Родился ребёнок Рождение — Регистрация рождения — Свидетельство о рождении Изготовление паспорта — Паспорт Наступило XX лет Начало действия —
Георгий КорнеевВведение в базы данных / Временны́е данные11
Временны́е атрибуты
- Значение зависит от времени
- Хранение меток времени
- Метка на запись
- Простота запросов
- Компактность
- Проблема с отслеживанием изменений
- Метка на атрибут
- Более сложные запросы
- Большая гибкость
- Отсутствие дублирования
Георгий КорнеевВведение в базы данных / Временны́е данные12
Операции
- Внесение изменений
- Обычные запросы
- На заданный момент времени
- Запросы изменений
- Как менялись атрибуты в указанный период времени
- Статистические запросы
- С усреднением по времени
Георгий КорнеевВведение в базы данных / Временны́е данные13
Корректность временны́х данных
- Для любого момента времени
- Известно значение
- Ровно одно значение
- Должна поддерживаться
Георгий КорнеевВведение в базы данных / Временны́е данные14
Полувременны́е базы данных
Содержание
Георгий КорнеевВведение в базы данных / Временны́е данные15
Полувременны́е базы данных
- Текущие данные
- Хранимые данные
- Текущее значение
- Отметка времени, с которой данные действительны
- Представление данных
- Каждому атрибуту — по метке времени!
Георгий КорнеевВведение в базы данных / Временны́е данные16
Пример
- Статус поступающих
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?
- Остальные данные не зависят от времени
Георгий КорнеевВведение в базы данных / Временны́е данные17
Операции
- Внесение изменений
- Изменение данных и меток времени
- Обычные запросы
- С какого момента действителен результат?
- Запросы изменений
- Не поддерживаются
- Статистические запросы
- Не поддерживаются
Георгий КорнеевВведение в базы данных / Временны́е данные18
Временны́е базы данных
Содержание
Георгий КорнеевВведение в базы данных / Временны́е данные19
Временна́я реляционная алгебра
- Распакованное представление
- Состояние на каждый момент времени
- Не практично
- Требуется другое представление
- Все операции исполняются так, как если бы они исполнялись на неупакованных данных
- Соединения
- Статистика
- Отслеживание изменений
Георгий КорнеевВведение в базы данных / Временны́е данные20
Модель интервалов
Содержание
Георгий КорнеевВведение в базы данных / Временны́е данные21
Представление данных
- В месте с атрибутом хранится интервал действительности
- Полуоткрытый
- Возможны противоречия в данных
Георгий КорнеевВведение в базы данных / Временны́е данные22
Пример
- Информация о студенте
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, nd date )
Георгий КорнеевВведение в базы данных / Временны́е данные23
Вопросы
- Почему две таблицы?
- Чтобы избежать дублирования
- По таблице на каждый темпоральный атрибут
- Что является ключом studentGroups?
- (studentId, groupId, bg)
- или (studentId, groupId, nd)
- Как хранить данные, верные до текущего момента времени?
- Полувременная база данных
- Отметка "бесконечность" (inf = date '9999-12-31')
Георгий КорнеевВведение в базы данных / Временны́е данные24
Ограничения целостности
- Интервалы не пересекаются
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) ))
- Что делать для нескольких временны́х атрибутах?
- Отдельные ограничения
Георгий КорнеевВведение в базы данных / Временны́е данные25
Пример соединения
- Студенты, учащиеся в одной группе
- Не 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:
Георгий КорнеевВведение в базы данных / Временны́е данные26
Соединения
- Общий случай
- Не 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:
Георгий КорнеевВведение в базы данных / Временны́е данные27
Операции над множествами
- Пересечение
- Естественное соединение
- Объединение
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)
- Разность
Георгий КорнеевВведение в базы данных / Временны́е данные28
Запросы
- На текущий момент
select * from studentGroups where nd = inf
- На заданный момент
select * from studentGroups where bg <= :time and :time < nd
- Изменений
select * from studentGroups
- Статистика
select avg(cast(nd - bg as float) * price) from prices
Георгий КорнеевВведение в базы данных / Временны́е данные29
Изменение текущих значений
- Вставка
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
Георгий КорнеевВведение в базы данных / Временны́е данные30
Изменение значений в прошлом
- Случай нескольких интервалов
- Случай одного интервала
Георгий КорнеевВведение в базы данных / Временны́е данные31
Случай нескольких интервалов
- Укоротить первый
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)
Георгий КорнеевВведение в базы данных / Временны́е данные32
Случай одного интервала
- Продублировать интервал
insert into studentGroups select sid, groupId, :nd, nd from studentGroups where studentId = :sid and bg < :bg and :nd < nd
- Действовать как в случае нескольких интервалов
Георгий КорнеевВведение в базы данных / Временны́е данные33
Модель событий
Содержание
Георгий КорнеевВведение в базы данных / Временны́е данные34
Представление данных
- В месте с атрибутом хранится метка времени
- Нет противоречий
- Случайное изменение истории
- Нет удалений
Георгий КорнеевВведение в базы данных / Временны́е данные35
Пример
- Информация о студенте
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) )
Георгий КорнеевВведение в базы данных / Временны́е данные36
Запросы
- На текущий момент?
- argmax с условием
- На заданный момент?
- argmax с условием
- Соединения?
- Построить интервальное представление
- Множественные операции?
- Построить интервальное представление
Георгий КорнеевВведение в базы данных / Временны́е данные37
Изменения
- Текущих значений
insert into studentGroups(studentId, groupId, when) values (:studentId, :groupId, current_date)
- Изменение значений в прошлом
- Семантика не ясна
- Изменение значений на интервале
- Аналогично интервальной модели
Георгий КорнеевВведение в базы данных / Временны́е данные38
Выводы
- Плюсы
- Небольшая экономия памяти
- Интуитивно понятна
- Минусы
- Случайное изменение истории
- Нет удалений
- Очень сложные запросы
- Низкая производительность запросов
Георгий КорнеевВведение в базы данных / Временны́е данные39
Литература
Содержание
Георгий КорнеевВведение в базы данных / Временны́е данные40
Основная литература
- Дейт К. Введение в системы баз данных (глава 23)
- Celko J. SQL for Smarties (глава 4)
- Gao D., Jensen C., Snodgrass R., Soo M. Join operations in temporal databases
Георгий КорнеевВведение в базы данных / Временны́е данные41