数据库共708.6万行,数据388MB,索引213MB共600MB。查周数据第一次查询587行用281ms,以后几次为47ms和63ms,查日数据2846行,花费125ms。
没有进行过SQL的优化,谁能改进查询请在下面跟贴。下面是我的代码:
library(RODBC)
#连接数据库
channel <- odbcConnect("postgresql35w", uid="postgres", pwd="admin")
#从股价数据库中读入上证日指数
a<- sqlQuery(channel ,"
SELECT
stockprice.date,
stockprice.price
FROM
public.stockprice
WHERE
stockprice.code = 'SH000001' AND
stockprice.date > '1997-10-28'
ORDER BY
stockprice.date ASC")
#从股价数据库中读入深证日指数
sz<- sqlQuery(channel ,"
SELECT
stockprice.date,
stockprice.price
FROM
public.stockprice
WHERE
stockprice.code = 'SZ399001' AND
stockprice.date > '1997-10-28'
ORDER BY
stockprice.date ASC")
#从股价数据库中读入相关股票周价格
gg<- sqlQuery(channel ,"
select * from
(select LAST_VALUE(date) OVER (PARTITION BY to_char(date , 'IYYYIW')) as Wdate,
LAST_VALUE(price) OVER (PARTITION BY to_char(date , 'IYYYIW')) as Wprice
from (
SELECT
stockprice.date,
stockprice.price
FROM
public.stockprice
WHERE
stockprice.code = 'SH600030' AND
stockprice.date > '1997-10-28'
ORDER BY
stockprice.date ASC) as ap) as week
group by Wdate , Wprice
order by Wdate")
#断开数据库连接
odbcClose(channel)