HSPポータル
サイトマップ お問い合わせ


HSPTV!掲示板

スレッド一覧 | 新規書き込み | 新着一覧 | 使い方 | 過去ログ

未解決 解決 停止 削除要請

2019
0905		masako99sqleleでテーブルを結合して値を抽出したい7解決


masako99

リンク

2019/9/5(Thu) 16:53:16|NO.88329



/*
	簡単な仮想テーブルです。
*/

;■ウィンドウ初期設定
screen 0,1200,700,0,0,0

#include "sqlele.hsp"
// DBオープン //
sql_open ":memory:"
;■ A (テーブル : コラム数 約30 : レコード数 約30万件)
sql_q "create table A(地域, 場所, 店名, 年月ID, 年月日ID)"
sql_q "insert into A values('関東', '東京', '麻布', '東京201001', '東京20100101')"
sql_q "insert into A values('関東', '埼玉', '大宮', '埼玉201001', '埼玉20100102')"
sql_q "insert into A values('関西', '大阪', '梅田', '大阪201001', '大阪20100103')"
sql_q "insert into A values('関東', '東京', '麻布', '東京201907', '東京20190705')"
sql_q "insert into A values('関東', '埼玉', '大宮', '埼玉201908', '埼玉20190805')"
sql_q "insert into A values('関西', '大阪', '梅田', '大阪201909', '大阪20190905')"
;■ B (テーブル : コラム数 5 : レコード数 約2,000件)
sql_q "create table B(販売 integer, 原価 integer, 単価 integer, 年月ID, 年月日ID)"
sql_q "insert into B values(2000, 1000, 3000, '東京201001', '東京20100101')"
sql_q "insert into B values(2500, 1100, 3100, '埼玉201908', '埼玉20190805')"
sql_q "insert into B values(3000, 1200, 3200, '大阪201909', '大阪20190905')"
;■ C (テーブル : コラム数 約30 : レコード数 約30,000件)
sql_q "create table C(売上 integer, 人員 integer, 給与 integer, 年月ID)"
sql_q "insert into C values(6000, 13, 2600, '東京201001')"
sql_q "insert into C values(7750, 10, 2100, '埼玉201908')"
sql_q "insert into C values(9600, 15, 3200, '大阪201909')"
sql_q "select * from A"
su=stat
	mes "■ A (テーブル : コラム数 約30 : レコード数 約30万件)"
	mes "┏━━┯━━┯━━┯━━━━┯━━━━━┯━━━━━━┓"
	mes "┃地域│場所│店名│・・・・│　年月ID　│　年月日ID　┃"
	mes "┣━━┿━━┿━━┿━━━━┿━━━━━┿━━━━━━┫"

	repeat su
	mesA = "┃"+sql_v("地域")+"│"+sql_v("場所")+"│"+sql_v("店名")+"│・・・・│"+sql_v("年月ID")+"│"+sql_v("年月日ID")+"┃"
	if (cnt=0)|(cnt>3){
		mes mesA+"★"
	} else {
		mes mesA
	}
	sql_next
	loop

sql_q "select * from B"
su=stat
	pos 500,0
	mes "■ B (テーブル : コラム数 5 : レコード数 約2,000件)"
	mes "┏━━┯━━┯━━┯━━━━━┯━━━━━━┓"
	mes "┃販売│原価│単価│　年月ID　│　年月日ID　┃"
	mes "┣━━┿━━┿━━┿━━━━━┿━━━━━━┫"

	repeat su
	mes "┃"+sql_i("販売")+"│"+sql_i("原価")+"│"+sql_i("単価")+"│"+sql_v("年月ID")+"│"+sql_v("年月日ID")+"┃"
	sql_next
	loop

sql_q "select * from C"
su=stat
	pos 500,150
	mes "■ C (テーブル : コラム数 約30 : レコード数 約3万件)"
	mes "┏━━┯━━┯━━┯━━━━┯━━━━━┓"
	mes "┃売上│人員│給与│・・・・│　年月ID　┃"
	mes "┣━━┿━━┿━━┿━━━━┿━━━━━┫"

	repeat su
	mes "┃"+sql_i("売上")+"│　"+sql_i("人員")+"│"+sql_i("給与")+"│・・・・│"+sql_v("年月ID")+"┃"
	sql_next
	loop

	SELECT="select count(*)"
	SELECT+=" from A inner join B"
	SELECT+=" on A.年月日ID = B.年月日ID"
/////////////
sql_q SELECT
/////////////
su=stat
	pos 10,220
	mes "・テーブルA.Bを結合して下記のような結果を出したいのですが\n"
	mes "レコード数 = "+sql_i("count(*)")

	SELECT="select A.年月日ID, A.店名, B.販売"
	SELECT+=" from A inner join B"
	SELECT+=" on A.年月日ID = B.年月日ID"
	;SELECT+=" where A.地域 = '関東' and B.原価 > 1000"
/////////////
sql_q SELECT
/////////////
su=stat
	mes "年月日ID　　;店名;販売 "
	repeat su
	mes sql_v("年月日ID")+";"+sql_v("店名")+";"+sql_i("販売")
	sql_next
	loop

/*
	テーブルAの[年月ID]は重複がありますが,テーブルBは単独です。
	テーブルA.Bの[年月日ID]は単独です。
	テーブルCの[年月ID]は単独ですが[年月日ID]は有りません
	
やりたい事
	�� テーブルA.Bを結合してそれぞれのコラムの値を抽出したい。
	�� �，能个�[年月ID]からテーブルCのコラムの値を抽出したい。
	�� 試したsql文を1つの纏めたい。
	
実際のデータベースで試した事
	まず [ inner join ] ではポインターの先にグルグルマークが出てうまく行きませんでした。
	 [ outer left join ] , [ inner join ] , [ INTERSECT ] 等も同様でした。
	唯一、[ UNION All ]で何とかなりそうなので。
*/
mes "\n===[ UNION All ] の準備===\n"
mes "��-Subquery count---------"
	//// Subquery count

	SELECT="SELECT count(年月日ID) FROM B"
	SELECT+=" WHERE 年月日ID IN "
	SELECT+=" (SELECT 年月日ID FROM A)"
//////////// 
sql_q SELECT
//////////// 
mes "B から レコード数 = "+sql_i("count(年月日ID)")
count_A=sql_i("count(年月日ID)")

mes "\n---Subquery 店名---------"

	//// Subquery 店名

	SELECT="SELECT 年月日ID, 店名 FROM A"
	SELECT+=" WHERE 年月日ID IN "
	SELECT+=" (SELECT 年月日ID FROM B)"
//////////// 
sql_q SELECT
//////////// 
	su=stat
mes "A から 店名"

	repeat su
	mes sql_v("年月日ID")+" : "+sql_v("店名")
	sql_next
	loop
	
mes "\n-----Subquery 販売-------"

	//// Subquery 販売

	SELECT="SELECT 年月日ID, 販売 FROM B"
	SELECT+=" WHERE 年月日ID IN "
	SELECT+=" (SELECT 年月日ID FROM A)"
//////////// 
sql_q SELECT
//////////// 
	su=stat
mes "B から 販売"

	repeat su
	mes sql_v("年月日ID")+" : "+sql_i("販売")
	sql_next
	loop
	pos 300,365
mes ";---[ UNION All ]-----------"

		SELECT=" SELECT 年月日ID, 店名 FROM A"
		SELECT+=" WHERE 年月日ID IN "
		SELECT+=" (SELECT 年月日ID FROM B)"
	SELECT+=" UNION All "
		SELECT+=" SELECT 年月日ID, 販売 FROM B"
		SELECT+=" WHERE 年月日ID IN "
		SELECT+=" (SELECT 年月日ID FROM A)"

//////////// 
sql_q SELECT
//////////// 

	su=stat

mes "AとBから 年月日ID,店名,販売を[ UNION All ]で"
	mes "年月日ID　　;店名"
	repeat su
	mes sql_v("年月日ID")+";"+sql_v("店名")
	sql_next
	loop
	

	//// Subquery 店名 group_concat(店名)
	SELECT="SELECT 年月日ID, group_concat(店名) FROM"
	SELECT+=" ("
		SELECT+=" SELECT 年月日ID, 店名 FROM A"
		SELECT+=" WHERE 年月日ID IN "
		SELECT+=" (SELECT 年月日ID FROM B)"
	SELECT+=" UNION All "
		SELECT+=" SELECT 年月日ID, 販売 FROM B"
		SELECT+=" WHERE 年月日ID IN "
		SELECT+=" (SELECT 年月日ID FROM A)"

	SELECT+=" )"
	SELECT+=" group by 年月日ID"
;pos 800,300

//////////// 
sql_q SELECT
//////////// 
	su=stat
mes "\n��-[ UNION All ]-----------"
mes "販売が店名の所に来ているのでgroup_concat(店名)で"
i=0
	mes "年月日ID　　;店名;販売"
	repeat su
	split sql_v("group_concat(店名)"),",",a
	if stat=2{
		i+
		mes sql_v("年月日ID")+","+sql_v("group_concat(店名)")
	}
	
if i=count_A:break
	sql_next
	loop
	
	pos 750,365
mes "��-Subquery count---------"
mes "条件で絞ります"
	SELECT="SELECT count(年月日ID) FROM B"
	SELECT+=" WHERE 単価 > 3000 and 年月日ID IN "
	SELECT+=" (SELECT 年月日ID FROM A
	SELECT+=" where 地域 = '関東')"
//////////// 
sql_q SELECT
//////////// 
mes "B から レコード数 = "+sql_i("count(年月日ID)")
count_A=sql_i("count(年月日ID)")

mes "\n��-[ UNION All ]-----------"

	SELECT="SELECT 年月日ID, group_concat(店名), group_concat(地域) FROM"
	SELECT+=" ("
		SELECT+=" SELECT 年月日ID, 店名, 地域 FROM A"
		SELECT+=" WHERE 年月日ID IN "
		SELECT+=" (SELECT 年月日ID FROM B where 単価 > 3000)"
	SELECT+=" UNION All "
		SELECT+=" SELECT 年月日ID, 販売, 単価 FROM B"
		SELECT+=" WHERE 年月日ID IN "
		SELECT+=" (SELECT 年月日ID FROM A where 地域 = '関東')"

	SELECT+=" )"
	SELECT+=" group by 年月日ID"
;pos 800,300

//////////// 
sql_q SELECT
//////////// 
	su=stat
i=0
	mes "年月日ID　　;店名;販売;地域;単価"
	repeat su
	split sql_v("group_concat(店名)"),",",a
	if stat=2{
		i+
		mes sql_v("年月日ID")+","+sql_v("group_concat(店名)")+","+sql_v("group_concat(地域)")
	}
	
if i=count_A:break
	sql_next
	loop

// DBクローズ //
sql_close

stop

/*
実際のsql文では�，鉢△能侏茲泙靴燭�
join系が使えないのが全くの誤算で、テーブルCに行く前に力尽きてしまいました。
sql文を一つに纏めて更にテーブルCからコラムの値を抽出する事は可能でしょうか？
よろしくお願いいたします。(��、�い肋魴錣鮖遒靴討澆泙靴拭�)
*/



この記事に返信する

さか

リンク

2019/9/5(Thu) 22:11:38|NO.88333

inner joinが出来ないとかUNION ALLで行うとかPG内に記載してるので
質問がわかりづらいですがSQLの書き方の話ですよね？
以下でどうですか。
違っていたら教えてください。



;■ウィンドウ初期設定
screen 0,1200,700,0,0,0

#include "sqlele.hsp"
// DBオープン //
sql_open ":memory:"
;■ A (テーブル : コラム数 約30 : レコード数 約30万件)
sql_q "create table A(地域, 場所, 店名, 年月ID, 年月日ID)"
sql_q "insert into A values('関東', '東京', '麻布', '東京201001', '東京20100101')"
sql_q "insert into A values('関東', '埼玉', '大宮', '埼玉201001', '埼玉20100102')"
sql_q "insert into A values('関西', '大阪', '梅田', '大阪201001', '大阪20100103')"
sql_q "insert into A values('関東', '東京', '麻布', '東京201907', '東京20190705')"
sql_q "insert into A values('関東', '埼玉', '大宮', '埼玉201908', '埼玉20190805')"
sql_q "insert into A values('関西', '大阪', '梅田', '大阪201909', '大阪20190905')"
;■ B (テーブル : コラム数 5 : レコード数 約2,000件)
sql_q "create table B(販売 integer, 原価 integer, 単価 integer, 年月ID, 年月日ID)"
sql_q "insert into B values(2000, 1000, 3000, '東京201001', '東京20100101')"
sql_q "insert into B values(2500, 1100, 3100, '埼玉201908', '埼玉20190805')"
sql_q "insert into B values(3000, 1200, 3200, '大阪201909', '大阪20190905')"
;■ C (テーブル : コラム数 約30 : レコード数 約30,000件)
sql_q "create table C(売上 integer, 人員 integer, 給与 integer, 年月ID)"
sql_q "insert into C values(6000, 13, 2600, '東京201001')"
sql_q "insert into C values(7750, 10, 2100, '埼玉201908')"
sql_q "insert into C values(9600, 15, 3200, '大阪201909')"
sql_q "select * from A"
su=stat
	mes "■ A (テーブル : コラム数 約30 : レコード数 約30万件)"
	mes "┏━━┯━━┯━━┯━━━━┯━━━━━┯━━━━━━┓"
	mes "┃地域│場所│店名│・・・・│　年月ID　│　年月日ID　┃"
	mes "┣━━┿━━┿━━┿━━━━┿━━━━━┿━━━━━━┫"

	repeat su
	mesA = "┃"+sql_v("地域")+"│"+sql_v("場所")+"│"+sql_v("店名")+"│・・・・│"+sql_v("年月ID")+"│"+sql_v("年月日ID")+"┃"
	if (cnt=0)|(cnt>3){
		mes mesA+"★"
	} else {
		mes mesA
	}
	sql_next
	loop

sql_q "select * from B"
su=stat
	pos 500,0
	mes "■ B (テーブル : コラム数 5 : レコード数 約2,000件)"
	mes "┏━━┯━━┯━━┯━━━━━┯━━━━━━┓"
	mes "┃販売│原価│単価│　年月ID　│　年月日ID　┃"
	mes "┣━━┿━━┿━━┿━━━━━┿━━━━━━┫"

	repeat su
	mes "┃"+sql_i("販売")+"│"+sql_i("原価")+"│"+sql_i("単価")+"│"+sql_v("年月ID")+"│"+sql_v("年月日ID")+"┃"
	sql_next
	loop

sql_q "select * from C"
su=stat
	pos 500,150
	mes "■ C (テーブル : コラム数 約30 : レコード数 約3万件)"
	mes "┏━━┯━━┯━━┯━━━━┯━━━━━┓"
	mes "┃売上│人員│給与│・・・・│　年月ID　┃"
	mes "┣━━┿━━┿━━┿━━━━┿━━━━━┫"

	repeat su
	mes "┃"+sql_i("売上")+"│　"+sql_i("人員")+"│"+sql_i("給与")+"│・・・・│"+sql_v("年月ID")+"┃"
	sql_next
	loop



; ★　以下を追加
;-----------------------------------------------------------------------------------
pos 0
mes "\n★�� テーブルA.Bを結合してそれぞれのコラムの値を抽出したい。★"

sql={"
select * from A, B
where A.年月日ID = B.年月日ID
"}
sql_q sql: su = stat

repeat su
mes strf( "　年月日ID[%s]店名[%s]販売[%s]", sql_v("年月日ID"), sql_v("店名"), sql_v("販売") )
sql_next
loop

;-----------------------------------------------------------------------------------
pos 0
mes "\n★�� �，能个�[年月ID]からテーブルCのコラムの値を抽出したい。★"

sql={"
select * from C
where 
 c.年月ID in ( select A.年月ID from A, B where A.年月日ID = B.年月日ID )
"}
sql_q sql: su = stat

repeat su
mes strf( "　売上[%s]人員[%s]給与[%s]年月ID[%s]", sql_v("売上"), sql_v("人員"), sql_v("給与"), sql_v("年月ID") )
sql_next
loop


stop



masako99

リンク

2019/9/6(Fri) 15:52:41|NO.88336

>さかさん
ご回答ありがとうございました。
また、質問の本質が解りづらくて申し訳ございませんでした。
実際HSPの質問板なのにSQLの質問をしたのが間違っていたのかもしれませんがご容赦下さい。

一番にお聞きしたかったのは、
レコード数がそれぞれ[ 30万件と2千件,3万件のテーブルを結合 ]して値を抽出出来るのか、
特にjoin系で可能なのか、出来ない場合は今回提示したunion allの他に
何かもっと簡単な方法があるのでしょうか？と言う事です。
ご提示いただいたスクリプト


select * from A, B
where A.年月日ID = B.年月日ID
ですが、
select * は絶対無理(buffer over flow)ですので
countを取得してから次のsql文でlimitをかけなければ値を抽出する事が出来ません。
from A, B もテーブル2つからは無理(ポインターの先にグルグルマークが出て応答なし)です。
と言う訳で
where A.年月日ID = B.年月日ID も使う事が出来ません。

join系も同様である程度のレコード数を超えると使えないようです。
唯一何とかなったのが IN句を使った場合ですが
やはりSELECT 〜 FROM A, Bとテーブルを繋げるとグルグルマークが出て応答なしになります。
そこでunion allを使ってみたのですが・・・。


sql={"
select * from C
where 
 c.年月ID in ( select A.年月ID from A, B where A.年月日ID = B.年月日ID )
"}
も恐らく使えないと思います(検証もせずにゴメンナサイ)。
でも、Cの値を抽出する方法が形として明確に見えてきましたのでとてもうれしく思います。
もし、他にレコード数の多い複数のテーブルから値を抽出する方法をご存じでしたら
是非ご提示いただければと思います。必ず検証させていただきますので。

貴重なお時間を割いてご回答いただき本当にありがとうございました。



さか

リンク

2019/9/6(Fri) 22:53:59|NO.88339

なるほど、件数が多く「buffer over flow」となるのでjoinが出来ないと言うことですか。

そしたらまず各テーブルにプライマリーキーを設定したらどうですか？
年月日IDと年月IDですね。これで解決しないですか？
また「buffer over flow」なのでmemoryではなくファイルでＤＢ作成すれば違うかも知れな
いですよ。

それでもダメなら遅いと思いますが１つのＳＱＬとしないで１テーブルづつ読んでループさ
せてその中でまたテーブルを読めば固まらないんじゃないでしょうか。

それかもうSQLiteはやめて無償のORACLEやSQLSEVERを使ってODBCでやれば数十万件くらいの
件数なら速いと思います。

進展したらまた教えてください。



さか

リンク

2019/9/6(Fri) 23:13:36|NO.88340

と回答が安易だったかもと思いました。(^^;

「buffer over flow」の原因が何なのかですね。
データ数が多くてのオーバーフローだとプライマリーキーの話じゃないですね。
この場合だとすれば、１回のSQLでの実行件数を減らす必要があるのでループして配列
に入れていくのがいいですかね。
ただ30万くらいなら行けそうな気がしますが。PCの搭載メモリが少ないとかですか？

UNIONを使用するのは解決にならないような気がしますが。。



masako99

リンク

2019/9/7(Sat) 00:25:18|NO.88342

プライマリーキーという言葉自体はなんとなく知っていたと思いますが
スルーしていたので意味は全く分かりませんでした。
私が参考にしているサイトにもやはりプライマリーキーは載っていましたが
「高速にテーブルデータの検索が実行できたり、整合性の取れたデータを作成することが可能」
ということは今他のサイトを検索してみて初めて知りました。
もしかしたら使えるかもしれませんので是非検証してみたいと思います。
ただ、
>データ数が多くてのオーバーフローだとプライマリーキーの話じゃないですね
と言うのが気になりますが・・・。
>SQLiteはやめて無償のORACLEやSQLSEVERを使ってODBCでやれば数十万件くらいの件数なら速いと思います
これはダメ。HSPで動作するのはSQLiteなので。
私はHSPが大好きで、というかHSPしか出来ないのでここで使えるSQLiteで何とかしなければならないんです。
2年前くらいにHSPでデータベースが使えるのを初めて知って、
今ではワンテーブルですが30万件ほどのデータベースが扱えるようになりました。
それまではtextから変数に入れて情報を得るのに10秒近く待たなければならなかったので格段の違いです。
ちょっと話がずれてしまったので元に戻しますね。
因みに
メモリは16GB (8GB×2) DDR4-2400MHzでインテルR Core i7-8550U(8th Gen)です。
パソコンの性能のこともよくわかりませんが、少ない過ぎるのでしょうか。
今日はもう眠いので、明日プライマリーキーでぜひ検証してみたいと思います。
他にもいろいろご提案いただき本当にありがとうございました。　頑張ります！



さか

リンク

2019/9/7(Sat) 11:07:35|NO.88346

自分はSQLiteで270万件のテーブルデータあるのでちょっとHSPで試してみました。
条件にlimitの件数制限つけてのselectです。
limit 10000で10秒くらい。
limit 100000で数十分でも処理が終わりませんね(^^;
DBツール(A5:SQL)だとlimitつけなく全件でも一瞬でデータ表示されます。
自分のPCは少し古いのですがそれでもこれだけ違うのでhspの問題ぽいですね。

PC性能や:memory内での構築で違うと思いますが、つまりテーブル間の結合以前に
hspでの1回のselectの取得件数が問題なのかと思います。
30万件のAテーブルを全件読み込むだけで固まりませんか？

もし時間がかかっても処理が終わればプライマリやインデックスを貼れば処理が速く
なるのでいいかも知れないです。
もしダメなら年月で条件をしぼって抽出件数を少なくすればいいのではと思います。
自分は270万件をhspで処理してますが1度の抽出件数は数件なので問題ないです。

ちなみにSQLite以外のDBもHSPで扱えますよ。それがODBCドライバです。
ちょっとDBインストールからODBC設定と準備が要りますがHSPだけの話では
ないのでネットでいくらでも出てくると思います。
hspのsample\hspdb参照してみてください。



masako99

リンク

2019/9/7(Sat) 23:12:12|NO.88351

>さかさん
ありがとうございました〜〜〜〜〜〜〜〜〜〜〜〜〜！
プライマリーキーを設定したところ最初にご提示いただいた


; ★　以下を追加
;-----------------------------------------------------------------------------------
pos 0
mes "\n★�� テーブルA.Bを結合してそれぞれのコラムの値を抽出したい。★"

sql={"
select * from A, B
where A.年月日ID = B.年月日ID
"}
sql_q sql: su = stat

repeat su
mes strf( "　年月日ID[%s]店名[%s]販売[%s]", sql_v("年月日ID"), sql_v("店名"), sql_v("販売") )
sql_next
loop
完璧に表示されました。(約2秒ほど)

条件を加えて絞り込めばほぼ瞬時で〜す＾＾
もう、うれしさを通り越して半ベソ状態でした。
次にご提示いただいたスクリプトでＣの値も出せそうなので
これでＣテーブル、Ｄテーブルと欲張って増やせそうです、本当にありがとうございました。

補足ですが


sql_q "select * from A"
ではエラー
NO debug module.
#error 26 in line 200(sqlele.hsp)
-->メモリの確保ができませんでした
--------------------------------------


sql_q "select count(*) from A"
sql_i("count(*)") = 531311
瞬時です。
--------------------------------------
私のＰＣ環境でlimitをかけると


sql_q "select 全部 from A limit 5500"
sql_q "select 2個 from A limit 22000"
sql_q "select 1個 from A limit 31500"
までは表示されます(約2秒ほど)
--------------------------------------

およそ2週間くらい悩んでいたので、返信を書きながらではありますけど
すごくホッとした気分で力が抜けていく感じです。
それから、さかさんもそうですが他のスレでご回答されている上級者の方々の
スクリプトの書き方や解決方法はとても参考になります。
特に今回の


;-----------------------------------------------------------------------------------
pos 0
mes "\n★�� �，能个�[年月ID]からテーブルCのコラムの値を抽出したい。★"

sql={"
select * from C
where 
 c.年月ID in ( select A.年月ID from A, B where A.年月日ID = B.年月日ID )
"}
sql_q sql: su = stat

repeat su
mes strf( "　売上[%s]人員[%s]給与[%s]年月ID[%s]", sql_v("売上"), sql_v("人員"), sql_v("給与"), sql_v("年月ID") )
sql_next
loop
は、「えっ？こんなに簡単な事なの？」と思うくらい簡潔な感じですし
sql_q sql : su=stat
に至ってはスマートで分かりやすいのでぜひ使わせていただこうと思っています＾＾

join系の事とかODBCの事もありますが、これで一応解決とさせていただきますね。
ほんと〜にありがとうございました！

トップ | HSP3ダウンロード | HSPTVで遊ぶ | HSPで作る | サイト情報 | プライバシーポリシー | お問合せ

ONION software Copyright 1997-2025(c) All rights reserved.