1

Given an initial table t1:

i_t1 tk
400 t1
702 t2

and a second table t2:

tk i1_t2 i2_t2 v
t20 300 600 0.5
t19 350 550 0.6
t18 370 420 0.7
t17 500 800 0.2
t16 623 751 0.9

I would like to have the following result:

i_t1 tk tot
400 t1 1.8
702 t2 1.1

This means that I add a column tot to table t1 that contains the sum over all values in column v in t2, only when i_t1 (from t1) is within the range [i1_t2 , i2_t2] (from t2).

Alternative #1:

It was to generate intermediate rows in t2 corresponding to each value ì in the range. Then, group by that i and cross check i with the value in i_t1. This has been discussed in my other post. However, I noticed, that this will be adding a lot of rows (sometimes unnecessary, because there is no match in t1) in my case and creates a performance issue.

Alternative #2: (conceptual)

Go through t1 row by row, select the corresponding value of i_t1 (e.g. 400) and then pick up the value resulting from the query below:

select sum(v) from t2 where ((select i_t1 from t1 limit 1) >= i1_t2) and ((select i_t1 from t1 limit 1) <= i2_t2);

However, this requires to be repeated over all rows of t1 and I'm not sure how to bring the result back to t1.

Is there a more efficient way to achieve this?

Here are the queries that can be used to reproduce:

create table t1 (i_t1 int, tk varchar(5));
    
insert into t1 (i_t1, tk)
values (400,'t1'),(702,'t2');

create table t2 (tk varchar(5), i1_t2 int, i2_t2 int, v real);

insert into t2 (tk, i1_t2, i2_t2, v)
values ('t20',300,600,0.5),('t19',350,550,0.6),('t18',370,420,0.7),('t17',500,800,0.2),('t16',623,751,0.9);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
sqlquestionasker
  • 143
  • 2
  • 9

1 Answers1

1

demo:db<>fiddle

SELECT 
    t1.i_t1, t1.tk,
    SUM(t2.v)                                    -- 2
FROM t1
JOIN t2 ON (t1.i_t1 BETWEEN t2.i1_t2 AND i2_t2)  -- 1
GROUP BY t1.i_t1, t1.tk
  1. Join on your condition (i_t1 between i1_t2 and i2_t2)
  2. Group and sum the join result
S-Man
  • 22,521
  • 7
  • 40
  • 63