public inbox for [email protected]
help / color / mirror / Atom feedhelp with a particular multi-table query
3+ messages / 3 participants
[nested] [flat]
* help with a particular multi-table query
@ 2024-04-01 22:03 James Cloos <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: James Cloos @ 2024-04-01 22:03 UTC (permalink / raw)
To: [email protected]
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 <[email protected]>
OpenPGP: https://jhcloos.com/0x997A9F17ED7DAEA6.asc
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: help with a particular multi-table query
@ 2024-04-01 23:13 Steve Midgley <[email protected]>
parent: James Cloos <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Steve Midgley @ 2024-04-01 23:13 UTC (permalink / raw)
To: James Cloos <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: help with a particular multi-table query
@ 2024-04-04 10:18 Samed YILDIRIM <[email protected]>
parent: Steve Midgley <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Samed YILDIRIM @ 2024-04-04 10:18 UTC (permalink / raw)
To: Steve Midgley <[email protected]>; +Cc: James Cloos <[email protected]>; [email protected]
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-04-04 10:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-01 22:03 help with a particular multi-table query James Cloos <[email protected]>
2024-04-01 23:13 ` Steve Midgley <[email protected]>
2024-04-04 10:18 ` Samed YILDIRIM <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox