4 ms·
I noticed some funny stuff about the data, but I haven't tracked down the source. I'd expect the entry/exit counts to increase over the course of the day, and
by ibejoeb 13y ago
I noticed some funny stuff about the data, but I haven't tracked down the source. I'd expect the entry/exit counts to increase over the course of the day, and they generally do. Sometimes, though, the latest record doesn't have the greatest number. I suspect it either odd cutoff times, i.e., not aligned to the day.
If anyone wants to play with the data, here's some stuff to start with PostgreSQL:
create table stats (
ca varchar(10),
unit varchar(10),
scp varchar(10),
dt timestamp,
"desc" varchar(20),
entries integer,
exits integer
);
copy stats from '<path>/output.csv' delimiter ',' csv header;
Here's query that will show the entire set of exit counts align with both their greatest and latest values:
select
unit, scp, dt, exits,
max(exits) over (
partition by unit, scp, date_trunc('day', dt)
rows between unbounded preceding and unbounded following
) as largest_exits,
last_value(exits) over (
partition by unit, scp, date_trunc('day', dt)
order by dt
rows between unbounded preceding and unbounded following
) as latest_exits
from stats
order by 1, 2, 3;
If you want to see the discrepancies I described above, just wrap it up and find where latest <> greatest:
with x as (
select
unit, scp, dt, exits,
max(exits) over (
partition by unit, scp, date_trunc('day', dt)
rows between unbounded preceding and unbounded following
) as largest_exits,
last_value(exits) over (
partition by unit, scp, date_trunc('day', dt)
order by dt
rows between unbounded preceding and unbounded following
) as latest_exits
from stats
)
select *
from x
where largest_exits <> latest_exits
order by unit, scp, dt;