なっがいSQLをNode.jsで生成してみた

# 背景
- 似たような処理の繰り返しなので共通化できそう
- でも、SQLの知識がそこまでない
- Gitでコード管理しているが、なっがいSQLをプッシュすると、全コードにおけるSQLの割合が増えてなんか気分が悪い

## なっがいSQLサンプル 
SELECT箇所・LEFT JOIN箇所で多数の重複があり、見やすくするため省略しています

```sql
-- CREATE TABLE sample_table
SELECT
  pb.id, p.name, p.team,
  -- bat1
  IFNULL(fst.slug_ave, 0) AS rate1, 
  IFNULL(fst.pa, 0) AS pa1,
  IFNULL(fst.ab, 0) AS ab1,
  IFNULL(fst.tb, 0) AS cnt1,
  -- bat2
  -- ︙
  -- bat3
  -- ︙
  -- bat4
  -- ︙
  -- bat5
  -- ︙
  -- bat6
  -- ︙
  -- bat7
  IFNULL(sev.slug_ave, 0) AS rate7,
  IFNULL(sev.pa, 0) AS pa7,
  IFNULL(sev.ab, 0) AS ab7,
  IFNULL(sev.tb, 0) AS cnt7,
  -- 各項目合計
  CASE WHEN(IFNULL(fst.ab, 0) + IFNULL(scd.ab, 0) + IFNULL(thr.ab, 0) + IFNULL(fur.ab, 0) + IFNULL(fif.ab, 0) + IFNULL(six.ab, 0) + IFNULL(sev.ab, 0)) > 0 THEN ROUND((IFNULL(fst.tb, 0) + IFNULL(scd.tb, 0) + IFNULL(thr.tb, 0) + IFNULL(fur.tb, 0) + IFNULL(fif.tb, 0) + IFNULL(six.tb, 0) + IFNULL(sev.tb, 0))/(IFNULL(fst.ab, 0) + IFNULL(scd.ab, 0) + IFNULL(thr.ab, 0) + IFNULL(fur.ab, 0) + IFNULL(fif.ab, 0) + IFNULL(six.ab, 0) + IFNULL(sev.ab, 0)), 5) ELSE NULL END AS rate,
  IFNULL(fst.ab, 0) + IFNULL(scd.ab, 0) + IFNULL(thr.ab, 0) + IFNULL(fur.ab, 0) + IFNULL(fif.ab, 0) + IFNULL(six.ab, 0) + IFNULL(sev.ab, 0) AS ab,
  IFNULL(fst.pa, 0) + IFNULL(scd.pa, 0) + IFNULL(thr.pa, 0) + IFNULL(fur.pa, 0) + IFNULL(fif.pa, 0) + IFNULL(six.pa, 0) + IFNULL(sev.pa, 0) AS pa,
  IFNULL(fst.tb, 0) + IFNULL(scd.tb, 0) + IFNULL(thr.tb, 0) + IFNULL(fur.tb, 0) + IFNULL(fif.tb, 0) + IFNULL(six.tb, 0) + IFNULL(sev.tb, 0) AS cnt,
  'e' AS eol
FROM baseball._player_batter pb
  LEFT JOIN player p ON pb.id = p.id
  -- bat1
  LEFT JOIN (
    SELECT 
		  h.batter,
      COUNT(h.batter OR NULL) AS pa,
      COUNT(eb.name IS NULL OR NULL) AS ab,
      COUNT(hi.rst_id IN (2, 3, 4) OR NULL) + COUNT(hi.rst_id = 6 OR NULL) * 2 + COUNT(hi.rst_id = 8 OR NULL) * 3 + COUNT(hi.rst_id = 9 OR NULL) * 4 AS tb,
      CASE WHEN COUNT(eb.name IS NULL OR NULL) > 0 THEN ROUND((COUNT(hi.rst_id IN (2, 3, 4) OR NULL) + COUNT(hi.rst_id = 6 OR NULL) * 2 + COUNT(hi.rst_id = 8 OR NULL) * 3 + COUNT(hi.rst_id = 9 OR NULL) * 4) / COUNT(eb.name IS NULL OR NULL), 5) ELSE null END AS slug_ave
    FROM
        baseball._bat_all_info h
    LEFT JOIN exclude_batting_info eb ON h.`1_result` = eb.name
    LEFT JOIN hit_id_info hi ON  h.`1_rst_id` = hi.rst_id
    WHERE h.`1_result` IS NOT NULL
    GROUP BY batter
  ) AS fst ON fst.batter = pb.id
  -- bat2
  -- ︙
  -- bat3
  -- ︙
  -- bat4
  -- ︙
  -- bat5
  -- ︙
  -- bat6
  -- ︙
  -- bat7
  LEFT JOIN (
    SELECT 
		  h.batter,
      COUNT(h.batter OR NULL) AS pa,
      COUNT(eb.name IS NULL OR NULL) AS ab,
      COUNT(hi.rst_id IN (2, 3, 4) OR NULL) + COUNT(hi.rst_id = 6 OR NULL) * 2 + COUNT(hi.rst_id = 8 OR NULL) * 3 + COUNT(hi.rst_id = 9 OR NULL) * 4 AS tb,
      CASE WHEN COUNT(eb.name IS NULL OR NULL) > 0 THEN ROUND((COUNT(hi.rst_id IN (2, 3, 4) OR NULL) + COUNT(hi.rst_id = 6 OR NULL) * 2 + COUNT(hi.rst_id = 8 OR NULL) * 3 + COUNT(hi.rst_id = 9 OR NULL) * 4) / COUNT(eb.name IS NULL OR NULL), 5) ELSE null END AS slug_ave
    FROM
        baseball._bat_all_info h
    LEFT JOIN exclude_batting_info eb ON h.`7_result` = eb.name
    LEFT JOIN hit_id_info hi ON  h.`7_rst_id` = hi.rst_id
    WHERE h.`7_result` IS NOT NULL
    GROUP BY batter
  ) AS sev ON sev.batter = pb.id
;
```

## 共通化できそうなポイント
- SELECT箇所
    - `rate`, `pa`, `ab`, `cnt`の4カラムについて、末尾に1~7をそれぞれ付与しただけ
    - 各項目の合計算出カラム
        - カラム名を連結するだけなのでコードで簡単に実現できそう
- `baseball._player_batter`へ`LEFT JOIN`を7回繰り返している箇所
    - その中のSQLについても、ほとんど同じ
    - 違うのは以下の2点
        - `_bat_all_info`へ`LEFT JOIN`する際の結合条件やWHERE句で指定するカラムが`1_result`〜`7_result`であること
        - `LEFT JOIN`した後のAlias (`fst`~`sev`)

# 共通化コード
`require`している他のコードについては省略させていただきます。

-  `execute`: `fs`モジュールで`sql`形式のファイルを出力する
-  `getFileName`: ファイルのフルパスからファイル名のみを抽出する
-  `cols`: カラム名を` + `で連結した結果の末尾３文字を削除する

```javascript
// average_slugging.js

"use strict";

const { execute, getFilename, cols } = require("./util/func");
const { BATS_COL } = require("../constants");

let sql = `-- CREATE TABLE ${getFilename(__filename)}
`;

// -------------------- [select part] --------------------
// player_info
sql += `SELECT
  pb.id, p.name, p.team,
  `;

let abCols = "";
let paCols = "";
let tbCols = "";

// any info(rate, pa, ab, cnt) per inning
Object.keys(BATS_COL).map(bat => {
  const batName = BATS_COL[bat];
  sql += `-- bat${bat}`;
  sql += `
  IFNULL(${batName}.slug_ave, 0) AS rate${bat},
  IFNULL(${batName}.pa, 0) AS pa${bat},
  IFNULL(${batName}.ab, 0) AS ab${bat},
  IFNULL(${batName}.tb, 0) AS cnt${bat},
  `;

  abCols += `IFNULL(${batName}.ab, 0) + `;
  paCols += `IFNULL(${batName}.pa, 0) + `;
  tbCols += `IFNULL(${batName}.tb, 0) + `;
});

// about `total`
sql += `-- 各項目合計`;
sql += `
  CASE WHEN(${cols(abCols)}) > 0 THEN ROUND((${cols(tbCols)})/(${cols(abCols)}), 5) ELSE NULL END AS rate,
  ${cols(abCols)} AS ab,
  ${cols(paCols)} AS pa,
  ${cols(tbCols)} AS cnt,
  `;
// -------------------- /[select part] --------------------

sql += `'e' AS eol
FROM baseball._player_batter pb
  LEFT JOIN player p ON pb.id = p.id`;

// -------------------- [left join part] --------------------
// left join part per inning
Object.keys(BATS_COL).map(bat => {
  const batName = BATS_COL[bat];
  sql += `-- bat${bat}`;
  sql += `
  LEFT JOIN (
    SELECT 
		  h.batter,
      COUNT(h.batter OR NULL) AS pa,
      COUNT(eb.name IS NULL OR NULL) AS ab,
      COUNT(hi.rst_id IN (2, 3, 4) OR NULL) + COUNT(hi.rst_id = 6 OR NULL) * 2 + COUNT(hi.rst_id = 8 OR NULL) * 3 + COUNT(hi.rst_id = 9 OR NULL) * 4 AS tb,
      CASE WHEN COUNT(eb.name IS NULL OR NULL) > 0 THEN ROUND((COUNT(hi.rst_id IN (2, 3, 4) OR NULL) + COUNT(hi.rst_id = 6 OR NULL) * 2 + COUNT(hi.rst_id = 8 OR NULL) * 3 + COUNT(hi.rst_id = 9 OR NULL) * 4) / COUNT(eb.name IS NULL OR NULL), 5) ELSE null END AS slug_ave
    FROM
        baseball._bat_all_info h
    LEFT JOIN exclude_batting_info eb ON h.\`${bat}_result\` = eb.name
    LEFT JOIN hit_id_info hi ON  h.\`${bat}_rst_id\` = hi.rst_id
    WHERE h.\`${bat}_result\` IS NOT NULL
    GROUP BY batter
  ) AS ${batName} ON ${batName}.batter = pb.id
  `;
});
// -------------------- /[left join part] --------------------

// generate
execute(`${getFilename(__filename)}`, sql);
```

## 実行方法
```bash
$ node average_slugging.js
```

## SQLを生成する利点
- とにかくSQLの修正が容易になる
    - 例えば現在の5カラム(`batter`, `pa`, `ab`, `tb`, `slug_ave`)以外に出力したいカラムがある場合、1箇所を修正するだけで`LEFT JOIN`７箇所全てを反映できる
    - 7箇所全てを少しずつ修正するのは馬鹿らしい
- 新たなSQLを生成するのにも役に立つ
    - 今回はプロ野球選手の各打席（第１打席〜第７打席）の長打率を求めるSQLでしたが、出塁率や打率などのSQLを作成する際はすぐに作成することができる
- 生成したSQLは`.gitignore`に追加することでGitでの管理が不要
    - 生成するこのコードのみ管理することで、コードにおけるSQLの内訳が減る


# まとめ
よくいろんなエンジニアもおっしゃっている**楽をするために苦労する**ということを実践してみました
今後も何かめんどくさいな、と思ったことに対して、楽に何かできることがあれば投稿します

今回Node.jsで書いたこの内容をSQLでできる方法をご存知でしたら、ぜひご教授いただきたいです
最後まで読んでくださりありがとうございました