[AWS]RedShiftではgenerate_series関数が使えない

ということなので代替の方法を使用する必要がある。
下記の記事を参考にした。

 Qiita

データ分析で必要な連続した値を生成する - Qiita

https://qiita.com/onunu/items/398de78e475c385f6dc7

一日あたりのCV数を分析したいとかいう時に、CV数が少ないチャネルだったりすると、1CVも発生していない時に日付が連続せず、またそれに気づかないで分析してしまうこと...

 SQLで時系列データを日時の抜けなしで集計する（Redshift版）

http://SQLで時系列データを日時の抜けなしで集計する（Redshift版）

http://SQLで時系列データを日時の抜けなしで集計する（Redshift版）

公式ドキュメントも参考にした。

 docs.aws.amazon.com

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_Examples_of_WF_ROW_NUM...

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_Examples_of_WF_ROW_NUMBER_WF.html

ROW_NUMBER ウィンドウ関数の使用方法の例を示します。

列持ちデータを行持ちにする

過去に書いてた下記の記事のテーブルを使用する。

 yyuuiikk blog

[PostgreSQL]列持ちデータと行持ちデータの変換

https://yyuuiikk.org/entry/590

業務でデータ分析用途でSQLを書く必要があり、表題のような変換が必要になった。それぞれのデータへの変換方法をメモする。なお、使用したDBMSはPostgreSQL 9.6。Postgr...

テーブルの構成は下記のような感じ。

create database testdb;
create table recommend_item (item_id int PRIMARY KEY, recommend_item_1 int, recommend_item_2 int, recommend_item_3 int, recommend_item_4 int);

まずPostgreSQLのgenerate_series()を使用するやり方。下記のように連番のテーブルを作成することで列持ちデータを行データに変換できる。

SELECT
    *
FROM
(
    SELECT
        item_id,
        CASE p.id
            WHEN 1 THEN r.recommend_item_1
            WHEN 2 THEN r.recommend_item_2
            WHEN 3 THEN r.recommend_item_3
            WHEN 4 THEN r.recommend_item_4
        END AS r_item_id
    FROM recommend_item AS r
    CROSS JOIN (
        SELECT generate_series(1, 4) AS id
    ) p
) sub

item_id | r_item_id
---------+-----------
       1 |         8
       1 |      3085
       1 |      3241
       1 |      3535
       2 |      1186
       2 |      2547
       2 |      6764
       2 |      6959
       3 |       716
       3 |      3546
       3 |      5804
       3 |      8174
       4 |      2563
       4 |      6874
       4 |      7527
       4 |      8290
       5 |      2124
       5 |      2690
       5 |      3318
       5 |      7319

代替の関数としてROW_NUMBER()を使用する。

SELECT
    *   
FROM
(
    SELECT
        item_id,
        CASE p.id
            WHEN 1 THEN r.recommend_item_1
            WHEN 2 THEN r.recommend_item_2
            WHEN 3 THEN r.recommend_item_3
            WHEN 4 THEN r.recommend_item_4
        END AS r_item_id
    FROM recommend_item AS r
    CROSS JOIN (
        SELECT
            ROW_NUMBER() OVER (ORDER BY true) AS id
        FROM recommend_item
        LIMIT 4
    ) p     
) sub

ということで変換ができた。