axjack's blog

### axjack is said to be an abbreviation for An eXistent JApanese Cool Klutz ###

固定長ファイルをテキストエディタで分割する

メモ プログラミング

はじめに

3月もあと10日ほどで終わりですね。

本日は、COBOLの時代からひっそりと伝わる固定長ファイル分割術を、記憶が薄れる前にここに書き留めておこうという趣旨の記事です。

で、Linux環境だったら伝家の宝刀ddコマンドを駆使するわけですが、Windows環境となるとそのような手軽なツールは準備されていませんね。ということで、テキストエディタでグリッとこなす技を記載します。

分割ってどういうこと？

こんな感じの一行ファイルを、

1HHHHHHHHHHHHHHHHHHHHHHHHHHHHH21DDDDDDDDDDDDDDDDDDDDDDDDDDDD22DDDDDDDDDDDDDDDDDDDDDDDDDDDD23DDDDDDDDDDDDDDDDDDDDDDDDDDDD24DDDDDDDDDDDDDDDDDDDDDDDDDDDD25DDDDDDDDDDDDDDDDDDDDDDDDDDDD26DDDDDDDDDDDDDDDDDDDDDDDDDDDD27DDDDDDDDDDDDDDDDDDDDDDDDDDDD28DDDDDDDDDDDDDDDDDDDDDDDDDDDD29DDDDDDDDDDDDDDDDDDDDDDDDDDDD8TTTTTTTTTTTTTTTTTTTTTTTTTTTTT9EEEEEEEEEEEEEEEEEEEEEEEEEEEEE

このように区切ることを、

1HHHHHHHHHHHHHHHHHHHHHHHHHHHHH
21DDDDDDDDDDDDDDDDDDDDDDDDDDDD
22DDDDDDDDDDDDDDDDDDDDDDDDDDDD
23DDDDDDDDDDDDDDDDDDDDDDDDDDDD
24DDDDDDDDDDDDDDDDDDDDDDDDDDDD
25DDDDDDDDDDDDDDDDDDDDDDDDDDDD
26DDDDDDDDDDDDDDDDDDDDDDDDDDDD
27DDDDDDDDDDDDDDDDDDDDDDDDDDDD
28DDDDDDDDDDDDDDDDDDDDDDDDDDDD
29DDDDDDDDDDDDDDDDDDDDDDDDDDDD
8TTTTTTTTTTTTTTTTTTTTTTTTTTTTT
9EEEEEEEEEEEEEEEEEEEEEEEEEEEEE

分割と称することにします。

想定するデータの形式

ありがちな、

  • ヘッダレコード
  • データレコード
  • トレーラレコード
  • エンドレコード

の全銀フォーマット的な形式とします。何がありがちなのかよくわからな人は全銀フォーマット　固定長でggってください。

文字種は半角アルファベット・半角数字、ファイルに改行は含まれず、各レコードは30byteとします。

やりかた

ヘッダ→エンド→トレイラー→データの順に分割します。

ヘッダレコード

ヘッダレコードはファイルの先頭に陣取るレコードなので、

^.{30}

と正規表現を使って検索すれば、頭30byteが取得できます。

エンドレコード

エンドレコードはファイルの末尾に陣取るレコードなので、

.{30}$

と正規表現を使って検索すれば、お尻30byteが取得できます。

トレイラーレコード

エンドレコードを切り取ってからもう一度エンドレコードと同じやり方で検索すると、トレーラレコードが取得できます。

データレコード

ヘッダ・エンド・トレーラを前述の手順で切り出してしまえば、 .{30} で引っかかるのがデータレコード、となります。

レコード種別に関係なく30byteずつ分割する

(.{30})を\1\nと置換すると、30byte引っかかるごとに改行を挟むことになるので、結果的に分割できます。なお改行コードが気になる方は文字コード環境に合わせて\nなり\r\nなり¥r¥nなりに変更してください。

おわりに

機械学習や人工知能の波が押し寄せようと来まいと、データフォーマットの変換がなくなることはないでしょう。固定長・CSV・XML・JSOなど、どんな何が来てもグリっと変換できるようにしておけば、平成の次の時代でも困ることはないはず(たぶん)。

唐突にテキストファイルだけ渡された時、しれっとフォーマット変換しなければいけない、だが手元にテキストエディタしかないのだが？な時にこの記事が役に立ったら幸いです。

【追記】分割されたファイルから改行を消す

\nをブランク(≠半角スペース)で置換すれば元に戻ります。

ggplot, geom_point, facet_gridの練習

R

はじめに

ggplotやqplotを日々練習しています。ggplot, geom_point, facet_gridを組み合わせると、

  • グラフ内側のx軸(量的)
  • グラフ内側のy軸(量的)
  • グラフ外側の横側(質的)
  • グラフ外側の上側(質的)
  • 点の色(質的・量的）

のように５変数くらい同時に、割と分かりやすく表示することができる。分割が煩わしいなら、facet_gridの項を消せば１つの散布図色付きのように表示することができる。

コード

例１

library(dplyr)
library(ggplot2)
ggplot(mpg) + geom_point(aes(cty,displ,colour=fl)) + facet_grid(year~cyl)

f:id:axjack:20190228230847p:plain

例２

library(ISLR)
a1 <- Auto %>% mutate(year_ = ifelse(year <= 80, 70, 80))
ggplot(a1) + geom_point(aes(displacement,horsepower,colour=weight)) + facet_grid(year_~origin)

f:id:axjack:20190228231005p:plain

データミックス(DataMix)のデータサイエンティスト育成コースページにあるスクール紹介動画[0:29]~[0:31]辺りに表示される書籍を出来る限り書き起こす

調べ物

はじめに

インスタグラムで流れる企業宣伝動画を見ていたらデータミックス(DataMix)のデータサイエンティスト育成コースなるものが目に飛び込んだ。未経験から6か月間でデータサイエンティストを目指すと書いてあり、すごいなぁーと思いながらサイトを眺めていたところ、サイトの真ん中あたりでスクール紹介動画を発見。なるほどーと思いながら動画を見ると、統計関連の書籍が2,3秒ほどちらっと差し込まれていることに気づく。

ということで、これはメモる価値がありそうだなと思ったため、書籍のタイトルを書き起こす次第である。

データミックス(DataMix)とは？

以下のサイトを参照。 datamix.co.jp

書き起こし

当該箇所

以下である。 f:id:axjack:20190217042555p:plain 引用元：https://datamix.co.jp/data-scientist/

書籍リスト

左から順に

感想

割とガチ目の本が載っていてレベル高けぇーと思った。ハイレベル感。

補足

データサイエンティストとはそもそも何か、については下記も併せて読むとイメージが膨らむ。

第四次産業革命スキル習得講座

第四次産業革命スキル習得講座認定制度（METI/経済産業省）に指定されているということをサイトでは謳っていた。どうやらこのキーワードで調べればデータサイエンス講座を網羅的に発掘できそうだ。ということで、経済産業省のサイトを調べると、現在は認定が第三回まで実施されていて次回の認定は、平成31年10月以降に開講する講座を対象として、3月頃に申請受付をする予定とのこと。なお以下はこれまで認定を受けた講座一覧、が掲載されているページである。

不偏分散が不偏であることを確認する

統計 調べ物

元ネタ

統計の基礎 の 母集団からランダムに取り出した標本でなくても，例えば10個の固定した値を4個と6個に分ける場合，両者を比較するには何で割った分散を使うかという問題についても，一貫して n − 1で割るほうが比較としては正しくなります： を別データに差し替えて確認する。

何を確認するのか

大きさN=9 の xdataを母集団とみなし、大きさn = {2, ... ,9} の標本を抽出する時の、それぞれの不偏分散を確認する。

コード

#データを用意し
xdata <- c(11,22,33,44,55,66,77,88,99)

#不偏分散を自力で計算
huhenbunsan <- (1/( length(xdata) - 1)) * sum( ( xdata - mean(xdata) )^2)

#あらかじめ用意されている不偏分散の関数
var(xdata)
#> var(xdata)
#[1] 907.5

#答え合わせ
var(xdata) - huhenbunsan
#> var(xdata) - huhenbunsan
#[1] 0

#xdataから任意の2つの組み合わせで不偏分散を取り
#それらの平均値を求める
mean( combn(xdata,2,var) )
#> mean( combn(xdata,2,var) )
#[1] 907.5

#xdataから任意の3つの略
mean( combn(xdata,3,var) )
#> mean( combn(xdata,3,var) )
#[1] 907.5

#xdataから任意の4つの略
mean( combn(xdata,4,var) )
#> mean( combn(xdata,4,var) )
#[1] 907.5

#xdataから任意の5つの略
mean( combn(xdata,5,var) )
#> mean( combn(xdata,5,var) )
#[1] 907.5

#xdataから任意の6つの略
mean( combn(xdata,6,var) )
#> mean( combn(xdata,6,var) )
#[1] 907.5

#xdataから任意の7つの略
mean( combn(xdata,7,var) )
#> mean( combn(xdata,7,var) )
#[1] 907.5

#xdataから任意の8つの略
mean( combn(xdata,8,var) )
#> mean( combn(xdata,8,var) )
#[1] 907.5

#xdataから任意の9つの略
mean( combn(xdata,9,var) )
#> mean( combn(xdata,9,var) )
#[1] 907.5

おわりに

感想

標本の不偏分散の平均値が、母集団の不偏分散と全部一致していて気持ちいい(?)です。

combnについて

combn {utils}    R Documentation
Generate All Combinations of n Elements, Taken m at a Time

Description

Generate all combinations of the elements of x taken m at a time. 
If x is a positive integer, returns all combinations of the elements of 
seq(x) taken m at a time. If argument FUN is not NULL, 
applies a function given by the argument to each point. If simplify is FALSE, 
returns a list; otherwise returns an array, typically a matrix. ... 
are passed unchanged to the FUN function, if specified.

所沢市の平成29年版統計書-２　人口(その１)-１．人口の推移から、人口の総数＝男＋女となっていることを確認する

R 調べ物

はじめに

動機

昨今不正統計が話題である。ニュースを見て、我が市の統計データは大丈夫なのかと不安になったので、基礎中の基礎である人口の推移データに不備がないかを調べてみた。

やること

難しい分析をする気は毛頭無之、各年に於ける 人口の総数 = 男性の人口 + 女性の人口をRで確認するだけである。

ところで、市が提供してるのは、

  • htmlファイル
  • Excelファイル

の２形式であるが、私の手元にはExcelがないので、

  • htmlファイルから該当箇所をコピー
  • sublime で加工
  • Rで検算

を試みる。

なお、htmlファイルが公開されているのになぜ{rvest}を使わないのか、という疑問に対する回答として、htmlファイルが構造化されていないことおよびclassやidが振られていないことを挙げる。htmlファイルのソースを見ればわかる通り、XPathを頑張って書くのが面倒臭い系のhtmlである。

データソース

www.city.tokorozawa.saitama.jp

コード

sublimeでの加工

１．人口の推移の明治９年から平成29年行の一世帯当たり人員2.2までをコピる。続いて、以下のように検索・置換をしてCSVファイルを作成する。

1. 総数-男-女の列を取得する

検索：(.*?)   (.*?)   (.*?)   (.*?)   (.*?)   (.*?)   (.*?)   (.*?)   (.*)
置換："\4","\5","\6"

2. 数値表現をカンマ有りからカンマ無しへと置換する

検索：(\d),
置換：\1

3. ダブルクオーテーションを削除する

検索："
置換：

これで総数-男-女の列を数値表現カンマ区切りで加工できたので、CSVとして保存する。

Rで読み込み

先ほどのCSVをtoko_jinkou.csvとしてdatフォルダに格納し、列のヘッダを"total","man","woman"と指定して読み込む。

toko_ji <- read.csv("dat/toko_jinkou.csv",col.names = c("total","man","woman"))

結果確認

以下の通り、各年に於ける人口の総数は 男性の人口 + 女性の人口に等しいことが確認できた。

> toko_ji$total - ( toko_ji$man + toko_ji$woman )
 [1] 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
[52] 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

おわりに

結果確認の箇所で0じゃない要素が出てきたらどうしようと少し焦ったものの、まずは一安心。データの前処理方法は沢山あるのでいつでもさらっとスパッと書けるようにしたい。

次の元号をRで予想してみる〜前処理編〜

R 調べ物

はじめに

平成もあと3ヶ月足らずで終わってしまいますね。色んな方が次の元号を予想している流れがあるので、自分でも予想してみようと思います。今回は前処理編と称して、ひとまず大化から平成までの元号を取得します。取得するとは、webスクレイピングしてみるということです。どうやってwebスクレイピングするかというと、Rの{rvest}を使ってみます。

コード

rvestの準備

installしてlibraryします。

install.packages("rvest")
library(rvest)
library(dplyr) #テスト用で一瞬使います

{rvest}が使えるかテストします。

> read_html("http://example.com/") %>% html_nodes("p") %>% html_text
[1] "This domain is established to be used for illustrative examples in documents. You may use this\n    domain in examples without prior coordination or asking for permission."
[2] "More information..."

大丈夫そうなので次に進みます。

rvestでwebスクレイピング→テキスト処理（１文字ずつに分解）

元号は国立公文書館 デジタルアーカイブから取得します。が、このサイトは和暦元号・中国元号・朝鮮元号それぞれ記載されているので、後ほど力技で和暦のみ抽出します。 www.digital.archives.go.jp

以下、デジタルアーカイブのサイトから和暦元号を取得 → テキスト処理（１文字ずつに分解）するまで、を一気に書きます。

# table td周りをごそっと取得
src_url <- 'https://www.digital.archives.go.jp/DAS/meta/era#1'
gengou_html <- read_html(src_url)
gengou_html_nd <- html_nodes(gengou_html,"table td")

# 元号のみ取得し
gengou_ <- html_text(gengou_html_nd)[1:5 %% 5 == 1]

# 和暦で最も古い「大化」の添字を見つける
which(gengou_ == "大化")
#[1] 247


# 和暦だけ抽出
gengou_j <- gengou_[1:247]

  
# 確認
head(gengou_j)
#[1] "平成" "昭和" "大正" "明治" "慶応" "元治"

tail(gengou_j)
#[1] "和銅" "慶雲" "大宝" "朱鳥" "白雉" "大化"

# １文字ずつにパースするが、
gengou_jParse <- strsplit(gengou_j,"")

# パースするとリスト形式となるので、
head(gengou_jParse)[1]
#[[1]]
#[1] "平" "成"

# リストをベクトル形式に変換する
gengou_jParse2 <- unlist(gengou_jParse)

結果確認

いい感じに元号を取得できました。

> gengou_jParse2
  [1] "平" "成" "昭" "和" "大" "正" "明" "治" "慶" "応" "元" "治" "文" "久" "万"
 [16] "延" "安" "政" "嘉" "永" "弘" "化" "天" "保" "文" "政" "文" "化" "享" "和"
 [31] "寛" "政" "天" "明" "安" "永" "明" "和" "宝" "暦" "寛" "延" "延" "享" "寛"
 [46] "保" "元" "文" "享" "保" "正" "徳" "宝" "永" "元" "禄" "貞" "享" "天" "和"
 [61] "延" "宝" "寛" "文" "万" "治" "明" "暦" "承" "応" "慶" "安" "正" "保" "寛"
 [76] "永" "元" "和" "慶" "長" "文" "禄" "天" "正" "元" "亀" "永" "禄" "弘" "治"
 [91] "天" "文" "享" "禄" "大" "永" "永" "正" "文" "亀" "明" "応" "延" "徳" "長"
[106] "享" "文" "明" "応" "仁" "文" "正" "寛" "正" "長" "禄" "康" "正" "享" "徳"
[121] "宝" "徳" "文" "安" "嘉" "吉" "永" "享" "正" "長" "応" "永" "明" "徳" "康"
[136] "応" "嘉" "慶" "至" "徳" "永" "徳" "康" "暦" "永" "和" "応" "安" "貞" "治"
[151] "康" "安" "延" "文" "文" "和" "観" "応" "貞" "和" "康" "永" "暦" "応" "元"
[166] "中" "弘" "和" "天" "授" "文" "中" "建" "徳" "正" "平" "興" "国" "延" "元"
[181] "建" "武" "正" "慶" "元" "弘" "元" "徳" "嘉" "暦" "正" "中" "元" "亨" "元"
[196] "応" "文" "保" "正" "和" "応" "長" "延" "慶" "徳" "治" "嘉" "元" "乾" "元"
[211] "正" "安" "永" "仁" "正" "応" "弘" "安" "建" "治" "文" "永" "弘" "長" "文"
[226] "応" "正" "元" "正" "嘉" "康" "元" "建" "長" "宝" "治" "寛" "元" "仁" "治"
[241] "延" "応" "暦" "仁" "嘉" "禎" "文" "暦" "天" "福" "貞" "永" "寛" "喜" "安"
[256] "定" "嘉" "禄" "元" "仁" "貞" "応" "承" "久" "建" "保" "建" "暦" "承" "元"
[271] "建" "永" "元" "久" "建" "仁" "正" "治" "建" "久" "文" "治" "元" "暦" "寿"
[286] "永" "養" "和" "治" "承" "安" "元" "承" "安" "嘉" "応" "仁" "安" "永" "万"
[301] "長" "寛" "応" "保" "永" "暦" "平" "治" "保" "元" "久" "寿" "仁" "平" "久"
[316] "安" "天" "養" "康" "治" "永" "治" "保" "延" "長" "承" "天" "承" "大" "治"
[331] "天" "治" "保" "安" "元" "永" "永" "久" "天" "永" "天" "仁" "嘉" "承" "長"
[346] "治" "康" "和" "承" "徳" "永" "長" "嘉" "保" "寛" "治" "応" "徳" "永" "保"
[361] "承" "暦" "承" "保" "延" "久" "治" "暦" "康" "平" "天" "喜" "永" "承" "寛"
[376] "徳" "長" "久" "長" "暦" "長" "元" "万" "寿" "治" "安" "寛" "仁" "長" "和"
[391] "寛" "弘" "長" "保" "長" "徳" "正" "暦" "永" "祚" "永" "延" "寛" "和" "永"
[406] "観" "天" "元" "貞" "元" "天" "延" "天" "禄" "安" "和" "康" "保" "応" "和"
[421] "天" "徳" "天" "暦" "天" "慶" "承" "平" "延" "長" "延" "喜" "昌" "泰" "寛"
[436] "平" "仁" "和" "元" "慶" "貞" "観" "天" "安" "斉" "衡" "仁" "寿" "嘉" "祥"
[451] "承" "和" "天" "長" "弘" "仁" "大" "同" "延" "暦" "天" "応" "宝" "亀" "神"
[466] "護" "景" "雲" "天" "平" "神" "護" "天" "平" "宝" "字" "天" "平" "勝" "宝"
[481] "天" "平" "感" "宝" "天" "平" "神" "亀" "養" "老" "霊" "亀" "和" "銅" "慶"
[496] "雲" "大" "宝" "朱" "鳥" "白" "雉" "大" "化"

終わりに

次が果たしてあるのか無いのかいつになるのか、は分かりませんが・・・この情報を元に新元号が発表される４月１日より前になんらかの予想ができたらなぁと。

参考にしたサイト

非階層的な部署コードと階層的な部署テキストを持つテーブルから、部署テキストで階層構造を導出する。の補足

SQL

まえがき

前回の宿題の１つが解決できたのでメモを残す記事です。前回の宿題とは、

部署テキストの途中から他の部署名を含むという場合(e.g. システム開発第一部 vs 老朽化更改システム開発第一部 のような部署テキストが存在する場合、システム開発第一部 ⊃ 老朽化更改システム開発第一部)となってしまう)、これはおそらく正しく無いと思うものの、今の所これを回避する方法が分からない。

非階層的な部署コードと階層的な部署テキストを持つテーブルから、部署テキストで階層構造を導出する。 - axjack's blog

です。*1

今回は、前回と同じデータに「老朽化更改システム開発第一部」を加えた際、「システム開発第一部 ⊃ 老朽化更改システム開発第一部」とならないようなwhere句の作り方を書いてみます。

ソースコード

前回と同じスタイルで作成します。

実行環境

DB Fiddle の MySQL v8.0 です。

テーブル作成

    --部署テーブル
    create table  DeptList (
      deptNum varchar(256) -- 部署コード
      ,deptText varchar(256) -- 部署テキスト
    );

データinsert

    --データ投入
    insert into DeptList values('1000','システム開発第一部');
    insert into DeptList values('10001','システム開発第一部第一課');
    insert into DeptList values('10002','システム開発第一部第二課');
    insert into DeptList values('10003','システム開発第一部第三課');
    insert into DeptList values('10004','システム開発第一部第三課一係');
    insert into DeptList values('10005','システム開発第一部第三課二係');
    insert into DeptList values('1006','システム開発第二部');
    insert into DeptList values('1007','システム開発第二部国内課');
    insert into DeptList values('1008','システム開発第二部海外課');
    insert into DeptList values('1009','システム開発第二部海外課アジア係');
    insert into DeptList values('1010','システム開発第二部海外課ヨーロッパ係');
    insert into DeptList values('1011','品質管理部');
    insert into DeptList values('1012','運用管理部');
    insert into DeptList values('1013','運用管理部総務課');
    insert into DeptList values('1014','総務部');
    insert into DeptList values('1015','人事部');
    insert into DeptList values('10151','人事部人事課');
    insert into DeptList values('1016','総務部給与課');
    insert into DeptList values('1017','企画室');
    insert into DeptList values('1018','企画室企画課');
    insert into DeptList values('1019','企画室企画課第一企画係');
    insert into DeptList values('10191','企画室企画課第二企画係');
    insert into DeptList values('10192','企画室企画課第三企画係');
    insert into DeptList values('1022','経理部第一課');
    insert into DeptList values('1029','経理部第二課');
    insert into DeptList values ('10301','老朽化更改システム開発第一部');   -- 今回追加したデータ
    insert into DeptList values('10311','外部委託システム運用管理部');  -- 同様に今回追加したデータ

部署の紐付け

create temporary table dept0
    select
        a.deptNum as srcNum --紐付け元の部署コード
        , a.deptText as srcText --紐付け元の部署テキスト
        , b.deptNum as dstNum --紐付け先の部署コード
        , b.deptText as dstText --紐付け先の部署テキスト
    from 
        DeptList as a
        , DeptList as b
    where 
         length(concat('dummy',a.deptText))  >  length(replace(concat('dummy',a.deptText), concat('dummy',b.deptText), ''))
    ;

結果確認

今回は、「老朽化更改システム開発第一部」と「外部委託システム運用管理部」がそれぞれどのように紐付けられたかを確認します。

select文

select 
    * 
from 
    dept0
where 
    srcNum in ('10301','10311') 
order by 
    srcNum desc
;

実行結果

srcNum srcText dstNum dstText
10311 外部委託システム運用管理部 10311 外部委託システム運用管理部
10301 老朽化更改システム開発第一部 10301 老朽化更改システム開発第一部

前回の抽出ロジックの場合

select文

    create temporary table dept0
        select
            a.deptNum as srcNum 
            , a.deptText as srcText 
            , b.deptNum as dstNum 
            , b.deptText as dstText 
        from 
            DeptList as a
            , DeptList as b
        where 
    
             length(a.deptText)  >  length(replace(a.deptText,b.deptText, ''))
        ;

    select 
        * 
    from 
        dept0
    where 
        srcNum in ('10301','10311') 
    order by 
        srcNum desc
    ;

実行結果

srcNum srcText dstNum dstText
10311 外部委託システム運用管理部 1012 運用管理部
10311 外部委託システム運用管理部 10311 外部委託システム運用管理部
10301 老朽化更改システム開発第一部 1000 システム開発第一部
10301 老朽化更改システム開発第一部 10301 老朽化更改システム開発第一部

考察

where句はlength(concat('dummy',a.deptText)) > length(replace(concat('dummy',a.deptText), concat('dummy',b.deptText), ''))です。テキストの頭にdummyなる文字列を連結してみました。これを付ける場合と付けない場合を比較すると、下記のようになります。

a b replaceできる？ replaceすると？ replace後の文字数は？ replace前後で文字数を比較すると？ 文字数比較の真偽は？
老朽化更改システム開発第一部 システム開発第一部 yes 老朽化更改 5 14 > 5 true
dummy老朽化更改システム開発第一部 dummyシステム開発第一部 no dummy老朽化更改システム開発第一部 19 19 > 19 false

なんとも見辛い表ですが、、テキストの頭にdummyを追加することによって、中間一致から前方一致することができた、ということです。

*1:「開発」が抜けていたので、追記箇所を太字で補いました。

非階層的な部署コードと階層的な部署テキストを持つテーブルから、部署テキストで階層構造を導出する。

SQL

はじめに

タイトルの詳しい説明

日本語って難しいですね。非階層的な部署コードと階層的な部署テキストを持つテーブルとは、以下のようなテーブルを意味します。

部署コード 部署テキスト
1000 システム開発第一部
10001 システム開発第一部第一課
10002 システム開発第一部第二課
10003 システム開発第一部第三課
10004 システム開発第一部第三課一係
10005 システム開発第一部第三課二係
1006 システム開発第二部
1007 システム開発第二部国内課
1008 システム開発第二部海外課
1009 システム開発第二部海外課アジア係
1010 システム開発第二部海外課ヨーロッパ係

部署コードがシーケンシャルのようなそうでもないような微妙な付け方をしている一方で、部署テキストはなんとなく

┏システム開発第一部  
┃　┣システム開発第一部第一課
┃　┣システム開発第一部第二課
┃　┗システム開発第一部第三課
┃　　　┣システム開発第一部第三課一係
┃　　　┣システム開発第一部第三課二係
┃　　　┗システム開発第一部第三課二係
┃
┗システム開発第二部
　　┣システム開発第二部国内課
　　┗システム開発第二部海外課
　　　　┣システム開発第二部海外課アジア係
　　　　┗システム開発第二部海外課アジア係

のように階層化できそうですよね。ということで、SQLにて部署テキストで階層構造を導出することにします。

どうやるか？

日本語で書くと以下の２ステップを踏みます。

  • テーブルを自己結合する
    • 同じテーブルを別名で二つ指定するので、便宜上A・Bと名付ける
  • 以下の条件を満たすレコードを抽出する
    • Aの部署名をBの部署名で''(ブランク)に置換し(★1)
    • Aの部署名の長さ が ★1の長さより大きい

なぜこのように抽出すると(一見)うまくいくのか？

置換が成功する例を見てみます。

A.部署名 = システム開発第一部第一課
B.部署名 = システム開発第一部

なとき、

・Aの部署名をBの部署名で''(ブランク)に置換し(★1)
　→　「システム開発第一部第一課」が「第一課」と置換されます。
・ Aの部署名の長さ が ★1の長さより大きい
　→　12文字 vs 3文字 なので条件を満たす

一方、置換が失敗する例はというと、

----------【例１】----------
A.部署名 = システム開発第一部
B.部署名 = システム開発第一部第一課

なとき、

・Aの部署名をBの部署名で''(ブランク)に置換し(★1)
　→　置換ができないので「システム開発第一部」のままとなります
・ Aの部署名の長さ が ★1の長さより大きい
　→　12文字 vs 12文字 なので条件を満たさない
----------【例２】----------
A.部署名 = システム開発第一部第一課
B.部署名 = システム開発第二部

なとき、

・Aの部署名をBの部署名で''(ブランク)に置換し(★1)
　→　置換ができないので「システム開発第一部第一課」のままとなります
・ Aの部署名の長さ が ★1の長さより大きい
　→　12文字 vs 12文字 なので条件を満たさない

となるので、(一見)うまくいきます。

ソース

実際にテーブルを作ってSELECT文を実行してみます。

実行環境

DB Fiddle の MySQL v8.0 にて実行しました。

テーブル作成

    --部署テーブル
    create table  DeptList (
      deptNum varchar(256) -- 部署コード
      ,deptText varchar(256) -- 部署テキスト
    );

データinsert

階層的で無い(i.e. 子を持たない部)データも加えてinsertします。

    --データ投入
    insert into DeptList values('1000','システム開発第一部');
    insert into DeptList values('10001','システム開発第一部第一課');
    insert into DeptList values('10002','システム開発第一部第二課');
    insert into DeptList values('10003','システム開発第一部第三課');
    insert into DeptList values('10004','システム開発第一部第三課一係');
    insert into DeptList values('10005','システム開発第一部第三課二係');
    insert into DeptList values('1006','システム開発第二部');
    insert into DeptList values('1007','システム開発第二部国内課');
    insert into DeptList values('1008','システム開発第二部海外課');
    insert into DeptList values('1009','システム開発第二部海外課アジア係');
    insert into DeptList values('1010','システム開発第二部海外課ヨーロッパ係');
    insert into DeptList values('1011','品質管理部');
    insert into DeptList values('1012','運用管理部');
    insert into DeptList values('1013','運用管理部総務課');
    insert into DeptList values('1014','総務部');
    insert into DeptList values('1015','人事部');
    insert into DeptList values('10151','人事部人事課');
    insert into DeptList values('1016','総務部給与課');
    insert into DeptList values('1017','企画室');
    insert into DeptList values('1018','企画室企画課');
    insert into DeptList values('1019','企画室企画課第一企画係');
    insert into DeptList values('10191','企画室企画課第二企画係');
    insert into DeptList values('10192','企画室企画課第三企画係');
    insert into DeptList values('1022','経理部第一課');
    insert into DeptList values('1029','経理部第二課');

部署の紐付け

    create temporary table dept0
    select
        a.deptNum as srcNum --紐付け元の部署コード
        , a.deptText as srcText --紐付け元の部署テキスト
        , b.deptNum as dstNum --紐付け先の部署コード
        , b.deptText as dstText --紐付け先の部署テキスト
    from 
        DeptList as a
        , DeptList as b
    where 
         length(a.deptText)  >  length(replace(a.deptText, b.deptText, ''))
    ;

結果確認

なお、結果の解釈は「左側(srcNum, srcText)は右側(dstNum, dstText)の配下にある」です。

select * from dept0;

srcNum srcText dstNum dstText
1000 システム開発第一部 1000 システム開発第一部
10001 システム開発第一部第一課 1000 システム開発第一部
10002 システム開発第一部第二課 1000 システム開発第一部
10003 システム開発第一部第三課 1000 システム開発第一部
10004 システム開発第一部第三課一係 1000 システム開発第一部
10005 システム開発第一部第三課二係 1000 システム開発第一部
10001 システム開発第一部第一課 10001 システム開発第一部第一課
10002 システム開発第一部第二課 10002 システム開発第一部第二課
10003 システム開発第一部第三課 10003 システム開発第一部第三課
10004 システム開発第一部第三課一係 10003 システム開発第一部第三課
10005 システム開発第一部第三課二係 10003 システム開発第一部第三課
10004 システム開発第一部第三課一係 10004 システム開発第一部第三課一係
10005 システム開発第一部第三課二係 10005 システム開発第一部第三課二係
1006 システム開発第二部 1006 システム開発第二部
1007 システム開発第二部国内課 1006 システム開発第二部
1008 システム開発第二部海外課 1006 システム開発第二部
1009 システム開発第二部海外課アジア係 1006 システム開発第二部
1010 システム開発第二部海外課ヨーロッパ係 1006 システム開発第二部
1007 システム開発第二部国内課 1007 システム開発第二部国内課
1008 システム開発第二部海外課 1008 システム開発第二部海外課
1009 システム開発第二部海外課アジア係 1008 システム開発第二部海外課
1010 システム開発第二部海外課ヨーロッパ係 1008 システム開発第二部海外課
1009 システム開発第二部海外課アジア係 1009 システム開発第二部海外課アジア係
1010 システム開発第二部海外課ヨーロッパ係 1010 システム開発第二部海外課ヨーロッパ係
1011 品質管理部 1011 品質管理部
1012 運用管理部 1012 運用管理部
1013 運用管理部総務課 1012 運用管理部
1013 運用管理部総務課 1013 運用管理部総務課
1014 総務部 1014 総務部
1016 総務部給与課 1014 総務部
1015 人事部 1015 人事部
10151 人事部人事課 1015 人事部
10151 人事部人事課 10151 人事部人事課
1016 総務部給与課 1016 総務部給与課
1017 企画室 1017 企画室
1018 企画室企画課 1017 企画室
1019 企画室企画課第一企画係 1017 企画室
10191 企画室企画課第二企画係 1017 企画室
10192 企画室企画課第三企画係 1017 企画室
1018 企画室企画課 1018 企画室企画課
1019 企画室企画課第一企画係 1018 企画室企画課
10191 企画室企画課第二企画係 1018 企画室企画課
10192 企画室企画課第三企画係 1018 企画室企画課
1019 企画室企画課第一企画係 1019 企画室企画課第一企画係
10191 企画室企画課第二企画係 10191 企画室企画課第二企画係
10192 企画室企画課第三企画係 10192 企画室企画課第三企画係
1022 経理部第一課 1022 経理部第一課
1029 経理部第二課 1029 経理部第二課

感想

良い点

  • ひとまず１階層分( e.g. システム開発第一部 ⊃ システム開発第一部第一課) を紐づけることができた。
  • 自分は自分自身に含まれる(e.g 企画室企画課 は 企画室企画課を含む) 結果となっているが、これはwhere句を弄れば抽出対象から外すことができる。

改善すべき点

一見 と付けた理由を書き残します。

  • もし部署テキストが２階層・３階層…と続いてしまう場合、階層数が分からない場合、この手法では物足りなさが残る。
  • 部署テキストの途中から他の部署名を含むという場合(e.g. システム第一部 vs 老朽化更改システム第一部 のような部署テキストが存在する場合、システム第一部 ⊃ 老朽化更改システム第一部)となってしまう)、これはおそらく正しく無いと思うものの、今の所これを回避する方法が分からない。
axjack is said to be an abbreviation for An eXistent JApanese Cool Klutz.