LoginSignup
1
1

More than 3 years have passed since last update.

@2480jiviolet(Isamu U)

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

Posted at

背景

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

なっがいSQLサンプル

SELECT箇所・LEFT JOIN箇所で多数の重複があり、見やすくするため省略しています

-- 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: カラム名を+で連結した結果の末尾３文字を削除する
// 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);

実行方法

$ 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でできる方法をご存知でしたら、ぜひご教授いただきたいです
最後まで読んでくださりありがとうございました

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1