При анализе данных в SQL Server часто необходимо представить результаты в формате, удобном для анализа и презентации. В этой статье мы рассмотрим, как представить данные из таблиц EMPLOYEES и ORDERS в формате кросс-таблицы с использованием оператора CASE.
Предположим, у нас есть аналитик данных, который хочет узнать, сколько заказов было размещено каждым сотрудником в каждый год работы. Простой запрос GROUP BY может дать ответ:
SELECT E.LastName, E.FirstName, 'Year' = DATEPART(year, OrderDate), 'OrdersPlaced' = COUNT(*)
FROM EMPLOYEES E, ORDERS O
WHERE E.EmployeeID = O.EmployeeID
GROUP BY E.LastName, E.FirstName, DATEPART(year, OrderDate)
Этот запрос даст нам набор результатов, в котором перечислены фамилия, имя, год и количество размещенных заказов для каждого сотрудника:
| Фамилия | Имя | Год | Размещенные заказы |
|---|---|---|---|
| Бьюкенен | Стивен | 1996 | 11 |
| Бьюкенен | Стивен | 1997 | 18 |
| Бьюкенен | Стивен | 1998 | 13 |
| Каллахан | Лаура | 1996 | 19 |
| Каллахан | Лаура | 1997 | 54 |
| Каллахан | Лаура | 1998 | 31 |
| … | … | … | … |
Хотя этот набор результатов является правильным и предоставляет необходимую информацию, он может быть неудобным для анализа и не может быть непосредственно вставлен в документ презентации. Чтобы представить данные более естественным и удобным способом, мы можем использовать оператор CASE для имитации запроса кросс-таблицы.
Оператор CASE позволяет нам проверить, попадает ли запись под указанное условие и присвоить значение 1 (истина) или 0 (ложь) на основе этого условия. Используя оператор SUM, мы можем затем рассчитать общее количество размещенных заказов для каждого сотрудника, группируя результаты по их фамилии и имени:
SELECT E.LastName, E.FirstName,
'1996' = SUM(CASE WHEN DATEPART(year, OrderDate) = 1996 THEN 1 ELSE 0 END),
'1997' = SUM(CASE WHEN DATEPART(year, OrderDate) = 1997 THEN 1 ELSE 0 END),
'1998' = SUM(CASE WHEN DATEPART(year, OrderDate) = 1998 THEN 1 ELSE 0 END)
FROM EMPLOYEES E, ORDERS O
WHERE E.EmployeeID = O.EmployeeID
GROUP BY E.LastName, E.FirstName
Результатом этого запроса будет формат кросс-таблицы, в котором перечислены фамилия, имя и количество заказов, размещенных каждым сотрудником для каждого года:
| Фамилия | Имя | 1996 | 1997 | 1998 |
|---|---|---|---|---|
| Фуллер | Андрей | 16 | 41 | 39 |
| Додсворт | Энн | 5 | 19 | 19 |
| Леверлинг | Джанет | 18 | 71 | 38 |
| Каллахан | Лаура | 19 | 54 | 31 |
| Пикок | Маргарет | 31 | 81 | 44 |
| Суяма | Майкл | 15 | 33 | 19 |
| Даволио | Нэнси | 26 | 55 | 42 |
| Кинг | Роберт | 11 | 36 | 25 |
| Бьюкенен | Стивен | 11 | 18 | 13 |
| Фуллер | Андрей | 16 | 41 | 39 |
| Додсворт | Энн | 5 | 19 | 19 |
Этот формат кросс-таблицы более естественный и удобный для анализа и может быть легко скопирован и вставлен в документ презентации.
Если у вас есть вопросы или вы хотите предложить свои комментарии по улучшению этого метода, пожалуйста, не стесняйтесь связаться со мной по адресу msmirnov@swynk.com.