稟議を例に
前提知識、申請者(ここは投稿者のnacoponと仮定)が、
備品を買いたい、交通費を精算したいと稟議を承認者に上げていきます。
// 稟議番号(key),承認ステータス,承認者
0001,開始,織田信長
0001,終了,織田信長
0002,開始,豊臣秀吉
0002,申請,豊臣秀吉
0002,承認,豊臣秀吉
0002,承認,織田信長
0002,終了,織田信長
0003,開始,徳川家康
0003,申請,徳川家康
0003,否認,徳川家康
データの見方
①稟議番号:0001
稟議を申請すると、DBには必ず"開始"ステータスが登録され、
その稟議がこれ以上すすむことがなくなれば、"終了"ステータスとなります。
②稟議番号:0002
高額な稟議だと, 1次承認, 2次承認・・・と複数回承認が必要になることもある。
③稟議番号:0003
否認されたが、終了ステータスがないので、その後(再申請されたのか、それとも終了なのか)が不明な
宙ぶらりん状態である。
そこで、キーごとの最終行(=履歴の最新行)を見れば、稟議がどうなったかがわかるのである！！
そう、業務系のシステムは前提知識が必要なのでめんどくさい！！！
いざやってみると
DBはMySQL
第１案：履歴時間を見る
①上記のサンプルにはないが、DBには作成時間、履歴時間を持っている。(あとだしですまない)
②しかし、現実は甘くなかった。
// 稟議番号(key),承認ステータス,承認者,最終更新日時
0001,開始,織田信長,2017-09-01 09:30:00
0001,終了,織田信長,2017-09-01 09:30:00
あろうことか、開始フラグが経った瞬間にフラグをおろすと、システム上では同じ時間で登録されることがある。
(ミリ秒で登録しろや！と突っ込みたいが、DBを変更する権限はない)
やはり、レコードの行番号を見る必要がある。
第２案：キーごとの最終行を取得する
キーごとに連番を振り、
// no,稟議番号(key),承認ステータス,承認者
1,0001,開始,織田信長
2,0001,終了,織田信長 // ここが欲しい
1,0002,開始,豊臣秀吉
2,0002,申請,豊臣秀吉
3,0002,承認,豊臣秀吉
4,0002,承認,織田信長
5,0002,終了,織田信長 // ここが欲しい
1,0003,開始,徳川家康
2,0003,申請,徳川家康
3,0003,否認,徳川家康 // ここが欲しい
先頭が欲しければ → where no =1
最終行が欲しけれ →
oder by 稟議番号,no DESC を追加して where no =1
で取得できるはずだ。
(キーごとにオーダーの向きは変更できるのだ！)
参照：http://d.hatena.ne.jp/Choo/20080409/1207723058
LINUXでもhttps://abicky.net/2011/07/24/174632/
一応脳内では完結した、いざ実装！
①OracleなどのDBでキーごと列番号を振るには
row_number() over(partition by 稟議番号 order by 稟議番号)
https://www.shift-the-oracle.com/sql/functions/row_number-practice.html
でうまくいきそうに見えるが、
MySQLにはrow_number()関数はない。
また、ASTERIAはPARTITION句が使えない。
のでボツ。
②MySQLでキーごとの連番を実現したいのだ
なんと、PARTITON句を使わないやり方で実現できる！
http://qiita.com/toyottoyo/items/813338f4756dee41e49b
set @no:=0;
set @groupid:=null;
select
if(@groupid <> group_id, @no:=1, @no:=@no+1) as no,
@groupid:=稟議番号 -- ポイントはここで、
-- 今回の例だと、groupidではなく、稟議番号！
-- if文の後ろに代入することがポイント
が、しかし、
ASTERIAでは SET句が使えないのだ！！！
せっかくの希望の光が～～
私は怒られてしまったのですが、使えた方がいらっしゃいましたら、教えてください。
この案もボツ。
第３案：サブクエリを作って inner join か
①列番号を振る(キーごとではない)
↓RecordSQLでは出来ないので注意。(たぶん変数@iが読み取れない。)
マッパーの行番号でも可。
SELECT
cast(@i:=@i+1 as SIGNED) AS no -- 新しいレコードごとにインクリメント
,稟議番号,承認ステータス ,承認者 ・・・・
FROM
(SELECT @i:=0) -- 初期を設定
,稟議テーブル
ポイントは
cast(@i:=@i+1 as SIGNED) で行番号をString型からInteger型にすること(このあとmax()を使うので)
↓結果
1,0001,開始,織田信長
2,0001,終了,織田信長 // ここが欲しい
3,0002,開始,豊臣秀吉
4,0002,申請,豊臣秀吉
5,0002,承認,豊臣秀吉
6,0002,承認,織田信長
7,0002,終了,織田信長 // ここが欲しい
8,0003,開始,徳川家康
9,0003,申請,徳川家康
10,0003,否認,徳川家康 // ここが欲しい
②次に,最終行と稟議番号を取得するサブクエリ
select
max(field[1]), -- 行番号の最大値を
field[2] -- 稟議番号
from in[1]
group by field[2] -- 稟議番号でグループ化
③inner join
サンプルのため,RDBGetではなくFileGetになってるのはご愛嬌.
JOIN処理:inner join
| 入力キー1 | 入力キー2 |
|---|---|
| Mapper1.行番号 | RecordSQL1.行番号 |
| Mapper1.稟議番号 | RecordSQL1.稟議番号 |
最終出力
取れました。やったね！
まとめ
①業務系のお仕事では、履歴型のDBを扱わなければならない場面が多くあります。
しかし、扱うDBが履歴フラグや更新履歴が不適切な場合もあります。
②第２案のソートするやり方、第３案のinner joinする方法も、
どちらも内部的にループしているため、データ量が多くなると遅くなるという欠点があります。
どうでしょうか？実際の実務でいきなり欲しいデータを抽出できることは非常に稀です。
試行錯誤しながら、ゴールにたどりつけるかんじを壁にぶち当たった順で書いてみました。
MySQLをエイリアスで列をつくる(今回は、行番号)とwhere句で使えないという制約があります。
ASTERIAをつかうと、エイリアスなど気にしなくてもよいのはメリットかもしれませんね。
上記以外のやりかた(Mapperによるストリーム)でキーごとの最終行を抽出する方法をご存知の方は
是非教えていただきたいです。