sql

データの蓄積帰還が長くなってくると、例えば JSON 形式でログを取っているが、同じデータでもマイグレーションやロギングロジックの更新などでkey の名前が変化したりする場合がある。 その場合取り扱いに困るのが、古い key と新しい key をどのように併合するかだ。 例えば特定の日次できれいにデータが入れ替わっているのなら、色々やりようがあるが、クライアントなどのログの場合データの変化も均一ではないので、徐々に変化していることが大半なので、日次で別々の抽出をして結合するというアプローチも難しい。 その際に役立つのが Standard SQL 条件付き構文の COALESCE だ。 COALECSCE は、引数の最初の非 NULL の値を返す関数で、 1 COALESCE(NULL, 'B', 'C') だと Bが返される。この関数を使うことで、複数カラムを一つに併合することができる。 具体例を交えつつ実践してみる 例えば、以下のように昔のカラム名が title で、全く同じデータが新しいカラムの title_v2 に入ってきているとする。 NOTE: json を例題に key の抽出にしたほうが実際の状況に沿いますが、カラムのみで表現したほうが説明が簡単なので今回はそちらを採用。 用意したデータ 1 2 3 4 5 6 7 8 WITH menues AS (SELECT "うどん" as title, NULL as title_v2, "2021/10/06" as created UNION ALL SELECT "ラーメン", NULL, "2021/10/07" UNION ALL SELECT NULL, "そば", "2021/10/08" UNION ALL SELECT "カツ丼", NULL, "2021/10/09" UNION ALL SELECT "カツ丼", "カツ丼", "2021/10/10" UNION ALL SELECT NULL, "カレー", "2021/10/11") SELECT * FROM menues title title_v2 created うどん 2021/10/06 ラーメン 2021/10/07 そば 2021/10/08 カツ丼 2021/10/09 カツ丼 カツ丼 2021/10/10 カレー 2021/10/11 2021/10/10 のデータなどは旧カラムと新カラムにダブルライトされています。...

データ処理のタスクをこなしていると、PythonでSQLに変数を挿入し柔軟にSQLクエリを構築したくなる。 例えば、 中間テーブルを作るためにAirFlowなどで定期的なジョブを実行し、SQLの createdの時間を当日のものに変更する training, dev, test でデータを分割する際に、createdの条件を変更して3パターンのデータを取得する などが考えられる。 変数をSQLに組み込んで実行したい際には、kayak/pypikaのような SQL builder もあるが、個人的に可読性が悪くなったり、SQLクエリの作成のためだけに余計なパッケージをいれたくない。そのためパッケージを入れずにシンプルに完結する方法をここでは紹介する。 編集履歴 2021/05/12: twitter でdocstring ではなくstring literal ですよという指摘をいただき修正 ref 2021/05/12: twitter での意見を反映 1. 単なる文字列としてSQLクエリを構築 1 2 3 def get_guery(num: int, category: str): sql=f"SELECT field1, field2, field3, field4 FROM TABLE WHERE condition1={num} AND condition2={category}" return sql f-string で文字列に変数を挿入して、SQLクエリを構築 だが、 SQLが長くなるとPEP8に準拠せず、E501 line too longに抵触する 視認性が低く、SQLクエリの実行内容を理解しづらい 2. 複数行文字列としてSQLクエリを構築 1 2 3 4 5 6 7 8 def get_guery(num: int, category: str): sql = f""" SELECT field1, field2, field3, field4 FROM table WHERE condition1={num} AND condition2={category} """ return sql 1番目と比較すると、複数行を扱えるstring literal-longstringを採用することで、SQLクエリが複数行になることでで見やすい (@cocu_tan さん、ご指摘ありがとうございます!...

group by は集計作業において根幹となる処理ですが、少し手の混んだ集計をしたいときに毎回調べていることが多かったのでここに学んだことをまとめておく 今回やりたいことは A 列が α になっている行の B 列の種類を集計したい です。 はじめに 実際のデータを用意したほうが、理解が深まるので擬似的なテーブルを作成する。 テーブルのデータの概略として、何日に sender (送信者) が receiver (受信者) にいくら送金(price)したかを格納しているテーブルとする。 StandardSQL は WITH を使って簡単にモックテーブルを作れるのが良いところ。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 #standardSQL WITH `transactions` AS ( SELECT 'A' AS sender, 'B' AS receiver, 600 AS price, '2020-01-01' AS day UNION ALL SELECT 'A', 'B', 1200, '2020-01-01' UNION ALL SELECT 'A', 'B', 600, '2020-01-01' UNION ALL SELECT 'A', 'C', 2000, '2020-01-01' UNION ALL SELECT 'A', 'D', 3000, '2020-01-01' UNION ALL SELECT 'A', 'D', 2000, '2020-01-01' UNION ALL SELECT 'B', 'C', 700, '2020-01-01' UNION ALL SELECT 'B', 'C', 300, '2020-01-01' UNION ALL SELECT 'B', 'D', 250, '2020-01-01' UNION ALL SELECT 'A', 'B', 400, '2020-01-02' UNION ALL SELECT 'A', 'B', 1000, '2020-01-02' UNION ALL SELECT 'A', 'B', 1200, '2020-01-02' UNION ALL SELECT 'A', 'B', 2000, '2020-01-02' UNION ALL SELECT 'B', 'C', 450, '2020-01-02' UNION ALL SELECT 'B', 'C', 500, '2020-01-02' ) SELECT * FROM transactions sender receiver price day A B 600 2020-01-01 A B 1200 2020-01-01 A B 1800 2020-01-01 A C 2000 2020-01-01 A D 3000 2020-01-01 A D 2000 2020-01-01 B C 700 2020-01-01 B C 300 2020-01-01 B D 250 2020-01-01 A B 400 2020-01-02 A B 1000 2020-01-02 A B 1200 2020-01-02 A B 2000 2020-01-02 B C 450 2020-01-02 B C 500 2020-01-02 列と列の組み合わせの数を集計する 日次ごとに送金者が何人に送ったかを集計したい、つまり(sender, receiver)のペアを考えて、sender を固定した上で何人に送金したいかを集計したとする。 上記のデータだと...