找到每个名字的最小时间戳,然后计算每个时间戳中有多少个名字
select timestamp, count(*) as new_names from
(select name, min(timestamp) as timestamp from mytable
group by name)
group by timestamp
order by timestamp
包括所有日子,即使没有任何名字
select t.timestamp, nvl(new_names,0) as new_names from
(select timestamp, count(*) as new_names from
(select name, min(timestamp) as timestamp from mytable
group by name)
group by timestamp) c
RIGHT OUTER JOIN (select distinct timestamp from mytable) t
ON c.timestamp = t.timestamp
order by t.timestamp
要包含根本没有出现在表中的日期,您需要在某处有一个日历中的日期列表,然后将该表而不是我有 RIGHT OUTER JOINed 的子查询
你可以这样做
select t.timestamp, nvl(new_names,0) as new_names from
(select timestamp, count(*) as new_names from
(select name, min(timestamp) as timestamp from mytable
group by name)
group by timestamp) c
RIGHT OUTER JOIN (
SELECT TRUNC (SYSDATE - ROWNUM - 1) dt
FROM DUAL CONNECT BY ROWNUM < 366
) t
ON c.timestamp = t.timestamp
order by t.timestamp
但是您必须将 -1 和 366 调整为您想要的日期范围,并且使用数据库中已经存在的日历更加标准
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句