Почему готовые библиотеки не подходят для больших данных
Представьте: интернет-магазин с 10 млн заказов. Менеджеру нужно сформировать отчёт по продажам за квартал с группировкой по категориям и месяцам. Обычный экспорт в Excel занимает 30 минут и грузит базу. Сводная таблица с серверной агрегацией отдаёт данные за 200 мс и позволяет менять срезы в реальном времени. На практике готовые библиотеки не всегда справляются с такой нагрузкой: react-pivottable тормозит на 200k+ строк, AG Grid требует enterprise-лицензии для серверного режима, а Flexmonster не поддерживает ad-hoc запросы к реляционным базам. Мы комбинируем библиотеки и собственную серверную агрегацию, чтобы закрыть эти пробелы. Экономия на лицензиях делает такой подход дешевле, чем enterprise-лицензии на каждого разработчика.
Wikipedia определяет сводную таблицу как инструмент анализа данных с агрегацией по измерениям. В коммерческих проектах часто требуется кастомизация, которую готовые решения не обеспечивают.
Как мы строим серверную агрегацию для миллионов строк?
Для больших данных конфигурация pivot уходит на сервер. Используем ClickHouse или PostgreSQL с индексами. Каждое изменение осей отправляет запрос, сервер возвращает агрегаты за 100-500 мс. Кешируем результаты по ключу конфигурации. Ниже — пример генерации SQL из конфига.
// Сервер — генерация SQL из конфига
function buildPivotQuery(config: PivotConfig, dateRange: [Date, Date]): string {
const rowsExpr = config.rows.map(r => `"${r}"`).join(', ');
const colsExpr = config.cols.map(c => `"${c}"`).join(', ');
const valExpr = config.values[0]; // упрощение
const aggExpr = {
sum: `SUM("${valExpr}")`,
count: `COUNT(*)`,
avg: `AVG("${valExpr}")::numeric(18,2)`,
min: `MIN("${valExpr}")`,
max: `MAX("${valExpr}")`,
}[config.aggFn];
return `
SELECT ${rowsExpr}, ${colsExpr}, ${aggExpr} AS value
FROM events
WHERE created_at BETWEEN $1 AND $2
GROUP BY ${rowsExpr}, ${colsExpr}
ORDER BY ${rowsExpr}, ${colsExpr}
`;
}
Пошаговая инструкция
- Анализ структуры данных. Определяем поля для группировки (строки, столбцы) и меры (сумма, среднее).
- Проектирование SQL-запросов. Генерируем динамические запросы с GROUP BY.
- Настройка ClickHouse/PostgreSQL. Создаём индексы, настраиваем кеширование результатов.
- Интеграция с клиентом. По каждому изменению конфига отправляем запрос на сервер.
Сравнение клиентской и серверной агрегации
| Характеристика | Клиентская | Серверная |
|---|---|---|
| Макс. записей | ~200k | Без ограничений |
| Время отклика | Мгновенно | 100–500 мс |
| Стоимость инфры | Низкая | Выше (сервер + кеш) |
| Гибкость | Только предзагруженные данные | Любые SQL-запросы |
| Масштабируемость | Ограничена браузером | До миллиарда строк |
Клиентская агрегация в 5 раз быстрее при малых данных, но серверная масштабируется до миллиарда строк. Выбор зависит от объёма и требований к интерактивности.
Как реализовать клиентскую агрегацию: пошаговая инструкция
- Фильтрация данных — оставляем только записи, соответствующие установленным фильтрам.
- Группировка — определяем ключи для строк и столбцов на основе значений выбранных полей.
- Агрегация — вычисляем значение для каждой ячейки (сумма, количество, среднее, минимум, максимум).
- Вывод таблицы — рендерим HTML-таблицу с итогами и фиксированной шапкой.
Полный код функции агрегации (TypeScript)
type AggregateFunction = 'sum' | 'count' | 'avg' | 'min' | 'max';
interface PivotConfig {
rows: string[];
cols: string[];
values: string[];
aggFn: AggregateFunction;
filters: Record<string, string[]>;
}
interface PivotResult {
rowKeys: string[][];
colKeys: string[][];
data: Map<string, Map<string, number>>;
}
function computePivot(rawData: Record<string, any>[], config: PivotConfig): PivotResult {
const { rows, cols, values, aggFn, filters } = config;
const filtered = rawData.filter(row =>
Object.entries(filters).every(([field, allowed]) =>
!allowed.length || allowed.includes(String(row[field]))
)
);
const rowKeySet = new Set<string>();
const colKeySet = new Set<string>();
const accumulator = new Map<string, Map<string, number[]>>();
filtered.forEach(row => {
const rowKey = rows.map(r => String(row[r] ?? '(пусто)')).join('||');
const colKey = cols.map(c => String(row[c] ?? '(пусто)')).join('||');
rowKeySet.add(rowKey);
colKeySet.add(colKey);
const numVal = values.reduce((sum, v) => sum + (Number(row[v]) || 0), 0);
if (!accumulator.has(rowKey)) accumulator.set(rowKey, new Map());
const colMap = accumulator.get(rowKey)!;
if (!colMap.has(colKey)) colMap.set(colKey, []);
colMap.get(colKey)!.push(numVal);
});
const aggregated = new Map<string, Map<string, number>>();
accumulator.forEach((colMap, rowKey) => {
const row = new Map<string, number>();
colMap.forEach((vals, colKey) => {
let result: number;
switch (aggFn) {
case 'sum': result = vals.reduce((a, b) => a + b, 0); break;
case 'count': result = vals.length; break;
case 'avg': result = vals.reduce((a, b) => a + b, 0) / vals.length; break;
case 'min': result = Math.min(...vals); break;
case 'max': result = Math.max(...vals); break;
}
row.set(colKey, result);
});
aggregated.set(rowKey, row);
});
return {
rowKeys: Array.from(rowKeySet).sort().map(k => k.split('||')),
colKeys: Array.from(colKeySet).sort().map(k => k.split('||')),
data: aggregated,
};
}
Полный код компонента PivotTable (React+TypeScript)
function PivotTable({ result, config, format }: {
result: PivotResult;
config: PivotConfig;
format?: (val: number) => string;
}) {
const fmt = format ?? (v => v.toLocaleString('ru-RU'));
const rowTotals = result.rowKeys.map(rk => {
const rowKey = rk.join('||');
let total = 0;
result.colKeys.forEach(ck => {
total += result.data.get(rowKey)?.get(ck.join('||')) ?? 0;
});
return total;
});
const grandTotal = rowTotals.reduce((a, b) => a + b, 0);
return (
<div className="overflow-auto max-h-[600px]">
<table className="text-sm border-collapse w-full">
<thead className="sticky top-0 bg-white z-10">
<tr>
{config.rows.map(r => (
<th key={r} className="border px-3 py-2 text-left bg-gray-50 font-medium">{r}</th>
))}
{result.colKeys.map(ck => (
<th key={ck.join('/')} className="border px-3 py-2 text-right bg-gray-50 font-medium whitespace-nowrap">
{ck.join(' / ')}
</th>
))}
<th className="border px-3 py-2 text-right bg-blue-50 font-semibold">Итого</th>
</tr>
</thead>
<tbody>
{result.rowKeys.map((rk, ri) => {
const rowKey = rk.join('||');
return (
<tr key={rowKey} className="hover:bg-gray-50">
{rk.map((label, i) => (
<td key={i} className="border px-3 py-1.5 font-medium">{label}</td>
))}
{result.colKeys.map(ck => {
const val = result.data.get(rowKey)?.get(ck.join('||'));
return (
<td key={ck.join('/')} className="border px-3 py-1.5 text-right tabular-nums">
{val != null ? fmt(val) : '—'}
</td>
);
})}
<td className="border px-3 py-1.5 text-right tabular-nums font-medium bg-blue-50">
{fmt(rowTotals[ri])}
</td>
</tr>
);
})}
</tbody>
<tfoot>
<tr className="font-semibold bg-gray-100">
<td colSpan={config.rows.length} className="border px-3 py-2">Итого</td>
{result.colKeys.map(ck => {
const colTotal = result.rowKeys.reduce((sum, rk) => {
return sum + (result.data.get(rk.join('||'))?.get(ck.join('||')) ?? 0);
}, 0);
return (
<td key={ck.join('/')} className="border px-3 py-2 text-right tabular-nums">{fmt(colTotal)}</td>
);
})}
<td className="border px-3 py-2 text-right tabular-nums bg-blue-100">{fmt(grandTotal)}</td>
</tr>
</tfoot>
</table>
</div>
);
}
Экспорт в Excel
Используем exceljs для генерации .xlsx на клиенте:
import ExcelJS from 'exceljs';
async function exportToExcel(result: PivotResult, config: PivotConfig) {
const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet('Сводная таблица');
const headers = [...config.rows, ...result.colKeys.map(k => k.join(' / ')), 'Итого'];
ws.addRow(headers).font = { bold: true };
result.rowKeys.forEach(rk => {
const rowKey = rk.join('||');
const row = [...rk];
result.colKeys.forEach(ck => {
row.push(String(result.data.get(rowKey)?.get(ck.join('||')) ?? ''));
});
ws.addRow(row);
});
const buffer = await wb.xlsx.writeBuffer();
const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = 'pivot.xlsx';
a.click();
}
Как выбрать между клиентской и серверной агрегацией?
Если данные до 200k строк и не требуют сложной фильтрации — клиентский подход быстрее и дешевле. Для миллионов строк и ad-hoc запросов — серверная агрегация обязательна. Мы помогаем клиентам подобрать оптимальную архитектуру.
Этапы разработки и ориентировочные сроки
| Этап | Длительность | Описание |
|---|---|---|
| Анализ данных | 2-3 дня | Изучение структуры данных, требований к метрикам и фильтрации |
| Проектирование схемы агрегации | 1-2 дня | Определение полей, мер, индексов для ClickHouse/PostgreSQL |
| Разработка клиентского UI | 1-2 недели | Реализация drag-and-drop конфигуратора, таблицы с итогами и sticky header |
| Серверная агрегация (если нужна) | 1-2 недели | Настройка ClickHouse, кеширование, генерация SQL |
| Экспорт в Excel/CSV | 2-3 дня | Интеграция с exceljs, проверка форматирования |
| Нагрузочное тестирование | 2-3 дня | Проверка на 10 млн строк, оптимизация N+1 запросов |
Клиентский pivot до 50k строк с drag-and-drop конфигуратором и экспортом в Excel — 2–3 недели. Серверный режим с ClickHouse или PostgreSQL, кешированием результатов и поддержкой нескольких значений — дополнительно 1–2 недели.
Состав работ
- Анализ данных и проектирование схемы агрегации
- Разработка клиентского UI с drag-and-drop (React, TypeScript, Tailwind)
- Серверная агрегация на ClickHouse/PostgreSQL с кешированием
- Экспорт в Excel/CSV, печать
- Тестирование на нагрузку до 10 млн строк
- Документация и передача исходников
- Поддержка 3 месяца после запуска
Оценим ваш проект за 1 день. Свяжитесь с нами — обсудим данные, метрики и подберём оптимальное решение. Получите консультацию уже сегодня.