Блог

Я пытаюсь создать запрос в MS SQL Server 2012, который дает мне count , average и некоторые sum значения различных записей в таблице базы данных. Я постараюсь объяснить свою ситуацию и свои пожелания как можно лучше. Если что-то остается неясным или требуется дополнительная информация, пожалуйста, дайте мне знать.

Наличие следующей таблицы TEMP с 10 записями:

ТАБЛИЦА

 ╔════════╦═════════════╦════════╦═══════════╗
║ Number ║ DateOfBirth ║ Gender ║ Activity  ║
╠════════╬═════════════╬════════╬═══════════╣
║ 191806 ║ 1940-08-31  ║ F      ║ AMADMIN   ║
║ 196484 ║ 1940-09-23  ║ F      ║ AMHOST    ║
║ 199480 ║ 1949-10-16  ║ F      ║ AMTRAINER ║
║ 201089 ║ 1947-04-08  ║ M      ║ AMTRAINER ║
║ 204528 ║ 1950-05-02  ║ F      ║ AMHOST    ║
║ 226356 ║ 1966-04-12  ║ M      ║ AMADMIN   ║
║ 226356 ║ 1966-04-12  ║ M      ║ AMHOST    ║
║ 377599 ║ 1985-05-15  ║ F      ║ AMADMIN   ║
║ 377599 ║ 1985-05-15  ║ F      ║ AMHOST    ║
║ 395809 ║ 1980-03-03  ║ F      ║ AMADMIN   ║
╚════════╩═════════════╩════════╩═══════════╝
 

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

SQL

   SELECT COUNT([Number]) AS Number, ROUND(AVG(CAST(DATEDIFF(DAY, [DateOfBirth], GETDATE()) / 365.2425 AS FLOAT)), 1) AS AverageAge,
    SUM(CASE WHEN [Gender] = 'M' THEN 1 ELSE 0 END) AS Male,
    SUM(CASE WHEN [Gender] = 'F' THEN 1 ELSE 0 END) AS Female
  FROM [TEMP]
  WHERE [Activity] IN ('AMHOST', 'AMADMIN', 'AMTRAINER')
 

Этот запрос даст мне следующий результат:

Результат

 ╔════════╦════════════╦══════╦════════╗
║ Number ║ AverageAge ║ Male ║ Female ║
╠════════╬════════════╬══════╬════════╣
║     10 ║ 57,3       ║    3 ║      7 ║
╚════════╩════════════╩══════╩════════╝
 

Пока все хорошо! Но теперь самое сложное. Чего я действительно хочу, так это этого результата для всех distinct записей в таблице. Это означает вычисление среднего возраста и количества мужчин / женщин для всех лиц за вычетом двух «двойных» лиц (имеющих Number 226356 и 377599 ). Итак, мне нужен запрос, который выдает следующий результат:

ЖЕЛАЕМЫЙ РЕЗУЛЬТАТ

 ╔════════╦════════════╦══════╦════════╗
║ Number ║ AverageAge ║ Male ║ Female ║
╠════════╬════════════╬══════╬════════╣
║      8 ║ 56,9       ║    2 ║      6 ║
╚════════╩════════════╩══════╩════════╝
 

Я знаю, как получить distinct записи для одной части запроса следующим образом:

SQL

   SELECT COUNT(DISTINCT([Number])) AS Number, ROUND(AVG(CAST(DATEDIFF(DAY, [DateOfBirth], GETDATE()) / 365.2425 AS FLOAT)), 1) AS AverageAge,
    SUM(CASE WHEN [Gender] = 'M' THEN 1 ELSE 0 END) AS Male,
    SUM(CASE WHEN [Gender] = 'F' THEN 1 ELSE 0 END) AS Female
  FROM [TEMP]
  WHERE [Activity] IN ('AMHOST', 'AMADMIN', 'AMTRAINER')
 

Но это приводит к:

Результат

 ╔════════╦════════════╦══════╦════════╗
║ Number ║ AverageAge ║ Male ║ Female ║
╠════════╬════════════╬══════╬════════╣
║      8 ║ 57,3       ║    3 ║      7 ║
╚════════╩════════════╩══════╩════════╝
 

Теперь Number подсчет хороший, но значения AverageAge Male и Female неправильные.

Мой вопрос в том, как я могу настроить свой запрос таким образом, чтобы я получал значения, указанные в ТРЕБУЕМОМ наборе РЕЗУЛЬТАТОВ, если такой запрос вообще возможен для начала?

Поскольку activity не отображается ни в одной из агрегатных функций, вы можете просто исключить это из результатов и использовать подзапрос для получения отдельных записей перед агрегированием, а затем также применить COUNT(DISTINCT CASE.. к вашим подсчетам мужского / женского пола:

 SELECT  COUNT(DISTINCT [Number]) AS Number, 
        ROUND(AVG(CAST(DATEDIFF(DAY, [DateOfBirth], GETDATE()) / 365.2425 AS FLOAT)), 1) AS AverageAge,
        COUNT(DISTINCT CASE WHEN [Gender] = 'M' THEN [Number] END) AS Male,
        COUNT(DISTINCT CASE WHEN [Gender] = 'F' THEN [Number] END) AS Female
FROM    (   SELECT  DISTINCT Number, DateOfBirth, Gender
            FROM    [sw_test].[dbo].[TEMP]
            WHERE   [Activity] IN ('AMHOST', 'AMADMIN', 'AMTRAINER')
        ) AS t;
 

Пример на SQL Fiddle

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

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

 SELECT COUNT(DISTINCT(t.Number)) AS Number, ROUND(AVG(CAST(DATEDIFF(DAY, t.DateOfBirth, GETDATE()) / 365.2425 AS FLOAT)), 1) AS AverageAge,
    SUM(CASE WHEN t.Gender = 'M' THEN 1 ELSE 0 END) AS Male,
    SUM(CASE WHEN t.Gender = 'F' THEN 1 ELSE 0 END) AS Female
From
(   Select t.number, t.DateOfBirth, t.Gender
    From temp t
    Where activity in ('AMHOST', 'AMADMIN', 'AMTRAINER')
    Group by t.number, t.DateOfBirth, t.Gender) t
 

Этот запрос работает. Сделал подвыборку, чтобы получить базовый набор.

 SELECT COUNT([Number]) AS Number, ROUND(AVG(CAST(DATEDIFF(DAY, [DateOfBirth], GETDATE()) / 365.2425 AS FLOAT)), 1) AS AverageAge,
SUM(CASE WHEN [Gender] = 'M' THEN 1 ELSE 0 END) AS Male,
SUM(CASE WHEN [Gender] = 'F' THEN 1 ELSE 0 END) AS Female
FROM 
  (SELECT DISTINCT Number, DateOfBirth, Gender 
   FROM temp where [Activity] IN ('AMHOST', 'AMADMIN', 'AMTRAINER')) a