SQLをあんまり使わないけど、データ解析は得意分野という、ユゲタです。
技術的にどうやるのかというのは、企業秘密なので、細かく言えませんが、今回は仕事でSQLを使う場面があり、そのSQLが依存していたため、汎用性の高いSQLにリファクタリングしたという話です。
具体的にいうと、PostgreSQLのファミリー関数という、任意の値でランキング処理を行なって、その１位になった値のみを抜き出すという書式で、ポスグレであれば、まあまあ簡単に書けるんですが、これをLaravelに持っていこうとしてクエリビルダーで記述しようとしたら、このままでは使えないので、汎用性のあるSQL文に変更しないといけないという課題でした。
元のSQL文（サンプル）
まずは、どういったSQLだったのか、元を見てみましょう。
※（注意）企業内のコードだったので、完結に書き換えています。
SELECT *
FROM (
SELECT AP.id ArticlePost__id,
P.id post_id,
P.user_id post_user_id,
P.title post__title,
P.publish_date post__publish_date,
S.name summay_name,
S.name_e summay_name_e,
S.image summay_image
RANK() OVER (PARTITION BY P.summary_id order by P.publish_date DESC) AS post_rank
FROM posts P, summary S
WHERE P.summary_id = S.id
AND P.status = 1
AND P.publish_date < now()
AND P.summary_id IS NOT NULL
AND S.image IS NOT NULL
) AS Post
WHERE post_rank = 1
ORDER BY post_publish_date DESC
これ、一体何をやっているか分かりますか？初見でわかる人はなかなかのSQL脳ですね。
このwebサイトは、投稿記事を管理するシステムだったのですが、
登場するテーブルは２つで、postsという記事のデータを格納するテーブルと、summaryという、記事をまとめるテーブルが存在します。
記事をまとめるってどういう事かというと、連載記事などの場合は、同じposts.idを1つのsummary_idにまとめて管理するという仕様です。
ポイントは、RANK() OVERという箇所で、これは、PostgreSQLのファミリー関数という機能で、PARTITIONのカラムで絞り込んで順位をint型で作り出してくれるという機能です。
要するにこのSQLは何をやっているのかというと、「一覧でデータを取得した時に、連載ページは、最新の記事を1つのみに絞り込みたい」という内容でした。
いや〜、ややこしいけど、ありがちな要件ですね。
そして、このRANK() OVERは、Laravelのクエリビルダーには素直に変換できません。
なので、これを単純なSQL文にリファクタしてみたいと思います。
リファクタ後のSQL文
リファクタ結果は次の通りです。
SELECT
posts.id post_id,
posts.user_id post_user_id,
posts.title post__title,
posts.publish_date post__publish_date,
summay.name summay_name,
summay.name_e summay_name_e,
summay.image summay_image
FROM
posts
INNER JOIN (
SELECT *
FROM summary
) AS summary ON (
summary.id = posts.sumarry_id AND
summary.image IS NOT NULL
)
INNER JOIN (
SELECT
*,
RANK() OVER (
PARTITION BY summary_id
ORDER BY publish_date DESC
) as rank
FROM
posts
WHERE
posts.status = 1 AND
posts.publish_date < now() AND
posts.summary_id IS NOT NULL
) AS subQuery
ON (
subQuery.id = posts.id AND
subQuery.rank = 1
)
ORDER BY
posts.publish_date DESC
テーブル名をPやらSに置き換えると、逆に分かりにくかったので、そのまま使っています。
そしてリファクタリングのポイントは、2つのJOINです。
1つ目のJOINは、２つのテーブルのつなぎ込み処理で、2つ目のJOINは、RANK() OVERを内部で行なった結果をrankという結果で上位のSELECTでWHERE絞り込みできるように返しています。
結果的にSELECT３つになってしまいましたが、無事にLaravelのクエリビルダーで処理することができるようになりました。
とりあえず動くからこれでいいやという、エンジニアの特性
今回は、クエリビルダーの処理を行うことが大前提で処理を進めたんですが、そもそも、このテーブル構成でsummaryとして、別テーブルにグルーピング管理をさせている時点を解消して、１つのテーブルに単一値のグループIDを割り振ればいいだけなのに、全くもって、テーブル設計のミスをSQLでカバーしているのが、ホントにアホらしい感じでした。
まあ、仕事なので、できるようにやれることをやるだけだったのですが、初期構築時のエンジニアの本音として、「後で直すから、とりあえず・・・」という心境があります。
これは、後で直す事は、ほぼありません。
そしてそれは、重大なissueとして、サービス運営し続ける限り、足を引っ張り続けます。
ということで、初期構築時の設計がいかに重要であるか、改めて認識することができた、お仕事でした。
もちろん、そんな辛口コメントは、そのwebサービスを一生懸命構築運用している開発員達には、言わずに、そっと心にしまってありますけどね・・・
え？ブログに書いとるやないか？
その開発員の誰が、この記事を読んでも、まず分からないというぐらいに書き換えているので、この記事はテクニカル解説記事という事です。
0 件のコメント:
コメントを投稿