Группа SQL по нескольким столбцам, имеющим более одного уникального значения для группировки столбцов

#sql-server

#sql-сервер

Вопрос:

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

 CREATE TABLE [dbo].[MyTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Type] [varchar](20) NOT NULL,
    [UnitOfMeasure] [varchar](20) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) ON [PRimary]
) ON [PRimary];

INSERT INTO [MyTable] (Type, UnitOfMeasure)
VALUES ('height', 'cm')
,   ('distance', 'km')
,   ('weight', 'kg')
,   ('Glucose', 'mg/dL')
,   ('weight', 'kg')
,   ('Duration', 'hours')
,   ('Glucose', 'mg/dL')
,   ('Glucose', 'mg/dL')
,   ('height', 'cm')
,   ('Allergy', 'kUnits/L')
,   ('Volume', 'mL')
,   ('height', 'inch')
,   ('height', 'cm')
,   ('Chloride', 'mmol/L')
,   ('Volume', 'cup')
,   ('distance', 'km')
,   ('Volume', 'cup')
,   ('Duration', 'hours')
,   ('Chloride', 'mmol/L')
,   ('Duration', 'minutes');

Желаемый вывод выглядит следующим образом.

 Type        UnitOfMeasure
Duration    hours
Duration    minutes
height      cm
height      inch
Volume      cup
Volume      mL

Этот вывод включает длительность, поскольку он имеет две единицы измерения. Однако он не включает ни вес, ни хлорид, поскольку он имеет только одну единицу измерения.

1. Кажется странным, что у вас есть повторяющиеся строки; вы рассматривали возможность исправления этого?

2. Чтобы упростить вопрос, я не включил другие столбцы, которые, по вашему мнению, выглядят дублирующимися. Цель запроса — идентифицировать типы, которые имеют конфликтующие единицы измерения, например, тип, который имеет единицы измерения cm и mL. таблица, с которой я работаю, содержит миллионы строк с более чем 10 000 типами. Просто нужно ограничить количество проверяемых типов. Предоставленный запрос привел к сокращению количества типов до чуть более 700.

Ответ №1:

Вы можете использовать CTE для получения a DISTINCT COUNT , а затем использовать an EXISTS с дальнейшим DISTINCT . Я ожидаю, что это будет немного дороже, и в идеале вы, вероятно, захотите обратиться к тем повторяющимся строкам, которые у вас есть.

 WITH Counts AS(
    SELECT [Type],
           COUNT(DISTINCT UnitOfMeasure) AS DistinctMeasures
    FROM dbo.MyTable
    GROUP BY [Type])
SELECT DISTINCT
       [Type],
       UnitOfMeasure
FROM dbo.MyTable MT
WHERE EXISTS (SELECT 1
              FROM Counts C
              WHERE C.[Type] = MT.[Type]
                AND C.DistinctMeasures > 1);

Ответ №2:

Вы можете сделать это с помощью EXISTS:

 SELECT DISTINCT t.[Type], t.[UnitOfMeasure]
FROM [MyTable] t
WHERE EXISTS (
  SELECT 1 FROM [MyTable]
  WHERE [Type] = t.[Type] AND [UnitOfMeasure] <> t.[UnitOfMeasure]
)

Смотрите демонстрацию.
Результаты:

 > Type     | UnitOfMeasure
> :------- | :------------
> Duration | hours        
> Duration | minutes      
> height   | cm           
> height   | inch         
> Volume   | cup          
> Volume   | mL

Ответ №3:

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

 select distinct type, unitofmeasure
from (
    select t.*,
        min(unitofmeasure) over(partition by type) min_unit,
        max(unitofmeasure) over(partition by type) max_unit
    from mytable t
) t
where min_unit <> max_unit