こんにちは、鈴木です。
PostgreSQL ユーザの方！
GENERATE_SERIES という関数をご存知でしょうか。
集合を返す GENERATE_SERIES 関数
GENERATE_SERIES 関数は少し変わっていて、集合を返す関数です。
動作を見ていただいた方が分かりやすいかもしれません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# SELECT GENERATE_SERIES(1, 10); generate_series ----------------- 1 2 3 4 5 6 7 8 9 10 (10 rows) |
このように GENERATE_SERIES 関数は連続値を生成します。
第三引数を指定することで、刻み幅を指定することもできます。
|
1 2 3 4 5 6 7 8 9 |
# SELECT GENERATE_SERIES(1, 10, 2); generate_series ----------------- 1 3 5 7 9 (5 rows) |
適当な演算を行うことで、連続する日付を生成することもできます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# SELECT '2000-01-01'::DATE + GENERATE_SERIES(0, 9); ?column? ------------ 2000-01-01 2000-01-02 2000-01-03 2000-01-04 2000-01-05 2000-01-06 2000-01-07 2000-01-08 2000-01-09 2000-01-10 (10 rows) |
乱数を 1000 個！ という場合はこうします。
|
1 2 3 4 5 6 7 8 9 10 11 |
# SELECT RANDOM() FROM GENERATE_SERIES(1, 1000); random ---------------------- 0.0211320826783776 0.0161707666702569 0.944578718394041 ... 0.965048603247851 0.417635472025722 0.855229950975627 (1000 rows) |
テスト用の大量データを生成する
GENERATE_SERIES 関数を使うと、テストに使用する大量データを手軽に生成することができます。
例として、売上情報を保持する sales テーブルがあるとします。
|
1 2 3 4 5 6 7 8 9 |
-- 売上テーブル. CREATE TABLE sales ( -- 売上日. sold_on DATE NOT NULL, -- 売上金額. amount INTEGER NOT NULL ); |
この sales テーブルに大量データを登録したいのですが、どのような SQL を書けば良いでしょうか。
段階的に考えていきましょう。
まず、売上日と売上金額は固定のデータを生成する SQL を考えると、以下のようになりますね。
|
1 2 3 4 5 6 7 8 9 |
SELECT -- とりあえず固定の値 '2000-01-01'::DATE AS sold_on, -- とりあえず固定の値 100 AS amount FROM GENERATE_SERIES(1, 10) ; |
実行すると、以下の出力が得られます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
sold_on | amount ------------+-------- 2000-01-01 | 100 2000-01-01 | 100 2000-01-01 | 100 2000-01-01 | 100 2000-01-01 | 100 2000-01-01 | 100 2000-01-01 | 100 2000-01-01 | 100 2000-01-01 | 100 2000-01-01 | 100 (10 rows) |
それでは、売上日と売上金額を乱数で決めることにしましょう。
以下のような SQL になるでしょうか。
|
1 2 3 4 5 6 7 8 |
SELECT '2000-01-01'::DATE + (RANDOM() * 31)::INTEGER AS sold_on, (RANDOM() * 1000)::INTEGER + 1000 AS amount FROM GENERATE_SERIES(1, 10) ORDER BY sold_on ; |
実行してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
sold_on | amount ------------+-------- 2000-01-02 | 1635 2000-01-05 | 1226 2000-01-05 | 1827 2000-01-11 | 1311 2000-01-17 | 1534 2000-01-22 | 1482 2000-01-25 | 1003 2000-01-26 | 1903 2000-01-27 | 1441 2000-01-30 | 1059 (10 rows) |
良さそうな値が生成出来ましたので、それを sales テーブルに INSERT します。
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO sales (sold_on, amount) SELECT '2000-01-01'::DATE + (RANDOM() * 31)::INTEGER AS sold_on, (RANDOM() * 1000)::INTEGER + 1000 AS amount FROM -- 100 件生成するように変更 GENERATE_SERIES(1, 100) ORDER BY sold_on ; |
実行すると、sales テーブルに 100 件のレコードが登録されます。
|
1 |
INSERT 0 100 |
sales テーブルを SELECT すると、確かに 100 件のレコードが登録されていることが確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 |
# SELECT * FROM sales; sold_on | amount ------------+-------- 2000-01-02 | 1777 2000-01-02 | 1067 2000-01-02 | 1451 ... 2000-01-30 | 1661 2000-01-30 | 1419 2000-01-31 | 1583 (100 rows) |
良さそうな感じです。
日別の売上金額を求める
せっかく sales テーブルに大量データを登録したので、日別の売上金額を求めてみましょう。
日別の売上金額を求める SQL はこんな感じになるでしょうか。。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT sold_on, SUM(amount) FROM sales GROUP BY sold_on ORDER BY sold_on ; |
実行してみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
sold_on | sum ------------+------- 2000-01-02 | 10122 2000-01-03 | 4419 2000-01-04 | 7233 2000-01-05 | 4708 2000-01-06 | 2579 2000-01-07 | 1632 2000-01-08 | 2811 2000-01-09 | 4669 2000-01-10 | 5631 2000-01-11 | 4732 2000-01-12 | 1213 2000-01-13 | 3990 2000-01-14 | 3881 2000-01-15 | 6823 2000-01-16 | 10128 2000-01-17 | 7330 2000-01-19 | 8152 2000-01-20 | 1981 2000-01-21 | 8249 2000-01-22 | 4629 2000-01-23 | 6796 2000-01-25 | 2210 2000-01-26 | 6969 2000-01-27 | 15518 2000-01-28 | 5734 2000-01-29 | 4212 2000-01-30 | 4520 2000-01-31 | 1583 (28 rows) |
日付に抜けが出てしまいました・・。
売上が無い日がある場合も考慮しておかなければなりませんでした。
GENERATE_SERIES 関数を用いて、期待する結果が得られるような SQL に書き直すと次のようになります。
|
1 2 3 4 5 6 |
SELECT dates.day, (SELECT SUM(amount) FROM sales WHERE sold_on=dates.day) AS amount FROM (SELECT '2000-01-01'::DATE + GENERATE_SERIES(0, 30) AS day) AS dates ; |
実行します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
day | amount ------------+-------- 2000-01-01 | 2000-01-02 | 10122 2000-01-03 | 4419 2000-01-04 | 7233 2000-01-05 | 4708 2000-01-06 | 2579 2000-01-07 | 1632 2000-01-08 | 2811 2000-01-09 | 4669 2000-01-10 | 5631 2000-01-11 | 4732 2000-01-12 | 1213 2000-01-13 | 3990 2000-01-14 | 3881 2000-01-15 | 6823 2000-01-16 | 10128 2000-01-17 | 7330 2000-01-18 | 2000-01-19 | 8152 2000-01-20 | 1981 2000-01-21 | 8249 2000-01-22 | 4629 2000-01-23 | 6796 2000-01-24 | 2000-01-25 | 2210 2000-01-26 | 6969 2000-01-27 | 15518 2000-01-28 | 5734 2000-01-29 | 4212 2000-01-30 | 4520 2000-01-31 | 1583 (31 rows) |
期待する結果が得られました！
まとめ
今回は PostgreSQL の GENERATE_SERIES 関数をピックアップしてご紹介しましたが、あることを達成する SQL の書き方はひと通りとは限りません。こんなやり方もあるんだ、と思っていただければ幸いです。
