Сводная операция MySQL для получения разбивки процента от общего числа событий в день по типу события.

Есть таблица событий

created_at DATETIME
event_type STRING 
# Some other columns with data about the event

Я хотел бы получить процент от общего количества событий в день за event_type.

Итак, я сгруппировал события, чтобы получить количество событий в день:

# Lazily used date_bucket in GROUP BY since it's valid MySQL.
# Is that bad since it's not standard SQL?
#
SELECT 
    DATE(created_at) as date_bucket, 
    event_type, 
    COUNT(*) as number
FROM 
    example_table 
GROUP BY
    date_bucket, event_type

Если бы у нас были ряды

# Columns (date_bucket, event_type, number)
#
('2020-06-02', 'exampleG1', 5)
('2020-06-02', 'exampleG2', 10)
('2020-06-02', 'exampleG3', 20)
('2020-06-03', 'exampleG1', 10)

Я хотел бы иметь возможность получить что-то эквивалентное в обработке

# Columns (date_bucket, exampleG1, exampleG2, exampleG3)
#
('2020-06-02', 15/35, 10/35, 20/35)
('2020-06-03', 10/10, 0, 0)

Я не знаю отдельных значений event_type заранее, и не все значения группы могут присутствовать во все дни, и в этом случае значение для этого типа должно быть 0 в этот день.

Я думал сделать какую-то операцию поворота, но, похоже, MySQL не поддерживает повороты, поэтому я немного не понимаю, как к этому подойти.

Если бы я заранее знал набор допустимых типов событий, я думаю, что мог бы сделать какой-нибудь неприятный подробный запрос о возможных типах, но набор является переменным.

Есть ли элегантный способ добиться этого?


mysql sql pivot group-by dynamic-pivot
person Community    schedule 03.06.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Я не знаю различных значений event_type заранее

Вы запрашиваете динамический SQL. То есть динамически создайте строку запроса из другого запроса, в котором перечислены различные значения event_type, а затем выполните ее. В MySQL это реализуется с помощью подготовленных операторов.

Вот как это сделать:

select @sql := group_concat(distinct
    'sum(case when event_type = ''', 
    event_type, ''' then number else 0 end)/sum(number) as `ratio_', 
    event_type, '`'
) 
from example_table;

set @sql = concat(
    'select date(created_at) date_bucket, ', 
    @sql, 
    ' from example_table group by date(created_at) order by date_bucket'
);

-- debug
select @sql;

-- execute
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

Для ваших демонстрационных данных это создает следующий запрос:

select 
    date(created_at) date_bucket, 
    sum(case when event_type = 'exampleG1' then number else 0 end)/sum(number) as `ratio_exampleG1`,
    sum(case when event_type = 'exampleG2' then number else 0 end)/sum(number) as `ratio_exampleG2`,
    sum(case when event_type = 'exampleG3' then number else 0 end)/sum(number) as `ratio_exampleG3` 
from example_table 
group by date(created_at) 
order by date_bucket

И следующий результат:

date_bucket | ratio_exampleG1 | ratio_exampleG2 | ratio_exampleG3
:---------- | --------------: | --------------: | --------------:
2020-06-02  |          0.1429 |          0.2857 |          0.5714
2020-06-03  |          1.0000 |          0.0000 |          0.0000

Демонстрация скрипта БД

person GMB    schedule 03.06.2020
comment
Ах, на самом деле раньше не использовал подготовленные операторы. Пойду учиться! Спасибо! - person ; 03.06.2020