uuax3vFc

1CREATE TABLE `periodholdings ` (
2   `stkcode` varchar(255) NOT NULL,
3   `participantid` varchar(255) DEFAULT NULL,
4   `participantname` varchar(255) DEFAULT NULL,
5   `holdingdate` date DEFAULT NULL,
6   `shares` bigint(20) DEFAULT NULL,
7   `percentage` decimal(5,4) DEFAULT NULL,
8   KEY `sp` (`stkcode`,`participantname`)
9 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
10
11CREATE TABLE `pholdings` (
12   `stkcode` varchar(255) NOT NULL,
13   `participantid` varchar(255) DEFAULT NULL,
14   `participantname` varchar(255) DEFAULT NULL,
15   `holdingdate` date DEFAULT NULL,
16   `shares` bigint(20) DEFAULT NULL,
17   `percentage` decimal(5,4) DEFAULT NULL,
18   KEY `sp` (`stkcode`,`participantname`)
19 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
20
21CREATE TABLE `choldings` (
22   `stkcode` varchar(255) NOT NULL,
23   `participantid` varchar(255) DEFAULT NULL,
24   `participantname` varchar(255) DEFAULT NULL,
25   `holdingdate` date DEFAULT NULL,
26   `shares` bigint(20) DEFAULT NULL,
27   `percentage` decimal(5,4) DEFAULT NULL,
28   KEY `sp` (`stkcode`,`participantname`)
29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
30	
31drop table if exists pholdings;
32    create table pholdings (index sp(stkcode, participantname)) as select * from periodholdings where holdingdate = '2016-09-01'; 
33
34    drop table if exists choldings;
35    create table choldings (index sp(stkcode, participantname)) as select * from periodholdings where holdingdate = '2016-09-21'; 
36
37   explain
38    (select p1.stkcode, p1.participantid, p1.participantname, if(c1.shares is null, -p1.shares/100, (c1.shares - p1.shares)/100) as averagediff from pholdings p1 use index(sp) left join choldings c1 use index(sp) on p1.stkcode = c1.stkcode and p1.participantname = c1.participantname)
39    union
40    (select c1.stkcode, c1.participantid, c1.participantname, if(p1.shares is null, c1.shares/100, (c1.shares - p1.shares)/100) as averagediff from pholdings p1 use index(sp) right join choldings c1 use index(sp) on c1.stkcode = p1.stkcode and c1.participantname = p1.participantname);