Месяц за месяцем запросов в нескольких таблицах измерений

В настоящее время у нас есть хранилище данных (Azure SQL Server), к которому я подключаюсь. Проблема: мне нужно иметь месячный подсчет свободных владений, разделенных по регионам, в которых они расположены. Недвижимость можно разделить на многоквартирную (более одной двери) или на одну семью (одна дверь). Если недвижимость пустует в течение одного месяца и продолжает оставаться вакантной в следующем месяце, нам необходимо ее посчитать. Существует вероятность того, что запись останется бездействующей, поэтому у меня не будет строки в БД, которую я могу связать с более поздним месяцем.

Контекст (упрощенный):

tblPWDimBuilding

Идентификатор здания Положение дел RowEffectiveDate Название организации 1234 Занято 2023-05-17 Кливленд 1235 Вакантно 2023-04-17 Кливленд 1236 Занято 2023-05-17 Кливленд 1237 Вакантно 2023-05-17 Кливленд 1238 Вакантно 2023-03-17 Кливленд

tblPWDimUnit

Идентификатор единицы Положение дел RowEffectiveDate Название организации Идентификатор здания 2123 Занято 2023-05-17 Кливленд 1238 2124 Вакантно 2023-04-17 Кливленд 1238

RowEffectiveDate — поскольку у нас есть исторические данные в тусклых таблицах, этот столбец указывает, что запись активна с этой конкретной даты.

RowExpirationDate — это снова для исторических данных. В этом столбце указано, что срок действия записи истек в эту конкретную дату, т. е. неактивная запись, для активных записей значение равно нулю.

RowIsActive - 1 означает активную запись, тогда как 0 означает неактивную запись (исторические данные).

Ожидаемые результаты из таблицы выше:

Год Месяц Название организации Свободное количество 2023 04 Кливленд 2 2023 05 Кливленд 3 (не 1)

(обратите внимание, что статус собственности в многоквартирном доме игнорируется, и вместо этого рассматривается единица)

Где в апреле у нас есть результаты для дома на одну семью с идентификатором 1235 и для многоквартирного дома, где одна дверь свободна с идентификатором здания 1238 и идентификатором единицы 2124. В мае у нас нет любые новые записи в таблице DIM для зданий или квартир, так что 2 двери с апреля все еще свободны в мае и должны быть учтены вместе с новым свободным зданием с идентификатором 1237. Этот процесс необходимо будет использовать при просмотре замыкающего списка. 6-12 месяцев, так что мы не можем просто смотреть на период последнего месяца. Я не уверен, как подражать этому водопаду, как процесс подсчета. Моя неудачная попытка ниже:

    SELECT
         DATEPART(YEAR, RowEffectiveDate) AS Year,
         DATEPART(MONTH, RowEffectiveDate) As Month,
         OrganizationName,
         COUNT(DISTINCT b.BuildingId) AS [Vacant Count]
    FROM curated.tblPWDimBuilding b
    WHERE b.Status = 'Vacant'
        AND b.Active = 'True'
    GROUP BY
        DATEPART(YEAR, RowEffectiveDate),
        DATEPART(MONTH, RowEffectiveDate),
        OrganizationName

    UNION

    SELECT
        DATEPART(YEAR, u.RowEffectiveDate) AS Year,
        DATEPART(MONTH, u.RowEffectiveDate) As Month,
        u.OrganizationName,
        COUNT(DISTINCT u.UnitID) AS [Vacant Count]
    FROM curated.tblPWDimBuilding b
              INNER JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
                 AND u.Status = 'Vacant'
    WHERE b.Active = 'True'
    GROUP BY
        DATEPART(YEAR, u.RowEffectiveDate),
        DATEPART(MONTH, u.RowEffectiveDate),
        u.OrganizationName;

В приведенном выше коде у меня есть союз между перспективой для одной семьи и перспективой для нескольких семей на уровне единицы. Затем я группирую по году и месяцу, когда запись была включена в базу данных, и группирую по организации или региону, в котором находится недвижимость. Флаг Active=True — это флаг, специфичный для исходной системы, чтобы убедиться, что она не была деактивирована.

Вышеприведенное близко, но при использовании даты вступления в силу строки не учитывается случай, когда свободная недвижимость должна быть отражена в следующем месяце и простаивает (где запись в прошлом месяце будет помечена как активная) группировка больше не будет работать корректно. Я застрял! Как это можно решить? Где я мог ошибиться?

Если бы вы могли предоставить некоторые образцы данных и ожидаемый результат (из этих данных), у вас гораздо больше шансов быстро получить работоспособное решение. Необходимо некоторое объяснение, но слова действительно не могут адекватно заменить данные. О, данные не означают результат неработающего запроса — это небольшие образцы данных для каждой таблицы.

Paul Maxwell 18.05.2023 04:15

По вашему запросу были добавлены простые дополнения @PaulMaxwell. Спасибо за ответ!

NerdyWithAByte 18.05.2023 18:35
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
2
59
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Для записей, которые остаются бездействующими и которые необходимо подсчитать в следующем месяце, используйте таблицу измерения даты или создайте список месяцев в CTE, а затем соедините его с текущим запросом. Под CTE MonthList находится необходимый список начала месяца, созданный из столбца RowEffectiveDate в обеих таблицах. Затем для каждого месяца запрос проверяет, активна ли запись в течение этого месяца, если RowEffectiveDate меньше или равно началу месяца, а RowExpirationDate больше начала месяца или NULL, что будет включать незанятые записи в следующем месяце. счет месяца:

WITH MonthList
AS (
    SELECT DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0, RowEffectiveDate), 0) AS MonthStart
    FROM (
        SELECT RowEffectiveDate FROM curated.tblPWDimBuilding
        UNION
        SELECT RowEffectiveDate FROM curated.tblPWDimUnits
        ) AS Dates
    )
SELECT DATEPART(YEAR, m.MonthStart) AS Year, DATEPART(MONTH, m.MonthStart) AS Month, OrganizationName, COUNT(DISTINCT b.BuildingId) AS [Vacant Count]
FROM MonthList m
INNER JOIN curated.tblPWDimBuilding b ON b.STATUS = 'Vacant'
    AND b.Active = 'True'
    AND b.RowEffectiveDate <= m.MonthStart
    AND (
        b.RowExpirationDate > m.MonthStart
        OR b.RowExpirationDate IS NULL
        )
GROUP BY DATEPART(YEAR, m.MonthStart), DATEPART(MONTH, m.MonthStart), OrganizationName

UNION /* should this be UNION ALL ?? */

SELECT DATEPART(YEAR, m.MonthStart) AS Year, DATEPART(MONTH, m.MonthStart) AS Month, u.OrganizationName, COUNT(DISTINCT u.UnitID) AS [Vacant Count]
FROM MonthList m
LEFT JOIN curated.tblPWDimBuilding b ON b.Active = 'True'
    AND b.RowEffectiveDate <= m.MonthStart
    AND (
        b.RowExpirationDate > m.MonthStart
        OR b.RowExpirationDate IS NULL
        )
INNER JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
    AND u.STATUS = 'Vacant'
    AND u.RowEffectiveDate <= m.MonthStart
    AND (
        u.RowExpirationDate > m.MonthStart
        OR u.RowExpirationDate IS NULL
        )
GROUP BY DATEPART(YEAR, m.MonthStart), DATEPART(MONTH, m.MonthStart), u.OrganizationName;

обратите внимание, что без образцов данных или ожидаемого результата невозможно проверить этот запрос, но я думаю, что важная часть его заключается в том, чтобы: создать и использовать таблицу измерения даты или продолжить создание списка месяцев - затем присоединиться к вашему данные к этому, так как это обеспечивает необходимую логику «заглянуть вперед».

Я также не уверен, следует ли вам использовать UNION или UNION ALL - возможно, UNION неверен, поскольку он может подавлять строки, о которых следует сообщать, - но опять же, без данных трудно судить.

Спасибо за ваш вклад! Умное решение. В качестве вопроса, когда у вас есть внутреннее соединение, где RowEffectiveDate <= m.monthStart, не будут ли пропущены значения из текущего отчетного периода, если, скажем, они были вставлены и вступили в силу через пару дней после начала месяца? Возможно, было бы лучше сделать b.RowEffectiveDate <= DateAdd(MONTH,1, m.MonthStart), чтобы гарантировать включение строк в текущий отчетный период? Спасибо!

NerdyWithAByte 18.05.2023 19:59

Очень вероятно, что мое предложение нуждается в доработке, поэтому наличие реалистичных данных имеет значение. Теперь, когда это доступно, я скоро вернусь, чтобы рассмотреть это снова.

Paul Maxwell 18.05.2023 20:19

насколько я понял: dbfiddle.uk/0Vx39QYa Недопустимое имя столбца «Активно». и Недопустимое имя столбца «RowExpirationDate».

Paul Maxwell 19.05.2023 00:28

Кажется, что вам нужно отслеживать свободную недвижимость месяц за месяцем, учитывая как односемейные, так и многоквартирные свойства. Чтобы решить проблему свободных свойств, которые остаются свободными в следующем месяце, но могут не иметь соответствующей строки в базе данных, вы можете изменить свой запрос, включив подзапрос, который идентифицирует свойства, которые были свободными в предыдущем месяце.

Вот пример того, как вы можете изменить свой запрос, чтобы добиться этого:

WITH PreviousMonthVacant AS (
    SELECT
        DATEADD(MONTH, -1, RowEffectiveDate) AS PreviousMonth,
        CASE
            WHEN Status = 'Vacant' THEN BuildingId
            WHEN u.Status = 'Vacant' THEN u.BuildingId
            ELSE NULL
        END AS VacantBuildingId,
        CASE
            WHEN Status = 'Vacant' THEN NULL
            WHEN u.Status = 'Vacant' THEN u.UnitId
            ELSE NULL
        END AS VacantUnitId
    FROM curated.tblPWDimBuilding b
    LEFT JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
    WHERE
        b.Active = 'True'
        OR u.Active = 'True'
)
SELECT
    DATEPART(YEAR, RowEffectiveDate) AS Year,
    DATEPART(MONTH, RowEffectiveDate) AS Month,
    OrganizationName,
    COUNT(DISTINCT b.BuildingId) AS [Vacant Count]
FROM curated.tblPWDimBuilding b
LEFT JOIN curated.tblPWDimUnits u ON b.BuildingId = u.BuildingId
WHERE
    b.Active = 'True'
    OR u.Active = 'True'
    OR b.BuildingId IN (
        SELECT VacantBuildingId
        FROM PreviousMonthVacant
        WHERE VacantBuildingId IS NOT NULL
            AND DATEPART(YEAR, PreviousMonth) = DATEPART(YEAR, b.RowEffectiveDate)
            AND DATEPART(MONTH, PreviousMonth) = DATEPART(MONTH, b.RowEffectiveDate)
    )
GROUP BY
    DATEPART(YEAR, RowEffectiveDate),
    DATEPART(MONTH, RowEffectiveDate),
    OrganizationName

Я использовал общее табличное выражение (CTE) для определения свободных свойств в предыдущем месяце. CTE включает дату предыдущего месяца, идентификатор свободного здания и идентификатор свободной единицы.

Левое соединение выполняется для таблиц Building и Unit, чтобы включить в результат свободные свойства. При этом добавляется условие для включения свободных свойств за предыдущий месяц путем проверки наличия идентификатора здания в результатах подзапроса.

Включив эту логику, вы должны иметь возможность фиксировать свободные свойства месяц за месяцем, даже если они простаивали в предыдущем месяце и не имеют соответствующей строки в базе данных.

Спасибо за ваш ответ! Основная проблема с этим запросом заключается в том, что он не обрабатывает случаи, когда недвижимость свободна в течение x месяцев. Если это было 2, запрос выше, похоже, не обрабатывает этот случай.

NerdyWithAByte 18.05.2023 19:34

Другие вопросы по теме