הנה המבנה והקוד
הקוד ליצירת הטבלה
CREATE TABLE `bank_account_status` ( `id` int(11) NOT NULL auto_increment, `log_import_id` int(11) NOT NULL default '0', `account` varchar(50) NOT NULL default '', `status_date` char(8) NOT NULL default '00000000', `account_currency` char(3) NOT NULL default '', `balance` double NOT NULL default '0', `open_positions_fx` double NOT NULL default '0', `open_positions_otc` double NOT NULL default '0', `open_positions_stock` double NOT NULL default '0', `open_positions_cfd` double NOT NULL default '0', `open_positions_stock_option` double NOT NULL default '0', `open_positions_futures` double NOT NULL default '0', `open_positions_contract_option` double NOT NULL default '0', `open_positions_managed_fund` double NOT NULL default '0', `open_positions_bonds` double NOT NULL default '0', `reserved_for_future_instrument2` double NOT NULL default '0', `reserved_for_future_instrument3` double NOT NULL default '0', `reserved_for_future_instrument4` double NOT NULL default '0', `total_equity` double NOT NULL default '0', `margin` double NOT NULL default '0', `net_equity_for_trading` double NOT NULL default '0', `partner_account_key` int(11) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `bank_account_status_u002` (`account`,`status_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=403934 ;
הנה ה VIEW שמוצא את היום הראשון בחודש ואת היום האחרון בחודש לכל לקוח. צפייה בשלושים התוצאות הראשונות של ה VIEW לוקחת 7-9 שניות תודה רבה....
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_bank_account_status` AS select `bank_account_status`.`account` AS `account`,`bank_account_status`.`balance` AS `balance`,min(`bank_account_status`.`status_date`) AS `status_date` from `bank_account_status` group by `bank_account_status`.`account`,month(`bank_account_status`.`status_date`) union select `bank_account_status`.`account` AS `account`,`bank_account_status`.`balance` AS `balance`,max(`bank_account_status`.`status_date`) AS `status_date` from `bank_account_status` group by `bank_account_status`.`account`,month(`bank_account_status`.`status_date`)$$
