PostgreSQL: How to get a weekly average over a year including ‘gap’ weeks per animal from years of data

datepostgresql

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…

The commented out part was a fiddle to see if I could get the yearly
average instead of the total of ALL years
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');

Best Answer

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

Related Solutions

Postgresql – How to get the current unix timestamp from PostgreSQL

In postgres, timestamp with time zone can be abbreviated as timestamptz, and timestamp without time zone as timestamp. I will use the shorter type names for simplicity.

Getting the Unix timestamp from a postgres timestamptz like now() is simple, as you say, just:

select extract(epoch from now());

That's really all you need to know about getting the absolute time from anything of type timestamptz, including now().

Things only get complicated when you have a timestamp field.

When you put timestamptz data like now() into that field, it will first be converted to a particular timezone (either explicitly with at time zone or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps as timestamp and would normally use timestamptz — maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.

If you only ever work in a single time zone you might get away with (mis)using timestamp. Conversion back to timestamptz is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:

select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz
     , '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;

/*
|timestamptz           |timestamptz           |
|:---------------------|:---------------------|
|2011-03-27 00:59:00+00|2011-03-27 02:00:00+01|
*/

_DBFiddle

But, note the following confusing behaviour:

set timezone to 0;

values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
    , (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);

/*
|column1|column2               |
|------:|:---------------------|
|      1|1970-01-01 00:00:00+00|
|      2|1970-01-01 00:00:00+00|
*/

_DBFiddle

This is because:

PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both […] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication

Postgresql – How to Restore Postgresql Cluster from data directory

Here's what I'd do:

Download postgresql-8.1_8.1.8-1.diff.gz, postgresql-8.1_8.1.8-1.dsc and postgresql-8.1_8.1.8.orig.tar.gz from http://archive.debian.org/debian/pool/main/p/postgresql-8.1/ on a machine running the same OS as what you intend to install on.
dpkg-source -x postgresql-8.1_8.1.8-1.dsc
Examine debian/rules for configure options.

This shows:

DEB_CONFIGURE_EXTRA_FLAGS := --mandir=\$${prefix}/share/postgresql/8.1/man \
                    --with-docdir=\$${prefix}/share/doc/postgresql-doc-8.1 \
                    --datadir=\$${prefix}/share/postgresql/8.1 \
                    --bindir=\$${prefix}/lib/postgresql/8.1/bin \
                    --includedir=\$${prefix}/include/postgresql/ \
                    --enable-nls \
                    --enable-integer-datetimes \
                    --enable-thread-safety \
                    --enable-debug \
                    --disable-rpath \
                    --with-tcl \
                    --with-perl \
                    --with-python \
                    --with-pam \
                    --with-krb5 \
                    --with-openssl \
                    --with-gnu-ld \
                    --with-tclconfig=/usr/lib/tcl$(TCL_VER) \
                    --with-tkconfig=/usr/lib/tk$(TCL_VER) \
                    --with-includes=/usr/include/tcl$(TCL_VER) \
                    --with-pgport=5432 \
                    $(ARCH_OPTS) \
                    CFLAGS='$(CFLAGS)' \
                    LDFLAGS='$(LDFLAGS)'

This should help you do your own source build. In particular I think you probably need --enable-integer-datetimes.

Alternately, you could dpkg-buildpackage -rfakeroot -uc -b to build the package, then copy it to the target server and install it.

Best Answer

Related Solutions

Postgresql – How to get the current unix timestamp from PostgreSQL

Postgresql – How to Restore Postgresql Cluster from data directory

Related Question