Replicating functionality of excel cell referencing (or calculations over multiple views)

I have a table with 3 columns that contains a number of data streams all in one table.

val_name is the name of the data stream, val_sequence is the incrementing sequence number and val contains the data. Combined name and sequence are like a composite index.

The existing val_name streams in this table are a, b. I would like users to be able to request stream c (which is not in the table), and the database to dynamically return a*b.

a*b in this case would be like multiplying two tables, one containing only val_name a, the other val_name b, and then joining on val_sequence (much like multiplying indexed python pandas series).

So the results would be:

val_sequence val_name val 
0            c        80
1            c        5

The idea is that users should be able to request a or b or c and receive data, without needing to know that a and b hold data, and c only holds references. it’s possible that some sequence numbers are missing both in the middle or at either end.

I haven’t been able to figure out a good way how to provide this kind of flexibility. Are SQL views flexible enough for this? And if so could you give me a simple example? If not, what might be a workable alternative? Any database engine of your choice is fine.

For convenience, I am providing SQL code that creates a table and inserts above values, and creates two views. This doesn’t do what I need it to do, but it’s a start for those who want to give it a try.

CREATE TABLE  IF NOT EXISTS valdb (
  val_name VARCHAR(255), 
  val INT, 
  val_sequence  int
);

INSERT into valdb (val_name, val, val_sequence) VALUES ("a",10,0);
INSERT into valdb (val_name, val, val_sequence) VALUES ("a", 1,1);

INSERT into valdb (val_name, val, val_sequence) VALUES ("b", 8,0);
INSERT into valdb (val_name, val, val_sequence) VALUES ("b", 5,1);

CREATE VIEW `a` AS SELECT val_name, val, val_sequence from valdb WHERE val_name = "a" ;
CREATE VIEW `b` AS SELECT val_name, val, val_sequence from valdb WHERE val_name = "b" ;

Answer

If, as you sample suggests, the sequence numbers are guaranteed to be the same for both streams, i.e. if and only if there exits n for a an n also exists for b, that would be an inner join on the sequence number and the * operator to get the multiplication.

CREATE VIEW c
AS
SELECT 'c' val_name,
       a.val_sequence,
       a.val * b.val val
       FROM a
            INNER JOIN b
                       ON a.val_sequence = b.val_sequence;

db<>fiddle (assuming MySQL from the syntax of the code you provided)

If the assumption doesn’t hold true you’d need to define what should happen in such cases where it fails. E.g. whether to get the next available sequence number and whether a or b provides the “leading” sequence number or if val should be assumed to be 0 for missing sequence numbers, etc..