SO52665170, MySQL - rextester

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version';

DROP TABLE IF EXISTS x_ports;
DROP TABLE IF EXISTS x_weights;
DROP TABLE IF EXISTS x_prices;

CREATE TABLE IF NOT EXISTS x_ports (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS x_weights (id INT NOT NULL AUTO_INCREMENT, port_id INT, inst_id INT, weight DOUBLE, PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS x_prices (id INT NOT NULL AUTO_INCREMENT, inst_id INT, trade_date DATE, price DOUBLE, PRIMARY KEY (id));

INSERT INTO x_ports (name) VALUES ('PORT A');
INSERT INTO x_ports (name) VALUES ('PORT B');

INSERT INTO x_weights (port_id, inst_id, weight) VALUES (1, 1, 20.0);
INSERT INTO x_weights (port_id, inst_id, weight) VALUES (1, 2, 80.0);
INSERT INTO x_weights (port_id, inst_id, weight) VALUES (2, 1, 100.0);

INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-01', 1.12);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-02', 1.13);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-03', 1.12);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-04', 1.12);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-05', 1.13);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-06', 1.14);

INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-01', 50.23);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-02', 50.45);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-03', 50.30);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-04', 50.29);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-05', 50.40);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-06', 50.66);

SELECT Z.Trade_Date
     , max(case when name = 'Port A' then P_RETURN end) as PortA
     , max(case when name = 'Port B' then P_RETURN end) as PortB
FROM (
## Raw data
SELECT CurDay.*, sum(CurDay.Price/NextDay.Price*CurDay.Weight)/CurDay.Inst_Cnt as P_Return
FROM (SELECT x1.*, @RN:=@RN+1 rn,x2.inst_cnt
      FROM (SELECT prt.name, W.port_ID, W.inst_ID, W.weight, prc.trade_Date, Prc.Price
            FROM x_ports Prt
            INNER JOIN x_weights W
              on W.Port_ID = prt.ID
            INNER JOIN x_prices Prc
              on Prc.INST_ID = W.INST_ID
            ORDER BY W.port_id, W.inst_id,trade_Date) x1
     CROSS join (SELECT @RN:=0) r
     INNER join (SELECT count(*) inst_Cnt, port_ID 
                 FROM x_weights
                 GROUP BY Port_ID) x2
        on X1.Port_ID = X2.Port_ID) CurDay
LEFT JOIN (SELECT x1.*, @RN2:=@RN2+1 rn2
           FROM (SELECT prt.name, W.port_ID, W.inst_ID, W.weight, prc.trade_Date, Prc.Price
                 FROM x_ports Prt
                 INNER JOIN x_weights W
                   on W.Port_ID = prt.ID
                 INNER JOIN x_prices Prc
                   on Prc.INST_ID = W.INST_ID
                 ORDER BY W.port_id, W.inst_id,trade_Date) x1
                 CROSS join (SELECT @RN2:=0) r
           ) NextDay
   on NextDay.Port_ID = CurDay.Port_ID
  and NextDay.Inst_ID = curday.Inst_ID
  and NextDay.RN2 = CurDay.RN+1
GROUP BY CurDay.Port_ID,  CurDay.Inst_ID,  CurDay.Trade_Date) Z
##END RAW DATA
GROUP BY Trade_Date;


absolute service time: 0,42 sec