Tech`s Report

MENU

WITH句-副問い合わせ の使い方【PostgreSQL】

サーバサイドのWEBシステムの開発を行ってますが、今までアプリケーション内でWITH構文を使用したことはありませんでした。
 
個人的に気になったので今回使用方法やどういった場面で使用するかなどをまとめてみました。
 

WITH構文とは

そもそもWITH構文はTBLの副問い合わせをよりシンプルにより便利に使用できるように埋まれた構文になります。
 
どちらかというとデータ分析など複雑なSQLを組む際に、クエリの内容をよりシンプルにできるといったメリットがあるように思えます。
   
公式doc:
https://www.postgresql.jp/document/9.1/html/queries-with.html  

 

WITH構文の使い方

構文

使用方法は下記の通りです。

f:id:TechnologyShare:20200812210605p:plain

通常の副問い合わせと何が違うかは、「名称」というWITH句による副問い合わせの結果を、下段のクエリで何度でも使用できる点です。  
 

サンプル

下記のテーブルから全会員の中から年齢の平均値以上の会員を抽出するSQLをWITH文を用いて組みたいと思います。  
 

kaiin_login_info

kaiin_no  age  sex  login_nitiji  name 
1 22 男性  20200711 田中 
2 27 男性  20190911 太田 
3 29 女性  20181221 木村 
4 17 男性  20200301 杉本 
5 30 女性  20200711 中村 
6 43 男性  20190203 大山 
7 26 男性  20200801 石橋 

 
 

平均年齢以上の会員を抽出するSQLのサンプルが以下の通りです。

WITH re_date AS (
        SELECT
            avg(age) as avg
        FROM
            kaiin_login_info
     )
SELECT
    kaiin_login_info.name,
    kaiin_login_info.age,
    re_date.avg
FROM
    re_date,kaiin_login_info
WHERE
    kaiin_login_info.age > re_date.avg
;

 

WITH句による副問い合わせにより、平均年齢を「re_date」というVIEWの中に持つイメージです。 WITH句のみの結果は以下の通りです。

avg 
27.7142857142857143

 
そしてサンプルのクエリのように、SQLのFROM句に「re_date」を指定することで、WHERE句の条件などでも、WITH句で算出した年齢の平均値を出力することができます。

 

実行結果は以下の通りになります。

name  age  avg 
木村  29 27.7142857142857143
中村  30 27.7142857142857143
大山  43 27.7142857142857143

 
WITH句で算出した、平均年齢以上の会員のみの情報が抽出できましたね。

 

WITH句の良い点・メリット

今回使用して感じたのはWITH句を使用するとSQLがすっきりする点です。
 

ただ、私自身はあまり使用したことないので、可読性が高いかと言われればピンとこないところもあります。(正直、今回サンプルで組んだくらいのSQLであれば、普通に副問い合わせを行った方が慣れてるし読みやすい・・・)

 
ただ、もっと複雑の副問い合わせを行うクエリなどの場合は、WITH句を使用するメリットは十分にあると思います。

 

以上です。