Currently I am storing the results of some simulations in SQLite3 tables with the following structure
Table: radiation
id, timestamp, surface_total_shortwave, cell_id
1 , 2010-01-01 09:00:00, 3.5, 1
2 , 2010-01-01 10:00:00, 2.5, 1
3 , 2010-01-01 11:00:00, 10.0, 1
4 , 2010-01-01 12:00:00, 6.5, 1
5 , 2010-01-01 09:00:00, 2.5, 2
6 , 2010-01-01 10:00:00, 1.5, 2
7 , 2010-01-01 11:00:00, 10.0, 2
8 , 2010-01-01 12:00:00, 5.5, 2
.., ..................., ....., .
100 , 2010-01-01 09:00:00, 1.5, 34
101 , 2010-01-01 10:00:00, 1.5, 34
102 , 2010-01-01 11:00:00, 4.0, 34
103 , 2010-01-01 12:00:00, 3.5, 34
104 , 2010-01-01 09:00:00, 1.5, 45
105 , 2010-01-01 10:00:00, 2.5, 45
106 , 2010-01-01 11:00:00, 7.0, 45
107 , 2010-01-01 12:00:00, 2.5, 45
.., ..................., ....., .
Actual create statement:
CREATE TABLE cfd(id INTEGER PRIMARY KEY, time DATETIME, u, cell_id integer)
For each cell_id I have the same number of timestamps.
I need to subtract the time series for a list of (cell1 - cell2) combinations, assign it to cell1 and create a view that the user can interrogate.
For example:
cell1 = 1 and cell2 = 34
cell1 = 2 and cell2 = 45 (duplicates are possible for cell2)
For one single cell combination I do
create view ds as
select time1, (sts1 - sts2) as sts, cell_id from
(select time as time1, cell_id, surface_total_shortwave as sts1 from radiation where cell_id = 1)
inner join
(select time as time2, surface_total_shortwave as sts2 from radiation where cell_id = 34)
on time1 = time2
How can I extend this query when I have a mapping like
(cell1, cell2)
(1, 34)
(2, 45)
(..., ...)
The resulting table, assuming that the mapping is only for the 2 cells above (1 -> 34) and (2 -> 45) would be the following
Table: radiation
id, timestamp, sts, cell_id
1 , 2010-01-01 09:00:00, 2.0, 1
2 , 2010-01-01 10:00:00, 1.0, 1
3 , 2010-01-01 11:00:00, 6.0, 1
4 , 2010-01-01 12:00:00, 3.0, 1
5 , 2010-01-01 09:00:00, 1.0, 2
6 , 2010-01-01 10:00:00, -1.0, 2
7 , 2010-01-01 11:00:00, 3.0, 2
8 , 2010-01-01 12:00:00, 3.0, 2
EDIT It seems that a possible solution could be to create a temporary table to store the mapping
Table: mapping
idx, cell1, cell2
1, 1 , 34
2, 2 , 45
.., ..., ...
Now I can rewrite the query in this way
select time1, (sts1 - sts2) as sts, cell1_id, cell2_id from
(select time as time1, cell_id as cell1_id, surface_total_shortwave as sts1 from radiation where cell_id in (1, 2))
inner join
(select time as time2, cell_id as cell2_id, surface_total_shortwave as sts2 from radiation where cell_id in (34, 45))
on time1 = time2 and cell1_id = (select mapping.cell1 from mapping where mapping.cell2 = cell2_id)
Just concatenate multiple queries:
CREATE VIEW ds AS
SELECT time,
r1.surface_total_shortwave - r2.surface_total_shortwave AS sts,
r1.cell_id
FROM radiation AS r1
JOIN radiation AS r2 USING (time)
WHERE (r1.cell_id, r2.cell_id) = (1, 34)
UNION ALL
SELECT ...
...
WHERE (r1.cell_id, r2.cell_id) = (2, 45);
Alternatively, use a single query with all cell comparisons:
CREATE VIEW ds AS
SELECT time,
r1.surface_total_shortwave - r2.surface_total_shortwave AS sts,
r1.cell_id
FROM radiation AS r1
JOIN radiation AS r2 USING (time)
WHERE (r1.cell_id, r2.cell_id) = (1, 34)
OR (r1.cell_id, r2.cell_id) = (2, 45);
(Both queries require proper indexes to be efficient. Which one is faster depends on the data; you have to measure it yourself.)
Using a temporary table for the mappings is possible, but worthwhile only if there any many more mappings.