public inbox for [email protected]
help / color / mirror / Atom feedFrom: Samed YILDIRIM <[email protected]>
To: Steve Midgley <[email protected]>
Cc: James Cloos <[email protected]>
Cc: [email protected]
Subject: Re: help with a particular multi-table query
Date: Thu, 4 Apr 2024 13:18:29 +0300
Message-ID: <CAAo1mbk8GOjtOTphuy_OHwsQL5xvjvFxJP8T-7apxptrZ0-Z7A@mail.gmail.com> (raw)
In-Reply-To: <CAJexoS+gyE6-GqRqwZ+e79dX0JSi8KOYKLFiaJNVC1L0mNMhTA@mail.gmail.com>
References: <[email protected]>
<CAJexoS+gyE6-GqRqwZ+e79dX0JSi8KOYKLFiaJNVC1L0mNMhTA@mail.gmail.com>
Hi James,
I guess you are looking for something like this.
WITH cte_1 AS (
SELECT
t1."date"::date as t1_date,
lag(t1."date"::date,1) OVER (ORDER BY t1."date"::date ASC) as
t1_previous_date
FROM t1
)
SELECT
t1_date,
t1_date - t1_previous_date as days,
count(t2."time")
FROM cte_1
JOIN t2 ON
t2."time" between t1_previous_date and t1_date
GROUP BY
t1_date,
t1_previous_date;
Test setup:
create table t1 ("date" timestamptz);
create table t2 ("time" timestamptz);
insert into t1 select now() - random()*'30 days'::interval from
generate_series(1,100);
insert into t2 select now() - random()*'30 days'::interval from
generate_series(1,100000);
WITH cte_1 AS (
SELECT
t1."date"::date as t1_date,
lag(t1."date"::date,1) OVER (ORDER BY t1."date"::date ASC) as
t1_previous_date
FROM t1
)
SELECT
t1_date,
t1_date - t1_previous_date as days,
count(t2."time")
FROM cte_1
JOIN t2 ON
t2."time" between t1_previous_date and t1_date
GROUP BY
t1_date,
t1_previous_date;
t1_date | days | count
------------+------+-------
2024-03-15 | 2 | 6625
2024-03-20 | 1 | 3336
2024-03-18 | 1 | 3325
2024-03-10 | 1 | 3437
2024-04-03 | 1 | 3316
2024-03-19 | 1 | 3392
2024-03-22 | 1 | 3431
2024-03-09 | 1 | 3196
2024-03-17 | 1 | 3241
2024-03-11 | 1 | 3380
2024-03-29 | 1 | 3344
2024-03-08 | 1 | 3390
2024-03-28 | 1 | 3298
2024-03-31 | 1 | 3469
2024-03-30 | 1 | 3352
2024-03-16 | 1 | 3364
2024-03-21 | 1 | 3288
2024-03-27 | 1 | 3331
2024-03-26 | 2 | 6766
2024-03-06 | 1 | 1445
2024-03-23 | 1 | 3277
2024-04-01 | 1 | 3074
2024-03-12 | 1 | 3314
2024-03-24 | 1 | 3289
2024-03-13 | 1 | 3317
2024-04-02 | 1 | 3388
2024-03-07 | 1 | 3349
(27 rows)
Best regards.
Samed YILDIRIM
On Tue, 2 Apr 2024 at 02:13, Steve Midgley <[email protected]> wrote:
>
>
> On Mon, Apr 1, 2024 at 3:03 PM James Cloos <[email protected]> wrote:
>
>> I'm attempting a three column select from two tables, where only a
>> single column from each of the tables matters.
>>
>> t1.date and t2.time are both timestamptz.
>>
>> I want the three columns to be:
>>
>> t1.date::date
>>
>> t1.date - lag(t1.date,1) over (order by date asc) days,
>>
>> and count(t2.time) from the interval lag(t1.date,1) and t1.date.
>>
>> but that syntax of course fails do to the placements I've tried for thae
>> between.
>>
>> I tried a sub-query but got what looked like an outer join.
>>
>> I want exactly count(*) from t1 rows in the result.
>>
>> What trick am I missing?
>>
>> I'm a little confused by your SQL, which appears to be incomplete? Could
> you give some code to create a simple table, populate it with a few sample
> rows, and then a full SQL query of what you are trying to accomplish? Also
> include what you get back from your query and what you wish you were
> getting back, in terms of result sets..
>
> The main thing I'm missing is how t1 and t2 are joined.. I can't see that,
> so it's hard to understand why your query is not giving you the results you
> want.
>
> Best,
> Steve
>
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: help with a particular multi-table query
In-Reply-To: <CAAo1mbk8GOjtOTphuy_OHwsQL5xvjvFxJP8T-7apxptrZ0-Z7A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox