【MySQL】 GROUP BY + SUM するときに重複レコードを除外したい

背景

• 検証時 MySQL8.0 系
• 実際のご利用はおすすめしません。自己満足記事です。
• 複数のテーブルをJOINして一覧を取得する際に、合計値の集計や関連テーブルの要約の列などの複雑な表示を 1回のSQL で済ませたかった。
• JOIN句で1:多のテーブルも取得すると、GROUP BY + SUM()関数で集計したかった値が想定より大きくなってしまった。
• JOIN句はそのままに、SUM()関数では一意のレコードのみ集計させたい。
  • ORDER BY で結果を並び替えできるようにしたいため
  • なんならRailsのActiveRecordで動的に扱える程度の簡易なSQLにしたい ！！！！！！！！

やりたいこと

• 実際のものより複雑度をとても落としております。記事化のために

before こいつらを

# お財布テーブル(wallets)
# 現在の使用額や初期資産などを保持している
# 複数財布持つ人もいるので年(year)で1件とは限らない
+----+---------+-------+---------+------+
| id | user_id | used  | initial | year |
+----+---------+-------+---------+------+
|  2 |       1 | 12000 |  500000 | 2022 |
|  3 |       1 |     0 |      10 | 2022 |
| 45 |      34 |  5000 | 1000000 | 2022 |
+----+---------+-------+---------+------+

# おレシートテーブル(contracts)
# お会計のタイトルやその他(省略)いろいろ持っている
+----+-----------+---------+-----------------------+
| id | wallet_id | shop_id | title                 |
+----+-----------+---------+-----------------------+
|  1 |         2 |       1 | おひるごはん          |
|  2 |         2 |       2 | おひるごはん          |
|  3 |         2 |       2 | 砂糖とスパイス        |
+----+-----------+---------+-----------------------+

# お店テーブル(shops)
+----+----------+
| id | name     |
+----+----------+
|  1 | ぱんだ軒 |
|  2 | プァミマ |
+----+----------+

after こうしたい

# ユーザー-年で集計、使ったお店もつなげて表示
+----+---------+-------+---------+------+-------------------+
|  # | user_id | used  | initial | year | 利用先            |
+----+---------+-------+---------+------+-------------------+
|  2 |       1 | 12000 |  500010 | 2022 | ぱんだ軒,プァミマ |
| 45 |      34 |  5000 | 1000000 | 2022 |                   |
+----+---------+-------+---------+------+-------------------+

やってみた

SELECT
  MIN(`wallets`.`id`) AS id,
  `wallets`.`user_id`,
  `wallets`.`year`,
  TRUNCATE(SUM(DISTINCT (`wallets`.`initial` * 1000000000 + `wallets`.`id`)) / 1000000000, 0) AS initial,
  TRUNCATE(SUM(DISTINCT (`wallets`.`used` * 1000000000 + `wallets`.`id`)) / 1000000000, 0) AS used,
  GROUP_CONCAT(
    DISTINCT `shop`.`name`
    ORDER BY `shop`.`id` ASC
    SEPARATOR ','
  ) AS 利用先
FROM `wallets`
LEFT OUTER JOIN `contracts` ON `contracts`.`wallet_id` = `wallets`.`id`
LEFT OUTER JOIN `shops` ON `shops`.`id` = `contracts`.`shop_id`
GROUP BY `wallets`.`year`, `wallets`.`user_id`

2022/12/21追記：
　上記のSELECT句はあくまで一例です。
　ORER BY 句と組み合わせる際は AS句で別名を割り振っておくとRailsでソートできたりします
2023/9/29修正：
　SUM DISTINCT CONCATを用いた方法では正しく計算されない場合があったため修正しました。( SUM(DISTINCT CONCAT(数字, "-", id)) のやり方だとハイフン前の数字部分が重複している値の集計がなぜかされませんでした)

解説

• SELECT句芸である。

外部結合により、GROUP BY 前の状態が

+----+---------+-------+---------+------+-------------------+
|  # | user_id | used  | initial | year | 利用先            |
+----+---------+-------+---------+------+-------------------+
|  2 |       1 | 12000 |  500000 | 2022 | ぱんだ軒          |
|  2 |       1 | 12000 |  500000 | 2022 | プァミマ          |
|  2 |       1 | 12000 |  500000 | 2022 | プァミマ          |
|  3 |       1 |     0 |      10 | 2022 |                   |
| 45 |      34 |  5000 | 1000000 | 2022 |                   |
+----+---------+-------+---------+------+-------------------+

となっているので、普通に GROUP BY + SUM() で集計してしまうと

+----+---------+-------+---------+------+-------------------+
|  # | user_id | used  | initial | year | 利用先            |
+----+---------+-------+---------+------+-------------------+
|  2 |       1 | 36000 | 1500010 | 2022 | ぱんだ軒          |
| 45 |      34 |  5000 | 1000000 | 2022 |                   |
+----+---------+-------+---------+------+-------------------+

のようにレシートの数だけ所持金が1次関数的に増えてしまう（うらやましい）のでNGだった
ここで、 DISTINCT句を用いて集計を一意のものだけにするために以下のように細工を施した

+----+---------+-----------------+--------------------+------+-------------------+
|  # | user_id | used * 1bn + id | initial * 1bn + id | year | 利用先            |
+----+---------+-----------------+--------------------+------+-------------------+
|  2 |       1 |  12000000000002 |    500000000000002 | 2022 | ぱんだ軒          |
|  2 |       1 |  12000000000002 |    500000000000002 | 2022 | プァミマ          |
|  2 |       1 |  12000000000002 |    500000000000002 | 2022 | プァミマ          |
|  3 |       1 |               3 |        10000000003 | 2022 |                   |
| 45 |      34 |  50000000000045 |  10000000000000045 | 2022 |                   |
+----+---------+-----------------+--------------------+------+-------------------+

上記にDISTINCT句を仕掛けることで、所持金について一意に値を取ることができるた

+----+---------+-----------------+-------------------+------+-------------------+
|  # | user_id | used * 1bn + id | initial * 1bn +id | year | 利用先            |
+----+---------+-----------------+-------------------+------+-------------------+
|  2 |       1 |  12000000000002 |   500000000000002 | 2022 | ぱんだ軒          |
|  3 |       1 |               3 |       10000000003 | 2022 |                   |
| 45 |      34 |  50000000000045 | 10000000000000045 | 2022 |                   |
+----+---------+-----------------+-------------------+------+-------------------+

しかし、この状態でSUM()関数を使った集計なんてすると、「計算できないんじゃないか？？？」と思いがちだが、
どうやらSUM()関数は集計対象に文字列リストが入ってきても内部でCASTして数値として認識してくれているらしい？？？
+---------------------------+----------------------------+
| CAST("12000-2" AS SIGNED) | CAST("500000-2" AS SIGNED) |
+---------------------------+----------------------------+
| 12000 | 500000 |
+---------------------------+----------------------------+

MySQLのこのような挙動を利用させていただき、所持金が3倍になるバグを回避できた

その後10億かけたものを10億で割り算してつぎ足した端数を切り捨てる
500010
1000000

まとめ

• SUM()関数内部で使われているであろうCAST動作に頼っているので確実ではなさそう お勧めできない
• 1本のSQLで実現したかったことは実現できた
• RailsのActiveRecordで記載できた