如何编写SQL查询按天统计唯一事件数？处理含时分秒的timestamp

Got it, let's tackle this problem. You have a dataset with timestamp, event, and user fields, and you need to count the number of unique events per day—the tricky part is that your timestamp field includes hours, minutes, and seconds, so you can't just group directly on it.

Here's your sample data:

timestamp               event           user
2020-04-28 20:07:55.503 log_in         john
2020-04-28 20:08:01.996 log_out        john
2020-04-28 20:08:02.470 log_in         john
2020-04-28 20:08:03.996 log_out        john
2020-04-28 20:08:05.729 log_failed     john
2020-04-29 10:06:45.683 log_in         mark
2020-04-29 10:08:58.299 password_change mark
2020-04-30 14:19:24.921 log_in         jeff
2020-04-30 14:20:31.266 log_out        jeff
2020-04-30 14:21:44.438 create_new_user jeff
2020-04-30 14:22:44.455 create_new_user jeff

And you want this end result:

timestamp   count
2020-04-28  3
2020-04-29  2
2020-04-30  3

The Solution Breakdown #

The fix has two key parts:

1. Strip the time from your timestamp: This lets you group all events from the same calendar day together, no matter what time they happened.
2. Count only unique events: Use DISTINCT to avoid counting the same event multiple times (like John's repeated log_in entries on 2020-04-28).

SQL Queries (By Database) #

Different databases use slightly different functions to truncate timestamps to dates—here are the most common versions:

MySQL/MariaDB

SELECT 
    DATE(timestamp) AS timestamp,
    COUNT(DISTINCT event) AS count
FROM 
    your_table_name -- Replace with your actual table name
GROUP BY 
    DATE(timestamp)
ORDER BY 
    timestamp;

PostgreSQL

SELECT 
    DATE_TRUNC('day', timestamp)::DATE AS timestamp,
    COUNT(DISTINCT event) AS count
FROM 
    your_table_name
GROUP BY 
    DATE_TRUNC('day', timestamp)::DATE
ORDER BY 
    timestamp;

SQL Server

SELECT 
    CAST(timestamp AS DATE) AS timestamp,
    COUNT(DISTINCT event) AS count
FROM 
    your_table_name
GROUP BY 
    CAST(timestamp AS DATE)
ORDER BY 
    timestamp;

How This Works #

• Date Truncation: Functions like DATE(), DATE_TRUNC(), or CAST(... AS DATE) remove the time portion of your timestamp, leaving just the YYYY-MM-DD date. This ensures all events from the same day are grouped into one row.
• COUNT(DISTINCT event): Instead of counting every single row, this counts each unique event type once per day. For example, even though Jeff created two users on 2020-04-30, create_new_user only counts as one.
• GROUP BY: Groups the results by the truncated date, so you get a single summary row for each day.
• ORDER BY: Sorts the results chronologically to match your expected output.

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