CODEWITHSUNDEEP
sqlpostgresqlselect
Moshe
Moshe

Reputation: 65

Postgres query for daily occupancy?

The table e_log has columns time_event (timestamp) and event_type ('E' or 'X' for entry or exit). There are no people overnight so on a given day the occupancy is the Es minus the Xs where time_event is that day. I'm trying to query for each day's occupancy of 2019, the result is supposed to be

Day                    Occupancy
2019-01-01         14
2109-01-02         22
.
.
.

Any suggestions? The closest is hard-coding each specific day and counting the number of entries but I am unsure of how to get the point in the day where the entries - exits is max. For example this query will always return 0 because everyone leaves by midnight - 

select sum(case when event_type ='E' then 1 else -1 end)
from e_log
where e_log.time_event > cast('2019-01-01 00:00:00' as timestamp) and
      e_log.time_event < cast('2019-01-01 23:59:00' as timestamp);

Thanks for any suggestions!

Upvotes: 1

Views: 148

Answers (2)

George Joseph
George Joseph

Reputation: 5922

Edited based on the max_occupancy at time of day

with data
  as( 
    select cast(e_log.time_event as date) day_val
           ,e_log.time_event
           ,sum(case when event_type ='E' then 1 
                     when event_type='X' then -1 
                 end) over(partition by cast(e_log.time_event as date) order by e_log.time_event) as total_occupancy
      from e_log 
     )
    ,ranked_by_max_occupancy
     as (
        select row_number() over(partition by day_val order by total_occupancy desc) as rnk
               ,*
          from data
         )
select *
  from ranked_by_max_occupancy
 where rnk=1

Updated dbfiddle link https://dbfiddle.uk/?rdbms=postgres_12&fiddle=3d924f2187373d764325ed555361a9ab

You are on the right track, just need to group it by cast(time_event as date) as follows

select cast(e_log.time_event as date)
       ,sum(case when event_type ='E' then 1 
                 when event_type='X' then -1 
             end) as total_occupancy
  from e_log 
group by cast(e_log.time_event as date)

db fiddle link https://dbfiddle.uk/?rdbms=postgres_12&fiddle=bb85ea98329829572ed1f234d90640f6

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270331

If I understand correctly I would be inclined to write this as:

select date_trunc('day', time_event) as dte,
       (count(*) filter (where event_type = 'E') -
        count(*) filter (where event_type = 'X')
       )
from e_log l
where e.time_event >= '2019-01-01'::timestamp
      e.time_event < '2020-01-01'::timestamp
group by dte
order by dte;

Upvotes: 0

Related Questions