SQL

SQLではよくあるケースだけど案外書くのが難しい書き方。 これが一番簡単だと思いますが、もっといい方法があったら教えてください。 やりたいこと なんらかのカテゴリーごとに順番で並べて一番数値が高い行の他のカラムのデータを取得するクエリです。 たとえば、下記のitemsというテーブルがあったときcategoryごとにpriceが最も高いnameを取得するという問題です。 itemsテーブル category name price belt a 2000 belt b 10000 belt c 4000 wallet d 3000 wallet e 60000 wallet f 15000 wallet g 20000 求める結果 category name price belt b 10000 wallet e 60000 クエリの例 この問題はwindow関数でrow_number関数を使うことで記述する方法がオススメです。 select category, name, price from ( select category, name, price, row_number() over (partition by category order by price desc) as price_order from items ) where price_order = 1 なにをやっているのか簡単に言うと partition by category でcategoryごとにわけて order by price desc でprice降順で並びかえたところに row_number() で順番に番号を割り当てています。 これによってpriceが最も高いとprice_orderカラムに1が入り降順で番号が割り当てられます。 これをサブクエリとして price_order = 1 に絞り込むことで最大の値が手に入るという仕組みです。 注意点として同じpriceの行が複数存在する場合はどれか1行しか得られません。 ...

タイトルどおりメッセージの往復回数をカウントするクエリの書き方です。 正確には異なるユーザがメッセージを送った回数をカウントする方法なのでチャットルームが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 変更があったり間違っていたらコメントください。 ...