MySQLでマテリアライズドビュー8 集計クエリー実装編 定期更新(start with句・next句)

• 昨日の集計を行うように修正

DROP   PROCEDURE access_daily_materialized_insert;
delimiter //
CREATE PROCEDURE access_daily_materialized_insert()
begin
  replace into mv_access_daily (access_date,access_daily_num)  
  select date_sub( curdate() , INTERVAL 1 DAY ) ,
  count( access_id ) 
  from access
  where date_sub( curdate() ,INTERVAL 1 DAY ) = date( access_time ); end;
//
delimiter ;

call access_daily_materialized_insert();

• イベントスケジューラーに登録

CREATE EVENT e_access_daily_materialized_insert_daily
    ON SCHEDULE 
      EVERY 1 DAY
    COMMENT 'updated materialized view each day.'
    DO CALL access_daily_materialized_insert();