[SQL] Как вычленить значение из группы на основании условия по другому столбцу?

Question

[Shedal]

CREATE TABLE Items
(
  ItemId INT NOT NULL,
  CategoryId INT NOT NULL,
  ItemValue INT NOT NULL
)

В таблице содержатся item'ы. У каждого item'а есть категория (CategoryId), а также, какое-то значение (ItemValue).


Нужно написать запрос, результат которого будет сгруппирован по категории и будет содержать следующие столбцы:

• id категории;
  
• id item'а с наименьшим ItemValue в категории;
  
• id item'а с наибольшим ItemValue в категории.
  

Производительность важна.

Answer 1

[tonyzorin]

select CategoryId,max(id_min_value),max(id_max_value)
from 
(select CategoryId,
case when ItemValue=min(ItemValue) over (partition by CategoryId) then ItemId end as id_min_value,
case when ItemValue=max(ItemValue) over (partition by CategoryId) then ItemId end as id_max_value 
from Items) Items
group by CategoryId

Answer 2

[4dmonster]

SELECT     CatsMMV.CategoryId, CatsMMV.MinV, CatsMMV.MaxV, MinI.ItemId AS MinItemID, MaxI.ItemId AS MaxItemID
FROM         (SELECT     CategoryId, MIN(ItemValue) AS MinV, MAX(ItemValue) AS MaxV
                       FROM          Items
                       GROUP BY CategoryId) AS CatsMMV LEFT OUTER JOIN
                      Items AS MaxI ON CatsMMV.CategoryId = MaxI.CategoryId AND CatsMMV.MaxV = MaxI.ItemValue LEFT OUTER JOIN
                      Items AS MinI ON CatsMMV.CategoryId = MinI.CategoryId AND CatsMMV.MinV = MinI.ItemValue

Comments

[4dmonster]

* OUTER JOIN на INNER JOIN ещё поменять

[Shedal]

Спасибо, плюсанул!
Решение работает, но по производительности несоклько хуже, чем те, что ниже.

Answer 3

[HiltoN]

Ну, например, так:

select * from Items a
where not exists (
    select 1 from Items b
     where b.CategoryId = a.CategoryId
       and b.ItemId <> a.ItemId
       and ((b.ItemValue > a.ItemValue) or 
            (b.ItemValue = a.ItemValue 
         and b.ItemId > a.ItemId)));

Comments

[Shedal]

Эммм… А где здесь максимальный И минимальный айтемы?

[HiltoN]

да, недочитал )
Тогда встречный вопрос — версия SQL Server?

[Shedal]

2008R2

Answer 4

[Shedal]

Для полноты картины, вот ещё одно решение, похожее на решение tonyzorin:

WITH CTE AS
(
  SELECT
    *, 
    ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue DESC) MaxRN,
    ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue ASC) MinRN
  FROM Items
)
SELECT
  CategoryId, 
  MIN(CASE WHEN MaxRN = 1 THEN ItemId END) IdMaxValue,
  MIN(CASE WHEN MinRN = 1 THEN ItemId END) IdMinValue
FROM CTE
GROUP BY CategoryId

Comments

[DIegoR]

я бы его так переписал, чтобы было ясно, что только один проход таблицы будет:

SELECT
  CategoryId, 
  MAX(CASE WHEN MaxRN = 1 THEN ItemId ELSE NULL END) IdMaxValue,
  MAX(CASE WHEN MinRN = 1 THEN ItemId ELSE NULL END) IdMinValue
(
  SELECT
    *, 
    ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue DESC) MaxRN,
    ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue ASC) MinRN
  FROM Items
) as DerivedTable
WHERE MaxRN = 1 or MinRN = 1
GROUP BY CategoryId
</code>

[DIegoR]

«FROM» забыл

SELECT
  CategoryId, 
  MAX(CASE WHEN MaxRN = 1 THEN ItemId ELSE NULL END) IdMaxValue,
  MAX(CASE WHEN MinRN = 1 THEN ItemId ELSE NULL END) IdMinValue
FROM
(
  SELECT
    *, 
    ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue DESC) MaxRN,
    ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY ItemValue ASC) MinRN
  FROM Items
) as DerivedTable
WHERE MaxRN = 1 or MinRN = 1
GROUP BY CategoryId