Выберите самое последнее значение на основе другого поля в объединенной таблице

ProgramBox

Выберите самое последнее значение на основе другого поля в объединенной таблице

Post author:admin
Запись опубликована:13 февраля, 2023
Post category:Вопросы по программированию

#sql #sql-server

#sql #sql-сервер

Вопрос:

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

 SELECT PPAV.BusinessID
                        , (cast(year(PPAV.PartnerAttributeValueStartDate) as char(4))   '0'   cast(datepart(qq, PPAV.PartnerAttributeValueStartDate) as char(1))) AS Quarter
                        , PAV.PartnerAttributeValue
                    FROM Partner_PartnerAttributeValue PPAV
                    JOIN PartnerAttributeValue PAV
                        ON PAV.PartnerAttributeValueID = PPAV.PartnerAttributeValueID
                    WHERE PAV.PartnerAttributeID = 7
                        AND (PPAV.PartnerAttributeValueID = 22 OR PPAV.PartnerAttributeValueID = 795 OR PPAV.PartnerAttributeValueID = 796)

                    GROUP BY PPAV.BusinessID
                            , (cast(year(PPAV.PartnerAttributeValueStartDate) as char(4))   '0'   cast(datepart(qq, PPAV.PartnerAttributeValueStartDate) as char(1)))
                            , PAV.PartnerAttributeValue

Это код, в котором возникла проблема. Мне нужно только одно значение за квартал. Иногда происходит изменение в середине квартала, и информация дублируется. Когда я пытался исправить это, я использовал этот код, и это фактически усугубило проблему, заставив проблемный квартал иметь 4 значения.

 SELECT PPAV.BusinessID
                        , (cast(year(PPAV.PartnerAttributeValueStartDate) as char(4))   '0'   cast(datepart(qq, PPAV.PartnerAttributeValueStartDate) as char(1))) AS Quarter
                        , CASE WHEN (cast(year(PPAV.PartnerAttributeValueStartDate) as char(4))   '0'   cast(datepart(qq, PPAV.PartnerAttributeValueStartDate) as char(1))) = (cast(year(PPAV.PartnerAttributeValueStartDate) as char(4))   '0'   cast(datepart(qq, PPAV.PartnerAttributeValueStartDate) as char(1))) 
                            THEN SubHist.PartnerAttributeValue
                            ELSE PAV.PartnerAttributeValue 
                            END AS PartnerAttributeValue
                FROM Partner_PartnerAttributeValue PPAV
                JOIN PartnerAttributeValue PAV
                    ON PAV.PartnerAttributeValueID = PPAV.PartnerAttributeValueID
                JOIN ( SELECT PPAV.BusinessID
                                    , MAX(PPAV.PartnerAttributeValueStartDate) AS MAX
                                    , PAV.PartnerAttributeValue
                                FROM Partner_PartnerAttributeValue PPAV
                                JOIN PartnerAttributeValue PAV
                                    ON PAV.PartnerAttributeValueID = PPAV.PartnerAttributeValueID
                                WHERE PAV.PartnerAttributeID = 7
                                AND (PPAV.PartnerAttributeValueID = 22 OR PPAV.PartnerAttributeValueID = 795 OR PPAV.PartnerAttributeValueID = 796)
                                GROUP BY PAV.PartnerAttributeValue
                                        ,PPAV.BusinessID
                        )SubHist
                    ON SubHist.BusinessID = PPAV.BusinessID
                WHERE PAV.PartnerAttributeID = 7
                    AND (PPAV.PartnerAttributeValueID = 22 OR PPAV.PartnerAttributeValueID = 795 OR PPAV.PartnerAttributeValueID = 796)
                GROUP BY PPAV.BusinessID
                        , (cast(year(PPAV.PartnerAttributeValueStartDate) as char(4))   '0'   cast(datepart(qq, PPAV.PartnerAttributeValueStartDate) as char(1)))
                        , PAV.PartnerAttributeValue
                        , SubHist.PartnerAttributeValue

Я очень не уверен в том, что я сделал, чтобы усугубить проблему. Я полагал, что мой СЛУЧАЙ, когда оператор, выходящий из дополнительной объединенной таблицы, исправит это.

Любая помощь приветствуется!

Ниже приведены некоторые примеры данных, которые я пытаюсь исключить

 4356    201501  REGISTERED
4356    201502  REGISTERED
4356    201503  REGISTERED
4356    201504  REGISTERED
4356    201601  GOLD
4356    201601  REGISTERED
4356    201602  REGISTERED
4356    201603  REGISTERED
4356    201604  REGISTERED

Проблема в том, что 1 квартал 2016 года имеет несколько значений, из-за этого данные искажаются. Должно быть только значение GOLD, а не GOLD и Registered

Спасибо!

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

2. Похоже, вы могли бы использовать оконную функцию, например, row_number() over (partition by BusinessID, Quarter, order by PartnerAttributeValueStartDate desc) RN обернуть ее в subselct или cte, а затем добавить Where RN = 1

3. Я не уверен, что сумма сработает. Это всего лишь небольшое подмножество более крупного запроса. Значение является символьным значением, поэтому мне нужно символьное значение для максимальной даты, когда четверти равны. Этот запрос является лишь частью более крупного CTE, который использует трюк ROW_number, производительность более крупной функции уже довольно низкая: (

4. где в вашем поле даты указано, что строка с ЗОЛОТОМ позже, чем строка с ЗАРЕГИСТРИРОВАННЫМ для того же идентификатора и квартала? Это PartnerAttributeValueStartDate?

5. все, что вам может понадобиться, это добавить условие к вашему объединению — где у вас есть ON SubHist.BusinessID = PPAV.BusinessID add AND startDate = max

Ответ №1:

Используйте оконную функцию для генерации номера строки для каждого квартала и бизнес-идентификатора. Затем ограничьтесь только номером 1-й строки (RN) каждой группы…

Поскольку RN должен быть сгенерирован, прежде чем мы сможем ограничить его, мы оборачиваем его в CTE или подзапрос, а затем применяем RN =1…

Я также:

Переключил ваши операторы OR на IN для удобства чтения и, возможно, производительности.
Изменен расчет квартала, чтобы использовать concat вместо агрегирование строк. (полагаясь на неявное преобразование, которое должно быть в порядке, если обработка действительной даты)

любое из этих дополнительных изменений могло также привести к синтаксическим ошибкам.

НЕПРОВЕРЕННЫЙ Если бы структура таблицы для приведенных ниже и примеров данных была предоставлена в скрипке SQL, я бы ее протестировал.

 Select * from (

    SELECT PPAV.BusinessID
         , concat(year(PPAV.PartnerAttributeValueStartDate)
                  , '0'
                  ,datepart(qq, PPAV.PartnerAttributeValueStartDate)
                 )
          AS Quarter
         , PAV.PartnerAttributeValue
         , row_number() 
           Over (PARTITION BY PPAV.BusinessID
               , year(PPAV.PartnerAttributeValueStartDate)
               , datepart(qq, PPAV.PartnerAttributeValueStartDate))
                 ORDER BY PartnerAttributeValueStartDate DESC) RN
     FROM Partner_PartnerAttributeValue PPAV
     JOIN PartnerAttributeValue PAV
       ON PAV.PartnerAttributeValueID = PPAV.PartnerAttributeValueID
     WHERE PAV.PartnerAttributeID = 7
       AND PPAV.PartnerAttributeValueID IN (22, 795,796)
     GROUP BY PPAV.BusinessID
           , concat(year(PPAV.PartnerAttributeValueStartDate)
                  , '0'
                  ,datepart(qq, PPAV.PartnerAttributeValueStartDate)
                 )
           , PAV.PartnerAttributeValue) cte

from cte where RN = 1

1. Я бы, вероятно, просто разделил на части за год и квартал, не объединяя их вместе. Есть ли преимущество в том, чтобы делать это таким образом? Та же история с group by , верно?

2. Бесполезно… и я, вероятно, должен отсортировать по дате начала desc вместо того, чтобы разрешать asc по умолчанию…

3. Я не думаю, что смогу запустить CTE в рамках подзапроса. Я должен был быть более конкретным с учетом того факта, что этот запрос отделен от объединения из гораздо большего запроса

4. вы можете сделать это как подзапрос … не обязательно должен быть cte .. трюк с rn на самом деле не так уж и сложен. оконные функции работают ОЧЕНЬ эффективно.