質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

Q&A

解決済

2回答

3704閲覧

postgresqlのgenerate_seriesについて

twin

総合スコア13

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

0グッド

2クリップ

投稿2017/02/21 06:51

編集2017/02/21 10:06

###前提・実現したいこと
以下の表があった時、generate_seriesを使って、下記を実現したいです。
(他の方法があれば、そちらでも良い)

①idが"1"と"2"のレコードをコピーし、idとcreatedをインクリメントしてshouhin_tableに挿入する
※idは重複しないようにする(オートインクリメントではないため、省略不可)。createdはYYYY-MM-DDのみ変動。時間についてはコピー元の値を使用する
②createdのデータが2020/03/31になるまで①を繰り返し実施する。
③上記で作成したデータを、categoryが"0100"になるまで繰り返し挿入する
※イメージとしては、idが1と2のレコードを、2016-11-01から2020-03-01の各日付で挿入したデータを、全てcategory"0002" "0003"……として追加して行く感じです。

分かりづらくて申し訳ないです。

generate_seriesの使い方が良く分かっていないため、色々と試行錯誤しているのですが、目的としていることが実現できないでいます。
方法をご教示頂けますと幸いです。

###例

テーブル名:shouhin_table id | item_cd | category | name | created ----+-------------+----------+--------------+---------------------- 1 | 0000000001 | 0001 | 商品1 | 2016-11-01 00:42:30.138823 2 | 0000000002 | 0001 | 商品2 | 2016-11-01 00:42:30.147692 (2 行)

###補足情報(言語/FW/ツール等のバージョンなど)
postgreSQL 9.3

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2017/02/21 08:51

《日付のみを1日インクリメント》する日付はどれですか? createdでしょうか? 時刻成分はどう扱いますか? categoryは0001から0100まで総当りでやるのでしょうか?
guest

回答2

0

ベストアンサー

with区で作成してみました。

sql

1 テーブル "public.table_t" 2|| 修飾語 3----------+-----------------------------+-------- 4 id | integer | 5 item_cd | text | 6 category | text | 7 name | text | 8 created | timestamp without time zone | 9 10 11WITH RECURSIVE r AS ( 12 SELECT * FROM ( 13 SELECT id,item_cd,to_char(category::int+n,'0000') category,name,created from table_t, GENERATE_SERIES(0,99) n 14 ) t1 WHERE id in (1,2) 15 UNION ALL 16 SELECT id, item_cd,category,name,created + '1 day' FROM r WHERE created::date < '2020/03/31' 17) 18SELECT * FROM r; 19 20 21categoryが'0100'になるまででしたので調整 22insert into hoge_tbl select * from (WITH ..... SELECT * FROM r) tx where category::int<=100; 23 24

読込が足りず失礼いたしました。(変更)

sql

1INSERT INTO shouhin_table 2SELECT ROW_NUMBER() OVER (), item_cd, category, name, created FROM ( 3WITH RECURSIVE r AS ( 4 SELECT * FROM ( 5 SELECT id,item_cd,to_char(category::int+n,'0000') category,name,created from table_t, GENERATE_SERIES(0,99) n 6 ) t1 WHERE id in (1,2) 7 UNION ALL 8 SELECT id, item_cd,category,name,created + '1 day' FROM r WHERE created::date < '2020/03/31' 9) 10SELECT * FROM r 11) tx where category::int<=100;

一度には無理そうなのでまずは+2しなくても良い2個以上対応バージョンです。
・前提
shouhin_tableには数個の商品が有り、IDが並んでいるを前提としています。さらにshouhin_table自身に展開します。
・概要
shouhin_tableに有るレコードを100倍して、更に日付倍しています。
RECURSIVEを使いcreated日付が指定日になるまでループし追加しています。
GENERATE_SERIES(0,99)によりcategoryの1~100を作成しています。
インサート前にshouhin_tableを全件削除しています。
念のためcategoryが'0100'以上をインサート対象外としています。

sql

1WITH RECURSIVE r AS ( 2 SELECT * FROM ( 3 SELECT id,item_cd,to_char(category::int+n,'0000') category,name,created from shouhin_table, 4 GENERATE_SERIES(0,99) n) t1 5 UNION ALL 6 SELECT id, item_cd,category,name,created + '1 day' FROM r WHERE created::date < '2020/03/31' 7), q AS (delete from shouhin_table returning *) 8INSERT INTO shouhin_table SELECT ROW_NUMBER() OVER (), item_cd, category, name, created FROM r where category::int<=100 9;

※テーブルqを作成していますが、後でINSERT時に使う予定

投稿2017/02/21 09:06

編集2017/02/21 23:12
A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

twin

2017/02/21 10:23 編集

ご回答頂きありがとうございます。 私には難解なSQL文だったため、とりあえずそのままSQL文を実行したところ、IDの重複によりエラーとなってしまいました。 条件にidの重複は許さない旨を追記いたしましたので、ご確認頂けますでしょうか。 引き続き上記SQL文の理解に努めます。 【追記】 select文を実行したところ、実行結果は、idの重複以外はすべて想定通りの結果となっておりました。現在idを全てユニークな値にする方法を模索中ですが、ご助言を頂けたら幸いです。
twin

2017/02/21 11:15 編集

迅速なご回答ありがとうございました。 何とか目的としていることが実現できましたので、ご報告申し上げると共に、ベストアンサーとさせて頂きました。 以下は蛇足となりますので、暇があればご回答頂けると幸いです。  ①当該テーブルに元々入っているデータの行数が分からない場合で、レコードの先頭から最大行までをコピーの対象としたい場合、どのように改造すれば良いか?  ⇒現状はコピー元となるidを明示的に指定しているため、上記のようなことを実現したい場合には、都度コピー範囲を変更する必要があるかと思います。より汎用性を持たせた実装にする場合、どのような案が考えられるでしょうか?  ②idをユニークな値とするための方法として、「ROW_NUMBER() OVER ()」以外の方法はあるか?  ⇒現状は当該テーブルにid 1,2 が存在しているため、「ROW_NUMBER() OVER () + 2」と書かなければ、id重複によりエラーが発生します。この値も明示的に指定していると汎用性が薄れると思うので、他に良い方法があればご教示頂けたら幸いです。 繰り返しになりますが、上記は蛇足ですので、暇があればご回答して頂く程度で結構です。 この度は迅速な回答により問題解決にご協力頂き、誠にありがとうございました。 私ももっとSQL文に詳しくなれるように精進いたします。
twin

2017/02/23 06:16

追加の質問にもご回答いただきありがとうございます。 無事目的のSQLを作成することができました。
guest

0

1. 日付の増加の検討

①idが"1"と"2"のレコードをコピーし、日付のみを1日インクリメントして挿入する
②createdのデータが2020/03/31になるまで①を繰り返し実施する。

  • GENERATE_SERIESを使って2016/11/01から2020/03/31まで増やすためには、1246日分のレコードが必要。

SQL

1select 2 GENERATE_SERIES(0, 1246) as days_counter 3 , SAMPLE_TABLE.id 4 , SAMPLE_TABLE.item_cd 5 , SAMPLE_TABLE.category 6 , SAMPLE_TABLE.name 7 , SAMPLE_TABLE.created 8from 9( 10 select 11 1 as id 12 , CAST('0000000001' as varchar) as item_cd 13 , CAST('0001' as varchar) as category 14 , CAST('商品1' as varchar) as name 15 , CAST('2016-11-01 00:42:30.138823' as timestamp) as created 16 union all 17 select 18 2 as id 19 , CAST('0000000002' as varchar) as item_cd 20 , CAST('0001' as varchar) as category 21 , CAST('商品2' as varchar) as name 22 , CAST('2016-11-01 00:42:30.147692' as timestamp) as created 23) AS SAMPLE_TABLE 24order by days_counter, SAMPLE_TABLE.ID

で、
|days_counter|id|item_cd|category|name|created|
|:--|:--:|--:|
|0|1|"0000000001"|"0001"|"商品1"|"2016-11-01 00:42:30.138823"|
|0|2|"0000000002"|"0001"|"商品2"|"2016-11-01 00:42:30.147692"|
|1|1|"0000000001"|"0001"|"商品1"|"2016-11-01 00:42:30.138823"|
|1|2|"0000000002"|"0001"|"商品2"|"2016-11-01 00:42:30.147692"|

days_counterが1246になるまでレコードが増える。


  • days_counterを使って、2020/3/31までのレコードを作成する

SQL

1select 2 SAMPLE_TABLE_2.id 3 , SAMPLE_TABLE_2.item_cd 4 , SAMPLE_TABLE_2.category 5 , SAMPLE_TABLE_2.name 6 , (SAMPLE_TABLE_2.created + CAST(SAMPLE_TABLE_2.days_counter || ' days' AS interval)) as created 7from 8( 9 select 10 GENERATE_SERIES(0, 1246) as days_counter 11 , SAMPLE_TABLE.id 12 , SAMPLE_TABLE.item_cd 13 , SAMPLE_TABLE.category 14 , SAMPLE_TABLE.name 15 , SAMPLE_TABLE.created 16 from 17 ( 18 select 19 1 as id 20 , CAST('0000000001' as varchar) as item_cd 21 , CAST('0001' as varchar) as category 22 , CAST('商品1' as varchar) as name 23 , CAST('2016-11-01 00:42:30.138823' as timestamp) as created 24 union all 25 select 26 2 as id 27 , CAST('0000000002' as varchar) as item_cd 28 , CAST('0001' as varchar) as category 29 , CAST('商品2' as varchar) as name 30 , CAST('2016-11-01 00:42:30.147692' as timestamp) as created 31 ) AS SAMPLE_TABLE 32) AS SAMPLE_TABLE_2 33order by SAMPLE_TABLE_2.days_counter, SAMPLE_TABLE_2.id

で、
|id|item_cd|category|name|created|
|:--|:--:|--:|
|1|"0000000001"|"0001"|"商品1"|"2016-11-01 00:42:30.138823"|
|2|"0000000002"|"0001"|"商品2"|"2016-11-01 00:42:30.147692"|
|1|"0000000001"|"0001"|"商品1"|"2016-11-02 00:42:30.138823"|
|2|"0000000002"|"0001"|"商品2"|"2016-11-02 00:42:30.147692"|

createdが2020/03/31になるまでレコードが増える。


** 2. categoryの増加を検討 **

③上記で作成したデータを、categoryが"0100"になるまで繰り返し挿入する

  • 同じくGENERATE_SERIESを使ってcategoryを0001から0100に増やす。

一度categoryを数値にし、0~99を加算したのちに、"0000"にフォーマットする。

SQL

1select 2 SAMPLE_TABLE_3.id 3 , SAMPLE_TABLE_3.item_cd 4 , trim(to_char(to_number(SAMPLE_TABLE_3.category, '9999') + GENERATE_SERIES(0, 99), '0000')) as category 5 , SAMPLE_TABLE_3.name 6 , SAMPLE_TABLE_3.created 7from 8( 9 select 10 SAMPLE_TABLE_2.id 11 , SAMPLE_TABLE_2.item_cd 12 , SAMPLE_TABLE_2.category 13 , SAMPLE_TABLE_2.name 14 , (SAMPLE_TABLE_2.created + CAST(SAMPLE_TABLE_2.days_counter || ' days' AS interval)) as created 15 from 16 ( 17 select 18 GENERATE_SERIES(0, 1246) as days_counter 19 , SAMPLE_TABLE.id 20 , SAMPLE_TABLE.item_cd 21 , SAMPLE_TABLE.category 22 , SAMPLE_TABLE.name 23 , SAMPLE_TABLE.created 24 from 25 ( 26 select 27 1 as id 28 , CAST('0000000001' as varchar) as item_cd 29 , CAST('0001' as varchar) as category 30 , CAST('商品1' as varchar) as name 31 , CAST('2016-11-01 00:42:30.138823' as timestamp) as created 32 union all 33 select 34 2 as id 35 , CAST('0000000002' as varchar) as item_cd 36 , CAST('0001' as varchar) as category 37 , CAST('商品2' as varchar) as name 38 , CAST('2016-11-01 00:42:30.147692' as timestamp) as created 39 ) AS SAMPLE_TABLE 40 ) AS SAMPLE_TABLE_2 41) as SAMPLE_TABLE_3 42

で、完成
|id|item_cd|category|name|created|
|:--|:--:|--:|
|1|"0000000001"|"0001"|"商品1"|"2016-11-01 00:42:30.138823"|
|1|"0000000001"|"0002"|"商品1"|"2016-11-01 00:42:30.138823"|
|1|"0000000001"|"0003"|"商品1"|"2016-11-01 00:42:30.138823"|
|1|"0000000001"|"0004"|"商品1"|"2016-11-01 00:42:30.138823"|

categoryは"0001"から"0100"、createdは"2020/03/31"まで増加する

投稿2017/02/21 08:56

sugar_yas

総合スコア135

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

twin

2017/02/21 10:10

ご回答頂きありがとうございます。 条件に書いておらず申し訳ないのですが、できるだけ一つのSQL文で処理を完了させたいです。 まだ全容を理解できていないため、引き続き理解に努めます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問