Широкие и длинные таблицы

Данные в таблицах можно располагать разными способами, но есть 2 основных общепринятых: широкие таблицы и длинные таблицы. Ниже – о том, чем они различаются, где используются и как преобразовывать одни таблицы в другие.

В чем разница между широкими и длинными таблицами?

Обычные таблицы строятся по модели EAV, «сущность-атрибут-значение». Например, у нас есть таблица с данными людей, для каждого человека указано имя и возраст. Имя – это сущность (о ком мы говорим), возраст – это атрибут (о чем мы говорим), конкретное число – это значение (то, чему равен атрибут). У каждой сущности может быть несколько атрибутов – например, для каждого человека мы записываем возраст, рост и вес.

Так вот, разница между широким форматом и длинным форматом – в том, каким образом мы записываем атрибуты (с шириной столбцов это не связано, если что). Для создания таблицы в широком формате мы помещаем сущности в заголовки строк, а атрибуты – в заголовки столбцов, что позволяет сформировать матрицу:

Для узкой таблицы мы помещаем атрибут сразу за сущностью – это позволяет обойтись всего 3 столбцами, «сущность-атрибут-значение», но для каждой сущности нам нужно вписывать несколько атрибутов/значений, что делает таблицу длинной:

В плане оформления таблиц для людей – широкие однозначно выигрывают, потому что использование заголовка столбца в виде атрибута существенно повышает читаемость таблицы. Но есть проблема: системные таблицы, хранящиеся в реляционных базах данных (то есть всех, в которых есть SQL), оптимизированы под длинные таблицы, поэтому вынесение атрибутов в отдельные столбцы существенно уменьшает производительность базы данных – экспоненциально растет количество операций записи/чтения при составлении сложных запросов. Поэтому для хранения данных в сводных таблицах принято использовать узкий формат, в то время как временные таблицы для презентаций и анализа делают широкими.

Как преобразовать узкие таблицы в широкие?

Из того, что мы описали выше, следует логичный вопрос: как преобразовать длинную таблицу в широкую? Обратное преобразование тоже иногда требуется, но – крайне редко, плюс для преобразования широкой таблицы в длинную нужно совершить противоположные действия, поэтому кейс «широкие -> длинные таблицы» мы рассматривать не будем. Универсального ответа на вопрос нет, потому что у каждой системы хранения информации – свои инструменты преобразования таблиц.

Начнем с SQL. У этого языка есть специальная команда – PIVOT – которая нужна для того, чтобы преобразовать нормализованную таблицу в более широкую. У нее есть антипод – команда UNPIVOT – которая делает таблицу более узкой. Например, у нас есть такая вот узкая таблица с данными по продажам за каждый квартал последних двух лет:

CREATE TABLE Sales (
Year INT,
Quarter INT,
Amount DECIMAL(10, 2)
);
INSERT INTO Sales (Year, Quarter, Amount) VALUES
(2023, 1, 1000.00),
(2023, 2, 1500.00),
(2023, 3, 2000.00),
(2023, 4, 2500.00),
(2024, 1, 1100.00),
(2024, 2, 1600.00),
(2024, 3, 2100.00),
(2024, 4, 2600.00);

Преобразование этой таблицы в широкую с помощью PIVOT:

SELECT Year,
[1] AS Q1,
[2] AS Q2,
[3] AS Q3,
[4] AS Q4
FROM (
SELECT Year, Quarter, Amount
FROM Sales
) AS SourceTable
PIVOT (
SUM(Amount)
FOR Quarter IN ([1], [2], [3], [4])
) AS PivotTable;

Сначала мы указываем четверти года в качестве столбцов, затем через PIVOT заполняем отдельные ячейки – и получаем готовую широкую таблицу:

В Excel для преобразования есть специальная функция – «Сводная таблица». Сначала вам нужно выделить всю таблицу, затем на вкладке «Вставка» выбрать «Сводная таблица». Excel предложит выбрать диапазон сводной таблицы, после чего в настройках полей сводной таблицы нужно будет указать те данные, которые будут использоваться в качестве строк, столбцов и значений (содержимое ячеек). Примените настройки – и получите новую широкую таблицу.

С Google Sheets это работает так же, как и с Excel – выделяете таблицу, используете инструмент «Сводная таблица» (лежит на вкладке «Вставка»), настраиваете строки-столбцы-значения, создаете новую таблицу.

Кратко о главном

• Все таблицы придерживаются модели EAV – сущность, атрибут, значение.
• В широких таблицах атрибуты – колонки, в длинных таблицах каждый атрибут для каждой сущности занимает свою строку.
• Широкие таблицы лучше читаются, длинные таблицы лучше обрабатываются базами данных.
• У каждого инструмента для работы с табличными данными есть свои методы создания широких таблиц из узких – PIVOT у SQL, сводные таблицы у Excel и Google Sheets.