Thread: [MASSMAIL]help with a particular multi-table query

[MASSMAIL]help with a particular multi-table query

From

James Cloos

Date:

02 April, 01:03:26

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?

-JimC
-- 
James Cloos <cloos@jhcloos.com>
            OpenPGP: https://jhcloos.com/0x997A9F17ED7DAEA6.asc

Re: help with a particular multi-table query

From

Steve Midgley

Date:

02 April, 02:13:09

On Mon, Apr 1, 2024 at 3:03 PM James Cloos <cloos@jhcloos.com> 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

Re: help with a particular multi-table query

From

Samed YILDIRIM

Date:

04 April, 13:18:29

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 <science@misuse.org> wrote:

On Mon, Apr 1, 2024 at 3:03 PM James Cloos <cloos@jhcloos.com> 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