スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
拍手コメントを見る

[2] SQL と ロト6

2009/11/01 内容変更。"NO"カラム名を"DRAW"に変更しました。

ロト6のデータをSQLでいろいろやってみよう!・・・のパート2です。
前回は、各数字の出現頻度を出力するビューを作成しました。

元となるテーブル Loto6Result は毎週木曜日の抽選会の後で毎回アップデート(手動)するわけですが、前回作成したビュー v_freq6 も当然ながらそのときの最新データを元に出力されます。

さて、では抽選回のn回目までの集計結果が見たい!という場合はどうでしょうか?
もう一歩進んで、n回目からn+m回目まで見てみたいという場合はどうでしょうか?

v_freq6 は全データを対象にしているので、n回までといった事はできません。

こんなときは、SQL関数を利用するという手があります。
関数ですから引数を持たせることができて、範囲指定もお茶の子さいさいになるはずです。

とりあえず作成するSQL関数は以下の3つとします。
1:最新のすべての集計結果を返すもの(つまり、v_freq6 の結果と同等)
2:初回からn回目までの集計結果を返すもの
3:n回目からn+m回目までの集計結果を返すもの

では、1番目のSQL関数を作ってみます。
-- sql example 2-1
-- 全データをもとにした各数字の出現頻度を求めます。

CREATE OR REPLACE FUNCTION f_freq6()
RETURNS table (value integer, total numeric) AS
$$
SELECT value, total FROM v_freq6;
$$
LANGUAGE SQL;


使い方は、SELECT * FROM f_freq6(); です。
v_freq6 と同じ結果を返すだけなのに、わざわざ関数にする必要があるの?と考えたりもしますが、2番目と3番目の関数名との統一のためと思ってください。
そう、引数さえ違えば、同一の関数名が使えるんですね。いわゆるオーバーライドというやつです。

次に2番目、といきたいところですが、よくよく考えてみると、、、
2番目の「初回からn回目までの集計結果」というのは、「1~n回目」という事になります。
なので、「n回目からn+m回目までの集計結果」を返す SQL関数を用意しておいて、n に 1 を渡すことと同じになります。

というわけで、3番目の関数を先に作ります。基本的に、中身は v_freq6 と似ていますが、WHERE 句を用いて取得する範囲の指定を行っています。
-- sql example 2-2
-- 開始、終了の指定範囲における各数字の出現頻度を求めます。

CREATE OR REPLACE FUNCTION f_freq6(s integer, e integer)
RETURNS table (value integer, total numeric) AS
$$
SELECT value, SUM(freq) as total
FROM (
SELECT ID1 AS value, COUNT(ID1) AS freq FROM Loto6Result
WHERE DRAW BETWEEN $1 AND $2 GROUP BY ID1
UNION ALL
SELECT ID2, COUNT(ID2) FROM Loto6Result
WHERE DRAW BETWEEN $1 AND $2 GROUP BY ID2
UNION ALL
SELECT ID3, COUNT(ID3) FROM Loto6Result
WHERE DRAW BETWEEN $1 AND $2 GROUP BY ID3
UNION ALL
SELECT ID4, COUNT(ID4) FROM Loto6Result
WHERE DRAW BETWEEN $1 AND $2 GROUP BY ID4
UNION ALL
SELECT ID5, COUNT(ID5) FROM Loto6Result
WHERE DRAW BETWEEN $1 AND $2 GROUP BY ID5
UNION ALL
SELECT ID6, COUNT(ID6) FROM Loto6Result
WHERE DRAW BETWEEN $1 AND $2 GROUP BY ID6
) t
GROUP BY t.value
ORDER BY total DESC, value;
$$
LANGUAGE SQL;

ながったらしいですね。同じようなことが沢山記述されています。
各SELECT文は結局、WHERE 句の指定により範囲 n ~ n+m まで取得するように条件付けられているだけですので、こいつをまとめて別関数にしてしまいましょう。
-- sql example 2-3
-- 開始、終了の指定範囲のレコードを取得します。

CREATE OR REPLACE FUNCTION f_Result(s integer, e integer)
RETURNS table(DRAW integer, ID1 integer, ID2 integer, ID3 integer, ID4 integer,
ID5 integer, ID6 integer, IDB integer, DATE DATE) AS
$$
SELECT DRAW, ID1, ID2, ID3, ID4, ID5, ID6, IDB, DATE FROM Loto6Result
WHERE DRAW BETWEEN $1 AND $2
$$
LANGUAGE SQL;

sql example 2-3 で作った f_Result(s, e) を利用して、sql example 2-2 を改造します。
-- sql example 2-4
-- 【2-2改造】 開始、終了の指定範囲における各数字の出現頻度を求めます。

CREATE OR REPLACE FUNCTION f_freq6(s integer, e integer)
RETURNS table (value integer, total numeric) AS
$$
SELECT value, SUM(freq) as total
FROM (
SELECT ID1 AS value, COUNT(ID1) AS freq FROM f_Result($1, $2) GROUP BY ID1
UNION ALL
SELECT ID2, COUNT(ID2) FROM f_Result($1, $2) GROUP BY ID2
UNION ALL
SELECT ID3, COUNT(ID3) FROM f_Result($1, $2) GROUP BY ID3
UNION ALL
SELECT ID4, COUNT(ID4) FROM f_Result($1, $2) GROUP BY ID4
UNION ALL
SELECT ID5, COUNT(ID5) FROM f_Result($1, $2) GROUP BY ID5
UNION ALL
SELECT ID6, COUNT(ID6) FROM f_Result($1, $2) GROUP BY ID6
) t
GROUP BY t.value
ORDER BY total DESC, value;
$$
LANGUAGE SQL;

・・・ややスッキリ。って大したことないですけど(^^ゞ

最後に引数を1つしか持たない2番目の関数を作ります。
-- sql examle 2-5
-- 初回から指定回数までの範囲で数字の出現頻度を求めます。

CREATE OR REPLACE FUNCTION f_freq6(e integer)
RETURNS table (value integer, total numeric) AS
$$
SELECT value, total FROM f_freq6(1, $1);
$$
LANGUAGE SQL;


それぞれ、次のように利用可能です。

SELECT * FROM f_freq6(); -- 全データ
SELECT * FROM f_freq6(5); -- 初回から5回まで
SELECT * FROM f_freq6(8, 10); -- 8回から10回まで


最後におまけとして、初回から第469回(2009/10/29) までの全データを用いた出現回数ベスト5とワースト5を書いておきます。

ベスト5
順位数字出現回数
13079
22077
23777
43176
43676

ワースト5
順位数字出現回数
382158
383458
40256
413255
421452
432443


もうひとつおまけに、469回までの出現頻度のチャートをつけます。
確率の線をつけましたが、回数を重ねる毎に実データはこの線に近づくはずです。
つまり、現状谷になっている部分の出現確率は高くなり、山になっている部分は低くなるはずなんですが、、、理想と現実は違うようですね。
469回までの出現頻度

たぶん、to be continued... (ぁゃιぃ)

拍手コメントを見る

テーマ : データベース
ジャンル : コンピュータ

SQL と ロト6

2009/11/01 記事内容変更。"NO"カラム名を"DRAW"に変更

ロト6は、43個の数字のうちから重複しないものを6つ選んで買う一口200円のクジですが、先週で467回となっていました。

気が向いたときに一口だけ購入していたのですが、いい加減回数も増えてきているので、サンプリングしてみよっかな?という気になってきました。

出目の傾向がつかめたら、限られた数字の中から実際に買う番号をピックアップすれば済むって寸法です。毎回毎回43個から6個選ぶ必要がなくなるだけでもストレスから解放される、、はず。

なんて邪推はおいておいて、単純に「なんちゃって予想できるのかな?」という知的好奇心が70%くらいを占めています。・・・たぶん。

さてと、

自宅のPCにはDBが入っていないので、MySQLPostgreSQL のどっちが良いのか考えましたが、これまでに使ったことがない PostgreSQL に手を出してみようと思いまして、おもむろにインストール。。

あっさりとインストールは終わりましたが、立ち上げてみると業務で使っている Microsoft SQL Server と使い勝手が違うため、違和感ありありで凹みそうになりました。

データベースやテーブルを pgAdminIII というプログラムから作ると、余計な事に名称をダブルクォーテーションで囲ってくれてます。
ダブルクォーテーションで囲まれたデータベース名やテーブル名だけでなく、カラム名など、全てがケースセンシティブになるようです。
うぎゃ、面倒くさぁ。。。

と思いましたが、コマンドプロンプトから作ればいいんじゃん!という事で最初のハードルは突破。

作ったテーブルはこんな感じ。
回数#1#2#3#4#5#6bonus#開催日
DRAWID1ID2ID3ID4ID5ID6IDBDATE

DRAW はロトの抽選回数。
ID1 ~ ID6 にはそれぞれ6個の数字。
IDB はボーナス数字。
DATE は抽選日。

ということで、467回分の記録をこのテーブルに書き込んでいく作業から開始です。
ロト6の本家に過去データがあるので、それを加工しつつ、、少々お時間頂いております・・・みたいな。
思ったよりも面倒だ、、いやいや負けるなおとっつぁん!これも自己啓発だぜ、動機がどうであれな。

なんとかレコードの登録は済みました。

ここで、ちょっとした傾向がわかったので、つかみで公表しておきます。

確率の問題なのですから、1~43の数字が満遍なく出現しているのかと思いきや、なんとまぁ、出現率の高い数字と出現率の低い数字には倍違うことがわかりました。

ぱんぱかぱーん、発表します。
間違いここから~
467回中、77回も登場したのは、「37」。およそ6回に1度はでる事になります。
467回中、35回しか登場しなかったのは、「24」。およそ13回に1度しかでない。
~間違いここまで
467回中、78回も登場したのは、「30」。およそ6回に1度はでる事になります。
467回中、43回しか登場しなかったのは、「24」。およそ10回に1度しかでない


ま、これで言えるのは、「24」は選ぶな、って事だけですね。
・・・ほら、これだけで、42個から6つを選べばよくなりました、、、よね?

では、今回の最後に、この結果を導き出すに至った、SQL を書いておきます。
もっとスマートな方法があったらご指導いただければ幸いです。

-- のちのち使えそうなので、view として定義
-- 1~43の出現数を出します。
-- デフォルトソートは出現数の降順です。
-- 利用するときは、普通に
-- select * from v_freq6
-- でどうぞ。

CREATE OR REPLACE VIEW v_freq6 AS
SELECT value, SUM(freq) as total
FROM (
SELECT ID1 as value, COUNT(ID1) as freq FROM Loto6Result GROUP BY ID1
UNION ALL
SELECT ID2, COUNT(ID2) FROM Loto6Result GROUP BY ID2
UNION ALL
SELECT ID3, COUNT(ID3) FROM Loto6Result GROUP BY ID3
UNION ALL
SELECT ID4, COUNT(ID4) FROM Loto6Result GROUP BY ID4
UNION ALL
SELECT ID5, COUNT(ID5) FROM Loto6Result GROUP BY ID5
UNION ALL
SELECT ID6, COUNT(ID6) FROM Loto6Result GROUP BY ID6
) t
GROUP BY t.value
ORDER BY total DESC;
-- 以上


・・・ to be continued ・・・(ほんとかな?) 拍手コメントを見る

テーマ : データベース
ジャンル : コンピュータ

tag : ロト6 PostgreSQL MySQL

担当じゃないところの問題修正

人手不足のため、担当ではないところの問題修正もやっています。
---------------------
「登録されたら消されるファイルが消されていません。」

調べてみました。

「あぁ、それはね、登録に失敗したからファイルを消してないんですね。」

じゃ、なんで登録に失敗したんでしょうか?

複数のファイルを混合すると、重複したデータが発生していることがわかりました。
これは、通常だと起こりえない問題なのですが、異常を来たしたときに、複数のファイルに同一データを出力してしまうケースがある事が判明したのです。

異常が起きないようにすればいいのは、その通りですが、これでは不十分です。
というのも、予期せぬ異常が起きたケースには、結局対応ができていないから。

じゃ、登録する前に、重複があったら排除するように書き直せばいいのだという答えが思いつきます。

ちゃちゃっと書いてみる。うまく動くじゃん。

今度は、どでかいファイルでやってみる。

おわんない、、、ファイルから読み込んでいるのは確認できるのだけど、遅い、遅すぎる!!

計ってみました。(いや、ただ待つだけなんですけど・・・)

なんと、ファイルの読み込み処理だけで、1時間45分も掛かってしまいました。
読み込んだ後の登録部分は、処理時間が7秒程度です。

コードを見直します。

んー、これに違いない。ちゃちゃっと書いて試してみる。

読み込みが2秒で終了。

結果として早くなったのはいいけど、直前の1時間45分の計測がまったく無意味な時間を過ごしてしまった事になりました。
なんとなく損した気分。遅い経験をしたのが僕だけで、他の人は早いのが当たり前だと思うからですけど。

拍手コメントを見る

テーマ : ソフトウェア開発
ジャンル : コンピュータ

根暗な趣味?スパムフィルタ

やー、しかし、毎日イヤになるほどスパムメールが届くようになりました。
おかげで久々に風邪引いてますよ。あ、関係ないですね。

-------------

なんだかんだで Norton Internet Security を使い続けている自分なのですが、、
NIS2009ってかなり高速になりましたよ。。。
・・・ただ、高速化のための Outlook の再設定はやらない方がいいかも。蓄えたメールの量にもよりますけどね。設定完了まで丸2日かかった・・・ような気がします。
こいつのスパムブロックは概ね賢いとは思うんですが、結局は無駄なメールをローカルにダウンロードしている事には違いないんですよね。

で、現在(というか、ずっと昔から)利用しているプロバイダにはスパムブロックサービスというのがあって、「おまかせフィルター」というプロバイダ様が毎日育てている(であろうフィルタ)サービスに加えて、自分でカスタマイズしたフィルタを設けることができるんですよ。

こいつを有効にしておくと1週間分の保存期間という制限はあるものの、フィルタでふるいにかけられたスパムと"思われる"メールが、プロバイダ様の一次保管庫に入ってくれて、ローカルのメーラーまで届かないでくれるんです。んーブラボー・・・・

・・・当然、フィルタは完璧ではないので漏れてくるスパムは我がPCに襲い掛かるわけですが、そこからはNIS2009の出番となります。ま、ここでも漏れてくる姑息なスパムがあるわけで、これらは手動でNIS2009にブラックリストとして登録する・・・という作業を日々繰り返していたわけです。黙々と。

で、いい加減、日に50~100件とか無駄なメールを仕分けるよりも、自分としてはできるかぎり水際で防ぎたくなるわけで、先のプロバイダ様が用意してくださったカスタマイズフィルターに手を出してしまったわけなんですね。

最初は鬱陶しいきゃつらめー、えいやー、ほらやー、とやっていたのですが、ものには限度というものがあって、登録できるフィルタ量にも上限があるんですよ。
まぁ、そもそもスパムメールなんて日々刻々と陰の技術力を高めてくれているようで、メール自体にパターンがあって無いようなものですし、それらに対する防御壁(フィルタね)の構築なんて、それこそイタチゴッコなわけです。

が、それでも。それでも、です。

プロバイダ様が用意してくださった素敵なプレゼントを本日発見してしまったのです。

スパムメールを一次保存しているところにアクセスすると、ずぁぁぁとフィルタリングされたメール一覧を閲覧できます。

?何のタメか?そりゃ、フィルタリングされてしまった不幸な必要メールをサルベージするためです。

で、サブジェクト部分にマウスをホバリングすると、どうしてフィルタリングされたのか、どのフィルタに引っかかったのか、その理由が tooltip で表示されるんです。これがうれしいプレゼントなんですね。

自分が作ったカスタムフィルタに引っかかったメールがあったと想像してみてください。
んもね、「うりゃっ!」って気になるもんですよ。

ざまぁみろ!と。
勝った!と。

さて、このブログにもフィルタ設定しているのですが、何をブロックしたのかというその結果を見ることができないのが残念なところです。
以前も設定を間違ったまま運用しており、お知り合い方から「どうしてブロックすんの?」とお叱りを受けたことがあります。
原因は、その方が属しているプロバイダさんの他のユーザーさん(不特定多数)からスパムコメントを多数受け取っていたので、(手抜きで)ドメインごと拒否していたからです。ごめんなさい。

その後設定を見直ししましたが、うまく動いているのかどうか、実際のところわからんのですよ。
人気の無いブログなのでコメントが付かないから。。。わっはっは。


拍手コメントを見る

iKnow の例文

英語勉強のための SNS である iKnow に登録してみました。
ブログパーツも提供してくれているので、「雷ブ」にも貼ってみました。興味がある方はクリックしてみてください。

コースが色々選べるのですが、あえて初級コースからコツコツと始めています。

このパーツで、僕のやる気と成績が丸見えになります。

さて、そんな中での "another" という単語を使った例文のシチュエーションがブラックなんですよ。
iKnow の著作権の関係で例文も画像も転載できないのが残念ですが・・・。

1日10分程度、勉強に充てる様に頑張らねばね。 拍手コメントを見る

tag : iKnow

ブログ内検索
プロフィール

雷ぶ

Author:雷ぶ

最近の記事
最近のコメント
最近のトラックバック
カテゴリー
月間アーカイブ
ブロとも申請フォーム

この人とブロともになる

RSSフィード
リンク

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。