❄️

Frosty Friday Week21 Basic Pivoting

Gaku Tashiro＠ちゅらデータエンジニア

2024/09/06に公開

Snowflake

frostyfriday

tech

ちょっと涼しくなってきましたね！
がく＠ちゅらデータエンジニアです。

またブログの期間が空いてしまいました・・・がんばるぞー

Frosty Friday Live Challenge Vol.10

こちら（20:47）から解説がはじまっています

Frosty Friday

問題

新しいインターン生にデータ収集を任せたのは私たちのせいかもしれませんが、SuperHero Inc. の全員が私たちが望むほどデータに精通しているわけではありません。

彼らは単に古い紙の採点シートを収集データに変換しただけだという報告を受けています。

私たちのアナリストは結果にあまり熱心ではなく、データを少し整理するように求めてきました。
現在、スーパーヒーローは 1 行に記録されており、各スーパーパワーの列には ++、+、または - がマークされています。

アナリストは、値を数えたり、値を簡単に表示したりするのに問題を抱えており、ヒーローのパワーごとに新しい行を作成できるかどうか尋ねてきました。
インターン生が作成したものは以下のようになります。
新しいインターン生にデータ収集を任せたのは私たちのせいかもしれませんが、SuperHero Inc. の全員が私たちが望むほどデータに精通しているわけではありません。

彼らは単に古い紙の採点シートを収集データに変換しただけだという報告を受けています。

私たちのアナリストは結果にあまり熱心ではなく、データを少し整理するように求めてきました。
現在、スーパーヒーローは 1 行に記録されており、各スーパーパワーの列には ++、+、または - がマークされています。

アナリストは、値を数えたり、値を簡単に表示したりするのに問題を抱えており、ヒーローのパワーごとに新しい行を作成できるかどうか尋ねてきました。
インターン生が作成したものは以下のようになります。
ご覧のとおり、すべてのヒーローには 2 つのパワーがあり、メイン パワーは「++」、セカンダリ パワーは「+」と表記されます。
アナリストが求めているのは次の結果です。
今週の課題：
各ヒーローが1行になるようにデータを変換する
++のパワーをmain_power列に入力します
二次パワー列に+パワーを入れます

Starging Data

create or replace table hero_powers (
hero_name VARCHAR(50),
flight VARCHAR(50),
laser_eyes VARCHAR(50),
invisibility VARCHAR(50),
invincibility VARCHAR(50),
psychic VARCHAR(50),
magic VARCHAR(50),
super_speed VARCHAR(50),
super_strength VARCHAR(50)
);
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Impossible Guard', '++', '-', '-', '-', '-', '-', '-', '+');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Clever Daggers', '-', '+', '-', '-', '-', '-', '-', '++');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Quick Jackal', '+', '-', '++', '-', '-', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('The Steel Spy', '-', '++', '-', '-', '+', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Agent Thundering Sage', '++', '+', '-', '-', '-', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Mister Unarmed Genius', '-', '-', '-', '-', '-', '-', '-', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Doctor Galactic Spectacle', '-', '-', '-', '++', '-', '-', '-', '+');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Master Rapid Illusionist', '-', '-', '-', '-', '++', '-', '+', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Galactic Gargoyle', '+', '-', '-', '-', '-', '-', '++', '-');
insert into hero_powers (hero_name, flight, laser_eyes, invisibility, invincibility, psychic, magic, super_speed, super_strength) values ('Alley Cat', '-', '++', '-', '-', '-', '-', '-', '+');

私の解法解説

今回は

unpivot + pivot
unpivot + dynamic pivot
dynamic unpivot ( unofficial function) + dynamic pivot
の3つの解法で解いています

処理の動きとしては、

元データを取り込む
unpivotして縦持ち、「-」をフィルタリングして、不要な必殺技を外す。各ヒーロ2レコード持ち
pivotして整形

と大きく分けて3段階の処理になります

unpivot + pivot

こちらでは最も基本的なunpivot と pivot を使って解いていきます。

まず最初に元データを確認しましょう

select * from hero_powers;

unpivot

次に、unpivotして、データを縦持ちにします。
一旦、その部分だけを確認しています。

select *
  from hero_powers
    unpivot (
      powers for power in (
          flight
        , laser_eyes
        , invisibility
        , invincibility
        , psychic
        , magic
        , super_speed
        , super_strength
      )
    );

ここで気になるのは、flight, laser_eyes・・・・とテーブルを見て、カラム名を手動で列挙しているところです。
もしこのフィールドが1000とかあればそれをすべてSQLに記載・転記するのって・・・・キツイですよね！？
なので、後の解法で、極力、必殺技名をSQL内でハードコーディングをしない方向での解法を行います

filtering

先程のunpivotの部分を、with句で記載。CTEとします。
この部分では2つの処理を行っています。

++ を MAIN_POWERへ、+をSECONDARY_POWERに書き換え
power_levelが - のものを弾く

pivoting ( final )

select 
    hero_name
    , "'MAIN_POWER'" as main_power
    , "'SECONDARY_POWER'" as secondary_power
from
    filtered
    pivot (
        min(power) for categorized_power_level in (
            'MAIN_POWER'
            , 'SECONDARY_POWER'
        )
    )

ここで注意するのは、pivotをする際に、MAIN_POWERは「'MAIN_POWER'」、SECONDARY_POWERが「'SECONDARY_POWER'」と余計なもの（'）が入っていて、気持ち悪いのでこれを除いています。
"'MAIN_POWER'" とダブルコーテーションで囲まないとうまくいきません

pivot (
  min(power) for categorized_power_level in (

組み込みの集計関数AVG、 COUNT、MAX、 MIN、SUMにしなければならないので、
min(power）にしています。
今回においては、max(power）でも良いと思います

unpivot + dynamic pivot

こちらの解法は unpivot, filteringの部分は解法①と同じです。

select * from filtered
    pivot ( max(power) for categorized_power_level in ( any order by categorized_power_level))
--    AS p (hero_name, main_power, secondary_power)
;

any order by categorized_power_level というところが動的Pivotです。
ANYは、ピボット列のすべての個別値に基づいてPivot
ORDER BY は、出力内のPivot列を制御しています。

※なお、更にサブクエリを付け加えて、Pivotする列をフィルタリングすることも可能！

dynamic unpivot + dynamic pivot

3つ目の解法は、dynamic unpivot （ただし、Snowflakeの公式機能ではない）です。
目玉としては、dynamic なので、必殺技名を手動でSQLにハードコーディングしない、たとえ必殺技が10000あっても問題なく行く？です（笑）

動的Unpivotについては、以下のブログ（Snowflakeの中のスーパーな方）を参考にしました

こちらの肝としては、

object_construct を用いる
カラム名を取得するには、多分object_constructしかなさそう　とのこと（上記ブログより）

データの確認として

select * from hero_powers;
select object_construct(*) from hero_powers;

こちらで、元テーブルをJSON化できました。

with queries as (
 select 
        hero_name,
        object_construct(*) line
    from 
        hero_powers
)
, unpivoting as (
    select
        queries.hero_name
        , f.*
    from queries, lateral flatten(queries.line) f
)
select * from unpivoting;

unpivotingについては、JSONを lateral flatten を用いて、縦持ちに変換しています
ここがdynamic unpivot 的に動作しています

あとの部分は同じです。

Dynamic unpivot + Dynamic Pivot ( + 関数の中に * exclude）

先程の解法では

with queries as (
 select 
        hero_name,
        object_construct(*) as line
    from 
        hero_powers
)

lineの中のJSONの中に、here_nameが入っているのが、重複して、無駄ですよね？
ですので、

with queries as (
 select 
        hero_name,
        object_construct(* exclude hero_name ) as line
    from 
        hero_powers
)

を使いました。

こちらを最終版としたいとおもいます！

with queries as (
 select 
        hero_name,
        object_construct(* exclude hero_name) line
    from 
        hero_powers
)
-- select * from  queries;
, unpivoting as (
    select
        queries.hero_name
        , f.*
    from queries, lateral flatten(queries.line) f
)
-- select * from unpivoting;
, rename_colums as (
    select 
        hero_name, 
        key as power, 
        case
            when value = '++' then 'MAIN_POWER'
            when value = '+' then 'SECONDARY_POWER'
            else null
         end as categorized_power_level 
    from 
        unpivoting
    where 
        value != '-'
    ) 
-- select * from rename_colums;
, pivoting as (
select * from rename_colums
    pivot ( max(power) for categorized_power_level in ( any order by categorized_power_level))
)
-- select * from pivoting;
, final as (
    select
        hero_name
        , "'MAIN_POWER'" as MAIN_POWER
        , "'SECONDARY_POWER'" as SECONDARY_POWER
    from
        pivoting
)
select * from final;

ちゅらデータ株式会社Publication

沖縄に「最高に面白い仕事」を作る IT ベンチャーです！エンジニア積極採用中！！