Zenn
🤒

AWS AuroraMySQL3時代のデータ分析

2023/12/05に公開
MySQL
SQL
tech

はじめに

この記事はREADYFORアドベントカレンダー2023の5日目の記事です。

https://qiita.com/advent-calendar/2023/readyfor

こんにちは！ READYFORでエンジニアをしている五十嵐です。
エンジニア業務の合間に職権を利用してデータ分析などしながら過ごしています。

背景

AuroraMySQL3（MySQL8系互換）が出てからそこそこ時も過ぎました。
AuroraMySQL2（MySQL5.7系互換）のEOLが近づいてきていることもあり、今年も多くの企業でつらく大変な移行作業が行われた、あるいは計画されているのではないでしょうか。
インフラチーム、SREチームが大変な思いをしてがんばって移行してくれるのですから、せっかくなら新機能を使おうというのが今回の趣旨です。

対象読者はMySQL系のデータベースでデータ分析をする人、したい人達です。
エンジニア向けに厳密に書くと長くなる箇所は、ある程度意図的に省略しています。

NGパターンになった書き方

最初に、以前は許されていたのにNGパターンになった書き方について言及しておきます。
デフォルト設定値を使うかにもよるのですが、MySQLの特徴だった「曖昧さ」はほとんどNGになる方向に動いています。
たとえば以下のようなものが、標準SQLとしては本来ダメで、MySQLでもエラーにされるようになるクエリです。

[NG] GROUP BYへの列記載の省略

GROUP BYを用いる時、SELECT区に記載される列名は、集計関数以外であれば、必ずGROUP BY句にも記載が必要です。
しかし古きMySQLはこの省略が可能でした。
ちなみに省略した場合どうなるか？ というと、代表値としてテキトーな値が取られます。
そしてその値は普変であることも最初の値であることも何も保証されていません…。怖いですね。

SELECT
  order.user_id,
  items.name,
  MAX(items.price)
FROM
  ~~中略~~
GROUP BY
  order.user_id

たとえば上記のようなクエリを書いてもMySQLは怒ってくれません。
一見すると、ユーザーが購入した一番高い商品を取得したい、というようなクエリに見えますが、ここで選ばれる item.name はランダムな値であり、 MAX(item.price) とは何の関係もありません。
ハマったことがある方もいるのではないでしょうか。

もちろん詳しい人向けに書くと、 ONLY_FULL_GROUP_BY の設定次第で5.7系以前でもエラーになります。
詳しくは公式のドキュメントにて

[NG] GROUP BYでの順序指定

またもやGROUP BYの話です。独自拡張が多いですね。
先ほどと同じクエリを引っ張り出しますが

SELECT
  order.user_id,
  items.name,
  MAX(items.price)
FROM
  ~~中略~~
GROUP BY
  order.user_id DESC

このように GROUP BY order.user_id DESC と書くと、なんとその順番に並んでくれていました。
ORDER BYを省略できて便利…だったんですかね？
これも怒られるようになりましたので、使っていた方は気をつけましょう。

分析が便利になる新機能

さて、本題です。

1. Window関数

Windowsではないです。単数系です。
簡単に言うと、従来のSQLでは、各結果行に属する値しか表示できなかったんですが、他の結果行の内容も含んだ値を取れるようになる便利な拡張機能です。

百聞は一見にしかず、ということで。

SELECT
  items.name,
  items.price,
  AVG(items.price) OVER (PARTITION BY items.category_id) AS 'カテゴリ平均価格',
  RANK(items.price) OVER (PARTITION BY items.category_id ORDER BY item.price) AS 'カテゴリ内価格順位'
FROM
  items
LEFT JOIN item_categories ON items.category_id = item_categories.id

実際に使いそうな例を挙げてみました。
各商品の行に、その商品カテゴリの平均価格や順位を追加でつけているクエリです。

従来であれば、各カテゴリごとの平均価格はサブクエリで集計して、各商品に結合するなどで表現する必要がありました。
順位に関してはスマートに表現する方法はあまりなかったんじゃないかと思います。spreadsheetに結果を貼り付けて関数で番号ふってましたよね。
それをすっきりとSQLだけで書くことができてます。

OVER (PARTITION BY の部分があまり見慣れない箇所ですが、ここで、各集計関数ごとに範囲を切り分けている形です。
従来は最後にGROUP BYして同一の範囲でしか集計関数が使えなかったのですが、それを個別にできるようになっています。

他にどのようなWindow関数があるかは公式ドキュメントにてご確認ください

2. WITH句

次も待望の機能だったWITH句です。
これはSQLの結果を一時テーブルとして定義して使いまわしたり、再帰処理を行ったりできる機能です。
同じ内容のサブクエリを複数回書いていた場合などは非常に楽に書けるようになります。

-- 最初に一時テーブルを定義
WITH
  category_avg AS (
    SELECT items.category_id, AVG(item.price) AS avg_price
    FROM items
    GROUP BY items.category_id
  )
-- ここからが本文のSELECT
SELECT
  item.name,
  item.price,
  category_avg.avg_price AS 'カテゴリ平均価格'
FROM
  items
LEFT JOIN category_avg ON items.category_id = category_avg.category_id

これは先ほどのWindow関数で説明したクエリを、WITH句を利用した形でそれっぽく書き換えたものです。
category_avg というのがWITH句で定義した一時テーブルで、これを通常のSQLで参照することができます。サブクエリでやるよりはすっきり見えるのではないでしょうか。

3. WITH RECURSIVE句（再帰処理）

WITH RECURSIVE という書き方をすると再帰処理が可能です。
SQL内で自分自身を参照して、多くの場合は UNION などで目的の形を作っていくことができます。

個人的によく使うのは、以下のような「月」テーブルを使い基軸にしてデータ集計する場合などがあります。

WITH RECURSIVE past_year AS (
  SELECT DATE('2023-01-01') as month
  UNION ALL 
  SELECT date(month + interval 1 month)
  FROM past_year
  WHERE month < '2024-01-01'
)
SELECT past_year.* FROM past_year

2023-01-01	
2023-02-01	
2023-03-01	
中略	
2023-11-01	
2023-12-01

月別集計の目的とするカラムに対して、月の形に整形してGROUP BYをすれば事足りるように思えますが、その月に属するレコードが1件もない場合に、月ごと欠落してしまいます。
属するレコードが存在しない場合でもその月が出るように、軸としてこうした月テーブルを置くことがあります。
従来であれば、1月12月まで愚直にUNIONしていたクエリが、WITHの再帰処理を使うと非常にすっきりと書けますね。

他に再帰が便利なのは、木構造になっているメッセージ、ユーザー同士のやりとりなどを探索して、親メッセージから綺麗に並べる時などがあります。

WITH句の公式ドキュメントはこちらです

最後に

これは言語やフレームワークのアップデートの際にも言えますが、EOLを迎えるのでイヤイヤ…というモチベーションではなく、せっかくなので新しい機能を使おうと前向きに取り組みたいですよね。
今回はそのAurora MySQL版でした。

今回紹介したWITH句やWindow関数を上手に用いると、たとえばSQLだけで機械学習のようなレコメンド機能が書けたりもします。

もちろん他にも改善点がいっぱいあって、たとえば 降順INDEX は分析系のクエリでも高速化に貢献できるかもしれません。

明日のカレンダーは日々改善に取り組んでくれている@takaherawさんです！

Discussion