1

I have a lot of occurrences from different animals (see below) from many many years.

I am trying to figure out how to get the average occurrence per week on a year scale per animal. I eventually want to use the data to create a graph that shows when you can see the animal during the year.

I came up with the following sql but I got stuck as it does not fill the 'gap' weeks as I hoped. I am almost there I feel, but...

  1. The commented out part was a fiddle to see if I could get the yearly average instead of the total of ALL years
  2. I am missing the 'gap' weeks from my generate_series. Now they are generated over all animals. I miss some understanding here.

My fiddle is here

(There is also a bias in the data so that dec-31 and jan-1 should be skipped as only the year was known and data had to be entered as 'date')

    --WITH occurrence as (
    SELECT name, w.week, count(total) as total
    FROM  ( SELECT generate_series(1,53,1) ) w(week)
    LEFT JOIN occurrence i ON w.week = cast(date_part('week',i.from_date) as integer)
    AND TO_CHAR(from_date :: DATE, 'dd/mm') NOT IN ('31/12','01/01')
    AND name IS NOT NULL
    GROUP  BY name,w.week
    ORDER  BY name,w.week;
    --)
    --SELECT o.week, ROUND(AVG(o.total))
    --FROM occurrence o
    --GROUP BY o.week

Will give me the following shortened result:

name    week    total
bird    15      2
bird    16      2
...
bird    30      1
bird    38      1
cat     11      1
cat     14      1
...
cat     30      4
dog     14      1
dog     15      2
...
dog     33      1
dog     38      1
(null)  1       0
...
(null)  53      0

the test sql:

CREATE TABLE occurrence (name text,total int,from_date date);
INSERT INTO occurrence (name,total,from_date) VALUES
('cat',1,'1800-01-01'),
('bird',1,'1846-06-13'),
('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'),
('dog',1,'1884-05-11'),
('cat',1,'1884-06-22'),
('dog',1,'1885-04-23'),
('cat',1,'1885-07-08'),
('cat',1,'1885-07-10'),
('dog',1,'1890-05-25'),
('dog',1,'1894-05-18'),
('bird',1,'1894-06-16'),
('bird',1,'1894-06-16'),
('cat',1,'1895-05-14'),
('dog',1,'1896-06-01'),
('dog',1,'1900-05-24'),
('dog',1,'1903-05-27'),
('cat',1,'1905-03-14'),
('bird',3,'1905-04-23'),
('dog',1,'1905-05-19'),
('dog',1,'1905-05-19'),
('dog',1,'1905-05-19'),
('dog',3,'1905-06-12'),
('cat',1,'1905-06-27'),
('dog',1,'1905-06-29'),
('bird',1,'1906-05-15'),
('bird',1,'1906-05-15'),
('bird',1,'1906-05-15'),
('bird',1,'1907-05-12'),
('bird',1,'1907-05-12'),
('bird',1,'1907-05-12'),
('bird',1,'1909-06-21'),
('bird',1,'1909-06-21'),
('bird',3,'1910-05-14'),
('bird',4,'1910-05-21'),
('bird',1,'1910-05-21'),
('dog',3,'1910-05-22'),
('dog',1,'1910-05-27'),
('dog',1,'1910-05-27'),
('dog',1,'1910-05-27'),
('dog',1,'1912-05-28'),
('dog',1,'1912-06-11'),
('dog',1,'1913-05-12'),
('dog',1,'1913-05-12'),
('bird',2,'1913-05-14'),
('bird',2,'1913-05-14'),
('dog',1,'1914-01-01'),
('cat',3,'1914-07-03'),
('cat',1,'1914-07-06'),
('cat',1,'1916-05-28'),
('dog',1,'1916-05-29'),
('dog',1,'1916-05-29'),
('dog',1,'1916-05-29'),
('cat',1,'1916-06-14'),
('bird',1,'1916-06-18'),
('dog',1,'1917-05-01'),
('dog',1,'1917-05-25'),
('dog',1,'1918-01-01'),
('dog',3,'1918-05-20'),
('dog',1,'1919-05-24'),
('dog',1,'1919-05-25'),
('dog',2,'1919-05-29'),
('bird',1,'1919-06-09'),
('bird',1,'1919-06-09'),
('dog',2,'1920-05-20'),
('bird',1,'1920-05-29'),
('bird',1,'1920-05-29'),
('dog',1,'1921-05-05'),
('dog',1,'1921-05-09'),
('dog',1,'1922-05-08'),
('cat',1,'1922-06-15'),
('dog',1,'1922-07-02'),
('dog',1,'1923-04-04'),
('dog',1,'1923-05-29'),
('bird',1,'1923-06-25'),
('dog',1,'1924-01-01'),
('dog',1,'1924-05-17'),
('bird',1,'1925-04-08'),
('bird',1,'1925-04-08'),
('bird',1,'1926-06-28'),
('bird',2,'1927-05-21'),
('bird',2,'1927-05-21'),
('bird',1,'1927-05-26'),
('dog',1,'1928-06-12'),
('cat',1,'1931-05-23'),
('dog',1,'1932-05-16'),
('bird',2,'1932-06-15'),
('bird',1,'1932-06-19'),
('bird',3,'1932-06-19'),
('bird',1,'1933-07-22'),
('cat',1,'1934-06-02'),
('bird',1,'1934-06-04'),
('bird',1,'1934-06-05'),
('bird',1,'1934-06-08'),
('bird',1,'1934-06-09'),
('cat',1,'1934-06-10'),
('bird',1,'1934-06-13'),
('bird',1,'1937-05-29'),
('cat',1,'1937-06-07'),
('bird',1,'1938-07-03'),
('cat',1,'1938-07-07'),
('dog',1,'1939-01-01'),
('dog',3,'1939-05-09'),
('dog',1,'1939-05-21'),
('dog',1,'1939-05-23'),
('dog',1,'1939-05-29'),
('cat',1,'1939-06-11'),
('bird',1,'1939-06-11'),
('dog',2,'1940-05-03'),
('dog',3,'1940-05-03'),
('dog',1,'1941-05-16'),
('cat',1,'1942-07-12'),
('cat',1,'1942-07-13'),
('dog',1,'1943-04-26'),
('dog',1,'1943-05-09'),
('bird',1,'1943-05-13'),
('dog',1,'1944-05-06'),
('dog',1,'1944-05-06'),
('cat',1,'1945-07-02'),
('dog',1,'1945-07-03'),
('dog',1,'1946-04-12'),
('dog',1,'1946-05-08'),
('bird',1,'1946-05-12'),
('bird',1,'1946-05-12'),
('bird',2,'1946-05-12'),
('bird',1,'1946-07-25'),
('bird',1,'1947-06-01'),
('cat',1,'1947-06-19'),
('bird',1,'1949-04-29'),
('dog',1,'1949-05-04'),
('bird',1,'1949-05-15'),
('bird',1,'1949-06-05'),
('cat',2,'1949-07-16'),
('bird',3,'1950-04-17'),
('cat',1,'1950-06-24'),
('dog',1,'1951-05-08'),
('bird',1,'1956-05-27'),
('cat',1,'1957-06-07'),
('dog',1,'1958-01-01'),
('bird',1,'1958-06-25'),
('bird',1,'1958-07-11'),
('cat',1,'1958-07-20'),
('bird',15,'1960-05-22'),
('bird',1,'1960-05-22'),
('dog',1,'1960-05-29'),
('dog',1,'1960-06-11'),
('cat',2,'1960-06-26'),
('dog',0,'1960-09-22'),
('dog',1,'1961-04-12'),
('cat',1,'1961-06-22'),
('bird',1,'1961-07-16'),
('cat',1,'1962-06-29'),
('cat',2,'1962-07-06'),
('bird',1,'1963-05-23'),
('bird',1,'1963-06-21'),
('bird',1,'1963-06-25'),
('dog',2,'1963-07-17'),
('dog',0,'1963-07-17'),
('cat',1,'1964-04-02'),
('bird',1,'1964-05-17'),
('dog',1,'1964-05-26'),
('bird',1,'1964-06-05'),
('bird',1,'1964-06-08'),
('bird',1,'1964-06-15'),
('cat',1,'1964-06-26'),
('bird',1,'1964-07-01'),
('cat',1,'1964-07-09'),
('bird',1,'1965-06-11'),
('bird',1,'1965-07-11'),
('dog',1,'1966-05-16'),
('cat',1,'1966-06-18'),
('cat',1,'1966-07-01'),
('dog',0,'1966-08-08'),
('dog',0,'1966-08-18'),
('dog',1,'1967-01-01'),
('dog',1,'1967-05-05'),
('bird',1,'1967-05-07'),
('dog',1,'1967-05-08'),
('bird',1,'1967-05-30'),
('bird',1,'1967-06-18'),
('bird',1,'1967-06-25'),
('dog',1,'1967-06-27'),
('bird',1,'1967-06-28'),
('cat',1,'1967-07-16'),
('cat',1,'1967-07-16'),
('dog',1,'1968-04-24'),
('bird',1,'1968-05-16'),
('bird',1,'1968-05-23'),
('bird',1,'1968-05-25'),
('dog',2,'1968-05-30'),
('cat',1,'1968-06-19'),
('dog',1,'1969-05-08'),
('dog',2,'1969-05-14'),
('bird',1,'1969-06-07'),
('bird',1,'1969-06-08'),
('bird',1,'1969-06-08'),
('bird',3,'1969-06-08'),
('bird',2,'1969-06-11'),
('bird',1,'1969-06-11'),
('bird',1,'1969-06-29'),
('bird',1,'1969-06-29'),
('cat',1,'1969-07-21'),
('cat',1,'1969-07-21'),
('bird',3,'1970-05-24'),
('bird',3,'1970-05-24'),
('bird',2,'1970-06-07'),
('bird',2,'1970-06-13'),
('bird',1,'1970-06-24'),
('bird',0,'1970-09-16'),
('dog',1,'1971-04-22'),
('dog',1,'1971-05-04'),
('dog',1,'1971-05-05'),
('dog',1,'1971-05-05'),
('dog',1,'1971-05-06'),
('bird',1,'1972-05-06'),
('bird',5,'1972-05-07'),
('cat',1,'1972-05-19'),
('bird',1,'1972-05-19'),
('bird',1,'1972-05-20'),
('dog',1,'1972-05-24'),
('cat',1,'1972-07-29'),
('bird',1,'1973-05-05'),
('bird',1,'1973-05-06'),
('dog',2,'1973-05-17'),
('bird',1,'1973-05-18'),
('dog',1,'1973-05-21'),
('dog',1,'1973-05-28'),
('cat',1,'1973-06-24'),
('bird',4,'1974-05-16'),
('dog',1,'1974-05-23'),
('dog',4,'1974-05-24'),
('cat',1,'1974-06-16'),
('cat',3,'1974-06-16'),
('bird',1,'1974-07-04'),
('cat',1,'1975-05-25'),
('cat',2,'1975-05-25'),
('cat',1,'1975-06-23'),
('cat',1,'1975-06-23'),
('cat',1,'1975-07-02'),
('bird',2,'1975-07-08'),
('bird',12,'1976-05-23'),
('bird',12,'1976-05-23'),
('bird',4,'1976-05-30'),
('bird',4,'1976-05-30'),
('cat',1,'1976-07-20'),
('dog',1,'1977-01-01'),
('dog',1,'1977-04-26'),
('cat',1,'1977-05-09'),
('bird',3,'1977-06-06'),
('bird',7,'1977-06-06'),
('bird',3,'1977-06-07'),
('bird',1,'1977-06-07'),
('bird',6,'1977-06-07'),
('bird',1,'1977-06-09'),
('cat',2,'1977-06-20'),
('cat',3,'1977-06-20'),
('cat',2,'1977-06-22'),
('cat',1,'1977-06-22'),
('cat',1,'1977-06-24'),
('cat',1,'1977-06-24'),
('cat',1,'1977-07-21'),
('bird',1,'1978-05-18'),
('bird',4,'1978-05-18'),
('dog',1,'1978-05-27'),
('cat',1,'1978-05-28'),
('cat',1,'1978-05-28'),
('dog',1,'1978-05-30'),
('cat',2,'1978-05-30'),
('dog',1,'1978-05-31'),
('dog',3,'1978-06-01'),
('bird',1,'1978-06-15'),
('cat',1,'1978-06-15'),
('cat',4,'1978-06-15'),
('bird',1,'1978-06-22'),
('cat',2,'1978-06-22'),
('cat',2,'1978-06-23'),
('cat',10,'1978-06-24'),
('cat',1,'1978-07-06'),
('cat',1,'1978-07-06'),
('cat',1,'1978-07-09'),
('cat',1,'1978-07-09');
1

you had a scaffoold for the week numbers, that was a good start, but you also need a scaffold for the names, then you can left join both scaffolds and get a result that contains zeroes.

SELECT b.name, w.week, count(total) as total
FROM  generate_series(1,53,1) w(week)
CROSS JOIN ( values ('cat'),('dog'),('bird') ) b(name) 
LEFT JOIN occurrence i ON w.week = cast(date_part('week',i.from_date) as integer)
   AND TO_CHAR(from_date :: DATE, 'dd/mm') NOT IN ('31/12','01/01')
   AND i.name = b.name 
GROUP  BY b.name,w.week
ORDER  BY b.name,w.week;

http://sqlfiddle.com/#!17/c96d0/5

Here I used inline values for b but if you have a table with all the names you could use that instead. or you could use (select distinct name from occurrence) as b

4
  • Thank you @Jasen! I indeed had to use the select distinct. I must admit I still do not see how this is working but it does. (I am a visual person) Feb 17 '20 at 6:38
  • I changed the COUNT(total) into AVG(total) as this is what I was looking for. Somehow i get NULL value for the 'empty' weeks. Feb 18 '20 at 13:27
  • Any idea how this is possible? I ended up with CAST(ROUND(COUNT(total)) as INTEGER) at the end so I get back 0's Feb 18 '20 at 13:35
  • 1
    Note that FROM ( SELECT generate_series(1,53,1) ) w(week) can be simplified to FROM generate_series(1,53,1) w(week) May 8 '20 at 7:23

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.