Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Структура БД для интервальных данных  [new]
LykovAleksey
Member

Откуда:
Сообщений: 218
Приветствую.
Сервис по подписке, соответственно пользователь может подписываться на услугу и отписываться, могут быть разные услуги.
В будущем захочется получить ответы на подобные вопросы:
Сколько пользователей было активных 05.05.2015
Сколько пользователей подписалось в день,
Сколько отписалось.
Причём нужно получать исторические данные.

Как лучше организовать структуру базы данных?


Пока видится решение из 2х таблиц:
1. subscription (active_from, acttive_to) - актуальные данные подписки
2. subscription_history (active_from, acttive_to) - исторические данные подписки, где интервалы по одному пользователю и услуге не пересекаются.

Но по запросам возникает вопрос, что если надо построить график например количества активных подписчиков на каждый день, то надо на каждый день(дату, нпример на 00:00:00) делать запросы к истории. А хочется как-то одним запросом

Ммжет есть какое-то стандартное удобное решение?
19 май 15, 12:24    [17660463]     Ответить | Цитировать Сообщить модератору
 Re: Структура БД для интервальных данных  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21253
А почему надо обязательно хранить состояние, а не изменение подписки?
Тупо (date, state). ПодписАлся чел бессрочно - одна запись в таблице, подписАлся на срок - две записи (подписка и отписка). Соответственно получение состояния на некий кол времени - просто получение последней записи для каждого подписчика с датой не более заданной.
19 май 15, 12:27    [17660488]     Ответить | Цитировать Сообщить модератору
 Re: Структура БД для интервальных данных  [new]
sti
Member

Откуда:
Сообщений: 769
LykovAleksey,

заведите табличку с календарем и всё будет одним запросом.
19 май 15, 14:57    [17661686]     Ответить | Цитировать Сообщить модератору
 Re: Структура БД для интервальных данных  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
LykovAleksey,

автор
Ммжет есть какое-то стандартное удобное решение?


ага есть, DWH называется:)

и потом еще возникнут вопросы типа, "а сколько в среднем живет одна подписка?"

по существу,
Почему вы думаете что нужно будет делать несколько запросов? приведите примеры
19 май 15, 15:22    [17661963]     Ответить | Цитировать Сообщить модератору
 Re: Структура БД для интервальных данных  [new]
LykovAleksey
Member

Откуда:
Сообщений: 218
Akina
А почему надо обязательно хранить состояние, а не изменение подписки?
Тупо (date, state). ПодписАлся чел бессрочно - одна запись в таблице, подписАлся на срок - две записи (подписка и отписка). Соответственно получение состояния на некий кол времени - просто получение последней записи для каждого подписчика с датой не более заданной.

Не будет понятно, как сделать запрос активности подписку на определённую дату и тем более связывать эти строчки между собой т.к. у них нет однозначного соответствия, а сравнивать между собой интервалы будет трудоёмко.
19 май 15, 15:35    [17662078]     Ответить | Цитировать Сообщить модератору
 Re: Структура БД для интервальных данных  [new]
LykovAleksey
Member

Откуда:
Сообщений: 218
sti
LykovAleksey,

заведите табличку с календарем и всё будет одним запросом.

Так, и тогда будет надо на каждый день подписки заводить запись? Не будет ли это избыточно?
19 май 15, 15:36    [17662091]     Ответить | Цитировать Сообщить модератору
 Re: Структура БД для интервальных данных  [new]
LykovAleksey
Member

Откуда:
Сообщений: 218
WarAnt
LykovAleksey,

автор
Ммжет есть какое-то стандартное удобное решение?


ага есть, DWH называется:)


DWH - да, это решение, но это уже после структуры СУБД и требует дальнейшей поддержки. Хочется сделать сейчас самую правильную реляционную, чтобы потом DWH быстро и удобно работало и сейчас небольшие статистические запросы быстро написать.

WarAnt
"а сколько в среднем живет одна подписка?"

Да, конечно возникнут, но тут уж на больших данных это надо будет строить DWH.

WarAnt
Почему вы думаете что нужно будет делать несколько запросов? приведите примеры

Типичный запрос, который сейчас возникает - это определить количество активных подписчиков на каждый день.
Сейчас это выглядит так
WITH mycte AS
(
  SELECT CAST('2015-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    mycte   
  WHERE   DateValue + 1 < GETDATE()
)

SELECT  DateValue, (SELECT count (distinct [PARTNER_SUBSCRIPTION_ID])
  FROM [CG_PARTNER_SUBSCRIPTIONS_HISTORY] where SUBSCRIPTION_STATUS_ID = 2 AND DateValue > TIMESTAMP  AND DateValue < ACTIVE_TO ) as cnt
FROM    mycte

OPTION (MAXRECURSION 0)
19 май 15, 15:40    [17662137]     Ответить | Цитировать Сообщить модератору
 Re: Структура БД для интервальных данных  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21253
LykovAleksey
как сделать запрос активности подписку на определённую дату
Отобрав записи по штампу не позднее заданной даты, вывести первые при сортировке по уменьшению даты. Использовать оконные функции. Например.
19 май 15, 15:58    [17662291]     Ответить | Цитировать Сообщить модератору
 Re: Структура БД для интервальных данных  [new]
LykovAleksey
Member

Откуда:
Сообщений: 218
Akina,
да, наверняка можно, но подозреваю, что запросы будут сложными.
Собственно вопрос и был, как лучше сделать, тут мне спорить сложно, надо опытным путём узнавать.
Если так, то ок. Спасибо.
19 май 15, 16:02    [17662325]     Ответить | Цитировать Сообщить модератору
 Re: Структура БД для интервальных данных  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21253
LykovAleksey, будьте честными - это для Вас они будут сложными. Но скажу по секрету - это пройдёт, причём достаточно быстро.
19 май 15, 16:03    [17662336]     Ответить | Цитировать Сообщить модератору
 Re: Структура БД для интервальных данных  [new]
sti
Member

Откуда:
Сообщений: 769
LykovAleksey
sti
LykovAleksey,

заведите табличку с календарем и всё будет одним запросом.

Так, и тогда будет надо на каждый день подписки заводить запись? Не будет ли это избыточно?


Это ещё зачем? Вы же привели запрос где у вас нет записей на каждый день. Вот и земените ваш рекурсивный cte на табличку с календарем.

Сам запрос для меня логичнее так написать

SELECT  c.DateValue, count (distinct s.[PARTNER_SUBSCRIPTION_ID]) as cnt
FROM calender c
join [CG_PARTNER_SUBSCRIPTIONS_HISTORY] s on on c.DateValue between s.TIMESTAMP and s.ACTIVE_TO
where s.SUBSCRIPTION_STATUS_ID = 2 
and c.DateValue between @DateFrom and @DateTo
group by c.DateValue 
order by c.DateValue 
19 май 15, 16:14    [17662428]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить