[SQL] PostgreSQLやOracleなどのファミリー関数をJOINで置き換える方法

2022年4月4日

テクノロジー

eyecatch SQLをあんまり使わないけど、データ解析は得意分野という、ユゲタです。 技術的にどうやるのかというのは、企業秘密なので、細かく言えませんが、今回は仕事でSQLを使う場面があり、そのSQLが依存していたため、汎用性の高いSQLにリファクタリングしたという話です。 具体的にいうと、PostgreSQLのファミリー関数という、任意の値でランキング処理を行なって、その1位になった値のみを抜き出すという書式で、ポスグレであれば、まあまあ簡単に書けるんですが、これを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サイトは、投稿記事を管理するシステムだったのですが、 登場するテーブルは2つで、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つのテーブルのつなぎ込み処理で、2つ目のJOINは、RANK() OVERを内部で行なった結果をrankという結果で上位のSELECTでWHERE絞り込みできるように返しています。 結果的にSELECT3つになってしまいましたが、無事にLaravelのクエリビルダーで処理することができるようになりました。

とりあえず動くからこれでいいやという、エンジニアの特性

今回は、クエリビルダーの処理を行うことが大前提で処理を進めたんですが、そもそも、このテーブル構成でsummaryとして、別テーブルにグルーピング管理をさせている時点を解消して、1つのテーブルに単一値のグループIDを割り振ればいいだけなのに、全くもって、テーブル設計のミスをSQLでカバーしているのが、ホントにアホらしい感じでした。 まあ、仕事なので、できるようにやれることをやるだけだったのですが、初期構築時のエンジニアの本音として、「後で直すから、とりあえず・・・」という心境があります。 これは、後で直す事は、ほぼありません。 そしてそれは、重大なissueとして、サービス運営し続ける限り、足を引っ張り続けます。 ということで、初期構築時の設計がいかに重要であるか、改めて認識することができた、お仕事でした。 もちろん、そんな辛口コメントは、そのwebサービスを一生懸命構築運用している開発員達には、言わずに、そっと心にしまってありますけどね・・・ え?ブログに書いとるやないか? その開発員の誰が、この記事を読んでも、まず分からないというぐらいに書き換えているので、この記事はテクニカル解説記事という事です。

このブログを検索

ごあいさつ

このWebサイトは、独自思考で我が道を行くユゲタの少し尖った思考のTechブログです。 毎日興味がどんどん切り替わるので、テーマはマルチになっています。 もしかしたらアイデアに困っている人の助けになるかもしれません。