Разработка сводных таблиц (Pivot Table) для аналитики на сайте

Разработка сводных таблиц (Pivot Table) для аналитики на сайте

Сводная таблица — это UI для группировки, агрегации и сравнения данных в режиме реального времени. Пользователь перетаскивает поля между осями, выбирает метрики и получает нужный срез без обращения к разработчику. Excel и Google Sheets популярны именно благодаря этой возможности — встраивание аналогичного инструмента в веб-приложение убирает необходимость экспортировать данные.

Архитектура

Есть два принципиально разных подхода:

Клиентская агрегация — все данные загружены в браузер, pivot считается на JavaScript. Работает до ~100–200k строк. Быстрый отклик при манипуляциях с осями, не требует roundtrip к серверу.

Серверная агрегация — каждое изменение конфигурации отправляет запрос на сервер, который считает агрегаты в базе данных. Обязателен для больших объёмов. ClickHouse или PostgreSQL с правильными индексами возвращают агрегаты по миллионам строк за сотни миллисекунд.

Готовые библиотеки

Прежде чем писать с нуля, стоит оценить:

• react-pivottable — открытая, drag-and-drop, поддерживает несколько рендереров (таблица, bar chart, heatmap). Ограничена клиентской обработкой
• AG Grid (с row grouping) — enterprise-уровень, серверный режим, огромная экосистема. Платная для продвинутых фич
• Flexmonster — специализированный pivot, подключается к OLAP-кубам, коммерческая лицензия

Клиентская реализация

Ядро pivot — функция агрегации:

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,
  };
}

Компонент таблицы

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>
  );
}

Drag-and-drop конфигуратор

import { DndContext, useDraggable, useDroppable } from '@dnd-kit/core';

function PivotConfigurator({ fields, config, onChange }: {
  fields: string[];
  config: PivotConfig;
  onChange: (c: PivotConfig) => void;
}) {
  function handleDragEnd(event: any) {
    const { active, over } = event;
    if (!over) return;

    const field = active.id as string;
    const target = over.id as 'rows' | 'cols' | 'values' | 'unused';

    const newConfig = { ...config };
    // Убираем поле из всех зон
    newConfig.rows = newConfig.rows.filter(f => f !== field);
    newConfig.cols = newConfig.cols.filter(f => f !== field);
    newConfig.values = newConfig.values.filter(f => f !== field);

    if (target === 'rows') newConfig.rows.push(field);
    else if (target === 'cols') newConfig.cols.push(field);
    else if (target === 'values') newConfig.values.push(field);

    onChange(newConfig);
  }

  return (
    <DndContext onDragEnd={handleDragEnd}>
      <div className="grid grid-cols-4 gap-3 mb-4">
        <DropZone id="rows" label="Строки" items={config.rows} />
        <DropZone id="cols" label="Столбцы" items={config.cols} />
        <DropZone id="values" label="Значения" items={config.values} />
        <DropZone
          id="unused"
          label="Поля"
          items={fields.filter(f => !config.rows.includes(f) && !config.cols.includes(f) && !config.values.includes(f))}
        />
      </div>
    </DndContext>
  );
}

Серверная агрегация

Для больших данных конфигурация pivot уходит на сервер:

// Клиент
async function fetchPivotData(config: PivotConfig, dateRange: [Date, Date]) {
  const response = await fetch('/api/analytics/pivot', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ config, dateRange }),
  });
  return response.json();
}

// Сервер — генерация 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}
  `;
}

Экспорт в Excel

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();
}

Сроки

Клиентский pivot до 50k строк с drag-and-drop конфигуратором и экспортом в Excel — 2–3 недели. Серверный режим с ClickHouse или PostgreSQL, кешированием результатов и поддержкой нескольких значений — дополнительно 1–2 недели.