类似于MySQL枢轴的操作,以获取每种事件类型每天总事件数的细分 [英] MySQL pivot-like operation to get breakdown of percentage of total events per day per event type
问题描述
有一张事件表
created_at DATETIME
event_type STRING
# Some other columns with data about the event
我想做的是能够获取每个event_type
每天事件总数的百分比.
What I would like to do is be able to get the percentage of the total number of events per day per event_type
.
因此,我将事件分组以获取每个事件每天的计数:
So, I grouped the events to get the count per day per event:
# 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.
I don't know the distinct event_type
values before hand, and not all group values may be present on all days, in which case the value for that type should be 0 on that day.
我当时正在考虑进行某种枢轴操作,但是MySQL似乎不支持枢轴,因此我对如何实现这种方法有点茫然.
I was thinking to do some kind of pivot operation, but it appears MySQL doesn't support pivots, so I'm at a bit of a loss how to approach this.
如果我提前知道有效事件类型的集合,我想我可以对可能的类型进行一些讨厌的冗长查询,但是该集合是可变的.
If I knew the set of valid event types ahead of time, I think I could do some nasty verbose query on the possible types, but the set is variable.
是否有一种优雅的方法来实现这一目标?
Is there an elegant way of achieving this?
推荐答案
我不知道事前有哪些不同的event_type值
I don't know the distinct event_type values before hand
您正在请求动态SQL.即,从另一个列出了不同的event_type
值的查询中动态构建查询字符串,然后执行它.在MySQL中,这是使用准备好的语句实现的.
You are asking for dynamic SQL. That is, dynamically build the query string from another query that lists distinct event_type
values, then execute it. In MySQL, this is implemented using prepared statements.
这是操作方法:
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;
对于您的示例数据,这将产生以下查询:
For your sample data, this produces the following query:
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
Demo on DB Fiddle
这篇关于类似于MySQL枢轴的操作,以获取每种事件类型每天总事件数的细分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!