postgresql max（count（*）） - php

Question

I have a problem in postgresql. I have one cohorte (gathering of people) and i would like counting the persons in this cohorte.

Begin date : "2014-09-01", End date : "2014-11-30".

• I have 5 persons between 09/01 and 09/22
• I have 5 persons between 09/20 and 09/25
• I have 5 persons between 09/26 and 10/05
• I have 5 persons between 10/01 ans 11/30

I want to have the max of accommodation for each month between the begin date and the end date in SQL (or PHP). Expected max person count:

• September(09) => 10
• October(10) => 10
• November(11) => 5

Answer 1

can't you use window function? I'd try something like this (I've not tested this code, just exposed my thoughts)

SELECT max(count) FROM (
  SELECT count(*) OVER (PARTITION BY ???) as count
  FROM contract
  WHERE daterange(dateStart, dateEnd, '[]') && daterange('2014-09-01', '2014-10-01', '[)')
) as max

Here, my problem remains that I can't find a way to partition for each day of the interval. Maybe this is a wrong approach, but I would be interested by a solution based on windows.

---

edit: with this request, you have the max of simultaneous present, but over all the time, not only a given month

with presence as (
  SELECT id, generate_series(begin_date, end_date, '1 day'::interval) AS date
  FROM test
),
presents as (
  SELECT count(*) OVER (PARTITION BY date) AS count
  FROM presence
)
SELECT max(count) from presents;

---

Here we come, I think

Imagine your person table has 3 columns :

• id
• entrance_date
• leaving_date

the request would look like

WITH presents as (
  SELECT id, 
         daterange(entrance_date, leaving_date, '[]') * daterange('2014-09-01', '2014-11-30', '[]') as range
  FROM person
  WHERE daterange(entrance_date, leaving_date, '[]') && daterange('2014-09-01', '2014-11-30', '[]')
),
present_per_day as (
  SELECT id,
         generate_series(lower(range), upper(range), '1 day'::interval) AS date
  FROM presents
),
count_per_day as (
  SELECT count(*) OVER (PARTITION BY date) AS count,
         date
  FROM present_per_day
),
SELECT max(count) OVER (PARTITION BY date_part('year', date), date_part('month', date)) as max,
       date_part('year', date),
       date_part('month', date)
FROM count_per_day;

(I have to leave, I hope I'll have time to test it later)

In fact, @erwin solution is much much more easy and efficient than this one.