Re: Awkward Join between generate_series and long table

On Wed, Nov 8, 2023 at 6:26 PM Lincoln Swaine-Moore <lswainemoore(at)gmail(dot)com>
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.