如果时间戳有小时，则每天计算唯一值

axkjgtzd 于 2021-07-15 发布在 ClickHouse

关注(0)|答案(2)|浏览(434)

我有数据集：

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

如何编写sql查询来统计每天所有唯一的事件。我不清楚的是时间戳中的小时数。预期结果如下所示：

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

sql clickhouse

来源：https://stackoverflow.com/questions/61504990/count-unique-values-per-day-if-timestamp-has-hours

2条答案

按热度按时间

mitkmikd1#

我认为clickhouse的语法是：

select distinct toDate(timestamp), event
from t;

编辑：
如果要计算事件数，请使用 count(distinct) :

select toDate(timestamp), count(distinct event)
from t
group by toDate(timestamp);

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

nkhmeac62#

create table xx(timestamp DateTime64(3), event String, user String) Engine=Memory;
insert into xx values
('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')
SELECT
    toDate(timestamp) AS d,
    uniq(event)
FROM xx
GROUP BY d
┌──────────d─┬─uniq(event)─┐
│ 2020-04-28 │           3 │
│ 2020-04-29 │           2 │
│ 2020-04-30 │           3 │
└────────────┴─────────────┘

展开查看全部

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

我来回答

如果时间戳有小时，则每天计算唯一值

2条答案

相关问题

热门标签

最新问答