0

I have a table (table1) in a MySQL database with data similar to this:

Category total_sold revenue profit
fruit 32 200 150
veggies 12 50 23
chips 23 170 110
fruit 43 300 180
chips 5 25 15

New data is inserted into the table regularly through a python script using sqlalchemy (appends csv files to the mysql table using Pandas).

I want to create an aggregated table (table2) based on category using a query, and I want the aggregation to update whenever I add new records to the table1.

For example after executing the query:

CREATE TABLE table2 AS
SELECT Category, AVG(total_sold) avg_sold, AVG(revenue) avg_revenue, AVG(profit) avg_profit
FROM table1
GROUP BY 1

I get table2 as:

Category avg_sold avg_revenue avg_profit
fruit 37.5 250 165
veggies 12 50 23
chips 14 97.5 62.5

Now if I insert several new records to table1, how can I make table2 update automatically? (recalculate all aggregations based on all table1 records including the newly added ones)

I read about creating a trigger, but I'm not sure how to execute it or if it would work in a case like this.

I Would appreciate any help.

Samba
  • 51
  • 5
  • 1
    Instead of creating a table you should create a View. Then you can select from that View and always have up to date results. – forpas Jan 08 '23 at 10:02
  • @forpas I'm not familiar with views but I will take a look online and try it. Thanks for your answer! – Samba Jan 08 '23 at 10:10

1 Answers1

0

MySQL does not support "materialized views". You have to do the aggregation yourself. Build a separate Summary Table and keep it updated. A simple IODKU is one way to do that maintenance; the link discusses other, such as a nightly batch update of the day's data.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks for your answer! I think a view will do the trick for now, I just wasn't familiar with views. – Samba Jan 08 '23 at 20:43