Блог

Главная - Вопросы по программированию - Найти максимальный / минимальный произвольный приоритет

Найти максимальный / минимальный произвольный приоритет

#tsql #refactoring

#tsql #рефакторинг

Вопрос:

Я пытаюсь агрегировать строки на основе максимального или минимального значения, где max / min определяется бизнес-правилом, а не внутренним значением. Другими словами, мне нужно создать пользовательский max / min — без использования CLR. У меня есть работающий автономный пример во фрагменте ниже, но я чувствую, что должен быть более простой способ.

В этом конкретном случае мне нужно, чтобы бизнес-приоритет столбца IPA определялся значением [Seq], показанным в CTE [IPAMap] ниже. Затем я беру произвольный набор строк из таблицы [TestData] / cte (в этом примере всего четыре строки) и объединяю их в две строки, в которых выбраны правильные значения IPA на основе приоритета.

 --Table [IPAMap] holds all possible values for [IPA], and reflects the relative
--'priority' of each with the [Seq] column.  This table is used as a lookup for
--business rules.
;WITH IPAMap (Seq, IPA) AS ( 
SELECT 1, 'Q' UNION ALL 
SELECT 2, 'S' UNION ALL 
SELECT 3, 'A' UNION ALL 
SELECT 4, 'L'
) 
--Table [TestData] represents the real data. 
, TestData (CustomerID, IPA) AS ( 
SELECT '123', 'A' UNION ALL 
SELECT '123', 'S' UNION ALL 
SELECT '234', 'L' UNION ALL 
SELECT '234', 'Q' 
) 
--Table [PartyIPASeq] finds the lowest Seq rank from [TestData] 
,PartyIPASeq (CustomerID, IPASeq) AS ( 
 SELECT  
   CustomerID,  
   MIN(IP.Seq) 
 FROM TestData [TD] 
  INNER JOIN IPAMap [IP] ON IP.IPA = TD.IPA 
  GROUP BY CustomerID 
) 
--The final selection provides the key and the 'IPA' with the lowest rank 
SELECT TD.CustomerID, TD.IPA 
FROM TestData [TD] 
 INNER JOIN IPAMap [IP] ON IP.IPA = TD.IPA 
 INNER JOIN PartyIPASeq [SEQ] ON SEQ.CustomerID = [TD].CustomerID 
   AND SEQ.IPASeq = IP.Seq

Результатом приведенного выше запроса является следующее:

 CustomerID  IPA
234         S
123         Q

Это правильно, но я надеюсь, что кто-нибудь сможет показать мне более простой способ выполнить ту же работу.

Комментарии:

1. Ваш DISTINCT избыточен. Использование GROUP BY гарантирует, что строки в результирующем наборе будут разными.

2. Вы можете использовать операторы CASE внутри агрегатов, MIN(CASE MA.IPA, КОГДА 3, ЗАТЕМ 7, КОГДА 4, ЗАТЕМ 6 и т. Д., Но я сомневаюсь, что это намного проще или эффективнее, кроме того, что приводит к немного более простому плану выполнения. Если сопоставления не меняются, почему бы не поместить их в постоянную таблицу?

Ответ №1:

Похоже, ответ заключается в использовании оконной функции с упорядочением charindex, например:

 WITH TestData (CustomerID, IPA) AS ( 
    SELECT '123', 'A' UNION ALL 
    SELECT '123', 'S' UNION ALL 
    SELECT '234', 'L' UNION ALL 
    SELECT '234', 'Q' 
    ) 
SELECT CustomerID, IPA
FROM (
        SELECT CustomerID, 
        IPA, 
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CHARINDEX(IPA, 'QSAL')) rownum
        FROM TestData
    ) A
WHERE rownum = 1

Если бизнес-данные используют многосимвольные значения, то CHARINDEX заменяется функцией PATINDEX.

Метки: