Thread

1. help with a particular multi-table query

   James Cloos <cloos@jhcloos.com> — 2024-04-01T22:03:26Z

   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
   
   
   
   

2. Re: help with a particular multi-table query

   Steve Midgley <science@misuse.org> — 2024-04-01T23:13:09Z

   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
   

3. Re: help with a particular multi-table query

   Samed YILDIRIM <samed@reddoc.net> — 2024-04-04T10:18:29Z

   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
   >