Joining per group in group by

Question

I have a table with some animal sightings data where each sighting entry has a region field. There can be 0 to many entries per region per day.

I need to create a query where there is one row per day (all days even without sightings) per region. So I thought to join the table with a generate_series command like so:

WITH dates AS (
    SELECT
        MAX(DATE(sighting_time)) AS max_date,
        MIN(DATE(sighting_time)) AS min_date
    FROM sightings
)
SELECT
    generate_series(dates.min_date::date, dates.max_date::date, '1 day') as gen_date
FROM dates

and do a left join with the original table. This does fill in the gaps where there are no entries for either region but I need it to fill in the date for each region that doesn't have an entry for that day, basically an empty row for that region maybe even with the region name still there.

I could also create separate queries that explicitly filter for each region and then union them but I do not want to have to hard code the regions, this should work even if we add more regions.

Any help is appreciated, thanks!

Answer 1

I'm not entirely sure what you want your output to look like from your question. And I'm pretty new to postgresql (and db fiddle) but I'm trying to learn here, so this is what I came up with based on your question.

WITH
    DATES AS
        (   SELECT  GENERATE_SERIES(MIN(SIGHTING_TIME)::DATE,MAX(SIGHTING_TIME)::DATE, '1 day') AS GEN_DATE
            FROM    SIGHTINGS
         ),
    REGIONS AS
        (   SELECT  DISTINCT
                    REGION
            FROM    SIGHTINGS
         )
SELECT  B.GEN_DATE,
        B.REGION,
        S.SIGHTING_TIME
FROM    (   SELECT  GEN_DATE,
                    REGION
            FROM    DATES
            CROSS JOIN
                    REGIONS
         ) B
LEFT JOIN
        SIGHTINGS S
ON      B.GEN_DATE = S.SIGHTING_TIME::DATE
AND     B.REGION = S.REGION
ORDER BY    B.GEN_DATE,
            B.REGION,
            S.SIGHTING_TIME;

I have a db fiddle here if you want to check it out.

But this gets you a row for each date and region, regardless if there is data for that region on a particular day, which I think is what you want?