CodeIgniter,PostgreSQL,jQuery現場で学んだこと12月20日①【週末のアウトプット】

はじめに

業務でわからないことなどを週末で調べまとめて
アウトプットするコーナーです。

自分のためのアウトプットなのでわかりづらかったりしたらすみません。
コメントいただけたら自分の解釈を答えますし、間違ってたら教えていただきたいです。

早速本題！！

取得したデータを指定の桁数まで「0」埋めする PHP,SQL

取得したデータが例えば「10」だったとしたとき、
「00010」のように先頭に足りない分だけ0を足す方法。

・PHP

str_pad($data, 5, '0', STR_PAD_LEFT);

第一引数・・・対象データ　今回は10の想定
第二引数・・・桁数の指定　今回は5桁
第三引数・・・足りない桁数を何で埋めるか？　今回は0で埋める
第四引数・・・足りない分どこから0で埋めるか？　今回は先頭（左）からを指定

・SQL(postgreSQL)

SELECT TO_CHAR(CAST(column_name AS INTEGER), 'FM00000') AS formatted_value
FROM table_name;

①対象のカラムをCASTの第一引数に入れ数値型に変更（文字列の場合エラーになるのであった方が無難）
②TO＿CHAR(指定のカラム,変換型)のようにすることで第二引数で指定した形に変換される。

※FMは先頭の空白を削除するもので、これがないと空白が先頭に入ってしまう場合がある。
FM00000とすることで先頭の空白を削除して5桁のデータになるように0で埋めるようになる。
他にもFM9,999.99のような指定をすれば先頭の空白を削除、3桁ごとに,をつける、小数点第二位まで表示と変換できる。

FM9,999.99 についてはまだ使ったことないです。
0の場合は足りない桁を0で埋めるでしたが9の場合は空白が入るようです。
ただ先頭にFMがあるので空白は消えるはずです。

取得したデータの必要な部分だけを抜き取る　PHP

取得したデータが例えば00010だったとしたとき、10のように必要な部分だけを取り出す方法。

$data_2 = substr($data, -2);
//　もしくは
$data_2 = substr($data, 3, 2);

第一引数・・・対象のデータ　今回は00010
第二引数（負）・・・後ろから何桁とるか　今回は-2で末尾から2桁
もしくは
第二引数（正）・・・何桁目からとるか？ 注意　※0から始める
第三引数（正）・・・長さ　今回2なので　2桁取得

ただ今回の場合0が先頭に来ているだけなので数値型に直せば解決できます。
※00010の文字列を数値型にすると10になるため

$data = (int) $data;

登録したいTBの主キーを取得してMAX値より連番でUpdate Selectで登録できるように整える方法

想定
登録管理用のテーブルと
処理用のテーブル（いったんデータをコピーして更新処理などデータ加工する用）がある。

処理用のテーブルから登録用テーブルに登録する前に主キーを更新しないと登録できないので
重複エラーになってしまうのでその準備を行う。

Update 処理用テーブル t1
Set primaryKey = TO_CHAR(
    (
        Select Coalesce(Max(Cast(primaryKey As Int)),0)
        From 登録管理用のテーブル
    ) + (
        Select seq From (
            Select primaryKey, 
                   Row_number() Over (Order By primaryKey) As seq 
            From 処理用テーブル t2
            Where t1.primaryKey = t2.primaryKey
    )
,'FM0000000000')

Update 処理用テーブル t1
Set primaryKey =　ここまではわかると思うので説明割愛

まず

    (
        Select Coalesce(Max(Cast(primaryKey As Int)),0)
        From 登録管理用のテーブル
    )

↑　①登録したいテーブルの主キーの最大値を取得する。
データがない場合には0と取得できる。

(
        Select seq From (
            Select primaryKey, 
                   Row_number() Over (Order By primaryKey) As seq 
            From 処理用テーブル t2
            Where t1.primaryKey = t2.primaryKey
    )

↑　②ここが難しい。
Row_number()・・・行番号を振る関数
Over (Order By primaryKey)・・・primaryKeyを元に昇順で並び替え

この二つを合わせることで
primaryKeyを小さい順で並び変えて上から連番を振る。連番の名前をseqとする。
Where句で対象のデータを特定

TO_CHAR(
    (
        ①　// 登録したい主キーのMAX値
    ) + (
        ②　// １からデータの数だけ採番した配列
    )
,'FM0000000000')

↑　③主キーのMAX＋採番という結果になるので次に
現在の主キーのMAXが100だった場合
100 + 1
100 + 2　
100 + 3・・・のように重複しないような値を数値型で作ることができる。

その数値型の値をFM0000000000型に直してSet句で更新をかける。

SQL慣れてない自分からするとかなりややこしかったが理解して感動しました。