Biz

タイトルどおりメッセージの往復回数をカウントするクエリの書き方です。 正確には異なるユーザがメッセージを送った回数をカウントする方法なのでチャットルームが1vs1なら往復回数だし3人以上いたら発信者が切り替わった回数と見ることができます。 最近はチャットサポートが当たり前になってきましたしチャットを提供するサービスも一般化してきたので使う機会は割とあるのではないでしょうか。 環境はBigQueryを想定しています。 データのイメージ こういう感じのデータをイメージしています。 massagesというテーブルに下記のようなデータが入っているとします。 id room_id user_id created_at 1 300 600 2023-01-01 00:00:00 2 300 700 2023-01-01 01:01:01 3 300 600 2023-01-01 08:01:02 4 400 800 2023-02-01 00:10:03 5 400 900 2023-03-01 01:00:04 6 400 900 2023-04-01 0500:05 idはmassageのユニークキーです。 room_idはチャットルームごとのユニークキーです。 room_idごとに何往復のチャットがやりとりされたのか数え上げることが目的です。 user_idはチャットルームに参加しているユーザのidでmassageを誰が送ったのかわかります。 ここではチャットルームに2人のユーザがいることを想定しています。 例文 with massages_with_lag as ( select id, room_id, user_id, lag(user_id, 1) over (partition by room_id order by created_at) as lag_user_id from massages ) select room_id, ceil(count(case when user_id <> lag_user_id then id end) / 2) as count_round_trips from massages_with_lag group by room_id このクエリは2つにわかれています。 前半は各メッセージごとに1つ前のメッセージのuser_idを横並びにさせ、後半でuser_idを比較して違ったらカウントが1つ増え、それを半分で割って小数点以下を繰り上げることで往復回数としています。 ...

valueに配列を含むJSONから配列を抽出し行へ変換するクエリの書き方 前提 下記のようなJSONが “テーブル名：table_1” の “列名：item_logs” に格納されいてるとき { "user_id": 1111, "item_names": [ "foo", "bar", "hoge" ] } このようなテーブルに変換するためのクエリ user_id item 1111 foo 1111 bar 1111 hoge 環境はBigQuery 書き方 json_query_arrayでほしい配列が格納されているペアのkey “$.item_names” を指定し目的の配列をとってくる unnestで配列を行に変換する（配列の順序が保証されないらしいので必要ならoffsetを使う） 上記で行に変換したものと元のテーブルをcross joinして目的のテーブルが完成 select user_id, json_value(items) as item, from table_1 as t_1 cross join unnest(json_query_array(t_1.item_logs, "$.item_names")) as items これを使ってJSONから item = "foo" のときだけフラグを建てる処理も簡単に書ける。 JSON値は比較とかできないのでjson_valueで変換する。 select id, json_value(items) = "foo" as is_foo, from table_1 as t_1 cross join unnest(json_query_array(t_1.item_logs, "$.item_names")) as items 変更があったり間違っていたらコメントください。 ...

Cloud ComposerからCloud Functionsにある関数を認証有りのHTTPトリガーで軌道する方法です。 公式のドキュメントに書いてありますが備忘録として書きます。 たぶん、これが一番簡単だと思いますが他に良い方法があったら教えてください。 環境 Airflow: 2.1.4 やりかた やることはわかってしまえば簡単です。 関数の実行権限をCloud Composerに付与する IDトークンを発行する HTTPリクエストのheaderにトークンを含めて送る これだけです。 詳しいやり方 関数の実行権限をCloud Composerに付与する GCPのコンソールからポチポチやるのが簡単だと思います。 具体的な手順はドキュメントを参照してください。 https://cloud.google.com/functions/docs/securing/authenticating#authenticating_function_to_function_calls ポイントは受信する側の関数ごとにroles/cloudfunctions.invokerを設定するということです。 やっていないのでわかりませんがComposerのサービスアカウントにroles/cloudfunctions.invokerを付与するのではなく関数側から設定する必要がありそうです。 一括で付与とかはできないのかな？できそうな気もするのでわかる人いたら教えてください。 IDトークンを発行する ここからはComposerのDAGのコードになります。 DAGでHTTPリクエストを送るまえに認証に使用するIDトークンを発行します。 import google.auth.transport.requests import google.oauth2.id_token auth_req = google.auth.transport.requests.Request() # endpoint はCloud Functionsにある関数のエンドポイントのURL id_token = google.oauth2.id_token.fetch_id_token(auth_req, endpoint) これで得られたid_tokenが目的のものになります。 endpoint はCloud Functionsにある関数のエンドポイントのURLです。 このトークンの発行に使っているGoogleのライブラリはComposerであればインストールされているはずなので簡単に利用できます。 HTTPリクエストを送る 最後はComposerからHTTPリクエストを送って関数を実行するだけです。 単純に関数を実行するだけであればエンドポイントをPOSTするだけで良いのですが認証情報をheaderに入れて送ります。 from airflow.providers.http.operators.http import SimpleHttpOperator end_point = "cloud functionsの関数のエンドポイント" invoke_task = SimpleHttpOperator( task_id = "invoke_function", endpoint=end_point, headers={"Content-Type": "application/json", "Authorization": f"Bearer {id_token}"}, dag=dag ) はい、これだけでOKです。 あとはよしなにDAGのなかにこのタスクを組み込んでください。 こういう単純なHTTPリクエストを送るのはAirflowの1系だと面倒だったのですが2系ではSimpleHttpOperatorという便利なものがあるのでこれを使えばOKです。 ...

pandasを使っていたら下記のwarningがでていたのを解決したので備忘録。 UserWarning: Could not import the lzma module. Your installed Python is incomplete. Attempting to use lzma compression will result in a RuntimeError. 環境 OS: macOS Catalina 10.15.7 Python: 3.8.1 pandas: 1.3.1 pyenv: 2.0.1 解決方法 どうやらpyenvを通してPythonをインストールしていると発生するようです。 https://github.com/pandas-dev/pandas/issues/27532 対処法は xz をインストールしてから改めてPythonをインストールします。 xzはbrewにあるので簡単です。 brew install xz これでxzが入るのでPythonをインストールしなおせばOK。 pyenv uninstall 3.8.1 pyenv install 3.8.1 これでwarningが消えました 🙌

この記事はビジネスにおいてデータ分析のレポートを作成する際に気をつけたほうがよいことを自分なりにまとめたものです。間違いやすい点なんかを集めたTIPSみたいな記事になっています。 レポートの書き方そのものについては良い書籍や記事がたくさんありますのでそちらを参照することをオススメします。 前提 データ分析のレポートでは基本構成としてIMRAD形式に則るのが良いです。IMRADとはIntroduction, Methods, Results And Discussionの頭文字を取ったもので、特に論文でよく使われる構成です。シンプルですが科学的検証に向いた形式でありデータ分析もデータを元に客観的に検証するという観点からIMRAD形式に合わせると適切に記述・検証することが可能になるので強く推奨です。逆に言えば、ビジネスのプレゼンテーションにありがちなインパクトを優先する恣意的な印象を与える方法は基本的にはNGです。 一方で、ビジネスの現場では重要な点をすぐに把握できる形が好まれます。そのため記述する形式はIMRADに限る必要はありません。私がよくやる方法は抄録を冒頭に置いてそれだけで要点をすぐに把握できるようにし、詳しく読みたい人向けに後ろにIMRAD形式で記述する方法です。要約には基本として議論を理解できる最低限の前提と手法そして重要な結果と考察を技術します。要約を書くことはとてもむずかしいです。ぜひ訓練を積みましょう。 Introduction この章では分析の目的・背景を記述します。あなたが何を目的にこの分析を行い、何を得たいのか明確にわかるように書きましょう。目的や背景を書くときに重要なことは、データ分析では分析の結果からどんな結果を得たいのか記述することです。これがなければ分析の意味がないので当然ながら重要な事柄になります。データ分析の目的は抽象化すると下記の3パターンにわけられます。 仮説をデータから検証する 変化を検知するために定常的なデータを取得する 数字感覚を知るために探索的分析をする 手元の分析が上記のどれに当てはまるのか分類し、それを踏まえて記述するとよいでしょう。たとえば、あなたが1番の仮説検証を行いたいのであれば、どんな仮説があり・どんな検証が必要で・その結果としてどんな判断をとることができるのか書くことになります。 背景を記述する際に気をつける点として、その分析が妥当であることを明記することが挙げられます。分析を行うまでには様々な背景や先行調査などがあるかと思います。それらを記載することで分析が妥当であることを説明します。1つの課題に対して手段は複数ありますが、なぜあなたはその手法を行ったのでしょうか？この問に対して十分な回答をここに記載する必要があります。もし、背景を省略した場合はあなたの分析が必要であることが伝わらず価値を理解してもらえないかもしれません。そのような状態を避けるために背景を記載することが重要です。そして、その背景は事前に関係者と共有し理解を得ていることが望ましいです。 Method この章では分析の対象や手法をすべて記載します。あなたがどこからデータを取得してきてどのように分析したのかわかり、再現できるようにしましょう。 基本として書くべきことは母集団の説明、データの取得方法（クエリやアンケート方法）、分析方法（特に統計的手法）です。ありがちなのが母集団に関する説明の不足です。データをどのような母集団から得たのか明記しましょう。データは取得した母集団によってバイアスを受けるため、バイアスに留意した記述が必要になります。 手法を記述する基本は5W1Hを用いた方法です。ビジネスの現場では特定の属性を対象にデータを集めることが多いと思うので、そのような事前に決めていた属性は網羅的に記述します。データの取得方法はアクティビティなどをデータベースから取得した場合は意図的に絞り込んだ条件の記載とクエリ、可能であれば生データを共有しておくと一番よいです。アンケートなど能動的にデータを取りに行った場合はいつ、どこで、どのようにして実施したのかなど可能な限り詳細にアンケート方法を記載しておくとよいでしょう。アンケートは様々な理由からバイアスが発生しうるので、なるべく詳細に実施内容を書いておくと良いです。どちらの手法にせよ、他の人が同じようにデータを取得できるレベルに記載しておくことが望ましいです。 分析方法は集計以上に統計的手法を用いた場合は明記しておくとよいです。統計的手法はいくらでも嘘の結論を導くことができるのでどんな手法を選んだのか明確に記述するべきです。とはいえ、そもそもレポートの書き方がままならないレベルの人は統計的手法を使わないほうが望ましいです。理解していない技術を使うことは思わぬ失敗を招きます。必要ならば専門家と共同して行うべきでしょう。 Results and Discussion この章では分析から得られた結果とその考察を記載します。あなたが分析から得られたデータや統計的解析結果とそれに対する考察を書きます。 結果と考察の項を書くときに最も重要なことは客観的事実と自分の考えを明確にわけて書くことです。データ分析はデータという客観的な情報を元に意思決定することを目的としています。にもかかわらず、あなたの主観と客観的情報を混ぜて書いてしまってはその価値は失われてしまうでしょう。 特にグラフの書き方はルールや作法がありますので留意すべき点でしょう。グラフを記載する際のルールはそれを守ることによってグラフを見た人が誤解をせず適切にグラフから情報を読み取れるようにするためのものです。軸の単位やラベルを書いたり説明を記述することは最低限であり、これが不足しているグラフを描くことは避けましょう。もし図や表の書き方を詳細に知りたい方は科学コミュニティにおける書き方を参考にすると良いでしょう。 統計学的手法を用いている場合はその結論を導いて良いのか適切に検討しましょう。例えば、相関関係と因果関係を見誤ることはとても多い問題です。他にも検定の結果の受け方は一癖あるので手法を理解し記述するよう気をつけるべきでしょう。これを間違うと一気にレポートの信頼性が落ちてしまいます。 また、バイアスの存在は重要です。データは常に何かしらのバイアスの影響を受けています。結論に影響がなくても影響が無いことを明記すべきです。バイアスについて留意した考察を行うことを行ったということが重要になります。 留意点 ここまでIMRAD形式をベースにレポーティングの留意点を記述しました。ここまで読んだ人の中にはこんなにたくさんの文字を書くことは非常に労力がかかり、まるで冗長な作業のように感じる方もいるでしょう。私も以前はそう思っていました。 もちろん、このような内容を網羅的に記述することは労力がかかります。実際に、ベテランは意図的にこれらのいくつかを書かないことがあります。しかし、それはケースごとの"重点"を理解した手抜きです。レポートを見る人の関係性や分析の重要度、実験の難易度などを加味した上で省略をします。 しかし、少なくとも初心者のうちはこのような省略をおこなわず、すべてを明確に書くべきだと考えます。なぜなら、このように網羅的な記述を行うことは物事を整理し言語化する力を強くしてくれるからです。大変ですが、それでも時間と労力をかけて明確に記述することを推奨します。 初めてこのようにレポートを書いたとき、とても大変で投げたくなるでしょう。大丈夫です、私もそうでした。もしよければ3ヶ月だけ我慢してみてください。きっとあなたのスキルが進化していることを体感できるでしょう。 あとがき この記事ではデータ分析を行ったときのレポーティングについて簡単に重要な点を述べさせていただきました。 この記事をとおして、あなたのレポートが良いものになれば嬉しい限りです。