Ответ 1
Вот модель для достижения вашего заявленного требования.
Ссылка на модель данных временных рядов
Ссылка на IDEF1X Notation для тех, кто незнаком с стандартом реляционного моделирования.
-
Нормализован до 5NF; нет дубликатов столбцов; нет обновлений аномалий, нет нулей.
-
Когда изменяется статус продукта, просто вставьте строку в ProductStatus с текущим DateTime. Не нужно касаться предыдущих строк (которые были истинными и остаются верными). Никакие фиктивные значения, которые предоставляют инструменты отчетов (кроме вашего приложения), не должны интерпретироваться.
-
DateTime - это фактическое значение DateTime, которое Продукт был помещен в этот статус; "От", если хотите. "To" легко получить: это DateTime следующей строки (DateTime > "From" ) для Продукта; где он не существует, значением является текущее DateTime (используйте ISNULL).
Первая модель завершена; (ProductId, DateTime) достаточно, чтобы обеспечить уникальность для Первичного ключа. Однако, поскольку вы запрашиваете скорость для определенных условий запроса, мы можем улучшить модель на физическом уровне и предоставить:
-
Индекс (у нас уже есть индекс PK, поэтому мы добавим второй индекс перед добавлением второго индекса) для поддержки закрытых запросов (на основе любой договоренности {ProductId | DateTime | Status} по индексу, без необходимости обращаться к строкам данных). Что изменяет отношение Status:: ProductStatus от неидентифицируемой (ломаной линии) к идентификационному типу (сплошная линия).
-
Расположение PK выбирается исходя из того, что большинство запросов будут Time Series, основанные на Product⇢DateTime⇢Status.
-
Второй индекс предоставляется для повышения скорости запросов на основе состояния.
-
В альтернативном расположении, которое отменяется; т.е. мы в основном хотим текущего статуса всех продуктов.
-
Во всех версиях ProductStatus столбец DateTime во вторичном индексе (а не PK) является DESCending; последнее в первую очередь.
Я предоставил запрошенную вами дискуссию. Конечно, вам нужно поэкспериментировать с набором данных разумного размера и принимать собственные решения. Если здесь есть что-то, что вы не понимаете, спросите, и я буду расширяться.
Ответы на комментарии
Сообщить обо всех товарах с текущим состоянием 2
SELECT ProductId,
Description
FROM Product p,
ProductStatus ps
WHERE p.ProductId = ps.ProductId -- Join
AND StatusCode = 2 -- Request
AND DateTime = ( -- Current Status on the left ...
SELECT MAX(DateTime) -- Current Status row for outer Product
FROM ProductStatus ps_inner
WHERE p.ProductId = ps_inner.ProductId
)-
ProductIdиндексируется, ведущий столбец, обе стороны -
DateTimeв индексированном, втором столбце в опции с закрытым запросом -
StatusCodeиндексируется, третий col в опции с закрытым запросом -
Так как
StatusCodeв индексе DESCending, для удовлетворения внутреннего запроса требуется только одна выборка -
строки требуются одновременно, для одного запроса; они близки друг к другу (из-за индекса Clstered); почти всегда на одной странице из-за небольшого размера строки.
Это обычный SQL, подзапрос, использующий мощь механизма SQL, обработка реляционных множеств. Это один правильный метод, нет ничего более быстрого, и любой другой метод будет медленнее. Любой инструмент отчета будет генерировать этот код несколькими щелчками мыши, без ввода текста.
Два срока в ProductStatus
Столбцы, такие как DateTimeFrom и DateTimeTo, являются грубыми ошибками. Возьмите его в порядке важности.
-
Это грубая ошибка нормализации. "DateTimeTo" легко выводится из одного DateTime следующей строки; поэтому он избыточен, дубликат столбца.
- Точность не входит в нее: это легко разрешается в силу DataType (DATE, DATETIME, SMALLDATETIME). Если вы показываете одну секунду, микросекунду или наносекунду, это деловое решение; он не имеет ничего общего с хранимыми данными.
-
Реализация столбца DateTo представляет собой 100% -ный дубликат (DateTime следующей строки). Это занимает в два раза больше места на диске. Для большой таблицы это будет значительным ненужным отходом.
-
Учитывая, что это короткая строка, вам потребуется в два раза больше логических и физических операций ввода-вывода, чтобы читать таблицу, при каждом доступе.
-
И вдвое больше кэширования (или иначе, только половина строк будет вписываться в любое заданное пространство кэша).
-
Введя повторяющийся столбец, вы указали на возможность ошибки (теперь это значение можно получить двумя способами: из двойного столбца DateTimeTo или DateTimeFrom из следующей строки).
-
Это также Аномалия обновления. Когда вы обновляете DateTimeFrom, обновляется DateTimeTo предыдущей строки (нет большого количества, поскольку она закрыта) и Обновлено (большое дело, поскольку это дополнительный глагол, которого можно избежать).
-
"Короче" и "ярлыки кодирования" не имеют значения, SQL - громоздкий язык манипулирования данными, но SQL - это все, что у нас есть (Just Deal With It). Любой, кто не может закодировать подзапрос, действительно не должен кодировать. Любой, кто дублирует столбец, чтобы облегчить незначительное кодирование "трудности", действительно не должен быть модельным базами данных.
Заметьте, что если было поддержано правило наивысшего порядка (нормализация), весь набор проблем более низкого порядка устраняется.
Подумайте в терминах наборы
-
Любой, у кого есть "трудность" или "боль" при написании простого SQL, искалечен при выполнении своей функции задания. Обычно разработчик не думает в терминах множеств, а реляционная база данных - это ориентированная на модель модель.
-
Для вышеуказанного запроса нам понадобится Current DateTime; поскольку ProductStatus - это набор состояний продукта в хронологическом порядке, нам просто нужен последний или MAX (DateTime) набора, принадлежащего Продукту.
-
Теперь посмотрим на что-то якобы "трудное", с точки зрения множеств. Для отчета о продолжительности, в течение которого каждый Продукт находился в определенном состоянии: DateTimeFrom является доступным столбцом и определяет горизонтальное отсечение, подмножество (мы можем исключить более ранние строки); DateTimeTo является самым ранним из подмножеств состояний продукта.
SELECT ProductId,
Description,
[DateFrom] = DateTime,
[DateTo] = (
SELECT MIN(DateTime) -- earliest in subset
FROM ProductStatus ps_inner
WHERE p.ProductId = ps_inner.ProductId -- our Product
AND ps_inner.DateTime > ps.DateTime -- defines subset, cutoff
)
FROM Product p,
ProductStatus ps
WHERE p.ProductId = ps.ProductId
AND StatusCode = 2 -- Request-
Мысль о том, чтобы следующая строка была ориентирована на ряд строк, а не на селекторную обработку. Crippling, при работе с сетно-ориентированной базой данных. Пусть оптимизатор сделает все, что задумается. Проверьте свой SHOWPLAN, это прекрасно оптимизируется.
-
Неспособность думать в наборах, ограничиваясь тем, что написала только одноуровневые запросы, не является разумным оправданием для: реализации массового дублирования и обновлений аномалий в базе данных; тратить ресурсы онлайн и дисковое пространство; гарантируя половину производительности. Намного дешевле узнать, как писать простые подзапросы SQL для получения легко полученных данных.
