How to subtract column's values from column's values if columns have different quantity of-CodePudding

I have a table like this, DB is ClickHouse:

SELECT name, value, timing 
FROM table1 tl1
WHERE
    timing >= '2022-05-23 01:00:00' AND timing <= '2022-05-23 02:00:00'
ORDER BY name, timing 

┌─name─┬─value─┬──────────────timing─┐
│ A    │     5 │ 2022-05-23 01:01:03 │
│ A    │     5 │ 2022-05-23 01:01:05 │
│ A    │     5 │ 2022-05-23 01:01:06 │
│ A    │     5 │ 2022-05-23 01:01:07 │
│ A    │     9 │ 2022-05-23 01:02:01 │
│ A    │     7 │ 2022-05-23 01:03:21 │
│ B    │     5 │ 2022-05-23 01:04:23 │
│ B    │     6 │ 2022-05-23 01:05:33 │
│ C    │     7 │ 2022-05-23 01:06:30 │
│ C    │     8 │ 2022-05-23 01:07:41 │
│ C    │     3 │ 2022-05-23 01:08:44 │
│ C    │     7 │ 2022-05-23 01:09:50 │
└──────┴───────┴─────────────────────┘

From each "A","B","C" value (first query) I need to subtract a value from the same table, that match min(timing).

With next query, I selected the values that satisfy a condition min(timing) per each unique name.

SELECT
    tpl.1 AS name,
    tpl.6 AS value,
    tpl.3 AS timing
FROM
(
    SELECT argMin(tuple(*), timing) AS tpl
    FROM table1 tl 
    WHERE timing >= '2022-05-23 01:00:00' AND timing <= '2022-05-23 02:00:00'
    GROUP BY name
)
ORDER BY name ASC, timing


┌─name─┬─value─┬──────────────timing─┐
│ A    │     5 │ 2022-05-23 01:01:03 │
│ B    │     5 │ 2022-05-23 01:04:23 │
│ C    │     7 │ 2022-05-23 01:06:30 │
└──────┴───────┴─────────────────────┘

The resulting values from second query I need to subtract from the original table, by matching the names. This is issue that I can't resolve. My goal is to obtain next table, where from each value "A","B","C" from first query, I subtract a "A","B","C" values (with the lowest timing) from second query.

As result I need a table like this:

┌─name─┬─value─┬──────────────timing─┐
│ A    │     0 │ 2022-05-23 01:01:03 │
│ A    │     0 │ 2022-05-23 01:01:05 │
│ A    │     0 │ 2022-05-23 01:01:06 │
│ A    │     0 │ 2022-05-23 01:01:07 │
│ A    │     4 │ 2022-05-23 01:02:01 │
│ A    │     3 │ 2022-05-23 01:03:21 │
│ B    │     0 │ 2022-05-23 01:04:23 │
│ B    │     1 │ 2022-05-23 01:05:33 │
│ C    │     0 │ 2022-05-23 01:06:30 │
│ C    │     1 │ 2022-05-23 01:07:41 │
│ C    │    -4 │ 2022-05-23 01:08:44 │
│ C    │     0 │ 2022-05-23 01:09:50 │
└──────┴───────┴─────────────────────┘

I just need to combine my queries, but I don't know how to do this.

Also, second query may be modified to:

SELECT name, value  
FROM table1 tbl1
INNER JOIN
(
    SELECT name, MIN(timing) AS min_timing
    FROM table1 tl
    WHERE timing >= '2022-05-23 01:00:00' AND timing <= '2022-05-23 02:00:00'
    GROUP BY name
) tbl2
    ON tbl2.name = tbl1.name AND
       tbl2.min_timing = tbl1.timing
WHERE
    timing >= '2022-05-23 01:00:00' AND timing <= '2022-05-23 02:00:00' 
ORDER BY name, timing

CodePudding user response：

Use the window function FIRST_VALUE to get the minimum value per name:

SELECT
  name,
  value - FIRST_VALUE(value) OVER (PARTITION BY name ORDER BY timing) as diff,
  timing
FROM table1
WHERE timing >= timestamp '2022-05-23 01:00:00'
  AND timing <  timestamp '2022-05-23 02:00:00'
ORDER BY name, timing;