GAS ライブラリー作成③ 結合

次はJOIN系列

DummyData

var tran = [
  ["001","国語","A"],
  ["001","数学","B"],
  ["001","英語","C"],
  ["002","国語","B"],
  ["002","数学","A"],
  ["002","英語","C"],
  ["003","国語","B"],
  ["003","数学","B"],
  ["003","英語","A"]
];

var master = [
  ["001","織田信長","暴君"],
  ["002","豊臣秀吉","下克上"]
] ;

join0 (集合選択)

トランザクションを集合で絞る. joinと付いているが, 結合はしていない.
innner join(後述のjoin1)してから, masterの非結合箇所をdelfしたものと結果は同じ.
keyは複数指定することも可能.
第3引数にtrueを入力すると,masterとtranが逆転する.

join0

    join0:
    function(argArr,keyArr,swap){
      var intmp = this.element.concat();        
      var output=[];
      var swap = typeof swap !== 'undefined' ?  swap : false;

      // 入れ替える
      if(swap){
        var tmpArg = argArr;
        argArr = intmp; 
        intmp = tmpArg;
      }
      intmp.forEach(function(row,ridx){
        argArr.forEach(function(arg){
          var flagArr =[];
          keyArr.forEach(function(key){
            if(row[key]==arg[key]){flagArr.push(true)}
            else{flagArr.push(false)}
          })

          if(flagArr.indexOf(false) == -1){
            output.push(row)
          }
        })
      })

      this.element=output;
      return this;
    }

結果

D2(tran).join0(master,[0]).get();

/*[
  ["001","国語","A"],
  ["001","数学","B"],
  ["001","英語","C"],
  ["002","国語","B"],
  ["002","数学","A"],
  ["002","英語","C"]
]*/

join1 (内部結合)

SQLのinner joinと同じ.
masterの結合keyは出現しない.
masterの非結合箇所は末尾に追加.(←本当はkeyの直後に追加したかったのだが. )
keyは複数指定することも可能.
第3引数にtrueを入力すると,masterとtranが逆転する.

join1

    join1:
    function(argArr,keyArr,swap){
      var intmp = this.element.concat();        
      var output=[];
      var swap = typeof swap !== 'undefined' ?  swap : false;

      // 入れ替える
      if(swap){
        var tmpArg = argArr;
        argArr = intmp; 
        intmp = tmpArg;
      }

      intmp.map(function(row,ridx){        
        argArr.map(function(arg,aidx){
          var flagArr =[];
          keyArr.forEach(function(key){
            if(row[key]==arg[key]){flagArr.push(true)}
            else{flagArr.push(false)}
          })

          // すべてがtrue、join     
          if(flagArr.indexOf(false) == -1){
            output.push(row.concat()); // 汚染させないため 
            for(var retu =0;retu<arg.length;retu++){
              if(keyArr.indexOf(retu)==-1){ // key列は除く
                output[output.length-1].push(arg[retu])
              }
            }
          }
        })
      })

      this.element=output;
      return this;
    }

結果

D2(tran).join1(master,[0],false).get(); // falseは明示しなくても別に良いけどね
/*[
  ["001","国語","A","織田信長","暴君"],
  ["001","数学","B","織田信長","暴君"],
  ["001","英語","C","織田信長","暴君"],
  ["002","国語","B","豊臣秀吉","下克上"],
  ["002","数学","A","豊臣秀吉","下克上"],
  ["002","英語","C","豊臣秀吉","下克上"]
]*/
// 結合keyである,"001"や"002"は1回しか出現していない
/* 本当は key の直後に結合させてたかったんだけどね。まぁselfしてください.
D2(tran).join1(master,[0],false).self([0,3,4,1,2]).get();
[
  ["001","織田信長","暴君","国語","A"],
  ["001","織田信長","暴君","数学","B"],
  ["001","織田信長","暴君","英語","C"],
  ["002","豊臣秀吉","下克上","国語","B"],
  ["002","豊臣秀吉","下克上","数学","A"],
  ["002","豊臣秀吉","下克上","英語","C"]
]
*/

join2 (左外部結合)

SQLのleft (outer) joinと同じ.
masterの結合keyは出現しない.
masterに項目がなければ,nullで補う(←本当は文字列や0など指定したかったのだが.)
masterの非結合箇所は末尾に追加.(←本当はkeyの直後に追加したかったのだが. )
keyは複数指定することも可能.
第3引数にtrueを入力すると,masterとtranが逆転する.

join2

    join2:
    function(argArr,keyArr,swap){
      var intmp = this.element.concat();        
      var output=[];
      var swap = typeof swap !== 'undefined' ?  swap : false;

      // 入れ替える
      if(swap){
        var tmpArg = argArr;
        argArr = intmp; 
        intmp = tmpArg;
      }

      // outputのjoin後の列の数
      var outputFinallength = intmp[0].length + argArr[0].length-keyArr.length;

      intmp.map(function(row,ridx){
        output[ridx] =[];
        output[ridx] =row.concat(); // copy

        argArr.map(function(arg,aidx){
          var flagArr =[];
          keyArr.forEach(function(key){
            if(row[key]==arg[key]){flagArr.push(true)}
            else{flagArr.push(false)}
          })

          // すべてがtrue、join
          if(flagArr.indexOf(false) == -1){       
            for(var retu =0;retu<arg.length;retu++){
              if(keyArr.indexOf(retu)==-1){ // key列は除く
                output[ridx].push(arg[retu])
              }
            }
          }
        })

        if(output[ridx].length!= outputFinallength){
          for(var retu2 =0 ;retu2<=outputFinallength-output[ridx].length;retu2++){
            output[ridx].push(null)
          }
        }
      })

      this.element=output;
      return this;
    }

結果

D2(tran).join2(master,[0]).get();
// master に003はないので,nullを補っている.
// nullを違う文字に置換する sedみたいなコマンドを作りたいね.
/*
  ["001","国語","A","織田信長","暴君"],
  ["001","数学","B","織田信長","暴君"],
  ["001","英語","C","織田信長","暴君"],
  ["002","国語","B","豊臣秀吉","下克上"],
  ["002","数学","A","豊臣秀吉","下克上"],
  ["002","英語","C","豊臣秀吉","下克上"],
  ["003","国語","B",null,null],
  ["003","数学","B",null,null],
  ["003","英語","A",null,null]
*/

右外部結合はまぁなくてもいいでしょう.

joinx (クロス結合) まだ作っていない

linuxだとヒアドキュメントとあわせて使うと, cross joinは使い勝手がいいのですが.
まぁ, JSだし, そんなに需要もなさそうだから, まだ作っていません.
時間があればつくるかも. あればね.

いかがでしたでしょうか？