Re: Incremental and decremental count based on a date
| От | David G. Johnston |
|---|---|
| Тема | Re: Incremental and decremental count based on a date |
| Дата | |
| Msg-id | CAKFQuwZuCKwamMzhNHMCL6ErgH4XhpUE2VbOhyezYpZsKiy3VA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Incremental and decremental count based on a date (koimoi <s.raddy.89@gmail.com>) |
| Ответы |
Re: Incremental and decremental count based on a date
|
| Список | pgsql-novice |
Name Start_date end_date
aaa 01/02/2017 05/03/2017
bbb 03/05/2017 07/07/2017
ccc 02/01/2017 10/09/2017
I want to write a query that calculates the number of people who exist in
the DB in a certain month/year.
Answer:
Jan 2017 1
Feb 2017 2
Mar 2017 3
Apr 2017 3
May 2017 2 (one person - aaa ,ended in May 2017)
Jun 2017 2
Jul 2017 1 (bbb ended in July 2017)
How do I write a PSQL query to get the desired output?
The generate_series and to_char functions will get you a table containing all the desired year/month values - and should include the "last day" of each the month. You can join that table against the data above:
year_month LEFT JOIN data ON (year_month.last_day_of_month BETWEEN data.start_date AND data.end_date)
Then just GROUP BY and count(name)
You'd use "first_day_of_month" if you want to count partial months, computing the last day of a month is trickier but doable (usually one computes the first day of the next month and then subtracts one day).
David J.
В списке pgsql-novice по дате отправления: