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やデータベース、業務システムを使っている方、使うことを検討中の方にも発信していきたいです。

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

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

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