I am currently trying to do some full joins on several MySQL tables.
I have these tables:
bz_fut_usd_nymex_bzf2
cl_fut_usd_nymex_clf2
hh_fut_usd_nymex_hhf2
ho_fut_usd_nymex_hof2
qm_fut_usd_nymex_qmf2
rb_fut_usd_nymex_rbf2
mcl_fut_usd_nymex_mclf2
Each table from that list has these columns:
DateTime
Ask
Bid
I am now trying to join these tables into one large view by using datetime as primary key.
First, I have created a single SQL statement using the Union command for full join. However, this was not working because I was getting a timeout error.
As a workaround I have tried to create a view f2_bz_cl with these tables
bz_fut_usd_nymex_bzf2
cl_fut_usd_nymex_clf2
and a view f2_hh_ho with these tables:
hh_fut_usd_nymex_hhf2
ho_fut_usd_nymex_hof2
After that I have created a View f2_BZ_CL_HH_HO based on the views that I have created earlier.
So far that has worked well, I continued with my procedure and tried to create the final large view f2_energies.
See below for my intermediate views:
Create view f2bz_cl as
SELECT bz.datetime
,min(bz.ask) as bz_ask
, min(bz.bid) as bz_bid
, min(cl.ask) as cl_ask
, min(cl.bid) as cl_bid
FROM ibkr.bz_fut_usd_nymex_bzf2 as bz
left join cl_fut_usd_nymex_clf2 as cl on cl.DateTime = bz.DateTime
union
SELECT bz.datetime
,min(bz.ask) as bz_ask
, min(bz.bid) as bz_bid
, min(cl.ask) as cl_ask
, min(cl.bid) as cl_bid
FROM ibkr.bz_fut_usd_nymex_bzf2 as bz
right join cl_fut_usd_nymex_clf2 as cl on cl.DateTime = bz.DateTime
group by datetime;
Create view f2hh_ho as
SELECT hh.datetimeho_fut_usd_nymex_hof2
,min(hh.ask) as hh_ask
, min(hh.bid) as hh_bid
, min(ho.ask) as ho_ask
, min(ho.bid) as ho_bid
FROM ibkr.hh_fut_usd_nymex_hhf2 as hh
left join ho_fut_usd_nymex_hof2 as ho on hh.DateTime = ho.DateTime
union
SELECT hh.datetime
,min(hh.ask) as hh_ask
, min(hh.bid) as hh_bid
, min(ho.ask) as ho_ask
, min(ho.bid) as ho_bid
FROM ibkr.hh_fut_usd_nymex_hhf2 as hh
right join ho_fut_usd_nymex_hof2 as ho on hh.DateTime = ho.DateTime
group by datetime;
Create view f2QM_RB as
SELECT QM.datetime
,min(QM.ask) as QM_ask
, min(QM.bid) as QM_bid
, min(RB.ask) as RB_ask
, min(RB.bid) as RB_bid
FROM ibkr.qm_fut_usd_nymex_qmf2 as QM
left join rb_fut_usd_nymex_rbf2 as RB on QM.DateTime = RB.DateTime
union
SELECT QM.datetime
,min(QM.ask) as QM_ask
, min(QM.bid) as QM_bid
, min(RB.ask) as RB_ask
, min(RB.bid) as RB_bid
FROM ibkr.qm_fut_usd_nymex_qmf2 as QM
Right join rb_fut_usd_nymex_rbf2 as RB on QM.DateTime = RB.DateTime
group by datetime;
Create view f2_QM_RB_MCL as
SELECT QMRB.datetime
,min(QMRB.QM_ask) as QM_ask
, min(QMRB.QM_bid) as QM_bid
, min(QMRB.RB_ask) as RB_ask
, min(QMRB.RB_bid) as RB_bid
, min(MCL.ask) as MCL_ask
, min(MCL.bid) as MCL_bid
FROM ibkr.f2QM_RB as QMRB
left join mcl_fut_usd_nymex_mclf2 as MCL on QMRB.DateTime = MCL.DateTime
union
SELECT QMRB.datetime
,min(QMRB.QM_ask) as QM_ask
, min(QMRB.QM_bid) as QM_bid
, min(QMRB.RB_ask) as RB_ask
, min(QMRB.RB_bid) as RB_bid
, min(MCL.ask) as MCL_ask
, min(MCL.bid) as MCL_bid
FROM ibkr.f2QM_RB as QMRB
Right join mcl_fut_usd_nymex_mclf2 as MCL on QMRB.DateTime = MCL.DateTime
group by datetime;
Create view f2_BZ_CL_HH_HO as
SELECT f2bz_cl.datetime
,min(f2bz_cl.bz_ask) as bz_ask
,min(f2bz_cl.bz_bid) as bz_bid
,min(f2bz_cl.cl_ask) as cl_ask
,min(f2bz_cl.cl_bid) as cl_bid
,min(f2hh_ho.hh_ask) as hh_ask
,min(f2hh_ho.hh_bid) as hh_bid
,min(f2hh_ho.ho_ask) as ho_ask
,min(f2hh_ho.ho_bid) as ho_bid
FROM ibkr.f2bz_cl
left join f2hh_ho on f2bz_cl.datetime = f2hh_ho.datetime
union
SELECT f2bz_cl.datetime
,min(f2bz_cl.bz_ask) as bz_ask
,min(f2bz_cl.bz_bid) as bz_bid
,min(f2bz_cl.cl_ask) as cl_ask
,min(f2bz_cl.cl_bid) as cl_bid
,min(f2hh_ho.hh_ask) as hh_ask
,min(f2hh_ho.hh_bid) as hh_bid
,min(f2hh_ho.ho_ask) as ho_ask
,min(f2hh_ho.ho_bid) as ho_bid
FROM ibkr.f2bz_cl
right join f2hh_ho on f2bz_cl.datetime = f2hh_ho.datetime
group by datetime;
Below is My final View where I combine all Views:
Create view f2_energies as
SELECT f2_BZ_CL_HH_HO.datetime
,min(f2_BZ_CL_HH_HO.bz_ask) as bz_ask
,min(f2_BZ_CL_HH_HO.bz_bid) as bz_bid
,min(f2_BZ_CL_HH_HO.cl_ask) as cl_ask
,min(f2_BZ_CL_HH_HO.cl_bid) as cl_bid
,min(f2_BZ_CL_HH_HO.hh_ask) as hh_ask
,min(f2_BZ_CL_HH_HO.hh_bid) as hh_bid
,min(f2_BZ_CL_HH_HO.ho_ask) as ho_ask
,min(f2_BZ_CL_HH_HO.ho_bid) as ho_bid
,min(f2_QM_RB_MCL.QM_ask) as QM_ask
, min(f2_QM_RB_MCL.QM_bid) as QM_bid
, min(f2_QM_RB_MCL.RB_ask) as RB_ask
, min(f2_QM_RB_MCL.RB_bid) as RB_bid
, min(f2_QM_RB_MCL.MCL_ask) as MCL_ask
, min(f2_QM_RB_MCL.MCL_bid) as MCL_bid
FROM ibkr.f2_BZ_CL_HH_HO
left join f2_QM_RB_MCL on f2_QM_RB_MCL.datetime = f2_BZ_CL_HH_HO.datetime
union
SELECT f2_BZ_CL_HH_HO.datetime
,min(f2_BZ_CL_HH_HO.bz_ask) as bz_ask
,min(f2_BZ_CL_HH_HO.bz_bid) as bz_bid
,min(f2_BZ_CL_HH_HO.cl_ask) as cl_ask
,min(f2_BZ_CL_HH_HO.cl_bid) as cl_bid
,min(f2_BZ_CL_HH_HO.hh_ask) as hh_ask
,min(f2_BZ_CL_HH_HO.hh_bid) as hh_bid
,min(f2_BZ_CL_HH_HO.ho_ask) as ho_ask
,min(f2_BZ_CL_HH_HO.ho_bid) as ho_bid
,min(f2_QM_RB_MCL.QM_ask) as QM_ask
, min(f2_QM_RB_MCL.QM_bid) as QM_bid
, min(f2_QM_RB_MCL.RB_ask) as RB_ask
, min(f2_QM_RB_MCL.RB_bid) as RB_bid
, min(f2_QM_RB_MCL.MCL_ask) as MCL_ask
, min(f2_QM_RB_MCL.MCL_bid) as MCL_bid
FROM ibkr.f2_BZ_CL_HH_HO
Right join f2_QM_RB_MCL on f2_QM_RB_MCL.datetime = f2_BZ_CL_HH_HO.datetime
group by datetime;
select * from f2_energies;
For this query, I am not using any indexes, only the default ones: Indexes
The view can be created without any issues. However, when trying to do the select command on the view:
select * from f2_energies;
I get an error:
09:52:39 select * from f2_energies LIMIT 0, 1000 Error Code: 2013. Lost connection to MySQL server during query 30.016 sec
There must be a better way to merge all these tables into one on MySQL. Any advise is highly appreciated.
Thanks in advance!
CodePudding user response：
What you might best be going with is ensure an index on date/time on EACH table. From that, do it all in a single select. The first FROM table will be a single query getting ALL possible date/time fields from ALL the tables. Then, immediately LEFT JOIN to all originating tables. This essentially prevents the need of all right-joins. Being distinct at least guarantees all unique date/times for your otherwise nested left/right of raw tables, then left/right of the views after their aggregates.
So, the prequery, result alias dt, has all date times. Then left joining them to their original tables, you can then apply the MIN() per each context. Something like:
select
min(bz.ask) bz_ask,
min(bz.bid) bz_bid,
min(cl.ask) cl_ask,
min(cl.bid) cl_bid,
min(hh.ask) hh_ask,
min(hh.bid) hh_bid,
min(ho.ask) ho_ask,
min(ho.bid) ho_bid,
min(QM.ask) as QM_ask,
min(QM.bid) as QM_bid,
min(RB.ask) as RB_ask,
min(RB.bid) as RB_bid,
min(QMRB.QM_ask) as QM_ask,
min(QMRB.QM_bid) as QM_bid,
min(QMRB.RB_ask) as RB_ask,
min(QMRB.RB_bid) as RB_bid,
min(MCL.ask) as MCL_ask,
min(MCL.bid) as MCL_bid
from
( select distinct datetime from bz_fut_usd_nymex_bzf2
union select distinct datetime from cl_fut_usd_nymex_clf2
union select distinct datetime from hh_fut_usd_nymex_hhf2
union select distinct datetime from ho_fut_usd_nymex_hof2
union select distinct datetime from qm_fut_usd_nymex_qmf2
union select distinct datetime from rb_fut_usd_nymex_rbf2
union select distinct datetime from f2QM_RB
union select distinct datetime from mcl_fut_usd_nymex_mclf2 ) dt
left join bz_fut_usd_nymex_bzf2 bz
on dt.datetime = bz.datetime
left join cl_fut_usd_nymex_clf2 cl
on dt.datetime = cl.datetime
left join hh_fut_usd_nymex_hhf2 hh
on dt.datetime = hh.datetime
left join ho_fut_usd_nymex_hof2 ho
on dt.datetime = ho.datetime
left join qm_fut_usd_nymex_qmf2 qm
on dt.datetime = qm.datetime
left join rb_fut_usd_nymex_rbf2 rb
on dt.datetime = rb.datetime
left join ibkr.f2QM_RB QMRB
on dt.datetime = QMRB.datetime
left join mcl_fut_usd_nymex_mclf2 MCL
on dt.datetime = MCL.datetime
group by
dt.DateTime
For those where no such matching date, those columns would be null in the results. You could apply a COALESCE( min(), 0 ) to prevent showing null, but dont know the importance of these as the context of the query is vague as-is.
CodePudding user response：
I Finally was able to get a result by increasing the timeout values: enter image description here
