2016年6月18日土曜日

FileMakerからPostgreSQLにレコードを送りつける・その2

先日出たFileMaker Pro 15で、PostgreSQL(以下PSQL)のデータベースを、FileMakerで扱えるようになったということを取り上げました。
FileMakerとOracleやMySQL、PSQLといったデータベースは違う世界に生きているので、その架け橋としてODBCという通訳さんが必要というところまで話したと思います。

突然ですが、PSQLの場合は、実は通訳さんが二人必要です。
イメージとしてはこんな感じ。


ODBCに加えて、Actual ESS Adapterというものを使わないと接続できません。
ひとまず、やりたいことは以下のように、FileMakerのリレーションシップグラフに、PSQLのテーブルを追加することです。


これをやるためにインストールが必要なものをまとめてみました。
環境はWindows7(64bit版)です。

・PostgreSQL 9.4.8-1-windows-x64
→PSQLのテーブルを作らないといけないので、PSQLそのものが不可欠。

・psqlodbc_09_03_0400
→通訳その1。

・Actual ESS Adapter win64
→通訳その2。

・FileMaker Pro 15
→14以前だと無理です。

・A5:SQL Mk-2
→SQL実行環境。なくてもいいですが、PSQLのデータベースを直接操作する際に便利。

ちなみに、FileMaker以外はフリーです。

上記の環境をインストールしたら、まずODBC接続のためのアカウントのようなもの(DSNといいます)を設定します。
コントロールパネル→システムとセキュリティ→管理ツールに、データソース(ODBC)というのがありますので、ここから設定します。

PSQL-ODBCのDSNはこんな感じで。


Actual ESS AdapterのDSNはこんな感じで。


DSNの設定が終わったら、FileMakerを起動して、テーブルと、送り付けたいレコードを作りましょう。


超適当ですが、こんなもんでいいですかね。
このレコードをPSQLのほうに送りたいので、受け皿となるPSQLのテーブルも作っておきましょう。


PSQL付属のSQL実行環境が使いにくいので、A5:SQL Mk-2というフリーソフトを使っています。
テーブルを作るときのコマンド「create table」で、FileMakerで作ったテーブルと同じ形式のテーブル(sample_psql)を作っています。
selectでテーブルの形を見た結果が、下の画面に表示されています。
データは1件も入ってませんが、これから、ここにFileMakerで作ったデータが放り込まれていくわけです。

それでは、先ほどのFileMakerのファイルのリレーションシップグラフに、sample_psqlを追加してみます。
テーブルの追加→データソース:ODBCデータソースの追加→Actual ESS Adapterを選択→sample_psqlを選択→idにチェックしてOK。


斜体のテーブルが現れればOKです。
あとは、sampleテーブルからsample_psqlにレコードをインポートするだけです。


インポート先に、先ほどリレーションシップグラフに追加したPSQLのテーブルを指定します。
このスクリプトをボタンにでも仕掛けておきます。


レコードが21件あることを確認してから、ボタンを押してみます。
手ごたえはまったくないですが、SQL実行環境でselect文を唱えて、確認してみましょう。


21件、入ってました。
ボタンを押しまくるとどんどん同じデータが入っていくので、一度入れたデータは送信しないように、フラグをつけるなどしたほうがいいかもしれません。

ちなみに、別のテーブルで試してみた結果。


18000件ほどやってみました。
データ件数が多いと、インポートに少し時間がかかるくらいで、結果としては問題ないみたいです。

こんなふうに、PSQLにデータが入ってしまえば、SQLを使ってデータ分析……とかできると思います。
データ登録はFileMakerで、データ管理と分析はPSQLで、という感じでしょうか。

今回は同じPC内でのデータの受け渡しということで、実用的にはあんまり意味がないですが……
もしPSQLデータベースが別PCで、しかも地理的に離れた所にある場合でも、同じようなことができるとしたら?
たとえば、各支店のFileMakerのPOSデータを、本社のPSQLデータベースに集めて分析、みたいなこともできると思います。

終わり。

2016年6月9日木曜日

FileMakerからPostgreSQLにレコードを送りつける・その1

先日、FileMaker Pro 15がリリースされました。

メンテナンスリリースで無償インストールできたので、早速使ってみた。

感想は……14とあまり変わってない感じ。

むしろ、メニューの表示が変わってて、ひじょうに使いにくくなっていました。

元々、今回はiOS関連の機能の追加が多いようでしたので、実はあまり興味がなかったのですが、ODBC接続でPostgreSQLが使えるようになるという点にだけ、注目していました。

ODBC?Post?なんのことやらわからんという人に言っておくと……



まずPostgreSQLですが……

同じ人間でも人種が違うように、データベースを扱うソフトウェアにも、種類がたくさんあります。

FileMaker以外にも、MySQLやOracleなど、いろいろありまして、PostgreSQLもそのうちのひとつ。

PostgreSQLはよくポスグレと呼ばれます。

タダで使えるうえに、同じタダで使えるMySQLよりも高機能なので、評価されているらしいです。



次にODBC。

実はFileMakerは、リレーションシップのグラフに、別データベースのテーブルを追加することができるのです(外部データソースと言います)。

こんな感じで。


ごちゃってますが、一番右のテーブル。

これはFileMakerのテーブルではなく、PostgreSQLのテーブルです。

テーブル名(order_psql)が斜体になっているのが目印。



それで、これができると何が良いかというと、FileMakerとは一切関係のないシステムで作ったデータベースに、FileMakerで作ったデータを放り込む……ということができます。

ただし、ここで注意点がひとつあります。

FileMakerと上述のPostgreSQLは別物、日本人とドイツ人みたいなものなので、両者をつなぐためには、その仲介をする「共通言語」が必要なのです。

この共通言語が、ODBC(Open Database Connectivity)になります。

その名の通り、データベースをオープンにつなぐ……データベース界の通訳さんです。

FileMaker ---- ODBC ---- PostgreSQL という構成をとることで、FileMakerのデータをPostgreSQLに転送することができるようになります。

実データはPostgreSQLデータベースで管理しているんだけど、データ入力はFileMakerでやりたいんだよね。

そんな時に使えばいいと思います。



本題に戻ると、FileMaker の14までは、PostgreSQLを外部データソースとして使うことができませんでした(リレーションシップグラフに追加できなかった)。

15になって初めて、PostgreSQLとの本格的な連携ができるようになったわけです。

ですので、今回はFileMakerで作ったデータを、ODBCを介してPostgreSQLデータベースに送りつけてみました。

わざわざFileMakerのデータをPostgreSQLに送って何の役に立つのかというと……

イメージとしては、各店舗のPOS端末から、本社のデータベースにデータを送って、本社側でデータ分析、みたいな。

データ分析、FileMakerでもできなくはないと思いますが、データ分析用の関数があるPostgreSQLでやったほうが良いと思います。



FileMaker→PostgreSQLへのデータ転送にはいろいろ準備が要ってめんどくさいですが、手順としては、以下のような流れになります。

① PostgreSQLをインストールする。

② PostgreSQL用のODBCドライバをインストールする。

③ Windowsの管理ツールで、②を使うためのアカウントのようなもの(DSNという)を作る。

④ Actual ESS Adapterをインストールする。

⑤ Windowsの管理ツールで、④を使うためのアカウントのようなもの(DSNという)を作る。

⑥ FileMakerでテーブルを作って、レコードをいくつか追加する。

⑦ PostgreSQLに、FileMakerで作ったテーブルと同じ形式のテーブルを作る(SQL実行環境が必要)。

⑧ FileMakerで外部データソースを追加して、リレーションシップグラフに⑦で作ったテーブルを追加する。

----ここまでが準備----

⑨ FileMakerのレコードを⑧のテーブルにインポートする。



ちなみに今回は、同じPCにPostgreSQLとFileMakerを入れた完全ローカル環境、OSはWindows7(64bit)での実験になります。

本当はサーバー・クライアントでやってみたかったのですが、最初なのでシンプルに。

……長くなりそうなので、詳細は次の投稿でやります。

2016年6月5日日曜日

スクリプト一時停止の罠をやっつける

FileMakerのスクリプトステップのひとつ、「スクリプト一時停止/続行」、使ってますか?

停止する時間を指定できますので、処理中にウェイトを挟むときとかに便利なやつです。

しかし、このスクリプトステップには罠があります。

デモ用ファイル



罠を再現するために作りました。

「メイン処理」ボタンを押すと、4秒間停止した後に、「終了」のダイアログが出るようになってます。

ところが、この停止している間に「割り込み処理」ボタンを押すと、停止したまま動かなくなります。

上のメニューバーに出ている「続行」を押すことでまた動き始めるのでよさそうなもんですが、

メニューバーは実用時には消しておくのが普通なので、実用時にこれが起きると、再開する方法がありません。

これがスクリプト一時停止の罠です。

やばくないですか?

というわけで今回は、この罠をやっつけたいと思います。



主役はポップオーバーさん。

レイアウト内に配置して、クリックしたときだけに表示させたいフィールドなどを置いておく、といったのが普通の使い方なのですが。

今回はこれを画面外に配置します(右上)。


スクリプト一時停止/実行の前に、このポップオーバーを開くようにしてあげます(ポップオーバーを開くというスクリプトステップはありませんので、ポップオーバーに名前をつけて、オブジェクト移動)。


すると、こういうふうになります。

ポップオーバーが開いているので、「割り込み処理」ボタンは押せません。

これで一件落着……に見えるんですが、実はそうじゃありません。

これだけだと、ポップオーバーがカバーしきれていない隙間をクリックしたとき、ポップオーバーが閉じてしまい、「割り込み処理」ボタンを押せるようになってしまいます。

ですので、追加で、スクリプト一時停止中は、ポップオーバーを閉じさせない細工をする必要があります。

具体的には、ポップオーバーのスクリプトトリガ・OnObjectExitにポップオーバーを閉じさせないためのスクリプトを設定します。

ポップオーバーから脱出されそうなときに、監視をさせるようなイメージ。

この監視網を張ってやれば、目的達成です。

ポップオーバーがレイアウトを守っているので、一時停止中にボタンは押せませんし、たとえ画面端をクリックされようとも、ポップオーバーが閉じることはありません。

何をしようが、4秒後には「終了」のダイアログが必ず表示されるというわけです。

ポップオーバーさん+監視スクリプト=鉄壁?

2016年5月5日木曜日

ポータルで遊んでみた・パート2

前回のパズルと一緒に作ったものですが、FileMakerを使ったトランプゲーム(神経衰弱)です。
神経衰弱ゲーム
※トランプの画像が入っているtrumpフォルダとFileMakerのファイルを同じ場所に置かないと、画像が表示されない気がします。

【11回目でまだ2組しか当てていないの図】



【テスト用画面】

ざっくりとした説明

① トランプの画像ファイルをレコードとして持つテーブルを作り、そこに位置情報や選択/非選択の状態、取得済/未取得の状態などの管理情報を入れています(これがデータベース)。

② カードの配置が毎回同じだと味気ないので、起動/リセット時にカードの位置情報がランダムに変わるようにしています。

③ トランプの表示部分は、懲りずにレコードを6行表示するポータルを横に9列。

④ カードの表と裏を切り替えるために、近年FileMakerに追加された「オブジェクトを隠す」という機能(とても便利です)を使いまくっています。

⑤ カードをクリックしたら、カードの状態を書き換えるスクリプトが起動するようになっています。

⑥ 2枚目のカードをクリックした後、間髪を容れずに他のカードがクリックされてしまうと目茶目茶になる恐れあり。なので、透明なポップオーバーを出すことで、一時的に操作を無効にしています(これは便利なので、いつかテーマとして扱おうと考えています)。


簡単なゲームですが、データベースを作りつつアプリケーションも作れるという、FileMakerならではの特典の紹介をしたかっただけです。



ゲームというと、最近はテレビゲームは落ち目で、スマートフォンのゲームに人気が集まっていますね。
テレビのCMも、ソーシャルゲームばっかりですね。
プレイステーションが大好きな僕のような人間には、面白くない状況です。

それはさておき、実はデータベースというのは、ゲームソフトやゲームアプリとも無関係ではありません。
ドラクエで例えるなら、勇者や戦士のステータスとか、装備中の武器とか、拾えるアイテムとか、敵の名前や体力、攻撃力などなど……
膨大な量、種類のデータを使うわけで、こういったデータたちをしっかりと管理するために、データベースが使われます。
データがきちんと管理されていないと、勇者のHPがマイナスになったり、昔のポケモンみたいにレベルがいきなり100になったりするかもしれません。
実際にゲーム開発プロジェクトの経験があるというわけではないので、詳しいことはわかりませんが……

今のFileMakerは、ゲームに必要な「動き」を表現する方法がたいへん貧弱で、簡単なものしか作ることができません(そして簡単なものならそもそもデータベースすらいらないので、FileMakerを使う必要はないはず)。
ですが、いつかFileMaker18くらいで、データベースソフトとしての機能を持ちつつ、ちゃんとした動きのあるゲームが作れるようになったら、画期的じゃないかなーと、個人的には思ってます。

2016年5月4日水曜日

ポータルで遊んでみた

暇だったので、ポータルでパズルらしきものを作ってみた。


移動元をクリック→移動先をクリックで、位置が入れ替わります。
↓↓↓


完成図(カンニング)
↓↓↓


4行の関連レコードを表示するポータルを横に3つ並べて、見た目4×3のパネルを作っています。
ポータルで表示するレコードに列、行というフィールドを作って、そこに値を入れてうまくリレーションを結べば、これは簡単にできます。
メニューや商品ガイドとかに使えるテクですね。

今回はクリックしたら自動でパズルのピースが動くようにしてみました。
最初はパネルをクリックするとポップオーバーが出てきて、そこで移動先の座標を入力するようになっていたんですが、全然パズルっぽくないので、却下した次第です。

本来は人力で入力するところを、完全自動化できるのが、スクリプトのすごいところですよね。
たぶんちゃんと動いているはずですが……

画像が入っているので少しサイズが大きい。
ポータルパズル

2016年4月20日水曜日

FileMakerでNow Loading...

FileMakerでロード中の画面を作ります。
ロード中の画面とは、ゲームとかでよくある、Now Loading的なあれです。

FileMakerで作ったシステムやアプリなら、たくさんのデータを修正する処理とか、WEBページにアクセスしてWEB上のデータをとってくる処理とか、ちょっと時間がかかりそうなことをやるときに欲しいもの。


サンプルファイルがこちらです。



ロード処理(テスト)を押すと、ロード画面を見せつつ、



実はその裏でレコードを2000件作っています。
レコード作成の処理が終わったら、ロード画面を抜けて、元の画面に戻ります。


まずは、ロード時に表示するためだけに使う画面を作ります。
真っ白な背景に、WEBビューアを置きます。
WEBビューアというのは、FileMakerのファイルの中でインターネットのページを見るためのものですが、実はこれで画像を表示することができます。


塗りつぶしを無効にしているのでわかりにくいですが、「ロード中です......」の下にある物体が、WEBビューアです。
レイアウトモードでダブルクリックすると、Webアドレスのところに呪文が書いてあります。
この欄には本来Google MapなどのURLを入れますが、今回は「画像を表示するためのURL」を入れています。

今回はロード画面に動きを出したいので、表示するファイルはgifアニメーション素材です。
ただしgif素材を単純に貼り付けただけでは、びくとも動きませんので、このようなことをしています。
素材があれば、ロード画面に限らず、いろいろできそうですね。

次に、レコードを2000件作る擬似ロード処理を、スクリプトで作ります。


6行目以降が実際のロード処理。
ここをやっている最中は、先ほど作ったロード画面を表示させておく、というのがやりたいことです。

ですので、まず最初に「ロード画面に移動」します。
移動したら、「ウインドウの固定」、これが重要です。
「ウインドウの固定」は、文字通り、画面をそのままの状態にしておけ、ということです。

6行目以降でレイアウト切替がいくつかありますが、「ウインドウの固定」をしているので、見た目はロード画面のままになります。
「ウインドウの固定」は、スクリプトが終了すると解除されるので、24行目が終わると、Start、つまり元の画面に戻ります。


1行目のロード画面へ移動するところは、単純にレイアウト切替でもいいですが、今回は関連レコードとポータルを使って、ロード中の画像を選べるようにしてみました(遊び心)。


素材はこちらからお借りしました。→ HP・メール素材 カツのGIFアニメ 様

2016年3月31日木曜日

売上集計(ExecuteSQL)・その4

ExecuteSQLで売上集計は、たぶんこれで最後です。
はじめに少しだけ、FileMakerとデータベースの関係について……

FileMakerはデータベースを使って何かを実現したい場合、とても便利なソフトです。

ふつう、データベース自体を作るにしても、データベースを利用するもの(アプリケーション)を作るにしても、専門的な知識がいります。
また、データベースとアプリケーションは別々に作らないといけないことが多く、とても時間がかかります。

FileMakerを使えば、データベースを直感的な操作で作ることができます。
まず、データベース自体を作るのが、FileMakerを使わない場合に比べて簡単だと思います。

また、FileMakerはデータベースだけでなく、データベースを利用する部分(使う人が直接触る部分)も、簡単に作ることができます。
たとえば請求書を発行するシステムを作ろうとすると、ふつうは請求データを管理するデータベースを作って、それとは別に、入力フォームや請求書のフォーマットなどを作る必要があります。
FileMakerなら全部まとめて、しかも短期間でできます。

データベースは勉強すると結構難しいし、入力フォームを作ったりするのにも、データベースとはまた別の知識が必要になり、すごく大変です。
このように本来は敷居の高いはずの何かが、手軽に作れてしまう。
ここに、FileMakerの凄さがあると思います。


本題に戻ります。
前回までの流れは、実は、集計をちょっとずつ動的にしていくという試みになっています。
最初はただどこそこにあるデータを集計するだけでした。
その後、期間を選べるようにしたことで、使う人の自由度を少しだけ高くしました。

仕事の内容は動的に変化しますから、仕事で使うシステムも、それに応じることができる柔軟性を備えているべきだと思います。

というわけで、今回は、さらに自由度を高くしてみたいと思います。
集計する商品を選べるようにしたサンプルファイルがありますので、参考にしてください。

デモ用ファイル

FileMaker Pro 14が使える環境で、mainとdataを同じフォルダに置いて、mainのほうを開いてください。

商品指定で、集計したい商品を選ぶことができます。



商品を3つだけ選んで、集計ボタンを押すと……



こんなふうに、全商品ではなく、選んだ商品だけの集計結果が出てきます。

現実的には、集計したい商品は、常に固定というわけではないはずです。
ケーキどうしの売上を比べたいときもあれば、カテゴリの違う主力商品どうしを比べたいときもあると思います。
それなら、集計したり分析したりする人が、自由に集計パターンを変えられるようにしなければ、使えないですよね。


実はこの集計用ファイルは、最初にFileMakerとデータベースについて言ったことと関係しています。
「集計したい商品を選ぶ」という部分(使う人に見える部分)と、「売上データを検索・抽出して集計する」という部分(見えない部分)。
これらをまとめて作ってしまえるので、FileMakerは便利ですね、と言いたかったのでした。

2016年3月5日土曜日

売上集計(ExecuteSQL)・その3

商品ごとで売上個数を計算しました、の続きをやります。
使用するファイルは、同じです。

デモ用ファイル

レイアウトは、Demo3です。
Demo2と何が違うかというと、期間指定ができるようになったことです。

前回は、売上データがたくさんあって、そこから、商品ごとに個数と金額を合計しました。
ですが、売上げた日付というのは、考慮されていませんでした。
とりあえず、今までの売上全部を対象にして、商品ごとに集計しよ、というものでした。

正直、実際には、このように、過去の売上全てを集計対象にするということは、あまりないんじゃないかと思います。
そこからわかることは、あぁ、これまでこんなに売ったのか。がんばったな。
という程度のものでしょう。
それよりも、1週間だけの売上とか、1日だけとか。
そのほうが、実用的だし、この日とこの日の比較をしたい、という場合など、比較対象としても優秀ですよね。
統計分析の基本は、適切な比較対象、らしいです。
今やってることは、統計と言えるほど、大それたものではないですが・・・


では、どうやってこの「期間指定の集計」をしているのでしょうか。
その前に、SQLのWHERE句というものについて、やっておきます。

下のような、売上データがあったとします。数字は個数とします。

-------------------------------
苺のショートケーキ 2 2016-02-17
苺のショートケーキ 2 2016-02-17
苺のショートケーキ 3 2016-02-18
苺のショートケーキ 1 2016-02-19
苺のショートケーキ 2 2016-02-19
-------------------------------

このテーブルから、2016-02-18のものだけを取って来たいと思ったとします。
その場合、SQLはこうなります。

SELECT 商品名, 数量, 日付 FROM 売上明細
WHERE 日付 = "2016-02-18"

翻訳すると。
売上明細テーブルから、商品名と数量と日付のレコード(行)を持ってきて~
でも全部はいらなくて、日付が2016-02-18のやつだけね。
という意味です。


このWHERE句というやつを、ExecuteSQL関数のSQLを書く部分で、使っています。

ExecuteSQL (
 "SELECT 商品ID, 商品名, 商品分類ID, 商品分類名, SUM(数量), SUM(金額)
  FROM 売上明細
  WHERE 日付 BETWEEN ? AND ?
  GROUP BY 商品ID, 商品名, 商品分類ID, 商品分類名"
 ; ", " ; "" ; Demo3::g_開始日付; Demo3::g_終了日付
)

WHEREの後で、「日付が、ある日付からある日付の間にある場合」、という条件を指定しています。
WHEREには、このように、~と等しい(=)という単純な条件だけではなく、二つの数値の間にある(BETWEEN)とか、商品名がチーズケーキまたはモンブランである(OR)とか、さまざまなタイプの条件を指定できます。
そうして、その条件に合致するレコードだけを絞り込んで取ってくるのがWHEREです。
SELECTでデータの抽出をするときは、全部欲しい場合というのは、あまりないでしょう。
ですので、SELECTとWHEREは大体、お友達です。

ところで、WHEREの条件に ? というのがありますね。
これは何なのかというと、? にはExecuteSQLの最後に指定した値が入ります。

たとえば、

ExecuteSQL (
 "SELECT 商品ID, 商品名, 商品分類ID, 商品分類名, SUM(数量), SUM(金額)
  FROM 売上明細
  WHERE 日付 BETWEEN ? AND ?
  GROUP BY 商品ID, 商品名, 商品分類ID, 商品分類名"
 ; ", " ; "" ; "2016-02-16"; "2016-02-17"
)

とすれば、2016-02-16と2016-02-17の売上だけを対象とします。
ただここには操作する人が決めた日付を入れたいので、FileMakerのフィールドの値が入るようになっています。
期間指定のボタンを押すと出てくる、「開始日付」と「終了日付」というフィールドがそれです。
こうすることで、日付を変えれば集計結果も変わる、ということができます。
ExequteSQL関数のこの ? の機能は、いろいろできるので面白いですよ。


次は、集計したい商品をリストから1つ、または複数選ぶ → 選らんだ商品だけ集計する
をやりたいと思います。
FileMakerのリレーションとExecuteSQL関数、両方を組み合わせて使います。

2016年2月26日金曜日

売上集計(ExecuteSQL)・その2


前回は、商品の一覧というテーブルに対して、商品の一覧のデータをくださいというSQLを唱えて、そのデータを取ってきました。
FileMakerのテーブルに対してSQLを唱えて、必要なデータを取ってくる。
ExecuteSQL関数の基本的な使い方をやりました。

次は売上データというテーブルに対してSQLを唱えます。
SQLの内容は、前回よりもちょっとだけ発展。
商品の売上データをください、ついでに商品ごとに売上個数と金額を合計してください。
という命令です。
この結果、苺のショートケーキは何個、チーズケーキは何個売れましたよ、というデータが取れます。

2つのファイルを同じフォルダに置いて、main.fmp12の方を開いてください。
※FileMakerのバージョン12以上の環境が必要です。

集計ボタンを押すと、集計結果が出ます。
左から、商品ID、商品名、商品分類ID、商品分類名、売上個数、売上金額です。

SQLの到着先は、data.fmp12(外部データソース)の「売上明細」テーブルです。
data.fmp12を開いて「売上明細」レイアウトを見てもらうとわかりますが、売上1件を1レコードとして記録していったものです。

ExecuteSQL関数は、ほぼ同じです。
SQLの部分が少し違います。

SELECT \"商品ID\", \"商品名\", \"商品分類ID\", \"商品分類名\", SUM(\"数量\"), SUM(\"金額\")
  FROM \"売上明細\"
  GROUP BY \"商品ID\", \"商品名\", \"商品分類ID\", \"商品分類名\"

SELECTにはテーブルの項目名を、FROMにはテーブル名を書くことで、このテーブルからこの項目のデータを取ってきてください、ということでした。
ただし、今回はSUMという部分と、GROUP BYという部分が新要素です。

SUM(\"数量\")は、数量という項目のデータを選んで、合計してくださいという意味になります。
全商品の売上数量でいいなら、GROUP BYは不要です。
しかし、商品「ごと」に数量の合計を出したい場合は、GROUP BYが必須です。

売上明細を眺めると、ここには同じ商品の売上データが重複してますね。
同じ商品を買っていくお客さんがいるので、当然です。
GROUP BYは、このような重複を除いて、売上明細をグループ分けしてくださいという命令です。

----------------------------
苺のショートケーキ 1個
苺のショートケーキ 2個
チーズケーキ 3個
チーズケーキ 2個
クロワッサン 1個
----------------------------

というデータしかなければ、グループ分けと先ほどのSUMによって

----------------------------
苺のショートケーキ 3個
チーズケーキ 5個
クロワッサン 1個
----------------------------

という結果になります。
SUMはグループごとに計算してくれるわけです。

注意したいのは、SELECTの後に書いたSUM以外の項目名は、GROUP BYの後にも全部書かないとダメだということ。
上の呪文の場合、GROUP BYの後に商品IDと商品名しかないケースは、エラーになります。

なぜかというと、仮に以下のようなデータがあったとしますね。

--------------------------------
苺のショートケーキ スイーツ 1個
苺のショートケーキ スイーツ 4個
苺のショートケーキ スウィーツ 2個
苺のショートケーキ スウィーツ 1個
チーズケーキ スイーツ 3個
チーズケーキ スイーツ 2個
クロワッサン パン 1個
--------------------------------

このデータ、商品だけでグループ分けして、合計数出してよ、と言われたら、

--------------------------------
苺のショートケーキ 8個
チーズケーキ 5個
クロワッサン 1個
--------------------------------

こうなりますよね。ここで、
「あれ、分類名は?分類名も表示してよ」
と言われたら、困ったことになりませんか。

苺のショートケーキ 8個
の行は、「スイーツ」と「スウィーツ」の分類がまとまっているので、どちらを表示していいかわからないのです。
ですので、分類名も表示したい場合、商品と商品分類でグループ分けをする必要があります。

--------------------------------
苺のショートケーキ スイーツ 5個
苺のショートケーキ スウィーツ 3個
チーズケーキ スイーツ 5個
クロワッサン パン 1個
--------------------------------

これが正解になります。

SELECTで選ぶ表示したい項目名と、GROUP BYで選ぶグループ化したい項目名は、よく考えましょう。
先ほどのスイーツ・スウィーツ問題のように、矛盾が生まれてしまう可能性があるので、とても大事です。
このへんのルールはいろいろあるのですが、とりあえずは、SELECTの後に書いたSUM(集計関数)以外の項目名は、GROUP BYの後にも全部書かないとダメということを覚えておけばいいと思います。

ちなみに、今回はSUMしか使っていないですが、SUMのように、いくつかのデータをまとめて計算するようなものを、SQLでは集計関数と呼んでいます。
平均値を求めるAVG、最大値を求めるMAXなどがあります。
これらももちろん、GROUP BYを使ってグループごとに計算すると効果的です。

デモ用ファイル内にDemo3という画面がありますが、長くなってしまったので、こちらについては次回に回します。

2016年2月21日日曜日

売上集計(ExecuteSQL)・その1

1回目のテーマは売上集計にしようと思います。

まずはこれを見てもらいたいです。



架空の飲食店の売上データを、FileMakerで作ってみました。
飲食店やスーパーなんかで使われているPOSシステムというのがあります。
お会計した後、POSレジが記録していくデータは、こんな感じだと思います。
いつ、どこで、どの商品を、どれだけ売りました、その積み重ねです。

さて、このとにかくたくさん貯めておいたデータの塊から、何がわかるでしょうか?
もちろん、このままの状態では、何もわかりません。
このよくわからないデータの羅列を、「使えるデータ」にしましょう。
これが売上の集計になります。

飲食店で言えば、集計をすることで、たとえば、こんなことがわかると思います。
今日一日で一番売れた商品ベスト10、一番お客さんが少なかった座席、複数ある支店の中で、一番よくがんばったお店、ランチタイムの集客数を昨日と今日で比較する……などなど。
可能性は無限大です。

へ~。集計したらこんなことがわかった。だから、明日はこうしようかな。
計算はコンピュータ、その後を決めるのは人間ですけど、何もわからない状態では、「明日はこうしようかな」という感情も生まれないですよね。
当たり前ですが、売上を集計・分析することは、とても大切なことだと思います。



ちょっと話が逸れました。

今回はFileMakerを使って、ごく簡単な売上の集計をしてみたいと思います。
その方法として、FileMakerには集計用のちゃんとした機能があるんですが、FileMakerをやったことがない人には、少しわかりにくい機能かもしれません。
また、ちょっと複雑で、かついろんなパターンの集計をしたい場合は合わない気がするので、今回はExecuteSQL関数という、別の方法を使います。

Exeなんたら~というのは、FileMakerに搭載されている機能の1つです。
※FileMakerのバージョン12以上でないと使えないと思います。

どういうものかというと、FileMakerで作ったデータの塊に対して、魔法の言葉を叫び、ほしいデータを抽出できるというものです。
魔法の言葉と言いましたが、これは正式には「SQL」と言う言葉です。
SQLは、データベース操作言語として、広く使われている技術です。

SQLについて簡単に説明すると、たとえば、ここに1万人の名簿を入れた箱があります。
この中から、「鈴木さん」の名簿だけを作りたいとき、人間の力でこれをすると発狂するのがオチです。
なので、人間はこの箱に向かってSQLという呪文を唱えます。
正しい呪文を唱えると、コンピュータが鈴木さんだけの名簿を作ってくれます。

僕自身まだ勉強中なので、こんな説明が限界です。すみません。

そういうわけで、FileMakerで作られたデータの箱に対して、このSQLを、Execute=実行するという機能。
それが、ExecuteSQL関数になります。

ところで、SQLはデータを抽出するだけではなく、複数のデータの合計を出したり、平均を出したりといったこともできます。
ExecuteSQL関数においてもそれができるため、売上データの集計に使うことができるのです。

ただ、今回の趣旨はこの関数をとりあえず使ってみるということにしておきます。
集計はまたの機会にして、データの抽出だけをやってみたいと思います。
使用するデータの箱についても、集計には最初の売上データを使うつもりですが、今回は単純に、飲食店のメニューの一覧を使います。

ここからは、デモ用のファイルをダウンロードしてもらうと、わかりやすいかと思います。


2つのファイルを同じフォルダに置いて、main.fmp12の方を開いてください。
※FileMakerのバージョン12以上の環境が必要です。



レイアウトを「Demo1」にして、「集計」のボタンを押してみてください。
メニューの一覧らしきものが出てきたと思います。
左から、商品ID、商品名、商品分類ID、商品分類名、単価という並びです。
「初期化」ボタンを押すと消えます。

何をしているかというと、FileMakerの別ファイルにあらかじめ、メニューの情報を登録した箱を用意しておいて、ボタンを押した瞬間、その箱からデータを取ってきています。

ここでちょっと、図を見てもらいたいです。


FileMakerでは、データの箱のことを「テーブル」と呼びます。
「商品Mt.」というテーブルには、この飲食店のメニューが12個、あらかじめ登録されています。
この商品Mt.テーブルに向かって、「商品Mt.さんに入っているデータ全部をください」という意味合いのSQLを唱えると、ご要望のデータが返ってきます。
Demo1の画面には、その結果が表示されているというわけです。

えっ、でもちょっと、その商品Mt.とかいうやつを見れば、どんなメニューがあるかわかると思うんですけど、こんなことして何になるんですか?
はい、その通りです。
でもこれが、ExecuteSQL関数の一番簡単な基本形だと思うので、今回はあえてこんなことをしました。

※余談ですが、本来、FileMakerは、別テーブルにあるデータを使う場合は、リレーションシップというものを使って、テーブルとテーブルを紐付けなければいけません。
ところがExequteSQL関数を使うと、そんなことをしなくても、別テーブルのデータを簡単に呼び出すことができます。
これはFileMakerの使い方としては道を踏み外したも同然だと思うんですが、データベースをやったことのない人には、こっちのほうが直感的でわかりやすい気がします。
ただし後々、ExecuteSQL関数とリレーションシップは組み合わせて使います。



ここからは、ExecuteSQL関数の中身についてです。
呪文が嫌いな人には辛いかもしれないですが、やっていることは単純です。

上のメニューから「スクリプト」を選んで、左のリストから「Demo1_集計」を選んでください。
変数を設定、ExequteSQLという部分が見えてくると思います。
ダブルクリックすると、詳細が見れます。

ExecuteSQL関数は、

ExecuteSQL ( "SQL文(呪文)" ; "フィールド区切り" ; "行区切り" )

という形をしています。
フィールド区切りと行区切りは、", " や " - " などの文字を指定することで、データに区切りをつけて、見やすくするためのものです。
たとえばフィールド区切りに ", "、行区切りに "@@" を指定すると、結果は、

IT_001, 苺のショートケーキ, IC_01, スイーツ, 400@@IT_002, ザッハトルテ, IC_01, スイーツ, 390@@……

こんなふうになります。

行区切りは、指定するとかえって見にくくなります。
 "" にすると、今回の場合、商品ごとで改行になるので、そのほうがいいと思います。

そんなことよりも重要なのは、()の中で最初に出てくる、SQL文(呪文)というところです。
どのテーブルから、どのデータを取ってくるか、という命令を、ここに書きます。
当然、何がしたいのか、どんなデータをどのように取りたいのかによって、内容は変わってきます。
SQLを勉強すると、好き勝手書けるようになると思います。

さて、今回の命令は、「商品Mt.テーブルのデータを全部ください」ということでした。
これを呪文に翻訳すると、以下のようになります。

SELECT \"商品ID\", \"商品名\", \"商品分類ID\", \"商品分類名\", \"単価\" FROM \"商品Mt.\"

ちょっと見にくいんですが、FROMの後に、テーブルの名前を入れます。
SELECTの後には、項目を入れます。
これだけです。

項目について、少しだけ補足します。
今回の商品Mt.テーブルならば、1つの商品(FileMakerでは1レコードといいます)に対して、商品ID、商品名、商品分類名など、いくつかの項目があります(FileMakerではフィールドといいます)。
飲食店のメニューの一覧というデータベースには、商品名以外にも、必要な項目がありますよね。
それぞれのメニューのカテゴリとか、値段とかですね。
これは最初に、商品Mt.テーブルというデータの箱自体を作るときに設定します。
商品1つ1つのデータは、こうして箱の大枠を作った後に入れていきます。

ということで、SELECTの後には、この項目の名前を全部書いています。
もし分類に関しての情報がいらないなら、どうすればいいでしょうか。
SELECTの後に続く、商品分類IDと商品分類名を消去すればいいです。
その場合、その項目のデータは取ってこないということになります。

当然、「商品ID」と書くところを、「商品番号」や「商品コード」と書くと、うまくいきません。
そんな名前の項目はありませんので、データを取ってくることができないからです。
ちなみに「商品id」と書くと動くみたいですが、同じにしておいたほうが安全だと思います。

また、面倒なんですけど、項目やテーブル名の両サイドに\"を入れないと、これまたうまくいきません。
これは仕様のようなので、仕方ありません。


今回はすでにある商品の一覧から、同じ形式のデータを取ってくるという、あまり意味がないことをしました。
次回からは、ここに計算を織り交ぜて、意味のあるデータを取ってくるようにしていきたいと思います。

2016年2月20日土曜日

FileMakerやりまーす

主にFileMakerを使っていろいろやってみたい人向けのブログを作りました。

自分が覚えたことを整理しつつ、こんなことができますよーということを紹介していく予定です。

データベースに詳しい方のアドバイス等、頂けると嬉しいです。

また、実際にFileMakerやデータベース、業務システムを使っている方、使うことを検討中の方にも発信していきたいです。

こんなことができたらいいんだけど。

いやいや、そんなことはしないんじゃない?

そういった現場目線のご意見もお待ちしています。