SELECT f.order_time,
f.code,
f.1_buy_price,
f.1_buy_qty,
f.1_sell_price,
f.1_sell_qty,
f.2_buy_price,
f.2_buy_qty,
f.2_sell_price,
f.2_sell_qty,
f.3_buy_price,
f.3_buy_qty,
f.3_sell_price,
f.3_sell_qty,
f.4_buy_price,
f.4_buy_qty,
f.4_sell_price,
f.4_sell_qty,
f.5_buy_price,
f.5_buy_qty,
f.5_sell_price,
f.5_sell_qty
FROM ( SELECT a.order_time,
a.code,
a.buy_price AS 1_buy_price,
a.buy_qty AS 1_buy_qty,
a.sell_price AS 1_sell_price,
a.sell_qty AS 1_sell_qty,
b.buy_price AS 2_buy_price,
b.buy_qty AS 2_buy_qty,
b.sell_price AS 2_sell_price,
b.sell_qty AS 2_sell_qty,
c.buy_price AS 3_buy_price,
c.buy_qty AS 3_buy_qty,
c.sell_price AS 3_sell_price,
c.sell_qty AS 3_sell_qty,
d.buy_price AS 4_buy_price,
d.buy_qty AS 4_buy_qty,
d.sell_price AS 4_sell_price,
d.sell_qty AS 4_sell_qty,
e.buy_price AS 5_buy_price,
e.buy_qty AS 5_buy_qty,
e.sell_price AS 5_sell_price,
e.sell_qty AS 5_sell_qty
FROM data a
LEFT JOIN data b ON b.code = '100001' AND a.order_time = b.order_time AND a.code = b.code
LEFT JOIN data c ON c.code = '100002' AND a.order_time = c.order_time AND a.code = c.code
LEFT JOIN data d ON d.code = '100003' AND a.order_time = d.order_time AND a.code = d.code
LEFT JOIN data e ON e.code= '100004' AND a.order_time = e.order_time AND a.code = e.code
WHERE a.code = '100000'
AND a.code = 'ETH'
and a.order_time like '2019-03-07%') f
전체 쿼리는 위와 같습니다. 하루 2천만건 이상 쌓이는 테이블에서 하루치 데이터를 한로우에 나올 수 있도록 하기 위해서 이런식으로 쿼리를 작성 했지만
30초 이상 걸리는 문제로 인해 고민에 빠졌습니다.
좀더 빠르게 셀렉트 할 수 있는 해결방법이 있을까요? ㅜ.ㅜ
한번 보시고 한번만 살려주세요 ㅜ.ㅜ
읽어 주셔셔 감사합니다.
|