KPIをSQLで集計するときに覚えておきたいTips（MySQL）

社内でRedashを運営しており、サービス側のDBからSQLでいろいろとKPIを抽出する作業を集中して行ったのですが、いくつか初めて触ったり覚えておきたいSQLのTipsが見つかったため、ブログにまとめます。

前提としてMySQLを使っている条件下です。

目次

• JOINの結合条件は不等号でもOK
• 日付変換Tips
  • DATE_ADD関数で特定の日付を指定 DATE_ADD(CURDATE(), INTERVAL -1 YEAR)
  • 直近1年間のデータを月次で取得したい DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 YEAR), '%Y%m') < DATE_FORMAT(created_at, '%Y%m')
• COUNT DISTINCTでユニークユーザー数を取得 count(distinct user_id)
• ユニークユーザーあたりの平均値を取得 sum(paid_amount)/count(distinct user_id)
• LEFT OUTER JOINで"○○していないユーザー"を取得
• 履歴系のテーブルで先月と比較する LEFT OUTER JOIN DATE_FORMAT(sale1.created_at, '%Y%m') = DATE_FORMAT(ADDDATE(sale2.created_at, INTERVAL 1 MONTH), '%Y%m')
• UNIONでバックアップテーブルを含めて検索 select * from hoge union select * from hoge_backup
• COUNT DISTINCT CASE式の組合せで、"特定の条件を満たすユニークユーザー数"を簡単に取得

JOINの結合条件は不等号でもOK

INNER JOINのON句はこれまで等号しか指定したことがなかったのですが、不等号を指定してもOKということがわかりました。

https://gotto50105010.hatenablog.com/entry/2019/02/17/140404

MySQL公式ドキュメントを読んでも、ON で使用される search_condition は、WHERE 句で使用できるフォームの条件式です。 通常、ON 句はテーブルの結合方法を指定する条件に使用され、WHERE 句は結果セットに含める行を制限します。と書いてありました。

https://dev.mysql.com/doc/refman/8.0/ja/join.html

不等号をどういうケースで使うかというと、一例としては、日時や月でグルーピングして、「その月以前の集計データ」を取得したいときに便利です。
日次で、その日時点での合計PV数を取得したい、といったケースにおいては、各行においてその行の日付以前のデータとJOINしてグルーピングしたいところです。そういったケースでは不等号のJOINが有効です。

日付変換Tips

DATE_ADD関数で特定の日付を指定

直近1年間の統計データがほしい、といった集計条件の際に、以下のようにDATE_ADD関数を使うことで表現できました。
DATE_ADD(CURDATE(), INTERVAL -1 YEAR)

INTERVALを使うところなど、普段使っているSQLと勝手が違う表現を用いています。INTERVALにはYEAR以外にもMONTHなど指定可能です。

• ADDDATE()とDATE_ADD()は同じ関数です
• ここではCURDATE()を使っていますが、時分まで含んだ値を返すNOW()を用いるほうが正確な場合はそちらを使います
• 同様の関数でSYSDATEがありますが、厳密にどの時点での時刻を返すかが若干異なります

直近1年間のデータを月次で取得したい

以下のように実装して実現しました。各レコードのcreated_atをFormatしてYMだけ取り出すことで、月次単位で比較しています。
DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 YEAR), '%Y%m') < DATE_FORMAT(created_at, '%Y%m')

COUNT DISTINCTでユニークユーザー数を取得

履歴系のテーブルから、ユニークユーザー数を取得したい場合などに、COUNTとDISTINCTを組合せることが有用でした。
count(distinct user_id)

ユニークユーザーあたりの平均値を取得

前節の応用で、ユニークユーザー数あたりの平均値を取得したい場合、AVG関数を使うとユニークユーザー数で取ることが難しそうだったため、対象の全レコードのSUMを取ってからユニークユーザー数で割ることで実現しました。
sum(paid_amount)/count(distinct user_id)

LEFT OUTER JOINで"○○していないユーザー"を取得

"○○していないユーザーを取得"したい場合、NOT EXIST句で副問合せすることも有効ですが、個人的にはLEFT OUTER JOINしてWHERE句でis NULLを条件に加えることで絞り込むのも好きです。

現状、好みでLEFT OUTER JOINばかり使っているため、副問合せしなければならない場面およびLEFT OUTER JOINしなければならない場面の使い分けができていないのは課題です。

履歴系のテーブルで先月と比較する

これまでのTipsの応用です。履歴系のテーブルで、先月のデータと比較したものを月次で集計したい場合は、LEFT OUTER JOINで結合条件に次月のYMにフォーマットしたものと単にフォーマットしたものの比較を指定することで可能でした。

LEFT OUTER JOIN DATE_FORMAT(history1.created_at, '%Y%m') = DATE_FORMAT(ADDDATE(history2.created_at, INTERVAL 1 MONTH), '%Y%m')

UNIONでバックアップテーブルを含めて検索

マナリンクでは一部のテーブルをバックアップテーブルを使った物理削除で運用しています。UNION句でバックアップテーブルとまるっと結合することで、集計時に「当時は存在していたが今は削除されている」ものも含んで正確な過去の値を集計できます。

select * from hoge union select * from hoge_backup

COUNT DISTINCT CASE式の組合せで、"特定の条件を満たすユニークユーザー数"を簡単に取得

以下はシンプルすぎる例なのでWHERE句でも代替できるのですが、COUNT/DISTINCT/CASE式を組合せて活用できます。CASE式でNULLを返すとカウントの対象外になるためです。

count(
    distinct
    case
        when account.is_student = true then account.user_id
        else null
    end
)

WHERE句で代替が難しいケースとして、一度に複数の条件で集計したものを出力したいケースがあります。このときはCASE式内で条件指定を行い、カウントしたい値をTHENで返すことで、最終的に集計ができます。

---

まとめ

細かいTipsを組合せることで、複雑な集計でもクエリすることができました。一方で、LEFT OUTER JOINと副問合せの使いどころなどについて、実際に記事に書こうとすると説明できるほど理解していないことが自覚できました。
今後クエリの改善を行っていく過程で色々と検証してみようと思います。