Сводные таблицы в SQLite

Допустим, у нас есть таблица sales с продажами продуктов за 2020-2023 годы:

┌─────────┬──────┬────────┐
│ product │ year │ income │
├─────────┼──────┼────────┤
│ alpha   │ 2020 │ 100    │
│ alpha   │ 2021 │ 120    │
│ alpha   │ 2022 │ 130    │
│ alpha   │ 2023 │ 140    │
│ beta    │ 2020 │ 10     │
│ beta    │ 2021 │ 20     │
│ beta    │ 2022 │ 40     │
│ beta    │ 2023 │ 80     │
│ gamma   │ 2020 │ 80     │
│ gamma   │ 2021 │ 75     │
│ gamma   │ 2022 │ 78     │
│ gamma   │ 2023 │ 80     │
└─────────┴──────┴────────┘

песочница • скачать

И мы хотим трансформировать ее в так называемую сводную таблицу, в которой продукты расположены по строкам, а годы — по столбцам:

┌─────────┬──────┬──────┬──────┬──────┐
│ product │ 2020 │ 2021 │ 2022 │ 2023 │
├─────────┼──────┼──────┼──────┼──────┤
│ alpha   │ 100  │ 120  │ 130  │ 140  │
│ beta    │ 10   │ 20   │ 40   │ 80   │
│ gamma   │ 80   │ 75   │ 78   │ 80   │
└─────────┴──────┴──────┴──────┴──────┘

Некоторые СУБД (например, SQL Server), предоставляют специальный оператор pivot для сводных таблиц. В SQLite ничего такого нет. Несмотря на это, существует несколько способов решить задачу. Давайте их разберем.

1. Фильтр по итогам

Извлечем каждый год в отдельный столбец и посчитаем фильтрованный суммарный доход для этого года по каждому продукту:

select
  product,
  sum(income) filter (where year = 2020) as "2020",
  sum(income) filter (where year = 2021) as "2021",
  sum(income) filter (where year = 2022) as "2022",
  sum(income) filter (where year = 2023) as "2023"
from sales
group by product
order by product;

Вот наша сводная таблица:

┌─────────┬──────┬──────┬──────┬──────┐
│ product │ 2020 │ 2021 │ 2022 │ 2023 │
├─────────┼──────┼──────┼──────┼──────┤
│ alpha   │ 100  │ 120  │ 130  │ 140  │
│ beta    │ 10   │ 20   │ 40   │ 80   │
│ gamma   │ 80   │ 75   │ 78   │ 80   │
└─────────┴──────┴──────┴──────┴──────┘

Это универсальный метод, который работает с любой СУБД. Даже если движок БД не поддерживает filter — всегда можно переделать на case:

select
  product,
  sum(case when year = 2020 then income end) as "2020",
  sum(case when year = 2021 then income end) as "2021",
  sum(case when year = 2022 then income end) as "2022",
  sum(case when year = 2023 then income end) as "2023"
from sales
group by product
order by product;

filter легко использовать, если столбцов немного и они известны заранее. Но что если нет?

2. Динамический SQL

Не будем «зашивать» конкретные годы в запрос. Вместо этого построим его динамически:

1. Выберем все существующие годы.
2. Для каждого года сгенерируем выражение ...filter (where year = X) as "X".

with years as (
  select distinct year as year
  from sales
),
lines as (
  select 'select product ' as part
  union all
  select ', sum(income) filter (where year = ' || year || ') as "' || year || '" '
  from years
  union all
  select 'from sales group by product order by product;'
)
select group_concat(part, '')
from lines;

Этот запрос вернет такое же SQL-выражение, что мы вручную писали на предыдущем шаге (за исключением форматирования):

select product , sum(income) filter (where year = 2020) as "2020" , sum(income) filter (where year = 2021) as "2021" , sum(income) filter (where year = 2022) as "2022" , sum(income) filter (where year = 2023) as "2023" from sales group by product order by product;

Осталось только выполнить его. Используем для этого функцию eval(sql), которая входит в состав расширения define:

select load_extension('./define');

with years as (
  select distinct year as year
  from sales
),
lines as (
  select 'drop view if exists v_sales; ' as part
  union all
  select 'create view v_sales as '
  union all
  select 'select product '
  union all
  select ', sum(income) filter (where year = ' || year || ') as "' || year || '" '
  from years
  union all
  select 'from sales group by product order by product;'
)
select eval(group_concat(part, ''))
from lines;

Примечание: расширения не работают в песочнице, так что используйте локальный SQLite, если хотите повторить этот шаг.

Здесь мы создаем представление v_sales, которое выполняет предварительно сгенерированный запрос. Теперь выберем из него данные:

select * from v_sales;

┌─────────┬──────┬──────┬──────┬──────┐
│ product │ 2020 │ 2021 │ 2022 │ 2023 │
├─────────┼──────┼──────┼──────┼──────┤
│ alpha   │ 100  │ 120  │ 130  │ 140  │
│ beta    │ 10   │ 20   │ 40   │ 80   │
│ gamma   │ 80   │ 75   │ 78   │ 80   │
└─────────┴──────┴──────┴──────┴──────┘

Работает!

3. Расширение для сводных таблиц

Если считаете, что динамический SQL это уже перебор — есть и более простое решение. Это расширение pivotvtab.

С ним достаточно перечислить три отдельных селекта:

1. Для выборки строк.
2. Для выборки столбцов.
3. Для выборки ячеек.

select load_extension('./pivotvtab');

create virtual table v_sales using pivot_vtab (
  -- строки
  (select distinct product from sales),
  -- столбцы
  (select distinct year, year from sales),
  -- ячейки
  (select sum(income) from sales where product = ?1 and year = ?2)
);

Дальше расширение само все сделает:

select * from v_sales;

┌─────────┬──────┬──────┬──────┬──────┐
│ product │ 2020 │ 2021 │ 2022 │ 2023 │
├─────────┼──────┼──────┼──────┼──────┤
│ alpha   │ 100  │ 120  │ 130  │ 140  │
│ beta    │ 10   │ 20   │ 40   │ 80   │
│ gamma   │ 80   │ 75   │ 78   │ 80   │
└─────────┴──────┴──────┴──────┴──────┘

Это даже проще, чем pivot в SQL Server!

Итого

Есть три способа построить сводную таблицу в SQLite:

1. Обычный SQL с sum() и filter.
2. Динамический запрос с вычислением через eval().
3. Специальное расширение pivotvtab.

──

P.S. Хотите освоить современный SQL? Обратите внимание на Оконные функции