#sql #database #tsql #dataset #dimensional-modeling
#sql #База данных #tsql #набор данных #размерное моделирование
Вопрос:
У меня проблема с набором данных HR. Для позиции у меня есть сведения о позиции, такие как категория, тип и т.д. Эти данные меняются со временем и независимо друг от друга, в результате чего получаются следующие наборы данных:
Position StartDate EndDate
1 '2020-01-01' '3000-01-01'
Position Type StartDate EndDate
1 'Part-time' '2020-01-01' '2020-07-31'
1 'Full-time' '2020-08-01' '2020-08-30'
1 'Part-time' '2020-09-01' '3000-01-01
Position Category StartDate EndDate
1 'Agency' '2020-01-01' '2020-06-30'
1 'Employee' '2020-07-01' '2020-08-30'
1 'Contractor' '2020-09-01' '3000-01-01
Итак, исходя из этого, я хотел бы получить такой набор данных, как:
Position Type Category StartDate EndDate
1 'Part-time' 'Agency' '2020-01-01' '2020-06-30'
1 'Part-time' 'Employee' '2020-07-01' '2020-07-31'
1 'Full-time' 'Employee' '2020-08-01' '2020-08-30'
1 'Part-time' 'Contractor' '2020-09-01' '3000-01-01'
Чтобы при фильтрации по дате возвращалась ситуация для этой позиции на эту дату. В этом примере на ‘2020-07-05’ должность будет отображаться как сотрудник, работающий неполный рабочий день.
Методы, которые я рассмотрел до сих пор, включают операторы CASE для создания полей StartDate и EndDate
И создание объединения наборов данных и выполнение ROW_NUMBER() ПОВЕРХ (РАЗДЕЛЕНИЕ ПО позиции ПОВЕРХ StartDate, EndDate), чтобы попытаться отфильтровать неправильные строки при объединении этих таблиц.
Редактировать: Извинения, я должен был добавить, это минимальный пример проблемы. В примере, который я рассматриваю, будет 6-12 столбцов. Поэтому решение должно быть масштабируемым.
Комментарии:
1. Зачем вам нужен последний набор данных, который вы перечислили? Если вы просто хотите узнать ситуацию для позиции на дату, в чем проблема с простым запросом первых 3 таблиц?
2. Для целей отчетности эта таблица будет объединена с измеряемым элементом в виде таблицы измерений.
3. Привет — я мог бы понять, что вы хотите создать таблицу такого типа, если бы вы разрабатывали звездообразную схему в стиле Кимбалла, но таблица, как вы ее описываете, не является измерением Кимбалла, и поэтому, я полагаю, вы на самом деле не строите звездообразную схему? Поэтому я все же считаю, что было бы намного проще просто запросить существующие таблицы, когда вы хотите сообщить о них, вместо того, чтобы пытаться создать эту промежуточную таблицу, которая дает мало дополнительных преимуществ
Ответ №1:
Метод, который я бы выбрал, — применить ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ между таблицами типов и категорий с фильтрацией по перекрывающимся фазам обеих таблиц.
SELECT * FROM Position p
INNER JOIN Type t
ON t.Position = p.Position
CROSS JOIN Category c
WHERE c.StartDate >= t.StartDate AND c.StartDate <= t.EndDate
OR (t.StartDate >= c.StartDate AND t.StartDate <= c.EndDate)
OR (c.StartDate < t.StartDate AND c.EndDate > t.EndDate)
ORDER BY t.StartDate
В списке ВЫБОРА вы можете применить инструкцию CASE, чтобы выбрать правильную дату для вашего диапазона дат.
Комментарии:
1. Возможно, вы на что-то наткнулись. Добавление таблицы за раз и вычисление промежуточных наборов данных будет тем, что я рассмотрю дальше. Я только что уточнил свою первоначальную отправку проблемы, но перекрестное объединение и фильтрация будут сложными / интенсивными, как только проблема будет масштабироваться до ~ 6000 сотрудников за несколько лет и нескольких атрибутов (столбцов).