1

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 12x3 '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]

3
  • Arbitrarily assign the beginning as 5, the mid as 15 and the end as 25? – Vérace May 6 '20 at 13:00
  • No because if I say mid june it will be from 11 till 20, right? At least this is what I want :). The reason I am using roughly 10 days is that per year it fluctuates depending on weather conditions. – Bastiaan Wakkie May 6 '20 at 15:23
  • Have you looked at PostgreSQL's interval types? – Vérace May 6 '20 at 16:37
1

One thing I can think of, is to generate a date range from the input date that splits the month into three parts. It's quite easy to keep the first two parts constant and have the third one vary:

create function get_first(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date, date_trunc('month', p_input)::date + 10, '[]')
$$
language sql
immutable;

create function get_second(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date + 11, date_trunc('month', p_input)::date + 20, '[]')
$$
language sql
immutable;

create function get_third(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date + 21, (date_trunc('month', p_input) + interval '1 month')::date, '[)')
$$
language sql
immutable;

With that you can do the following aggregation:

select animal,
       count(*) filter (where get_first(from_date) @> from_date) as start,
       count(*) filter (where get_second(from_date) @> from_date) as mid,
       count(*) filter (where get_third(from_date) @> from_date) as "end"
from occurrence
group by animal;

The three functions can be enhanced/changed to deal with the variable length of a month, but that would require much more complex calculations. It would boil down to making the +1 or +20 a percentage of the number of days in the month.

If you need to find the highest of those three parts, you can unpivot the values and then using distinct on() to get it:

select distinct on (x.animal) x.*
from (
  select animal,
         to_char(from_date, 'yyyy-mm') as month,
         count(*) filter (where get_first(from_date) @> from_date) as start,
         count(*) filter (where get_second(from_date) @> from_date) as mid,
         count(*) filter (where get_third(from_date) @> from_date) as "end"
  from occurrence
  group by animal, month
) t
  cross join lateral (
    values (animal, start, month, 'start'), (animal, mid, month, 'mid'), (animal, "end", month, 'end')
  ) x (animal, num, month, section) --on true 
order by x.animal, num desc

The cross join lateral essentially turns the three columns into rows and then distinct on () is used to pick the highest value of the three for each animal

Online example

8
  • Thanks for suggesting date range. I don't understand the outcome though. I would expect the peak to fall somewhere between the start en end moments. Perhaps I can use the range types to find the moment in the year with the highest count? – Bastiaan Wakkie May 8 '20 at 15:38
  • It counts the number of occurrences per each third of the month. That's how I understood the "then count per species per month section" from your question (third = month section) – a_horse_with_no_name May 8 '20 at 15:41
  • I see yes I get your solution now. I tried to explain it a bit better in the EDIT1 section. So basically I need to find the period (1/3 of a month) with the highest count. In my below answer I am I little bit further where the peak column is missing still. – Bastiaan Wakkie May 8 '20 at 19:41
  • @BastiaanWakkie: you can get the highest of those three values from that query. See my edit – a_horse_with_no_name May 8 '20 at 20:42
  • Thanks for taking the effort so far, really appreciated! I am missing the month part in your answer though. I have the feeling I need to use generate_series 12x3 to find out in what period the highest count is found. No? – Bastiaan Wakkie May 9 '20 at 7:54
0

I do not want to change my question above

From my example set ...

  (...                 ),
  ('dog',1,'1865-05-24'),
  ('dog',11,'1869-05-02'),
  ('dog',1,'1869-05-06'),
  ('dog',1,'1869-05-12'),
  ('cat',1,'1870-01-01'),
  ('dog',2,'1872-06-06'),
  ('dog',1,'1876-04-24'),
  ('dog',1,'1882-05-01'),
  ('dog',1,'1883-04-30'),
  (...                 )

I have a partly working solution now ...

SELECT animal, count(*), 
min(cast(concat(to_char(from_date,'mm'),'.',lpad(to_char(from_date,'dd'),2,'0')) as numeric)),
max(cast(concat(to_char(from_date,'mm'),'.',lpad(to_char(from_date,'dd'),2,'0')) as numeric)),
   concat(
   'From ',
       CASE  
           WHEN cast(split_part(cast(min(cast(concat(to_char(from_date,'mm'),'.',lpad(to_char(from_date,'dd'),2,'0')) as numeric)) as text),'.',2) as integer) <= 10 THEN 'beginning'
           WHEN cast(split_part(cast(min(cast(concat(to_char(from_date,'mm'),'.',lpad(to_char(from_date,'dd'),2,'0')) as numeric)) as text),'.',2) as integer) <= 20 THEN 'mid'
           WHEN cast(split_part(cast(min(cast(concat(to_char(from_date,'mm'),'.',lpad(to_char(from_date,'dd'),2,'0')) as numeric)) as text),'.',2) as integer) <= 31 THEN 'end'
       END
       , ' of '
       , TO_CHAR(TO_DATE (cast(split_part(cast(min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',1) as integer)::text, 'MM'), 'FMmonth' )
   , ' till ',
       CASE  
           WHEN cast(split_part(cast(max(cast(concat(to_char(from_date,'mm'),'.',lpad(to_char(from_date,'dd'),2,'0')) as numeric)) as text),'.',2) as integer) <= 10 THEN 'beginning'
           WHEN cast(split_part(cast(max(cast(concat(to_char(from_date,'mm'),'.',lpad(to_char(from_date,'dd'),2,'0')) as numeric)) as text),'.',2) as integer) <= 20 THEN 'mid'
           WHEn cast(split_part(cast(max(cast(concat(to_char(from_date,'mm'),'.',lpad(to_char(from_date,'dd'),2,'0')) as numeric)) as text),'.',2) as integer) <= 31 THEN 'end'
       END
       , ' of '
       , TO_CHAR(TO_DATE (cast(split_part(cast(max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',1) as integer)::text, 'MM'), 'FMmonth' )
   ,'.')

   FROM occurrence 
   WHERE to_char(from_date,'mm/dd') not in ('12/31','01/01','1/1') -- these are dates are probably unknown but the yeary
GROUP BY animal
ORDER BY animal

NOTE1: there slipped a huge error in by transferring the month.day to a numeric value to order: april the 4th> 4.4 and april the 30th > 4.30 which order wrong! So I had to add a leading '0' to the days: lpad(date_part('day',from_date)::char,2,'0')) and then april the 4th> 4.04 and april the 30th > 4.30 which does order correctly.

NOTE2: I do not know why but I could not use the case expression when ... structure]

... but I have the feeling it could be shorter? online example

current result:

animal count min max concat bird 113 4.08 9.16 From beginning of april till mid of september. cat 70 3.14 7.29 From mid of march till end of july. dog 96 4.04 9.22 From beginning of april till end of september.

I am still missing the peaks: the period (combination month.day without year) with the highest count.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.