Cucco’s Compute Hack

コンピュータ関係の記事を書いていきます。

mysqlでのテーブルの1対多のjoin。

mysql Python

mysqlでのテーブルの1対多のjoin。

TABLE_SECには、5秒ごとのデータが入っている。
TABLE_HOURには、1時間ごとのデータが入っている。

10:00:06の時点では、10:00:00からの5秒間のデータは確定しているが、
10:00:00から1時間のデータは確定していない。
そのため、9:00:00から1時間のデータを参考の資料として対応づける。

TABLE_SECでは、timeShift1、timeShift2は使用していないが、同じテーブル構成になるように定義を残している。
TABLE_HOURでは、timeShift1は1時間先、timeShift2は2時間先の時刻を指している。

INNER JOINで2つの条件で1件に絞ってJOINしている。

# -*- coding: utf-8

import mysql.connector
import datetime

config = {
    'user': 'root',
    'password': 'Password',
    'host': 'localhost',
    'database':'testdb',
    'charset':'utf8'
}
cnx = mysql.connector.connect(**config)
cur = cnx.cursor(buffered=True)

sql = 'DROP TABLE IF EXISTS TABLE_SEC;'
cur.execute(sql)
cnx.commit()

sql = 'DROP TABLE IF EXISTS TABLE_HOUR;'
cur.execute(sql)
cnx.commit()

sql = 'CREATE TABLE IF NOT EXISTS TABLE_SEC (id INT UNSIGNED NOT NULL AUTO_INCREMENT,\
							time DATETIME DEFAULT NULL, \
							timeShift1 DATETIME DEFAULT NULL, \
							timeShift2 DATETIME DEFAULT NULL, \
							volume INT UNSIGNED DEFAULT NULL,\
							valid INT UNSIGNED DEFAULT NULL ,\
							primary key(id),\
							unique (time));'
cur.execute(sql)
cnx.commit()

sql = 'CREATE TABLE IF NOT EXISTS TABLE_HOUR (id INT UNSIGNED NOT NULL AUTO_INCREMENT,\
							time DATETIME DEFAULT NULL, \
							timeShift1 DATETIME DEFAULT NULL, \
							timeShift2 DATETIME DEFAULT NULL, \
							volume INT UNSIGNED DEFAULT NULL,\
							valid INT UNSIGNED DEFAULT NULL ,\
							primary key(id),\
							unique (time));'
cur.execute(sql)
cnx.commit()
sql='INSERT INTO TABLE_SEC (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T9:59:55","2016-12-12T10:00:00","2016-12-12T10:00:5",1,0);'
cur.execute(sql)
cnx.commit()
sql='INSERT INTO TABLE_SEC (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T10:00:00","2016-12-12T10:00:05","2016-12-12T10:00:10",1,1);'
cur.execute(sql)
cnx.commit()
sql='INSERT INTO TABLE_SEC (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T10:00:05","2016-12-12T10:00:10","2016-12-12T10:00:15",1,2);'
cur.execute(sql)
cnx.commit()
sql='INSERT INTO TABLE_SEC (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T11:00:10","2016-12-12T11:00:15","2016-12-12T11:00:20",1,3);'
cur.execute(sql)
cnx.commit()
sql='INSERT INTO TABLE_SEC (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T11:00:15","2016-12-12T11:00:20","2016-12-12T11:00:25",1,4);'
cur.execute(sql)
cnx.commit()
sql='INSERT INTO TABLE_SEC (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T10:00:20","2016-12-12T10:00:25","2016-12-12T10:00:30",1,5);'
cur.execute(sql)
cnx.commit()

sql='INSERT INTO TABLE_HOUR (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T8:00:00","2016-12-12T9:00:00","2016-12-12T10:00:00",2,8);'
cur.execute(sql)
cnx.commit()
sql='INSERT INTO TABLE_HOUR (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T9:00:00","2016-12-12T10:00:00","2016-12-12T11:00:00",2,9);'
cur.execute(sql)
cnx.commit()
sql='INSERT INTO TABLE_HOUR (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T10:00:00","2016-12-12T11:00:00","2016-12-12T12:00:00",2,10);'
cur.execute(sql)
cnx.commit()
sql='INSERT INTO TABLE_HOUR (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T11:00:00","2016-12-12T12:00:00","2016-12-12T13:00:00",2,11);'
cur.execute(sql)
cnx.commit()
sql='INSERT INTO TABLE_HOUR (time,timeShift1,timeShift2,volume,valid) VALUES("2016-12-12T12:00:00","2016-12-12T13:00:00","2016-12-12T14:00:00",2,12);'
cur.execute(sql)
cnx.commit()

#ここからが本題。
#SELECT * FROM TABLE_HOUR INNER JOIN TABLE_SEC ON TABLE_SEC.time >=TABLE_HOUR.timeShift1 AND TABLE_SEC.time < TABLE_HOUR.timeShift2;
sql='SELECT TABLE_SEC.id,TABLE_SEC.time,TABLE_HOUR.time FROM TABLE_HOUR INNER JOIN TABLE_SEC ON TABLE_SEC.time >=TABLE_HOUR.timeShift1 AND TABLE_SEC.time < TABLE_HOUR.timeShift2;'

cur.execute(sql)
record = cur.fetchone()
while record!=None:
	print(record)
	record = cur.fetchone()

cur.close()
cnx.close()

結果

(1, datetime.datetime(2016, 12, 12, 9, 59, 55), datetime.datetime(2016, 12, 12, 8, 0))
(2, datetime.datetime(2016, 12, 12, 10, 0), datetime.datetime(2016, 12, 12, 9, 0))
(3, datetime.datetime(2016, 12, 12, 10, 0, 5), datetime.datetime(2016, 12, 12, 9, 0))
(6, datetime.datetime(2016, 12, 12, 10, 0, 20), datetime.datetime(2016, 12, 12, 9, 0))
(4, datetime.datetime(2016, 12, 12, 11, 0, 10), datetime.datetime(2016, 12, 12, 10, 0))
(5, datetime.datetime(2016, 12, 12, 11, 0, 15), datetime.datetime(2016, 12, 12, 10, 0))