テーブル作成権限がないときにSQLで擬似テーブルを作成する方法

こんにちは!
タダケン(@tadaken3)です。

データ分析をしていて、「ちょっとしたテーブルを作りたいな〜。けど、テーブルを作成する権限ないわ〜。つらたん」ってときありませんか？ ありますよね？

そんなときにWITH句を使ってちょっとしたテーブルを作成する方法をご紹介します。

標準SQLの場合

WITH mst_human_sex AS (
             SELECT 0 AS sex, '不明' AS sex_name
   UNION ALL SELECT 1 AS sex, '男性' AS sex_name
   UNION ALL SELECT 2 AS sex, '女性' AS sex_name
   UNION ALL SELECT 9 AS sex, '適用不能' AS sex_name )
SELECT *
FROM mst_human_sex;

SELECTとUNION ALLを組み合わせてテーブルを作ります。結果はこのようになります。 標準SQLに準拠しているため、多くのデータベースで動くと思います。

sex	sex_name
0	不明
1	男性
2	女性
9	適用不能

UNION ALLが比較的重たい処理なので、レコード数が多くなってくるとパフォーマンスに影響が出てくるかもです。

PostgreSQLの場合

PostgreSQLでは同様のことをVALUES句を使って実現できます。可読性も上がりますし、パフォーマンスも優れているため、出来る限りVALUSE句を使って疑似テーブルを作成したほうがよいでしょう。¹

WITH mst_human_sex (sex, sex_name) AS (
   VALUES
    (0 , '不明') 
 ,  (1 , '男性')
 ,  (2 , '女性')
 ,  (3 , '適用不能')
)
SELECT *
FROM mst_human_sex;

ちなみに、PostgreSQLの場合、VALUES句はSELECT句と等価なため、SELECTが使えるところなら、どこでも使えます。例えば、

VALUES (1, 'one'), (2, 'two'), (3, 'three') 
ORDER BY 1 desc;

とすると

COLUMN1	COLUMN2
3	three
2	two
1	one

のようにORDER BYも適応されてカラム１の降順で返ってきます。

連番を用いてテーブルを作成する

連番を生成したいときは、generate_series関数が使えます。

WITH series AS
-- 1から5までの連番を作成する
  (SELECT generate_series(1, 5) AS idx)
SELECT *
FROM series;

idx
1
2
3
4
5

まとめ

今回、テーブル作成権限がない場合にちょっとした疑似テーブルを作成する方法をお伝えしました。疑似テーブルはあくまで一時的なものなので、継続的な利用が見込まれたり、他のSQLで使用するシーンがでてきたら、きちんとテーブルとして準備するようにしましょう！

読者登録をお願いします

本ブログではプログラミングについての記事を公開しています。よろしければ読者登録していただけると更新の励みになります。ぜひ一緒にプログラミングを学びましょう。

---

1. PostgreSQLと書いていますが、SQL-99に準拠しているものはVALUESが使えます。↩