Re: Awkward Join between generate_series and long table

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Wed, Nov 8, 2023 at 6:26 PM Lincoln Swaine-Moore <lswainemoore@xxxxxxxxx> wrote:

            SELECT

                s at time zone 'utc'  AS period_start,
                LEAD(s) OVER (
                    ORDER BY
                        s
                )   at time zone 'utc' AS period_end

Maybe doesn't help overall but this can be equivalently written as:

s + '1 day'::interval as period_end

Resorting to a window function here is expensive waste, the lead() value can be computed, not queried.

 

SELECT
    p.period_start,
    p.period_end,
    COUNT (distinct d.id)
FROM
    periods p
    LEFT JOIN data d
    ON
        d.timestamp >= (p.period_start)
        AND d."timestamp" < (p.period_end)
        AND d.sn = 'BLAH'

This seems better written (semantically, not sure about execution dynamics) as:

FROM periods AS p

LEFT JOIN LATERAL (SELECT count(distinct? d.id) FROM data AS d WHERE d.timestamp >= p.period_start AND d.timestamp < p.period_end AND d.sn = 'BLAH') AS cnt_d

-- NO grouping required at this query level

David J.