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

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

ただいまの
回答率

90.50%

  • SQL

    2394questions

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

  • PostgreSQL

    1062questions

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

  • データベース

    700questions

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

postgresqlのgenerate_seriesについて

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 749

twin

score 5

前提・実現したいこと

以下の表があった時、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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • m6u

    2017/02/21 17:51

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

    キャンセル

回答 2

checkベストアンサー

+1

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

 テーブル "public.table_t"
    列    |             型              | 修飾語
----------+-----------------------------+--------
 id       | integer                     |
 item_cd  | text                        |
 category | text                        |
 name     | text                        |
 created  | timestamp without time zone |


WITH RECURSIVE r AS (
 SELECT * FROM (
   SELECT id,item_cd,to_char(category::int+n,'0000') category,name,created from table_t, GENERATE_SERIES(0,99) n
 ) t1 WHERE id in (1,2)
 UNION ALL
 SELECT id, item_cd,category,name,created + '1 day' FROM r WHERE created::date < '2020/03/31'
)
SELECT * FROM r;


categoryが'0100'になるまででしたので調整
insert into hoge_tbl select * from (WITH ..... SELECT * FROM r) tx where category::int<=100;

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

INSERT INTO shouhin_table
SELECT ROW_NUMBER() OVER (), item_cd, category, name, created FROM (
WITH RECURSIVE r AS (
 SELECT * FROM (
   SELECT id,item_cd,to_char(category::int+n,'0000') category,name,created from table_t, GENERATE_SERIES(0,99) n
 ) t1 WHERE id in (1,2)
 UNION ALL
 SELECT id, item_cd,category,name,created + '1 day' FROM r WHERE created::date < '2020/03/31'
)
SELECT * FROM r
) 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'以上をインサート対象外としています。

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


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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/21 19:08 編集

    ご回答頂きありがとうございます。

    私には難解なSQL文だったため、とりあえずそのままSQL文を実行したところ、IDの重複によりエラーとなってしまいました。
    条件にidの重複は許さない旨を追記いたしましたので、ご確認頂けますでしょうか。
    引き続き上記SQL文の理解に努めます。

    【追記】
    select文を実行したところ、実行結果は、idの重複以外はすべて想定通りの結果となっておりました。現在idを全てユニークな値にする方法を模索中ですが、ご助言を頂けたら幸いです。

    キャンセル

  • 2017/02/21 20:12 編集

    迅速なご回答ありがとうございました。
    何とか目的としていることが実現できましたので、ご報告申し上げると共に、ベストアンサーとさせて頂きました。

    以下は蛇足となりますので、暇があればご回答頂けると幸いです。

     ①当該テーブルに元々入っているデータの行数が分からない場合で、レコードの先頭から最大行までをコピーの対象としたい場合、どのように改造すれば良いか?
     ⇒現状はコピー元となるidを明示的に指定しているため、上記のようなことを実現したい場合には、都度コピー範囲を変更する必要があるかと思います。より汎用性を持たせた実装にする場合、どのような案が考えられるでしょうか?

     ②idをユニークな値とするための方法として、「ROW_NUMBER() OVER ()」以外の方法はあるか?
     ⇒現状は当該テーブルにid 1,2 が存在しているため、「ROW_NUMBER() OVER () + 2」と書かなければ、id重複によりエラーが発生します。この値も明示的に指定していると汎用性が薄れると思うので、他に良い方法があればご教示頂けたら幸いです。

    繰り返しになりますが、上記は蛇足ですので、暇があればご回答して頂く程度で結構です。
    この度は迅速な回答により問題解決にご協力頂き、誠にありがとうございました。

    私ももっとSQL文に詳しくなれるように精進いたします。

    キャンセル

  • 2017/02/23 15:16

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

    キャンセル

0

1. 日付の増加の検討

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

  • GENERATE_SERIESを使って2016/11/01から2020/03/31まで増やすためには、1246日分のレコードが必要。
select
        GENERATE_SERIES(0, 1246) as days_counter
    ,    SAMPLE_TABLE.id
    ,    SAMPLE_TABLE.item_cd
    ,    SAMPLE_TABLE.category
    ,    SAMPLE_TABLE.name
    ,    SAMPLE_TABLE.created
from
(
    select
            1 as id
        ,    CAST('0000000001' as varchar) as item_cd
        ,    CAST('0001' as varchar) as category
        ,    CAST('商品1' as varchar) as name
        ,    CAST('2016-11-01 00:42:30.138823' as timestamp) as created
    union all
    select
            2 as id
        ,    CAST('0000000002' as varchar) as item_cd
        ,    CAST('0001' as varchar) as category
        ,    CAST('商品2' as varchar) as name
        ,    CAST('2016-11-01 00:42:30.147692' as timestamp) as created
) AS SAMPLE_TABLE
order 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までのレコードを作成する
select
        SAMPLE_TABLE_2.id
    ,    SAMPLE_TABLE_2.item_cd
    ,    SAMPLE_TABLE_2.category
    ,    SAMPLE_TABLE_2.name
    ,    (SAMPLE_TABLE_2.created + CAST(SAMPLE_TABLE_2.days_counter || ' days' AS interval)) as created    
from
(
    select
            GENERATE_SERIES(0, 1246) as days_counter
        ,    SAMPLE_TABLE.id
        ,    SAMPLE_TABLE.item_cd
        ,    SAMPLE_TABLE.category
        ,    SAMPLE_TABLE.name
        ,    SAMPLE_TABLE.created
    from
    (
        select
                1 as id
            ,    CAST('0000000001' as varchar) as item_cd
            ,    CAST('0001' as varchar) as category
            ,    CAST('商品1' as varchar) as name
            ,    CAST('2016-11-01 00:42:30.138823' as timestamp) as created
        union all
        select
                2 as id
            ,    CAST('0000000002' as varchar) as item_cd
            ,    CAST('0001' as varchar) as category
            ,    CAST('商品2' as varchar) as name
            ,    CAST('2016-11-01 00:42:30.147692' as timestamp) as created
    ) AS SAMPLE_TABLE
) AS SAMPLE_TABLE_2
order 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"にフォーマットする。
select
        SAMPLE_TABLE_3.id
    ,    SAMPLE_TABLE_3.item_cd
    ,    trim(to_char(to_number(SAMPLE_TABLE_3.category, '9999') + GENERATE_SERIES(0, 99), '0000')) as category
    ,    SAMPLE_TABLE_3.name
    ,    SAMPLE_TABLE_3.created    
from
(
    select
            SAMPLE_TABLE_2.id
        ,    SAMPLE_TABLE_2.item_cd
        ,    SAMPLE_TABLE_2.category
        ,    SAMPLE_TABLE_2.name
        ,    (SAMPLE_TABLE_2.created + CAST(SAMPLE_TABLE_2.days_counter || ' days' AS interval)) as created    
    from
    (
        select
                GENERATE_SERIES(0, 1246) as days_counter
            ,    SAMPLE_TABLE.id
            ,    SAMPLE_TABLE.item_cd
            ,    SAMPLE_TABLE.category
            ,    SAMPLE_TABLE.name
            ,    SAMPLE_TABLE.created
        from
        (
            select
                    1 as id
                ,    CAST('0000000001' as varchar) as item_cd
                ,    CAST('0001' as varchar) as category
                ,    CAST('商品1' as varchar) as name
                ,    CAST('2016-11-01 00:42:30.138823' as timestamp) as created
            union all
            select
                    2 as id
                ,    CAST('0000000002' as varchar) as item_cd
                ,    CAST('0001' as varchar) as category
                ,    CAST('商品2' as varchar) as name
                ,    CAST('2016-11-01 00:42:30.147692' as timestamp) as created
        ) AS SAMPLE_TABLE
    ) AS SAMPLE_TABLE_2
) as SAMPLE_TABLE_3


で、完成

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 19:10

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

    まだ全容を理解できていないため、引き続き理解に努めます。

    キャンセル

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

  • ただいまの回答率 90.50%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • SQL

    2394questions

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

  • PostgreSQL

    1062questions

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

  • データベース

    700questions

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