Shunya Ueta

Standard SQLで 列と列の組み合わせの数を集計したい

group by は集計作業において根幹となる処理ですが、少し手の混んだ集計をしたいときに毎回調べていることが多かったのでここに学んだことをまとめておく

今回やりたいことは

A 列が α になっている行の B 列の種類を集計したい

です。

はじめに

実際のデータを用意したほうが、理解が深まるので擬似的なテーブルを作成する。 テーブルのデータの概略として、何日に sender (送信者) が receiver (受信者) にいくら送金(price)したかを格納しているテーブルとする。

StandardSQL は WITH を使って簡単にモックテーブルを作れるのが良いところ。

#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
senderreceiverpriceday
AB6002020-01-01
AB12002020-01-01
AB18002020-01-01
AC20002020-01-01
AD30002020-01-01
AD20002020-01-01
BC7002020-01-01
BC3002020-01-01
BD2502020-01-01
AB4002020-01-02
AB10002020-01-02
AB12002020-01-02
AB20002020-01-02
BC4502020-01-02
BC5002020-01-02

列と列の組み合わせの数を集計する

日次ごとに送金者が何人に送ったかを集計したい、つまり(sender, receiver)のペアを考えて、sender を固定した上で何人に送金したいかを集計したとする。 上記のデータだと

2020-01-01 では {(A,B), (A,C), (A,D)} の三通りになる。

愚直に思いつくのは

group by を２つの列で行うパターン

をgroup by して COUNT(distinct sender)で集計するアプローチがあるが、

SELECT
  day,
  sender,
  COUNT(distinct  receiver) AS receiver_uu
FROM
  transactions
GROUP BY
  day,
  sender

結果

daysenderreceiver_uu
2020-01-01A3
2020-01-01B2
2020-01-02A1
2020-01-02B1

day, sender の組み合わせとなっている。 このクエリを WITH でテーブルにして、Where でフィルターをかけると

receiver_uu AS (
  SELECT
    day,
    sender,
    COUNT(DISTINCT receiver) AS receiver_uu
  FROM
    transactions
  GROUP BY
    day,
    sender)
SELECT
  *
FROM
  receiver_uu
WHERE
  sender = 'A'
daysenderreceiver_uu
2020-01-01A3
2020-01-02A1

の結果が得られる。

だが、dayを基準に送信者、AとBの情報を 1 行にまとめたい場合はどうすべきだろうか?

1 行に 2 列のパターン数をまとめて集計する

AとBが送り先の数を調べたいなどの目的がある際には、COUNT()を行う際に IF 文で条件を集計することで上記を達成することができる。

SELECT
  day,
  COUNT(DISTINCT
    -- 以下の結果の重複排除
  IF
    (sender='A',
      -- sender がAだったときに
      receiver,
      -- receiver を返す
      NULL)
    -- それいがいはNULL
    ) AS from_A_receiver_uu,
  COUNT(DISTINCT
  IF
    (sender='B',
      receiver,
      NULL)) AS from_B_receiver_uu
FROM
  transactions
GROUP BY
  day

流れを説明すると

  1. day で日次を基準にした計算を行う
  2. IF(sender=‘A’, receiver, NULL) で、スキャン時にAが送信した場合はその同行のreciver を返し、Aでなかった場合は NULL を返す
  3. 2 の結果の重複を排除して、その行数をカウントする

上記の処理のイメージ (2020-01-01の場合)

  1. COUNT(Distinct IF(sender=‘A’,receiver, NULL))
  2. COUNT(Distinct (B,B,B,C,D,D,NULL,NULL,NULL))
  3. COUNT((B,C,D))
  4. 3

となり、Aが2020-01-01に送信した人数は 3 人となる。

結果

dayfrom_A_receiver_uufrom_B_receiver_uu
2020-01-0132
2020-01-0211

今回は(A,B)の組み合わせで A を固定した際のパターン数を集計したが、A の種類が大規模な場合は SELECT 文をすべて書ききるの現実ではないが、A 自体が有限で集計したい場合はよくある処理なのでメモとしてまとめた。 UU などを計算したいときは必須の集計になると思います。

Ref

---

関連しているかもしれない記事


📮 📧 🐏: 記事への感想のおたよりをおまちしてます。 お気軽にお送りください。 メールアドレス入力があればメールで返信させていただきます。 もちろんお返事を希望せずに単なる感想だけでも大歓迎です。

このサイトの更新情報をRSSで配信しています。 お好きなフィードリーダーで購読してみてください。

このウェブサイトの運営や著者の活動を支援していただける方を募集しています。 もしよろしければ、Buy Me a Coffee からサポート(投げ銭)していただけると、著者の活動のモチベーションに繋がります✨

#sql #dataanalysis