Используйте ГРУППИРОВКУ по значениям в качестве столбцов с ПОДСЧЕТОМ во времени

ProgramBox

Используйте ГРУППИРОВКУ по значениям в качестве столбцов с ПОДСЧЕТОМ во времени

Post author:admin
Запись опубликована:18 июля, 2022
Post category:Вопросы по программированию

#postgresql

Вопрос:

Я пытаюсь подсчитать различные типы электронных писем ( template ), которые моя система отправляла за день. Я могу получить эти значения с помощью:

 select to_char(created_at, 'YYYY-MM-DD') period,
    count(*) num,
    template
from email_log
group by period, template
order by period desc, template

Мои результаты выглядят так

 period     | num  | template
----------- ------ ---------
2021-12-09 | 185  | reminder
2021-12-09 | 80   | thankyou
2021-12-09 | 1224 | welcome
2021-12-08 | 327  | reminder
2021-12-08 | 108  | thankyou
2021-12-08 | 1698 | welcome

Однако из-за нескольких group by s я получаю более одной строки в день. Как я могу экстраполировать template значение в столбцы, используя в count(*) качестве значения ячейки? Это означает, что мои столбцы — это имена шаблонов, мои строки — это количество. Например, это будет выглядеть так

 period     | reminder | thankyou | welcome
----------- ---------- ---------- --------
2021-12-09 | 185      | 80       | 1224
2021-12-08 | 327      | 108      | 1698

Возможно ли это сделать в обычном SQL и эффективно? В противном случае мне нужно использовать другую программу для получения этих значений в формате CSV и реструктуризации данных.

Бонусные бонусные баллы, если я смогу добавить сумму всех столбцов в конце как total

Самое близкое, что я смог получить, это это, но это требует от меня жесткого кодирования значений шаблона, и это не очень эффективно

 select to_char(created_at, 'YYYY-MM-DD') period,
    sum(case when template = 'reminder' then 1 else 0 end) as reminder,
    sum(case when template = 'thankyou' then 1 else 0 end) as thankyou,
    sum(case when template = 'welcome' then 1 else 0 end) as welcome,
    count(*) total
from email_log
group by period
order by period desc

1. Это сводная / перекрестная таблица, но вам нужно заранее знать имена столбцов. Вы можете использовать select ... from crosstab(...) as (period date, reminder integer, thankyou integer, welcome integer, ...)

2. Это намного проще сделать в вашем интерфейсе, когда вы отображаете данные. Одним из подходов, упрощающих интерфейс, может быть объединение данных в пары ключ / значение JSON. Тогда вам не нужно знать имена столбцов заранее.

3. @PanagiotisKanavos Вы хотите превратить свой комментарий в ответ?

Ответ №1:

Сначала вы можете заменить

 sum(case when template = 'reminder' then 1 else 0 end)

Автор:

 count(*) filter (where template = 'reminder')

что может быть немного быстрее.

Тогда вот (не прямолинейное) решение, в котором имена и номера столбцов неизвестны до времени выполнения :

Первый шаг: создайте составной тип email_templates , который соответствует списку столбцов, как и ожидалось :

 CREATE OR REPLACE PROCEDURE email_templates ()
LANGUAGE plpgsql AS
$
DECLARE
  composite_type_list text ;
BEGIN
  SELECT '(period date,' || string_agg(DISTINCT template || ' integer', ',') || ',total integer)'
    INTO composite_type_list 
    FROM email_log ;

  EXECUTE 'DROP TYPE IF EXISTS email_templates' ;
  EXECUTE 'CREATE TYPE email_templates AS ' || composite_type_list ;
END ;
$ ;

CALL email_templates () ;

Второй шаг: создайте функцию daily_synthesis() plpgsql с динамическим запросом, структурированным в соответствии с новым составным типом email_templates , и возвращающим набор json :

 CREATE OR REPLACE FUNCTION daily_synthesis ()
RETURNS setof json LANGUAGE plpgsql AS
$
DECLARE
  columns_list text ;
BEGIN
  SELECT string_agg(DISTINCT 'count(*) filter (where template = ' || quote_nullable(template) || ')', ',')
    INTO columns_list 
    FROM email_log ;

  RETURN QUERY EXECUTE 
  E'select row_to_json(row(to_char(created_at, 'YYYY-MM-DD'), ' || columns_list || ', count(*)) :: email_templates)
     from email_log
    group by created_at
    order by created_at desc' ;
END ;
$ ;

Третий шаг: вызовите функцию daily_synthesis() и преобразуйте результат json в запись с ожидаемыми столбцами :

 select b.* 
  from daily_synthesis () as a
 cross join lateral json_populate_record (null :: email_templates, a) as b

результат в dbfiddle :

 period      reminder    thankyou    welcome     total
2021-12-09  2           2           2           6

Ответ №2:

Сделайте это на стороне клиента или с помощью запроса, который у вас уже есть.

В tablefunc есть функция перекрестной таблицы, но она довольно грубая, и я не думаю, что у нее есть какие-либо преимущества по сравнению с тем, что у вас уже есть.

Если вам нужна общая строка, а также общий столбец, вы можете изменить значение group by period на group by rollup (period)

Метки: Используйте ГРУППИРОВКУ ПО значениям в качестве столбцов с подсчетом во времени