kaztomo日記

_ [SQL]俄SQL使いでも、ググりながらなんとかデータ作成

ポスグレはたいそうだし、sqlite3のほうが軽い（と勝手に思っている）ので、 sqlite3 でも為替データ登録のための前準備処理が出来るか試してみた。

概要として、同じ日時であっても OHLC が異なる場合を想定し、 その場合は旧テーブルの行を削除しつつ、最新テーブルをコピーしてくる。

/* make hd, hd1 table. hd means Historical Data */
DROP TABLE IF EXISTS hd;
DROP TABLE IF EXISTS hd1;
DROP TABLE IF EXISTS hdtmp;

create table hd (
id INTEGER PRIMARY KEY AUTOINCREMENT,
Date TEXT not NULL,
Time TEXT not NULL,
Open REAL not NULL,
High REAL not NULL,
Low  REAL not NULL,
Close REAL not NULL
);

create temp table hdtmp (
Date TEXT not NULL,
Time TEXT not NULL,
Open REAL not NULL,
High REAL not NULL,
Low  REAL not NULL,
Close REAL not NULL
);

create temp table hd1 as select * from hd;

/* import csv to temp table */
.import /PathToFOREXhistorical/2017.csv hdtmp

/* insert values from hdtmp to hd with automated ID */
INSERT INTO hd (Date, Time, Open, High, Low, Close)
 SELECT * FROM hdtmp;

DROP TABLE hdtmp;

/* insert temp data to hd1 */
INSERT INTO hd1 VALUES(1, '2017/06/08', '05:58:00', 6,6,6,6);
INSERT INTO hd1 VALUES(2, '2017/06/08', '05:59:00', 7,7,7,7);
INSERT INTO hd1 VALUES(3, '2017/06/08', '06:00:00', 8,8,8,8);
INSERT INTO hd1 VALUES(4, '2017/06/08', '06:01:00', 9,9,9,9);

/* count items */
SELECT "hd:";
SELECT count(*) FROM hd;
SELECT * FROM hd ORDER BY date desc, time desc LIMIT 10;

SELECT "hd1:";
SELECT count(*) FROM hd1;
SELECT * FROM hd1 ORDER BY date desc, time desc LIMIT 10;

/* count comparable data (same date, same time, but wrong OHLC) */
SELECT "differ between hd and hd1";
SELECT count(*) FROM hd, hd1
 WHERE hd.date = hd1.date
 AND hd.time = hd1.time
 AND (NOT hd.open = hd1.open);
SELECT hd.date, hd.time FROM hd, hd1
 WHERE hd.date = hd1.date
 AND hd.time = hd1.time
 AND (NOT hd.open = hd1.open);

/* delete comparable data from hd */
SELECT "Deleting comparable data";
DELETE FROM hd WHERE (date,time) IN (
 SELECT hd.date,hd.time FROM hd, hd1
 WHERE hd.date = hd1.date
 AND hd.time = hd1.time
 AND (NOT hd.open = hd1.open)
);
SELECT count(*) FROM hd;
SELECT * FROM hd ORDER BY date desc, time desc LIMIT 5;

/* inserting values from hd1 to hd */
SELECT "Inserting...hd:";
INSERT INTO hd (Date,Time,Open,High,Low,Close)
 SELECT Date,time,Open,High,Low,Close FROM hd1;

SELECT count(*) FROM hd;
SELECT * FROM hd ORDER BY date desc, time desc LIMIT 5;

参考にさせて頂いたサイト：

• SQL As Understood By SQLite
• SQL、２つのテーブルで条件一致したものだけdeleteする方法は？
• SQLiteで最も新しく追加されたレコードを高速に取得