How to get the count of new unique ip address logged in to the website on each day using analytical-CodePudding

Ex:

Date - up, 
1/2 - 1.1.127.0 ,
1/3 - 1.1.127.1, 
1/3 - 1.1.127.0,
1/4 - 1.1.127.3,
1/4 - 1.1.127.5,
1/5 - 1.1.127.3,

Output:

Date-count,
1/2 - 1,
1/3 - 1,
1/4 - 2,
1/5  -0

New and unique ip logged in in each day

CodePudding user response：

You want to count how many IPs exist for a date that have not occurred on a previous date. You want to use analytic functions for this.

The number of new IDs is the total number of distinct IDs on a date minus the number of the previous date. In order to get this, first select the running count per row. Then aggregate per date to get the distinct number of IDs per date. Then use LAG to get the difference per day.

select
  date,
  max(cnt) - lag(max(cnt)) over (order by date) as new_ips
from
(
  select date, count(distinct ip) over (order by date) as cnt
  from mytable
) running_counts
group by date
order by date;

The same without analytic functions, which is probably more readable:

select date, count(distinct ip) as cnt
from mytable
where not exists
(
  select null
  from mytable before
  where before.date < mytable.date
  and before.id = mytable.id
)
group by date
order by date;

The DISTINCT in this latter query is not necessary, if there can be no duplicates (two rows with the same date and IP) in the table.

CodePudding user response：

You can also use below solution using left join.

with t (dt, ip) as ( 
select to_date( '1/2', 'MM/DD' ), '1.1.127.0' from dual union all
select to_date( '1/3', 'MM/DD' ), '1.1.127.1' from dual union all 
select to_date( '1/3', 'MM/DD' ), '1.1.127.0' from dual union all
select to_date( '1/4', 'MM/DD' ), '1.1.127.3' from dual union all
select to_date( '1/4', 'MM/DD' ), '1.1.127.5' from dual union all
select to_date( '1/5', 'MM/DD' ), '1.1.127.3' from dual
)

select t.DT, count( decode(t2.IP, null, 1, null) ) cnt
from t 
left join t t2
on ( t2.DT < t.DT and t2.IP = t.IP )
group by t.DT
order by 1
;

demo