Как создать сводную таблицу (pivot table) в PostgreSQL?

Question

Есть таблица:

CREATE TABLE my_table( FIO text, event_type text, event_id text, my_date text); 

INSERT INTO my_table(FIO , event_type, event_id, my_date) 
VALUES 
('JOHN',   'продажа', 'a123-45-67',   '2025-01-10'),
('Андрей', 'продажа', '45t-745-09',   '2025-02-14'),
('JOHN',   'покупка', 'a123-45-67',   '2025-02-01'),
('JOHN',   'покупка', 'a123-45-67',   '2025-02-05'),
('JOHN',   'покупка', '432-e6-78-9r', '2025-02-06'),
('JOHN',   'продажа', 'try-653-7t',   '2025-03-01'), 
('JOHN',   'покупка', '325-r6-78u',   '2025-03-15'),
('Андрей', 'продажа', '958-tui-56-64','2025-02-14'),
('Андрей', 'продажа', 'hrj-678-34',   '2025-02-18'), 
('Ji',     'продажа', '767-67-ui8',   '2025-03-10');

Как вывести количество уникальных event_id для каждого FIO и event_type с разбивкой по месяцам, т.е. создать сводную таблицу, в которой FIO и event_type - поля строк, месяц от даты my_date - поле столбцов, event_id - поле значений, а агрегирующая функция - количество уникальных значений.

Должно получиться так:

FIO	event_type	Jan	Feb	Mar
JOHN	продажа	1	0	1
JOHN	покупка	0	2	1
Андрей	продажа	0	3	0
Ji	продажа	0	0	1

Следующий запрос выводит некорректные значения:

select FIO, event_type, 
(select count (event_id) from my_table 
where my_date between TO_CHAR(DATE '2025-01-01', 'yyyy-mm-dd') and TO_CHAR(DATE '2025-01-31', 'yyyy-mm-dd')) as Jan,
(select count (event_id) from my_table 
where my_date between TO_CHAR(DATE '2025-02-01', 'yyyy-mm-dd') and TO_CHAR(DATE '2025-02-28', 'yyyy-mm-dd')) as Feb,
(select count (event_id) from my_table 
where my_date between TO_CHAR(DATE '2025-03-01', 'yyyy-mm-dd') and TO_CHAR(DATE '2025-03-31', 'yyyy-mm-dd')) as Mar
from my_table
group by FIO, event_type;

Подскажите, пожалуйста, в чем ошибка.

Answer 1

Вы вычисляете одно значение для каждого месяца, заполняя им всю колонку. Вместо подсчета по всей таблице select count(event_id) from my_table where my_date between ... нужно применить агрегирующую функцию к группе.

Например:

with cte as (
select 
  FIO,
  event_type, 
  event_id, 
  extract(month from my_date::date) as month
from 
  my_table
)
select 
  FIO,
  event_type,
  count(distinct case when month = 1 then event_id end) as Jan,
  count(distinct case when month = 2 then event_id end) as Feb,
  count(distinct case when month = 3 then event_id end) as Mar
from 
  cte
group by
  FIO,
  event_type
order by 1, 2;

БЛОГ НА HUSL

Как создать сводную таблицу (pivot table) в PostgreSQL?

Ответы (1 шт):