mysql pivot-like操作，用于获取每种事件类型每天总事件百分比的细分

yshpjwxd 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(310)

有一张事件表

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。
我想做一些pivot操作，但是mysql似乎不支持pivots，所以我有点不知道如何处理这个问题。
如果我提前知道有效事件类型的集合，我想我可以对可能的类型进行一些讨厌的详细查询，但是集合是可变的。
有没有一个优雅的方法来实现这一点？

sql mysql group-by pivot dynamic-pivot

来源：https://stackoverflow.com/questions/62172565/mysql-pivot-like-operation-to-get-breakdown-of-percentage-of-total-events-per-da

1条答案

按热度按时间

93ze6v8z1#

我不知道不同的事件类型值
您要求的是动态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

db小提琴演示

展开查看全部

赞(0）回复(0）举报 2021-07-26

我来回答

mysql pivot-like操作，用于获取每种事件类型每天总事件百分比的细分

1条答案

相关问题

热门标签

最新问答