CODEWITHSUNDEEP
mysqlsql
Aakash Dusane
Aakash Dusane

Reputation: 398

How do I store my 'view' as a permanent table in mySQL?

I'm new to SQL and still learning.

Once I've done operations on a table (say table 1) to get a view. How do I translate/convert/store this view into a new permanent table (table 2)? Also, is it possible to store it in the same table (table 1)?

My code is something like this: 

select `Date`, `Close Price`,
round(avg(`Close Price`) over (order by `date` asc rows 19 preceding), 2) 
as '20 Day MA' 
from table1;  -- this is my view

then i'd like table1 or a new table to store this view (the columns date, close price, 20 day ma)

basically,
Old table: date, close price

New table: date, close price, 20_day_ma

Maybe it's simpler to just add the new column 20_day_ma but I wouldn't know how to add the new values that rely on the other columns.

edit:

So just tried this:

DELIMITER $$
-- create table1
drop table if exists table1;

create table table1 as
select STR_TO_DATE(`Date`, '%d-%M-%Y') as `Date`, `Close Price`,
round(avg(`Close Price`) over (order by `date` asc rows 19 preceding), 2) as 
'20 Day MA'
from source_table $$

DELIMITER ;

And I constantly get the error: Commands out of sync

Upvotes: 2

Views: 4341

Answers (3)

gaborsch
gaborsch

Reputation: 15758

You have the following options:

  • View: does not store data, every time it is queried, a query will run against the source tables

  • "Materialized view": there is no such construct in MySQL, but you can emulate it with triggers on the source tables. If a row is created, updated or deleted, you can update the materialized view. Try to search for it, there are good descriptions about it. Useful if the source tables are rarely updated. May be a bit difficult if your select is complex.

  • Temporary table: CREATE TEMPORARY TABLE t1 (...); and INSERT INTO t1 SELECT (your query); (may be shortened to CREATE TEMPORARY TABLE t1 AS SELECT (your query)) . These tables create a snapshot of the current state, and live as long as your connection lives (even through transactions), but cannot be shared among connections. You can add indexes, etc, to speed up queries. It is useful if you have a complex process that needs to store data that are processed multiple times in the process.

  • Create a column to store values, and create a MySQL EVENT to update it regularly.

IMHO since the 20 days moving average should be updated daily, the last solution is the best. If you don'tr trust MySQL, or have another job scheduler, you can call the updater SQL statement from there, too.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108696

To create a new table from a result set is straightforward. CREATE ... SELECT is the way.

 CREATE my_new_table AS
 SELECT whatever FROM sometable;

You need to give a valid column name (alias) for every column in your select.

Pro tip: Don't create tables where views will serve. When you use a view it gets the latest and greatest data from the underlying tables each time you use it. But when you create a table it's a snapshot in time.

Pro tip: Professors aren't pros, so they may give you exercises to complete that are bad ideas in the real world.

Upvotes: 1

DDS
DDS

Reputation: 2479

You could do 3 things: 1> create a view (youi'll refer to the view as if it was a table)

create view my_new_view as
select `Date`, `Close Price`,
round(avg(`Close Price`) over (order by `date` asc rows 19 preceding), 2) 
as '20 Day MA' 
from table1;

then read data with select * from myview

If you need a 'snapshot' of the table you can 'materialize' with select into

CREATE TABLE my_new_table -- mysql syntax
select `Date`, `Close Price`,
round(avg(`Close Price`) over (order by `date` asc rows 19 preceding), 2) 
as '20 Day MA' 
-- into my_new_table --this forces the creation of new table -- sorry: SQL SERVER syntax
from table1

A third option is to add the new column than call an 'update' to populate it

Upvotes: 1

Related Questions