如何使用GROUP BY didmd5语句筛选event_type字段？

First, let's make your sample events table easier to read:

Below are common use cases for combining GROUP BY didmd5 with operations on the event_type field:

1. Calculate total counts per event type for each didmd5 #

If you want to see how many times each event type occurred for every user (grouped by didmd5), group by both didmd5 and event_type, then aggregate the count field:

SELECT didmd5, event_type, SUM(count) AS total_count
FROM events
GROUP BY didmd5, event_type;

For your sample data, this will return each event_type for the single didmd5 with its respective total count (since each type has only one record here).

2. Filter didmd5s that have specific event types #

To find users who performed a particular event (e.g., share), you can use GROUP BY with a HAVING clause for flexible filtering:

SELECT didmd5
FROM events
GROUP BY didmd5
HAVING SUM(CASE WHEN event_type = 'share' THEN 1 ELSE 0 END) > 0;

If you just need distinct didmd5s for a single event type, a simpler query works too:

SELECT DISTINCT didmd5
FROM events
WHERE event_type = 'share';

For more complex filters (e.g., users who did both read and like), HAVING is essential:

SELECT didmd5
FROM events
GROUP BY didmd5
HAVING SUM(CASE WHEN event_type = 'read' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN event_type = 'like' THEN 1 ELSE 0 END) > 0;

3. Aggregate all event types for each didmd5 #

If you want to list all unique event types associated with each didmd5 in a single string, use a database-specific string aggregation function:

MySQL/MariaDB: #

SELECT didmd5, GROUP_CONCAT(DISTINCT event_type SEPARATOR ', ') AS event_types
FROM events
GROUP BY didmd5;

PostgreSQL: #

SELECT didmd5, STRING_AGG(DISTINCT event_type, ', ') AS event_types
FROM events
GROUP BY didmd5;

For your sample data, this would return:
f8fdf8b7315c884b87361a4dd73d878 | share, like, read, readmore

4. Find the most frequent event type per didmd5 #

To get the event type with the highest total count for each user, combine GROUP BY with window functions:

WITH event_counts AS (
    SELECT didmd5, event_type, SUM(count) AS total_count,
           RANK() OVER (PARTITION BY didmd5 ORDER BY SUM(count) DESC) AS rnk
    FROM events
    GROUP BY didmd5, event_type
)
SELECT didmd5, event_type, total_count
FROM event_counts
WHERE rnk = 1;

In your sample, readmore has a total count of 2, so it would be returned as the top event type for the given didmd5.

内容的提问来源于stack exchange，提问作者user9705644