mysql - 每天都有新事件计数

Question

我有一个表 Events，它有 2 列 dt(日期)和 event。我正在尝试获取每天的新事件计数。例如如果事件出现在今天和明天，那么它应该只计入今天(因为它是第一次显示)并且应该从明天的计数中排除。

+------------+-------+
| dt         | event |
+------------+-------+
| 2019-02-01 | u1    |
| 2019-02-01 | u2    |
| 2019-02-01 | u3    |
| 2019-02-01 | u4    |
| 2019-02-01 | u1    |
| 2019-02-02 | u5    |
| 2019-02-02 | u1    |
| 2019-02-02 | u6    |
| 2019-02-02 | u4    |
| 2019-02-03 | u8    |
| 2019-02-03 | u9    |
| 2019-02-03 | u6    |
+------------+-------+

我正在尝试获得以下输出:

+------------+-------------------------+
| dt         | count(distinct c.event) |
+------------+-------------------------+
| 2019-02-01 |                       4 |
| 2019-02-02 |                       2 |
| 2019-02-03 |                       2 |
+------------+-------------------------+

我使用的查询是:

with cte (dt,event,rnk) as
(
select dt, 
event,
row_number() over (partition by dt order by event) as rnk
from events order by event
)
select e.dt, count(distinct c.event) from events e left join cte c
on  e.event <> c.event where e.dt = c.dt
group by 1;

但是我无法过滤掉前一天已经出现的事件。我相信缺少一些小东西。我的查询结果是:

+------------+-------------------------+
| dt         | count(distinct c.event) |
+------------+-------------------------+
| 2019-02-01 |                       4 |
| 2019-02-02 |                       4 |
| 2019-02-03 |                       3 |
+------------+-------------------------+

我不确定 LEAD/LAG 功能是否可以帮助解决问题。还有一种方法可以在不使用任何连接的情况下实现这一目标。

表创建和插入查询:

create table events (dt Date, event varchar(5));

insert into events values('2019-02-01', 'u1');
insert into events values('2019-02-01',  'u2');
insert into events values('2019-02-01',  'u3');
insert into events values('2019-02-01',  'u4');
insert into events values('2019-02-01',  'u1');
insert into events values('2019-02-02',  'u5');
insert into events values('2019-02-02',  'u1');
insert into events values('2019-02-02',  'u6');
insert into events values('2019-02-02',  'u4');
insert into events values('2019-02-03',  'u8');
insert into events values('2019-02-03',  'u9');
insert into events values('2019-02-03',  'u6');

Answer 1

您可以使用 row_number 查找事件出现的第一天是什么时候。

SQL DEMO

WITH ranks as (
    SELECT *, row_number() over (partition by event order by dt) as rn
    FROM events
) 
SELECT dt, COUNT(event)
FROM ranks
WHERE rn = 1 
GROUP BY dt

输出

|         dt | count |
|------------|-------|
| 2019-02-01 |     4 |
| 2019-02-02 |     2 |
| 2019-02-03 |     2 |