Q&A
《日付のみを１日インクリメント》する日付はどれですか？ createdでしょうか？ 時刻成分はどう扱いますか？ categoryは0001から0100まで総当りでやるのでしょうか？
###前提・実現したいこと
以下の表があった時、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
回答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;
一度には無理そうなのでまずは＋２しなくても良い２個以上対応バージョンです。
・前提
shouhin_tableには数個の商品が有り、IDが並んでいるを前提としています。さらにshouhin_table自身に展開します。
・概要
shouhin_tableに有るレコードを100倍して、更に日付倍しています。
RECURSIVEを使いcreated日付が指定日になるまでループし追加しています。
GENERATE_SERIES(0,99)によりcategoryの１～１００を作成しています。
インサート前に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
総合スコア4070
ご回答頂きありがとうございます。
私には難解なSQL文だったため、とりあえずそのままSQL文を実行したところ、IDの重複によりエラーとなってしまいました。
条件にidの重複は許さない旨を追記いたしましたので、ご確認頂けますでしょうか。
引き続き上記SQL文の理解に努めます。
【追記】
select文を実行したところ、実行結果は、idの重複以外はすべて想定通りの結果となっておりました。現在idを全てユニークな値にする方法を模索中ですが、ご助言を頂けたら幸いです。
迅速なご回答ありがとうございました。
何とか目的としていることが実現できましたので、ご報告申し上げると共に、ベストアンサーとさせて頂きました。
以下は蛇足となりますので、暇があればご回答頂けると幸いです。
①当該テーブルに元々入っているデータの行数が分からない場合で、レコードの先頭から最大行までをコピーの対象としたい場合、どのように改造すれば良いか？
⇒現状はコピー元となるidを明示的に指定しているため、上記のようなことを実現したい場合には、都度コピー範囲を変更する必要があるかと思います。より汎用性を持たせた実装にする場合、どのような案が考えられるでしょうか？
②idをユニークな値とするための方法として、「ROW_NUMBER() OVER ()」以外の方法はあるか？
⇒現状は当該テーブルにid 1,2 が存在しているため、「ROW_NUMBER() OVER () + 2」と書かなければ、id重複によりエラーが発生します。この値も明示的に指定していると汎用性が薄れると思うので、他に良い方法があればご教示頂けたら幸いです。
繰り返しになりますが、上記は蛇足ですので、暇があればご回答して頂く程度で結構です。
この度は迅速な回答により問題解決にご協力頂き、誠にありがとうございました。
私ももっとSQL文に詳しくなれるように精進いたします。
追加の質問にもご回答いただきありがとうございます。
無事目的のSQLを作成することができました。
0
１. 日付の増加の検討
①idが"1"と"2"のレコードをコピーし、日付のみを１日インクリメントして挿入する
②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になるまでレコードが増える。
** ２. 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
総合スコア135
ご回答頂きありがとうございます。
条件に書いておらず申し訳ないのですが、できるだけ一つのSQL文で処理を完了させたいです。
まだ全容を理解できていないため、引き続き理解に努めます。
あなたの回答
tips
プレビュー
