如何为用户计算事件的多列？ [英] How do I count multiple columns of events for users?

问题描述

我有一个事件表，用于存储用户的事件，并且我想获取每个用户的两个特定事件的数量。

I have a table of events that stores the events for users and I want to get the number of two specific events for each user.

一个示例表称为事件，它有2列

An example table is called "events" and it has 2 columns

user_id VARCHAR(50)
event_name VARCHAR(50)

user_id都是唯一的，事件名称可以是登录名，sent_message，liked_post

The user_ids are all unique and the event names can be things like login, sent_message, liked_post

例如，如何查询每个用户发送的邮件总数以及每个用户的Liked_posts数量？

How do I, for example, query for the total messages sent per user AND the number of liked_posts per user?

推荐答案

这是您可以使用的查询模式：

This is the pattern of query you can use:

select user_id, 
    sent_message_count=sum(case when event_name = 'Sent Message' then 1 else 0 end), 
    liked_post_count=sum(case when event_name = 'Liked Post' then 1 else 0 end),
from events
group by user_id

现在，您只需要确保何时案例中的c $ c>部分陈述符合您所需的条件。模式本身-将符合条件的1汇总成一堆，实际上是获得所要结果的关键。

Now, you just need to make sure the when part of each case statement fits the criteria you need. The pattern itself - summing a bunch of 1's where the criteria fits, is really the key to achieving the result you're after.

这篇关于如何为用户计算事件的多列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！