Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rsKBD-006Kis-Na for pgsql-sql@arkaria.postgresql.org; Thu, 04 Apr 2024 10:18:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rsKBC-002B8a-Qh for pgsql-sql@arkaria.postgresql.org; Thu, 04 Apr 2024 10:18:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rsKBC-002B8S-Bq for pgsql-sql@lists.postgresql.org; Thu, 04 Apr 2024 10:18:46 +0000 Received: from mail-ed1-x52f.google.com ([2a00:1450:4864:20::52f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rsKB9-000Vxa-2R for pgsql-sql@lists.postgresql.org; Thu, 04 Apr 2024 10:18:44 +0000 Received: by mail-ed1-x52f.google.com with SMTP id 4fb4d7f45d1cf-56e0c29ad5dso929523a12.1 for ; Thu, 04 Apr 2024 03:18:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=reddoc.net; s=google; t=1712225920; x=1712830720; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=H0W9a5MbZ6aV3Nx2EdKpIK9zluyEA0PpOGcMrrBQWBY=; b=EISlb/5fGT95iNazsDAWbn8clWAoh1txBOnEaAV9SogWIdihD7BTRyU4VsV0TjVnmC 6kn7l8J2Z92AR1d1drB/JpPv+zVJk5K+rQgNRy1h1n0lcUj4wVLSocM7x+1W8J58S11r EfloiBviI+l4DAN7Wi/uzhOVu75XmQWTVj8zA= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712225920; x=1712830720; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=H0W9a5MbZ6aV3Nx2EdKpIK9zluyEA0PpOGcMrrBQWBY=; b=nrlp3S4Z6BCSnOdPUO/BpjYV8dA6IlQ1kmF9WMntKHLyb9UevsZ+iI+yK79M1EnFex hZsVI4O5kmjh1iew3Oawd7JS4MaSEmJaumXiq7tSExoy+sLyuKt3QXafGpaAf5JPDkRI Y6t7ArHU2fFIODmISLF0F4HVFsZ2IqLZ7CpFhGRIQoCKDDePa3HpAPJtye8Y952rbThS K2+Y3nXbQTgAbN+3ax9IDEF0zXk7Gnc9ymphGAm8mFz5flfpi4S5jZFInTkUPnTH+x4F qkzLOsvp0I4vdURT0qinRwI9PJthe/vTZatf+0HUqwa57ff25I+jvQnk5hz2UAWWi3dv fDIw== X-Forwarded-Encrypted: i=1; AJvYcCVQZb7a6l7kwBC1XWv7ldKUBKRLQvpEsMvVS/B6eyhupn0zST06gGSHbdmnvBuvK7WxktX63uUxbHWSV5ugu2doiHmGZxntXZAr83OUy8I= X-Gm-Message-State: AOJu0Yzs8pXcN+62DUFEoK95nkRI4XIh1TwUAgQ+Dgh1AL+FixPWq2Or 5yGMBppTNmrCLH8fP49VwwKpFtTVJUeZvh7uD7uKXRCVE8kCuFOeMRMcKmTa4RU0TApb94fhFQV Alfd7WZFwMWqu7B+F5ZKaSlVEdIaCfaakwqGM7zTTWkZ+eQdkhUjvAq1/ X-Google-Smtp-Source: AGHT+IGEhTJnG/x92Fqezih31r6X1WnZwD7P79EbWsDc4UZzVFbW3OIz13iOQY8R6bXwGyk8E9wZ7CcmnkYGMvklCow= X-Received: by 2002:a50:9558:0:b0:56d:c929:999b with SMTP id v24-20020a509558000000b0056dc929999bmr1132701eda.41.1712225920426; Thu, 04 Apr 2024 03:18:40 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Samed YILDIRIM Date: Thu, 4 Apr 2024 13:18:29 +0300 Message-ID: Subject: Re: help with a particular multi-table query To: Steve Midgley Cc: James Cloos , pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000c34058061542a982" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c34058061542a982 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wrote: > > > On Mon, Apr 1, 2024 at 3:03=E2=80=AFPM James Cloos wr= ote: > >> 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 sampl= e > rows, and then a full SQL query of what you are trying to accomplish? Als= o > 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 y= ou > want. > > Best, > Steve > --000000000000c34058061542a982 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi James,

I guess you are lo= oking for something like this.
WITH cte_1= AS (
=C2=A0 SELECT
=C2=A0 =C2=A0 t1."date"::date as t1_dat= e,
=C2=A0 =C2=A0 lag(t1."date"::date,1) OVER (ORDER BY t1.&quo= t;date"::date ASC) as t1_previous_date
=C2=A0 FROM t1
)
SELEC= T
=C2=A0 t1_date,
=C2=A0 t1_date - t1_previous_date as days,
=C2= =A0 count(t2."time")
FROM cte_1
JOIN t2 ON
=C2=A0 t2.&qu= ot;time" between t1_previous_date and t1_date
GROUP BY
=C2=A0 t1= _date,
=C2=A0 t1_previous_date;

Test set= up:
create table t1 ("date" timestamptz);
create table t2 (&qu= ot;time" timestamptz);
insert into t1 select now() - random()*'= 30 days'::interval from generate_series(1,100);
insert into t2 selec= t now() - random()*'30 days'::interval from generate_series(1,10000= 0);
WITH cte_1 AS (
=C2=A0 SELECT
=C2=A0 =C2=A0 t1."date"= ;::date as t1_date,
=C2=A0 =C2=A0 lag(t1."date"::date,1) OVER = (ORDER BY t1."date"::date ASC) as t1_previous_date
=C2=A0 FROM= t1
)
SELECT
=C2=A0 t1_date,
=C2=A0 t1_date - t1_previous_date = as days,
=C2=A0 count(t2."time")
FROM cte_1
JOIN t2 ON=C2=A0 t2."time" between t1_previous_date and t1_date
GROUP = BY
=C2=A0 t1_date,
=C2=A0 t1_previous_date;
=C2=A0 t1_date =C2=A0 = | days | count
------------+------+-------
=C2=A02024-03-15 | =C2=A0 = =C2=A02 | =C2=A06625
=C2=A02024-03-20 | =C2=A0 =C2=A01 | =C2=A03336
= =C2=A02024-03-18 | =C2=A0 =C2=A01 | =C2=A03325
=C2=A02024-03-10 | =C2=A0= =C2=A01 | =C2=A03437
=C2=A02024-04-03 | =C2=A0 =C2=A01 | =C2=A03316
= =C2=A02024-03-19 | =C2=A0 =C2=A01 | =C2=A03392
=C2=A02024-03-22 | =C2=A0= =C2=A01 | =C2=A03431
=C2=A02024-03-09 | =C2=A0 =C2=A01 | =C2=A03196
= =C2=A02024-03-17 | =C2=A0 =C2=A01 | =C2=A03241
=C2=A02024-03-11 | =C2=A0= =C2=A01 | =C2=A03380
=C2=A02024-03-29 | =C2=A0 =C2=A01 | =C2=A03344
= =C2=A02024-03-08 | =C2=A0 =C2=A01 | =C2=A03390
=C2=A02024-03-28 | =C2=A0= =C2=A01 | =C2=A03298
=C2=A02024-03-31 | =C2=A0 =C2=A01 | =C2=A03469
= =C2=A02024-03-30 | =C2=A0 =C2=A01 | =C2=A03352
=C2=A02024-03-16 | =C2=A0= =C2=A01 | =C2=A03364
=C2=A02024-03-21 | =C2=A0 =C2=A01 | =C2=A03288
= =C2=A02024-03-27 | =C2=A0 =C2=A01 | =C2=A03331
=C2=A02024-03-26 | =C2=A0= =C2=A02 | =C2=A06766
=C2=A02024-03-06 | =C2=A0 =C2=A01 | =C2=A01445
= =C2=A02024-03-23 | =C2=A0 =C2=A01 | =C2=A03277
=C2=A02024-04-01 | =C2=A0= =C2=A01 | =C2=A03074
=C2=A02024-03-12 | =C2=A0 =C2=A01 | =C2=A03314
= =C2=A02024-03-24 | =C2=A0 =C2=A01 | =C2=A03289
=C2=A02024-03-13 | =C2=A0= =C2=A01 | =C2=A03317
=C2=A02024-04-02 | =C2=A0 =C2=A01 | =C2=A03388
= =C2=A02024-03-07 | =C2=A0 =C2=A01 | =C2=A03349
(27 rows)

Best regards.
Same= d YILDIRIM


On Tue, 2 Apr 2024 at 02:13= , Steve Midgley <science@misuse.or= g> wrote:


On Mon, Apr 1, 2024 at 3:03=E2=80=AF= PM James Cloos <c= loos@jhcloos.com> wrote:
I'm attempting a three column select from two tables, w= here 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 tha= e
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 c= reate a simple table, populate it with a few sample rows, and then a full S= QL query of what you are trying to accomplish? Also include what you get ba= ck from your query and what you wish you were getting back, in terms of res= ult sets..

The main thing I'm missing is how t= 1 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
--000000000000c34058061542a982--