【PostgresSQL】縦持ちデータを横並びにする

経緯

業務で使用しているシステム(PostgresSQL) で、以下のような抽出をしたかったが断念したことがあり、方法を調べました。

やりたいこと

個人コード 資料ID
0001 11111111
0002 22222222
0001 33333333
0002 44444444
0001 55555555

上記のようなデータについて、
下記の通りに個人ごとに資料を並べたい。

個人コード 資料1 資料2 資料3
0001 11111111 33333333 55555555
0002 22222222 44444444 (null)

やったこと

以下のサイトを使うと、ブラウザ上でデータベース環境やSQLの使用可。 sqlfiddle.com

データを用意。

CREATE TABLE Siryo
 (kojin_cd  CHAR(4) NOT NULL,
 siryo_no    VARCHAR(10) NOT NULL,
 PRIMARY KEY (siryo_no));

BEGIN TRANSACTION;
INSERT INTO Siryo VALUES ('0001', '11111111');
INSERT INTO Siryo VALUES ('0002', '22222222');
INSERT INTO Siryo VALUES ('0001', '33333333');
INSERT INTO Siryo VALUES ('0002', '44444444');
INSERT INTO Siryo VALUES ('0001', '55555555');
COMMIT;

このサイトを参考にし、以下のSQLを作ってみた。 dev.classmethod.jp

select
     tmp.kojin_cd
    ,max(case tmp.seq when 1 then tmp.siryo_no else null end) as 資料1
    ,max(case tmp.seq when 2 then tmp.siryo_no else null end) as 資料2
    ,max(case tmp.seq when 3 then tmp.siryo_no else null end) as 資料3
from
(
    select
         kojin_cd
        ,siryo_no
        ,row_number() over (partition by kojin_cd) as seq
    from
       Siryo
) tmp
group by
    tmp.kojin_cd
;

すると、以下のように抽出できた。

個人コード 資料1 資料2 資料3
0001 11111111 33333333 55555555
0002 22222222 44444444 (null)

処理の流れ

まず、サブクエリ部分で、個人ごとに持っている資料に連番を振っている。

    select
         kojin_cd
        ,siryo_no
        ,row_number() over (partition by kojin_cd) as seq
    from
       Siryo
個人コード 資料ID seq
0001 11111111 1
0001 33333333 2
0001 55555555 3
0002 22222222 1
0002 44444444 2

そして、上記のサブクエリの抽出結果を使用し、 個人コードでグループ化する。 連番が各個人コードごとで、資料に1から順に振られていることを利用し、caseで連番をそれぞれ取り出し、横並びにしている。

調べたこと

row_number() とは？　

=> ウィンドウ関数の1つ。抽出順に1から始まる行番号を付ける。

ウィンドウ関数とは?

ウィンドウ関数は現在の行に何らかとも関係するテーブル行の一纏まり全般の計算を行うhttps://www.postgresql.jp/document/9.1/html/tutorial-window.html より引用) 　

=> よくわからん

「何らかの条件で並んでいるデータに対して、特定の範囲のデータのみ参照できるようにする機能が用意されており、これをWindowと呼んでいます。ちょうど窓を開けて全体の中の一部を覗くようなイメージでです。そして、このWindowに対して、最大、最小、合計、平均などの集計を行う関数が用意されており、これらのことをWindow関数と呼んでいます」 【ひたすら図で説明】一番やさしい SQL window 関数（分析関数） の使い方 | 趣味や仕事に役立つ初心者DIYプログラミング入門

=> なんとなくわかった。 そして、SUMとかMAXとかMINとか、普段使っている集計関数のこと、Window関数っていうのね...(無知)

ウィンドウ関数は OVER句と一緒に使う

OVER句の( )内には、ORDER BYか、PARTITION BYが入る。PARTITION BY は指定した項目でグループ化するもの、 ORDER BY は指定した項目でソートするもの。 ORDER BYは並び変えであり、グループ化するわけではないので、今回の用途では不向き。

    select
         kojin_cd
        ,siryo_no
        ,row_number() over (order by kojin_cd) as seq
    from
       Siryo
個人コード 資料ID seq
0001 11111111 1
0001 33333333 2
0001 55555555 3
0002 22222222 4
0002 44444444 5

Terminalの基本を知る

はじめに

こちらに取り組んでいきます。
fjord.jp

コマンド

lsコマンド

$ ls
$ ls -a   #全て表示
$ ls -l   #ファイルの詳細も表示する
$ ls -1   #リストを縦に並べる
$ ls -t   # 更新時間順に並べる
$ ls -lS #降順にして一覧表示
$ ls -lSr # 昇順にして一覧表示
$ ls -R #サブディレクトリの中も表示(ホームディレクトリだと大量に表示されるので注意）

ファイルやディレクトリの一覧を表示するコマンド。
listの略。

参考リンク
lsコマンドの使い方と覚えたい15のオプション【Linuxコマンド集】

pwdコマンド

$ pwd

現在いるディレクトリの絶対パスを表示するコマンド。
Print Working Directoryの略。

echoコマンド

$ /bin/echo helloworld
helloworld
$ hoge = 3 #変数に代入するときは半角スペースは入れない
-bash: hoge: command not found
$ word_1="pen"
$ word_2="pineapple"
$ echo word_1+word_2
word_1+word_2
$ echo $word_1$word_2 #参照するときは、$をつける　文字列の連結するにはそのまま並べる
penpineapple
$ word_3="apple"
$ echo $word_1-$word_2-$word_3-$word_1
pen-pineapple-apple-pen #古い
$ exit # 次回ターミナルを開くと、$word_? は参照できなくなっていた。一時的な変数？
(=> シェル変数）

シェル変数は、そのシェル内のみ有効。
シェル変数と環境変数の違いをコマンドラインで確認する - Qiita

（ターミナルはGUI上でCUI操作をするためのもの、シェルはOSとの橋渡しをするもの)
【初心者向け】シェル・ターミナル・コンソールの違いとは？

環境変数として定義すると、子プロセスでも有効となる
（子プロセスとは？-> 後日調べる)

cdコマンド

$ cd /bin
$ pwd
/bin

現在のワーキングディレクトリを変更するコマンド。
Change Directoryの略。

manコマンド

$ man ls

LS(1)                     BSD General Commands Manual                    LS(1)

NAME
     ls -- list directory contents

SYNOPSIS
     ls [-ABCFGHLOPRSTUW@abcdefghiklmnopqrstuwx1] [file ...]

DESCRIPTION
     For each operand that names a file of a type other than directory, ls
     displays its name as well as any requested, associated information.  For
     each operand that names a file of type directory, ls displays the names
     of files contained within that directory, as well as any requested, asso-
     ciated information.

     If no operands are given, the contents of the current directory are dis-
     played.  If more than one operand is given, non-directory operands are
     displayed first; directory and non-directory operands are sorted sepa-
     rately and in lexicographical order.

     The following options are available:
     #  以下略

マニュアル読むコマンド。

curlコマンド

$ curl -I http://abehiroshi.la.coocan.jp/
HTTP/1.1 200 OK
Date: Sun, 22 Sep 2019 01:07:39 GMT
Content-Type: text/html
Content-Length: 336
Connection: keep-alive
Last-Modified: Mon, 05 Sep 2016 05:15:36 GMT
ETag: "150-53bbbc52a6bee"
Accept-Ranges: bytes
Server: Apache

Client for URLs”の略
-I オプション ヘッダー情報を取得

openコマンド

$ open http://abehiroshi.la.coocan.jp/ #早い
$ open . # Finderでカレントディレクトリを表示

関連付けられているソフトでひらく。

saykanaコマンド, sayコマンド

$ saykana -s 10 ゆっくりしていってね！！！

SayKanaをダウンロード
https://www.a-quest.com/quickware/saykana/

$ say すいすい  すいすい  水曜日 -r 200 --interactive
すいすい すいすい 水曜日

sayコマンドはデフォルトで使える。
-rを使うと、1分間で喋る速度を指定。
--interactiveを使うと、今しゃべっているところをハイライトしてくれる。
カラオケ機能？

Homebrew

$ sudo chown -R `whoami` /usr/local
$ ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

/usr/localディレクトリの所有者を自分のユーザーに変更
su(Super User)でdo
whoami 自分のユーザー名を表示するコマンド

ruby
- e 指定されたスクリプトをRubyで実行する

curl
-f(fail) 失敗してもエラーメッセージを表示しない
-s(silent) 実行中のメッセージを表示しない
-S (show-error) 進捗は表示しないが、エラーは表示する(sと併用して使う)
-L リダイレクト設定されている URL にアクセスした時、そのリダイレクト先へ飛ぶ（デフォルトでは飛ばない）

curl option 覚え書き - Qiita

Homebrewでtreeをインストール、アンインストール

$ brew install tree
$ brew uninstall tree

treeはカレントディレクトリ下のファイル構造をツリーで表示するコマンド。

ファイルの作成

$ touch hoge.html

ディレクトリの作成

$ mkdir sample
$ mkdir -p sample/hoge  # エラーを表示せず、記述したディレクトリがなければ作成

https://eng-entrance.com/linux-command-mkdir#-p--parents

ファイルのコピー

$ cp atti kotti   # あっちファイルをこっちディレクトリにコピー
$ cp sample sample2  # sampleファイルをコピーし、sample2と命名する
$ cp -r hoge hoge2  # hogeディレクトリを丸ごとコピーし、hoge2と命名する

ファイルの移動

$ mv sample1 myhouse   #sample1ファイルをmyhouseディレクトリに移動

ファイルの削除

$ rm obutu.txt # 消毒
$ rm -r obutu # ディレクトリごと消毒
$ sudo rm -rf / # この世界ごと消毒(最近はOSが待ったを一応かけてくれるとか）

-f 削除するかどうかのメッセージを省略

ファイルを表示

$ cat /etc/hosts

猫がファイルを表示してくださるコマンド
conCATenate(連結させる、の意) で、引数に渡した２つのファイルを繋げて表示するコマンドだが、 １つだけ指定して単に中身を表示する用途で主に使われる。

shebang(シバン)

$ vim echo-hello
#!/bin/sh
echo hello
:wq
$ ls -l echo-hello
-rw-r--r--  1 mac  staff  30  9 22 11:56 echo-hello    # 所有者に読み、書き権限
$ chmod u+x echo-hello  # 所有者に実行権限を付与
$ ls -l echo-hello
-rwxr--r--  1 mac  staff  30  9 22 11:56 echo-hello
$ ./echo-hello
hello world!

#! <= shebang <= sharp(#) bang(!)

「1行目の最初の二文字が#!だったら、その後に書いてあるコマンドに2行目以降の全てを渡す」機能

$ mkdir bin
$ mv echo-hello bin/
$ export PATH=$PATH:~/bin
$ echo-hello
hello world!

export PATH=$PATH:追加したいコマンド検索パス
環境変数を設定する
~(チルダ) = $HOME

Ruby合宿2018春に参加しました

（このブログは前に書いたものを移行したものです）

 

プログラミングを学習しはじめて、3ヶ月足らず。

無謀にも島根県で開催された「Ruby合宿2018春」に参加してきました。

学生が対象ですが、25歳以下の求職者も応募できたので、ギリギリすべり込めました。

 

www.rubycamp.jp

 

 

この日は不安で死にそう。ありのままの自分を受け入れる日なのだ...。

今回のテーマはDXRuby+Arduino+各種センサーでゲームを作ろうの巻。

マイコンやセンサーの制御は、経験がありそうな人に任せよう、と思ったので、

合宿前はRubyの入門書を何回かやって、DXRubyを触ることに専念。あとは、ひたすら

写経した。ちょっぴり改造したりもした。

 

 

それと、チーム開発なので、多分Gitは必要だろうと思って手をつけた...のは良かったが、git add&git commitぐらいしか使わず、githubへ一人pushしてマージしてpullして楽しんでいるだけになってしまい、いざ本番に使うときの想定を全くしていなかった...。

 

というわけで合宿本番。

 

 

合宿はまつもとゆきひろ氏の講演から始まった。初めて拝謁、拝聴。

予想より声が高い方。哲学も絡めて早口の講演。流石にしゃべりなれている感がある。

講演では、ダメで凡庸な自分を一瞬で変える魔法はないから、地道に這い上がってこい、ただしたまに現実も見ろよ、という教訓を得ました。

 

 

合宿初日終了時。仕事を辞めてから、他人と関わる機会が減ったので、こんな感想。

周りは大学生や専門学生だから自分より年下だけど、スキル的な意味で数百年も先

輩。かなりハイレベルな人たちばかりで、来る場所間違えたな、と思った。

 

 

2日目の感想。合宿までの自分の最大の準備物「コピペコード」をひな型というか、実験台みたいな感じで採用してもらった。これを土台にゲーム作りが始まった。多分この時点で自分のできることは終わった。もちろん「コピペ」だから、たまに何でこう書いたんですか？って聞かれると詰む。あー「なれる！SE」で読んだなあ。やっちゃいけないやつですよこれ。「いや、書いた自分だけど、どういう経緯で書いたんだっけなあ」これが仕事だったら、帰れ市ねって罵倒されそう。

なれる!SE 2週間でわかる?SE入門 (電撃文庫)

なれる!SE 2週間でわかる?SE入門 (電撃文庫)

 

 

理解を伴わないコピペはダメ絶対

 

 

3日目の感想。班員でGit/Githubを使ったことがあるのは私しかいなかったのでマージ役をやりました。これまでローカルでadd/commit/pushして、Github上で1人平和にpullリクエスト/マージしか知らない人がやったので、ひどい有様になりました。（それで使ったことがある！って言える自分って...)

Twitterでも、何だそりゃ？Git禁止なのか？と突っこんでいる人がおられました。確かに何を言ってるのかわからないですね...ええ..自分でもわかってません...

事実はマージしようとしたら→コンフリクトを起こさずに上手くいった→と思ったら全部上書きされて、まるまるファイル群が置き換わっているじゃん！→じゃあ地道に一人ずつ変更をなおしていこうか...という状態。はい、何言ってるのかわからない。

こんなのがIT業界に入ったら、周囲が氏ぬ。というかそもそも入れない。今回は基礎から要勉強ということでごめんなさい！

今回の課題？arduino？センサー？全然触ってません。任せっぱなしです。（ホントなにしにきたんだ..ってとび膝蹴られるレベル)

 

 

何かもうあきらめてる...しじみ汁のお絵かき（有料素材しかなかったので）頑張りました。コードは...まあちょっと書いただけです。

f:id:KoDoKuNa77:20180312221559p:plain

島根に来られたら是非しじみ汁を食べてください。今回の合宿の食事では出ませんでしたけど。

 

 

他のメンバーが優秀だったおかげで、徹夜作業にならなくてよかった。

 

 

結論: 自分以外のメンバーが優秀すぎる、というか自分がザコすぎた。

 自分は2年後の就職のために、という感じで勉強をしているが、他の参加者の方は「好きでたまらない」「もっと楽をしたい」「(Arduinoやセンサーを)もっといじりたい！」という好奇心であふれている人が多かった。そういう人にはこの先も勝てないだろうな、と思った。あと、絵が描けるひとは強い。

 

これからどうしよう：２年間、職業訓練(という名の専門学校)に通って時間を貰うので、何かしら継続して取り組んで、これをやりました！つくりました！って面接で自信をもって言いたい。

 

 

結論２:たのしいRuby(合宿のテキストでした）はあんまりたのしくない。(プログラミングガチ初心者の感想です）

 

たのしいRuby 第5版

たのしいRuby 第5版

 

 

 この本すき

プロを目指す人のためのRuby入門 言語仕様からテスト駆動開発・デバッグ技法まで (Software Design plusシリーズ)

プロを目指す人のためのRuby入門 言語仕様からテスト駆動開発・デバッグ技法まで (Software Design plusシリーズ)

 

 

参加者のみなさんおつかれさまでした。

 

おしまい