【BigQuery】#3 組み合わせごとに会員数（ID数）をカウントする【組み合わせ分析】
facilityの組み合わせごとの会員数をカウントする
前回作成したfacilityの組み合わせ（set_facility）を使って、facilityの組み合わせ毎に会員数がどのくらいいるのかカウントします。
SQLのコードは下記です。
SELECT set_facility, COUNT(DISTINCT ID) AS ID_count FROM `table_name` GROUP BY set_facility
COUNT( [数えたい要素] )
今回は[数えたい要素]をIDとすることで会員数をカウントしています。
またCOUNT関数内にDISTINCTを入れることでIDを重複せずにカウントすることができます。
またGROUP BY句でset_facilityを指定することで、set_facility毎に会員数を数えるようにしています。
BigQueryでのクエリ結果
それではBigQuery上で実際にクエリを実行します。
クエリ
SELECT set_facility, COUNT(DISTINCT ID) AS ID_count FROM `table_name` GROUP BY set_facility ORDER BY ID_count DESC
分かりやすく表示するために、ORDER BY句で会員数の多い順にデータを表示するようにしています。
クエリ実行結果
facilityの組み合わせ毎に会員数をカウントすることができました！
ジムが5人と一番多くなっていることがわかります。
まとめ
1～#3までのコードをまとめると以下のようになります。
WITH tbl_time AS (--time-stampをYYYY-MM表記に変換 SELECT FORMAT_DATE("%Y-%m", DATE(TIMESTAMP_SECONDS(time_stamp))) AS date, record_number, ID, facility FROM `table_name` ) , tbl_set AS (--ID毎にfacilityの組み合わせを作成 SELECT ID, STRING_AGG(DISTINCT facility ORDER BY facility asc) as set_facility FROM tbl_time GROUP BY ID ) SELECT time0.date, set0.set_facility, COUNT(DISTINCT set0.ID) AS ID_count FROM tbl_set AS set0 INNER JOIN tbl_time AS time0 ON ( set0.ID = time0.ID ) GROUP BY time0.date, set0.set_facility ORDER BY ID_count DESC
WITH句を使用して複数テーブルを作成しています。
また最後にINNER JOIN句を用いて、facilityの組み合わせ（set_facility）とdateをIDをキーに結合しています。
（今回はdateは「2022-02」のみのためtbl_setの中にdateを組み込んでもいいのですが、もしdateが複数ある場合はこのように最後に結合させる必要があります。）
クエリ実行結果
このデータ処理を行うことで組み合わせ分析がいろいろとできそうですね！
【BigQuery】#2 ID毎（会員別）にfacilityの組み合わせを作成する【組み合わせ分析】
ID毎にfacilityの組み合わせを作成する
会員がどのfacilityをどのような組み合わせで使っているかを調べるために、ID毎にfacilityの組み合わせを作成します。
使うコードは下記です。
SELECT ID, STRING_AGG(DISTINCT facility ORDER BY facility ASC) as set_facility FROM `table_name` GROUP BY ID ORDER BY ID ASC
STRING_AGG (カラム名, 区切り文字) WITHIN GROUP (ORDER BY ソート条件)
STRING_AGGで文字列式の値の間に区切り記号を挿入して、文字列同士を連結させることができます。
今回はデフォルト値の","区切りを使用するため、区切り文字の指定は省略しています。
重要な点としては、今回はID毎にfacilityの組み合わせを作りたいため、ID列をGROUP BYに指定することです。
BigQueryでのクエリ結果
それではBigQuery上で実際にクエリを実行します。
クエリ実行結果
ID毎にfacilityの組み合わせを作成することができました！
次回からはfacilityの組み合わせごとのID数（会員数）をカウントしていきます。
【BigQuery】#1 Time Stampデータを日付形式に変換する【組み合わせ分析】
TIME STAMPをYYYY-MMのフォーマットに変換する
データ内にある「time_stamp」列をYYYY-MMの表示形式に変換します。
SQLのコードは下記です。
FORMAT_DATE("%Y-%m", DATE(TIMESTAMP_SECONDS(time_stamp)))
FORMAT_DATE関数で指定したフォーマットにデータを変換します。
ここでは"%Y-%m"のフォーマットを指定し「YYYY-MM」で表記されるようにしています。
"%Y-%m"の部分を変更することにより、日付の表記を変更することができます。
日付変換フォーマット例
- "%Y" = 4桁の年(2022)
- "%y" = 2桁の年(22)
- "%M" = 英語の月名(JULY)
- "%m" = 2桁の月(07)
- "%d" = 2桁の日付(01)
- "%k" = 24時間表記の時間(24)
- "%i" = 2桁の分(01)
- "%s" = 2桁の秒(01)
今回使用するデータにある「time_stamp」は秒までのタイムスタンプのため、関数はTIMESTAMP_SECONDSを使います。
もしミリ秒まで情報が含まれる場合は、TIMESTAMP_MILLISを使います。
BigQueryでのクエリ結果
それではBigQuery上で実際にtime_stamp列をYYYY-MM表記に変換します。
クエリ
SELECT time_stamp, FORMAT_DATE("%Y-%m", DATE(TIMESTAMP_SECONDS(time_stamp))) AS YYYYMM, FORMAT_DATE("%Y-%m-%d", DATE(TIMESTAMP_SECONDS(time_stamp))) AS YYYYMMDD FROM `table_name` GROUP BY time_stamp ORDER BY time_stamp ASC
今回使用するフォーマットはYYYY-MMのみですが、日付フォーマットの変換の違いをみるためにYYYY-MM-DDバージョンもクエリに含みました。
クエリ実行結果
time_stampをYYYY-MMやYYYY-MM-DD形式に変換することができました。
次回からはID毎にfacilityの組み合わせを作成していきます。
【BigQuery】#0 データ加工して組み合わせ分析をする
導入
BigQueryでデータ加工をして、アイテム組み合わせでのデータテーブルを取得したいと思います！
今回使用するデータはこちら。
このCSVデータをBigQueryにインポートして加工していきます。
＜データ一部キャプチャ＞
スポーツ施設_利用履歴.csv - Google ドライブ
スポーツ施設の利用履歴をイメージして、サンプルデータを作成しました。
このスポーツ施設には「ジム」「プール」「スタジオ」の3種類の施設があって、利用した日がTimeStamp形式で記録され、そのほかに会員ID、利用した施設（facility）がデータとして記録されています。
このデータで「利用する施設はどの組み合わせが人気か」を調査するために、BigQueryでデータ加工をしていきたいと思います！
施設の人気な組み合わせがわかることで、それに合わせた価格プランを練るなどの施策ができそうですよね！
最終成果物イメージ
それでは早速次回から加工を始めていきます！
【2週間で合格】MOS Access 資格試験【勉強法】
MOS Access 2016資格取得
2022年3月6日にMOS Access 2016の試験を受けて合格しました！
Access自体、実務でもプライベートでも触ったことがなかったのですが、おおよそ2週間の勉強で合格することができたのでその勉強方法を書いていきます。
勉強方法
使用教材
使った教材は「Microsoft Office Specialist Microsoft Accsess 2016 対策テキスト＆問題集」のみです。
この教材の練習問題をさっと一周してあとは教材内の模擬試験を繰り返し解いて試験に挑みました。
操作自体はそこまで難しくなく、いかに操作に慣れるかが重要なポイントになってきます。
勉強手順
１．まずは練習問題を実際に手を動かしながら解いていきます。
簡単な操作もあるのですが、飛ばさずにすべての問題を実際にAccessで操作しました。
（約1週間で練習問題一周完了）
２．練習問題を一周して、さっそく第一回模擬試験を受けました。
練習問題の最初のほうの操作を忘れてしまっていたかつ時間が足りずにこのときの得点率は43%でした。
不正解問題の見直しをします。
（この時点で試験4日前）
３．第二回模擬試験を受けました。
このときは時間内にすべての問題を解くことができ、得点率は73%でした。
不正解問題の見直しをします。
（この時点で試験3日前）
４．最後の模擬試験を受ける前に練習問題をさっと一読しました（1～2時間程度）。
一週目であまり理解できなかった説明もこの一読で理解度をあげることができました。
（試験前日）
５．最後の模擬試験を受けました。
得点率は確か80％に満たない程度だったと思います。
６．最後の追い込みとして3回分の模擬試験を再度解き直しました。
得点率は安定して90%以上とることができたので、安心して本試験に挑みました。
（試験当日）
試験本番、結果は1000点中977点！
なかなかの高得点で嬉しかったです。
試験合格のコツ
私は試験勉強を始めた日に2週間後の日で試験予約をしました。
こうすることでだらだらせずに追い込むことができたので、おすすめです！
試験の難易度自体はそこまで高くなく、操作に慣れることができれば確実に合格できるので、短期決戦でさくっと合格しちゃいましょう。
お試し版Accessのインストール
私のPCにはAccessが入っていなかったため、１か月無料で使うことができる「Microsoft 365 Personal 体験版」を活用しました。
無料期間中に「定期請求の無効化」をすることを忘れないように要注意です。
（あと実際の試験とバージョンが違うためか、若干UIが違ったような気がします。）
https://www.9-soft.com/microsoft-365-free
【powerapps講座】#3 編集フォームの設置と保存ボタンの作成
編集フォームの設置と保存ボタンを作成する
前回は検索ボックスとデータテーブルの設置をしました。
今回は編集フォームと編集した内容のリストへの反映（保存）ボタンを作成していきます。
編集フォームを挿入する
まずは編集フォームを挿入します。
「挿入」タブ→「フォーム」→「編集」をクリックします。
フォームが挿入されました。
右端に出るフォームの編集画面からデータソースの設定をします。
編集画面のデータソースを対象のSharePoint Listに変更します。
データが反映されました。
不要な項目は削除します。
右端のフォーム編集画面から「フィールドの編集」をクリックします。
フィールドが開きますので削除したい列名の「・・・」をクリックし、「削除」をクリックします。
不要な項目の削除ができました。
データテーブルと編集フォームを連携させる
データテーブルで選択している項目の情報を編集フォームに表示するように設定します。
編集フォームの「Item」に以下のコードを設定します。
DataTable1.Selected
データテーブルで選択した項目を編集フォームで表示ができるようになりました！
保存ボタンを作成する
編集フォームで編集した内容をSharePoint Listに反映する保存ボタンを作成します。
まずは「挿入」タブ→「ボタン」をクリックでボタンを挿入します。
ボタンが挿入されました。
ボタンを選択し、右端の編集画面「テキスト」を変更することでボタンのテキストを変更することができます。
今回は「保存」に変更します。
ボタンの「OnSelect」に以下のコードを設定します。
SubmitForm(Form1)
これで「保存ボタン」を押すことで編集フォームの内容がリストに反映されるようになります。
アプリの完成！
見た目を整えてアプリの完成です！
【powerapps講座】#2 テーブルと複数検索ボックスを設置する
テーブルと複数検索ボックスを設置する
前回の記事でSharePoint Listのインポートをしました。
今回はリストの中身を表示するテーブルと検索ボックスを設置します。
データテーブルを挿入する
「挿入」タブから「データテーブル」を選択します。
データソースの選択で対象のリストを選択します。
データが反映されました！
SharePoint Listでタイトルに設定している列は列名がTitleと表記されるため、列名を変更します。
対象の列を選択し、右端に出てくる編集画面の「ヘッダーテキスト」を正しい列名に変更します。
検索ボックスを挿入する
続いて検索ボックスを設置します。
今回は「分類」列と「商品名」列の2つで検索ボックスを設置します。
検索ボックスはSharePoint Listでの列の設定によってコードの書き方が変わります。
「挿入」タブ→「入力」→「ドロップダウン」を選択します。
同じ要領で検索ボックスを2つ設置します。
検索ボックスの選択肢を設定する
「ツリービュー」→「App」→「OnStart」に検索ボックスの選択肢の設定をしていきます。
入力するコードは以下の通りです。
//1.分類の選択肢設定
ClearCollect(category, {Result:"-"});
Collect(category, Distinct(在庫管理リスト, 分類.Value));
//2.商品名の選択肢設定
ClearCollect(name, {Result:"-"});
Collect(name, Distinct(在庫管理リスト, 商品名))
「分類」のコードで1行ずつ説明します。
まずCollect関数を使って、「category」のコレクションを作成しています。
コレクションの中身としては、Distinct関数でリストで回答されている項目とするように設定しています。
SharePoint Listで選択肢の設定をしている場合は列名の後ろに「.Value」を追加します。
ここでは「分類」列は選択肢、「商品名」列は1行テキストで設定しているため、分類のみ.Valueをつけています。
ClearCollect関数で「category」コレクションの中身を削除し、「-」を追加する設定をしています。
「-」は検索ボックスで無選択のときに使うため追加しています。
「ツリービュー」→「App」の「・・・」をクリックし、「OnStartを実行します」をクリックします。
データにコレクションが追加されました。
コレクションの内容は「ファイル」→「コレクション」から確認することができます。
検索ボックスそれぞれにさきほど設定したコレクションの設定をしていきます。
検索ボックスの「Items」にコレクション名を入力します。
検索ボックスに選択肢が表示されるようになりました！
検索ボックスとテーブルを連携させる
続いて検索ボックスとテーブルの連携設定をしていきます。
検索ボックスの選択は全部で4通り考えられるため、4通りのコードを設定していきます。
4通りのコードは以下の通りです。
If(
//すべて選択されない
Dropdown_name.Selected.Result = "-" And Dropdown_category.Selected.Result = "-",
在庫管理リスト,
//どれか1つが選択
Dropdown_name.Selected.Result <> "-" And Dropdown_category.Selected.Result = "-",
Filter(在庫管理リスト, 商品名 = Dropdown_name.Selected.Result),
Dropdown_name.Selected.Result = "-" And Dropdown_category.Selected.Result <> "-",
Filter(在庫管理リスト, 分類.Value = Dropdown_category.Selected.Result),
//2つとも選択される
Dropdown_name.Selected.Result <> "-" And Dropdown_category.Selected.Result <> "-",
Filter(在庫管理リスト, 商品名 =Dropdown_name.Selected.Result, 分類.Value = Dropdown_category.Selected.Result))
検索ボックスがすべて選択されないとき、つまり検索ボックスの表示が「-」のときは在庫管理リストの一覧を表示します。
検索ボックスのうちどれか1つが選択されたとき、つまり検索ボックスの表示が片方が「-」でもう片方が「-」以外のものが表示されたときは「-」以外の指定されたワードで列の絞り込みをします。
コードの<>は≠を意味します。
検索ボックスが2つとも選択されたとき、つまり検索ボックスの表示が2つとも「-」以外の場合は、「-」以外の指定されたワードで2列の絞り込みをします。
このコードはテーブルの「Items」に入力します。
実際に検索ボックスを操作してみます。
＜検索ボックスの指定なし＞
＜片方の検索ボックスのみ選択＞
＜両方の検索ボックスを選択＞
それぞれの条件でうまくテーブルに絞り込みデータを出すことができました！
