Hello everyone, I recently started working with a twist and there was such a question - is it possible to perform any operations on the calculated twist columns? For example, there is a table:

CREATE TABLE IF NOT EXISTS schema.table( row_id INT(255) NOT NULL AUTO_INCREMENT PRIMARY KEY, val1 DOUBLE, val2 DOUBLE);

In val1 and val2 arbitrary data is inserted:

 INSERT INTO schema.table (va1, val2) VALUES (1, 2); INSERT INTO schema.table (va1, val2) VALUES (3, 4); INSERT INTO schema.table (va1, val2) VALUES (5, 6);

I try to create a view based on the tablet:

 CREATE OR REPLACE VIEW schema.table_VIEW AS select t1.row_id as row_id, LAG (val3,1,0) OVER() + t1.val1 - t1.val2 as val3, from schema.table t1

and I get the error that val3 does not exist in the table, which in general is logical. Tell me, can this somehow be implemented? The goal of the view is to get a column val3, in which the value from this column in the last row will look and val1 will be added and val2 will be subtracted from the current one.

And question number 2 - if you create a column val4 in the table, will it be possible to update it dynamically with values ​​from val3?

  • Подскажите, можно ли это как-то реализовать You can, but not in the way you do, create a variable and everything will work out . Question number two: You can . - Vladimir Klykov Nov.
  • Thanks for the reply, but could you give more details? It is not very clear how to use a variable here. Question two - is it some kind of trigger functionality? - mysqllearn Nov.
  • Variables, these are things that start at @ . ru.stackoverflow.com/a/905194/194569 - Mike
  • And you can clean the window functions. You don’t need a LAG at all, sum(val1 - vla2) over(order by x) will give approximately what you are trying to express with lag. Only order by is obligatory (as is the case with lag), when you accumulate a certain result, the order of the lines matters ... - Mike
  • Got it, thanks a lot for the answer! It remains to figure out how to implement the preservation of the calculated columns in the table. - mysqllearn 7:42

0