タダケンのEnjoy Tech

楽しみながらラクに成果を上げる仕組みを考える

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

f:id:tadaken3:20180508212442p:plain こんにちは!
タダケン(@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句を使って疑似テーブルを作成したほうがよいでしょう。1

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も適応されてカラム1の降順で返ってきます。

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

連番を生成したいときは、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が使えます。