Пивот

Кирилл Евсеев, April 19, 2011

Всем привет. Сегодня мы коснёмся одной очень интересной темы, которая очень слабо освещена в рунете. Тема эта больше теоретическая, и, скорее всего, в обычных проектах вы с этим не столкнётесь никогда, но уж если столкнётесь и не будете знать, что перед вами, пара-тройка бессонных ночей вам гарантированна. С другой стороны, если перед вами возникнет задача, где можно использовать пивот, вы сможете резко повысить производительность вашей системы. А для веб производительность всегда была критична. Попробуем же потратить пол часа сейчас и отлично выспаться потом, когда-нибудь в будущем 🙂

Итак, пивот. Что это такое и с чем его едят? Скажу сразу, к пиву это не имеет никакого отношения. А имеет отношения к реляционным базам данных, например, к MySQL. Пивот – это разворот таблицы. Или таблица разворота. Вот видите, это уже, практически, пивот. Придумали эту штуку два оч. умных дядьки – Бил Джелен и Майк Александер. Подробнее об истории пивота можно посмотреть в английской википедии (http://en.wikipedia.org/wiki/Pivot_table).

Приведём пример, чтобы понять, что такое пивот, т.к. без примера и на словах понять это практически невозможно (если Эйнштейн не был вашим дедушкой, а Тьюринг – братом, конечно).

Пусть есть исходная MySQL таблица, которая хранит результаты экзаменов –

CREATE TABLE exams (
pkey int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
score int,
PRIMARY KEY  (pkey)
);

insert into exams (name,exam,score) values ('Bob',1,75);
insert into exams (name,exam,score) values ('Bob',2,77);
insert into exams (name,exam,score) values ('Bob',3,78);
insert into exams (name,exam,score) values ('Bob',4,80);

insert into exams (name,exam,score) values ('Sue',1,90);
insert into exams (name,exam,score) values ('Sue',2,97);
insert into exams (name,exam,score) values ('Sue',3,98);
insert into exams (name,exam,score) values ('Sue',4,99);

mysql> select * from exams;
+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
|    1 | Bob  |    1 |    75 |
|    2 | Bob  |    2 |    77 |
|    3 | Bob  |    3 |    78 |
|    4 | Bob  |    4 |    80 |
|    5 | Sue  |    1 |    90 |
|    6 | Sue  |    2 |    97 |
|    7 | Sue  |    3 |    98 |
|    8 | Sue  |    4 |    99 |
+------+------+------+-------+
8 rows in set (0.00 sec)

Это обычная плоская таблица MySQL. Автоинкрементный первичный ключ нас вообще не интересует. В остальных колонках хранится номер экзамена и количество баллов, которые студент по имени name умудрился набрать (судя по всему, Sue вообще никогда не прогуливает). Понятно, что exam может быть в свою очередь внешним ключом для ссылки на другую таблицу с названием экзаменов, с экзаменаторами и т.д. Нам это не интересно.

При пивоте по экзамену результирующая таблица будет выглядеть так –

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

Сразу видно, насколько эта таблица полезнее. Например, если эти данные вернуть в PHP приложение, то обладая тем же набором данных мы сделаем всего лишь две итерации цикла для выборки всех данных из таблицы. Для первой плоской таблицы нам бы пришлось гонять цикл while все восемь раз. Согласитесь – прирост производительности в 4 раза – серьёзный аргумент за то, чтобы задуматься об использовании такого приёма. Как же получить такую пивот-таблицу? Для начала рассмотрим нематематический способ. Он ничем не хуже математического, но изначально пивот был изобретён для осуществления каких-то более осмысленных действий над данными, чем простая выборка, например – вычисление средних значений, вычисление разниц, использование каких-нибудь статистических функций с выбранными данными и т.п.

Итак, нематематическое решение выглядит так –

mysql> SELECT name,
SUM(IF(exam=1,score,NULL)) AS exam1,
SUM(IF(exam=2,score,NULL)) AS exam2,
SUM(IF(exam=3,score,NULL)) AS exam3,
SUM(IF(exam=4,score,0)) AS exam4
FROM exams GROUP BY name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

Заметьте, что без использования функции SUM результат будет ошибочным –

mysql> select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |  NULL |  NULL |  NULL |
| Sue  |    90 |  NULL |  NULL |  NULL |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

Почему так происходит? Интуитивно понятно, что запрос должен делать. Но почему он этого не делает? Чтобы ответить на этот вопрос, немного укоротим наш запрос, а именно – на группировку. Смотрим –

mysql> select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |  NULL |  NULL |  NULL |
| Bob  |  NULL |    77 |  NULL |  NULL |
| Bob  |  NULL |  NULL |    78 |  NULL |
| Bob  |  NULL |  NULL |  NULL |    80 |
| Sue  |    90 |  NULL |  NULL |  NULL |
| Sue  |  NULL |    97 |  NULL |  NULL |
| Sue  |  NULL |  NULL |    98 |  NULL |
| Sue  |  NULL |  NULL |  NULL |    99 |
+------+-------+-------+-------+-------+
8 rows in set (0.00 sec)

Сначала MySQL создаёт таблицу из 5ти колонок – name, exam1, exam2, exam3, exam4. Затем она вычисляет эту таблицу по 4 раза для каждого имени и заполняет результаты. На первом проходе всё ок. На втором проходе первое значение вычисляется как NULL благодаря оператору if, а другое – верное, третье и четвёртое – тоже NULL. Ну и т.д. По сути, группируя этот запрос по имени, мы разбиваем каждые 4 значения на две группы – группу Bob и группу Sue и возвращаем первое вхождение. Остальные результаты теряются.

Окей, это понятно. Но почему результаты не теряются в случае с SUM? Ответ прост – функция SUM суммирует элементы группы. Таким образом, на первом проходе SUM суммирует результаты первой строки и присваивает их exam1, на втором проходе SUM суммирует результаты второй строки и присваивает их exam2 и т.д. Если бы функцию SUM можно было бы использовать без группировки, то результат получился бы примерно таким –

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Bob  |    75 |    77 |    78 |    80 |
| Bob  |    75 |    77 |    78 |    80 |
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
| Sue  |    90 |    97 |    98 |    99 |
| Sue  |    90 |    97 |    98 |    99 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
8 rows in set (0.00 sec)

Но эта операция запрещена. А с группировкой по именам получается правильное значение и пивот-таблица –

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

Если нам нужно только это, то нематематический подход вполне оправдан. Однако, если нам нужно именно то, ради чего был придуман пивот, – оперировать с данными – такого подхода будет мало. Рассмотрим математику. А именно, функцию сигнум. Сигнум sign(x) – это функция, которая принимает значение 0, если x = 0; 1, если x>0; -1, если x<0. Надо отметить, что sign(x) – это обозначение, принятое в MySQL. Математическое обозначения сигнума выглядит так – sgn(x). Кроме того, есть ещё функция abs(x), которая возвращает модуль x. Модуль x = x, если x>=0, и модуль -x = x, если x<=0.

Учитывая всё вышесказанное, вызов в MySQL abs(sign(x)) вернёт 0, если x = 0 и 1 во всех остальных случаях. Соответственно, 1 – abs(sign(x)) вернёт 1, только если x = 0.

Отталкиваться будем от очевидного –
sign(-1) = -1,  abs( sign(-1) ) = 1,  1-abs( sign(-1) ) = 0

Этот запрос

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;

Вернёт аналогичную таблицу

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

И вы поймёте почему, если внимательно просчитаете каждые значения, которые возвращают функции. Заметьте – мы сделали это всего лишь одним запросом. Конечно, на Бобе и Сью прироста в производительности не видно, но если бы вы обрабатывали данные для тысяч студентов крупного ВУЗа, каждый из которых во время сессии сдаёт по 20 экзаменов, то использование пивота здорово разгрузило бы память вашего веб-сервера и заметно ускорило бы работу веб-приложения. А именно, ровно во столько раз, сколько экзаменов нужно обработать. Вам нравится цифра – прирост производительности в 20 раз? Только на правильно сформированном SQL-запросе!

Теперь попробуем пооперировать с данными, – собственно, то, ради чего и предназначен пивот.

Вычисляем разницу в баллах между экзаменами:

mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,

sum(score*(1-abs(sign(exam- 2)))) -
sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,

sum(score*(1-abs(sign(exam- 3)))) -
sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,

sum(score*(1-abs(sign(exam- 4)))) -
sum(score*(1-abs(sign(exam- 3)))) as delta_3_4

from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

+-----------+-----------+-----------+
| delta_1_2 | delta_2_3 | delta_3_4 |
+-----------+-----------+-----------+
|         2 |         1 |         2 |
|         7 |         1 |         1 |
+-----------+-----------+-----------+
2 rows in set (0.00 sec)

(Результат будет в одной таблице. Это я просто разбиваю, чтобы сохранить форматирование. )

Как мы видим, дельта всё время положительная, т.к. оба студента от экзамена к экзамену улучшали свои результаты.

Можно посчитать, насколько оба студента повысили свои результаты. Заметьте – это вычисление по строке, а не по колонке. Если бы мы сравнивали колонки, то это было бы тривиально и решалось бы просто обычным SQL языком. Со строками всё гораздо сложнее и без пивота не обойтись. Смотрим.

mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,

sum(score*(1-abs(sign(exam- 2)))) -
sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,

sum(score*(1-abs(sign(exam- 3)))) -
sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,

sum(score*(1-abs(sign(exam- 4)))) -
sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,

sum(score*(1-abs(sign(exam- 2)))) -
sum(score*(1-abs(sign(exam- 1))))  +

sum(score*(1-abs(sign(exam- 3)))) -
sum(score*(1-abs(sign(exam- 2))))  +

sum(score*(1-abs(sign(exam- 4)))) -
sum(score*(1-abs(sign(exam- 3))))  as TotalIncPoints

from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

+-----------+-----------+-----------+----------------+
| delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints |
+-----------+-----------+-----------+----------------+
|         2 |         1 |         2 |              5 |
|         7 |         1 |         1 |              9 |
+-----------+-----------+-----------+----------------+
2 rows in set (0.00 sec)

Теперь посчитаем средний бал:

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,

sum(score*(1-abs(sign(exam- 2)))) -
sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,

sum(score*(1-abs(sign(exam- 3)))) -
sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,

sum(score*(1-abs(sign(exam- 4)))) -
sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,

sum(score*(1-abs(sign(exam- 2)))) -
sum(score*(1-abs(sign(exam- 1))))  +

sum(score*(1-abs(sign(exam- 3)))) -
sum(score*(1-abs(sign(exam- 2))))  +

sum(score*(1-abs(sign(exam- 4)))) -
sum(score*(1-abs(sign(exam- 3))))  as TotalIncPoints,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

+-----------+-----------+-----------+----------------+-------+
| delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints | AVG   |
+-----------+-----------+-----------+----------------+-------+
|         2 |         1 |         2 |              5 | 77.50 |
|         7 |         1 |         1 |              9 | 96.00 |
+-----------+-----------+-----------+----------------+-------+
2 rows in set (0.00 sec)

Значения можно комбинировать как угодно. Например, так –

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))))/2  as AVG1_2,

(sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))))/2 as AVG2_3,

(sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/2 as AVG3_4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

+--------+--------+--------+-------+
| AVG1_2 | AVG2_3 | AVG3_4 | AVG   |
+--------+--------+--------+-------+
|  76.00 |  77.50 |  79.00 | 77.50 |
|  93.50 |  97.50 |  98.50 | 96.00 |
+--------+--------+--------+-------+
2 rows in set (0.00 sec)

Мы не используем ни хранимые процедуры, ни вложенные запросы, ни объединения или джоины. Мы решаем эту задачу одним запросом, который, к тому же, возвращает в 4 раза меньше результатов, чем если бы мы решили оставить вычисления PHP скрипту.

Надеюсь, вам было интересно 🙂

Всем удачи.

(при подготовке статьи использовались материалы, расположенные по адресу http://en.wikibooks.org/wiki/MySQL/Pivot_table)

В тему:

9комментариев

Очень полезная инфа!
Спасибо!

_bob_, September 6, 2011 10:17 pm Reply

А вот что делать в MySQL если количество экзаменов заранее не определено (может быть 4 а может и 14)? т.е. к примеру если бы мы переворачивали таблицу по столбцу со временем

Spyke, September 15, 2011 3:24 pm Reply

2 _bob_: благодарю 🙂 посещайте блог Easy4Web как можно чаще ) тут много всего интересного.

2 Spyke: благодарю за вопрос. подумаю и поиграюсь с запросом на выходных. сходу могу предложить использовать хранимые процедуры и конструкцию
WHILE условие DO
действие;
END WHILE;

для определения точного количества колонок, по которым нужен пивот.

кирилл, September 16, 2011 1:03 pm Reply

Шикарнейшая статья, просто великолепно!!! Я в восторге, это то что мне очень помогло. Большое такое человеческое спасибо.

andrace, October 4, 2011 8:41 pm Reply

Всегда рады быть полезными.

Вячеслав Гринин, October 5, 2011 8:22 am Reply

Спасибо, как раз то, что когда-то искал, но так и не нашел ответа – теперь все стало ясно. а как можно произвести обратный PIVOT , желательно пример.

Михаил, October 17, 2011 1:59 am Reply

или я не очень понял вопрос, или это не имеет смысла. зачем нужен обратный пивот, если можно использовать исходные данные?

кирилл, October 17, 2011 7:05 pm Reply

может кому пригодится:

если надо разворачивать данные произвольного формата (не только INT но и VARCHAR) то тогда использовать агрегативную функцию MAX вместо SUM

ну и в таблице поле score тоже должно быть VARCHAR

Дверник Алексей, July 16, 2013 9:42 pm Reply

Для меня, как человеку не имеющего опыта работы с MySQL(только эксель, азы VBA), Ваше объяснение оказалось самым “доступным”, спасибо Вам огромное!
А для таких же чайников как я скажу что можно делать сводную не по одному полю name, а вписывать все поля какие вам нужны, и группировать потом соответственно – получается сводная как в экселе! (даже корректнее если код материала один а в названиях ошибки) Вот мой пример, понимаю, может с точки зрения гуру SQLщиков я что-то делал не так но оно работает а это то что мне нужно)
SELECT GR_PR, KOD_PR, GRKD_PR, NAIM, PRIZN, GR_B, KOD_B, NAIMB,
SUM(IF(TIP=’norm’,KOL_B,NULL)) AS Plan,
SUM(IF(TIP=’fact’,KOL_B,NULL)) AS Fact
FROM test GROUP BY GR_PR, KOD_PR, GRKD_PR, NAIM, PRIZN, GR_B, KOD_B ORDER BY GR_PR, KOD_PR, PRIZN, GR_B, KOD_B;
Данный селект переформатирует таблицу с колонкой в которой есть записи norm или fact в таблицу где сгруппированы материалы, а количество справа разбивается на 2 колонки соответствующие норме или факту.

Дмитрий, July 25, 2015 5:43 pm Reply
Ваше имя
Ваш email*
Ваш сайт
Текст вашего комментария:

Поиск по блогу:
Подписаться:
Популярные:
Облако тегов:
Разное:
Счетчик: