In my last post PostgreSQL: How to get a weekly average over a year including 'gap' weeks per animal from years of data? with the fiddle http://sqlfiddle.com/#!17/0bff3/1
I got a weekly count. But it was difficult to graph.
I decided to go a different route. doing the month is 3 parts (beginning, middle and end)
My new objective: (and I won't graph this)
|animal|start |peak |end | |dog |beginning of may |mid of may|end of june| |cat |beginning of march|end of may|mid of june|
I was thinking of using a case like structure to assign a part of the month… something like the following…
SELECT animal, count(*), min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as start, max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as end FROM brachyopa_cleaned bc AND concat(from_month,'/',from_day) not in ('12/31','01/01','1/1') -- dates where month and day are unknown GROUP BY animal ORDER BY aminal
… and then find per species per month section the amount of occurrences to find the month section (there are in fact 12×3 'mm-dd' sections in a year) with the highest count.
Can someone give me some pointers how to do this?
[EDIT1: Changed the SELECT in what I am using so far, now I need somehow to find the peak, the 10 days period where there is the max count]
[EDIT2: rewrote the …per specie per month… section]