【MySQL＋Node.js】要素ごとに異なるｎを使って、1回目～ｎ回目の値を集計表示する方法

Last updated at 2024-08-29Posted at 2024-08-29

作業記録テーブルのcontents要素について、ｎ回目までの作業データを集計・整理したい（※ただしｎ≠一定）

作業記録テーブル(`test_log`)の概要

特徴

カラムはid、user(作業者)、contents(作業内容)、time(作業時刻)、upload_time(記録した時刻)
同じ作業内容が複数回行われるが、その際の作業者・作業時刻はランダムに決まっている
記録した時刻順にidが振られるため、レコードの順序はupload_timeの時系列と一致するが、実際の作業時刻であるtimeの時系列とは必ずしも一致していない
各作業内容の実行回数にはバラつきがある

MySQLのみで集計する方法（※nは固定値）

このデータに対して『contentsカラムの各要素（作業内容）ごとのn回目までの作業データを、MySQLのWINDOW関数のみで集計する方法』を別記事に記載済み。

この記事ではSELECT文にNTH_VALUE() OVER (PARTITION BY ~ ORDER BY ROWS BETWEEN)を用いて集計した。
それぞれの作業内容が行われる回数にはバラつきがあるので、最も多く行われた作業回数に合わせてどの作業も４回目までを表示するようにSELECT文で指定している。（work1とwork2は３回目までしか実行されてないので、４回目の欄ではnullとなる。）

「今回は4回目までで済んたが、もし10回目まで必要なときはSELECT文にNTH_VALUE()を何度も書き連ねることになるのか…」
という点が気にっなたので、work1とwork2は３回目までwork3は４回目までを自動で集計できるような、n可変バージョンを作成した。

【MySQL+node.js】nの値を変えながらn回目までの時刻を集計表示する

今回はwork1とwork2は３回目まで、work3は４回目までの時刻を表示するようにデータの内容に合わせて 「n回目」のnの値を変えながら 表示したい。

これがゴール

MySQLでの変数定義なども可能らしいが初心者には未知の世界だったので、とりあえず今回はMySQLから受け取ったデータをjavascriptで処理する方法を選択。

STEP.1　MySQLで集計したデータをnode.jsで受け取る

STEP.1-1 MySQLクエリ文の内容

まずMySQLのクエリ文にはn＝固定値のときと同じくOVER PARTITION BY ... ORDER BY ... を使って、contentsごとに分けてtimeの時刻順にデータを並べ替える。
さらにDENSE_RANK() OVER (ORDER BY contents)を追加し、contentsの各要素に番号mを振る。

SELECT
    DENSE_RANK() OVER (ORDER BY contents)  AS m, 
    contents, time, 
    COUNT(*) OVER w AS n
FROM
    test_log
WINDOW
    w AS (
    PARTITION BY contents 
    ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

◆MySQLで上記クエリを実行した結果◆

STEP.1-2 node.jsにデータを渡す

上記のクエリをconnection.query()の第一引数にし、node.jsで集計結果を受け取る。

app.get('/testpage', (req, res) => {
  connection.query(`
    SELECT
        DENSE_RANK() OVER (ORDER BY contents)  AS m, 
        contents, time,
        COUNT(*) OVER w AS n
    FROM
        test_log
    WINDOW
        w AS (
            PARTITION BY contents ORDER BY time 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);`,
    (error, results) => {
        console.log(results);
        res.json({data : results});
    });
});

node.jsが受け取るresultsの中身は、10個のRowDataPacketを要素に持つオブジェクト配列になっている。

◆コンソールに表示されるresults◆

STEP.2　HTML用のオブジェクト配列をつくる

受け取った配列から要素を取り出して、出力用の新しいオブジェクト配列をつくる。
配列の構造は下図のようなイメージ。key=timeのvalueには、複数の作業時刻を配列にして格納する。

STEP.2-1 RowDataPacketをJSON形式へ変換する

resultsの配列要素であるRowDataPacketは、見た目はJSONだけどJSONではないらしい。このままでは困るので、まずはJSONへの変換を行う。

JSON.stringify(object)
JavaScriptオブジェクトを取得し、JSON 文字列に変換
JSON.parse()
JSON 文字列を取得し、JavaScriptオブジェクトに変換

var jsonArray = JSON.parse(JSON.stringify(results)); 
console.log(jsonArray);

◆結果：コンソールに表示されるjsonArray◆

STEP.2-2 Arrayメソッドを使って要素を取り出し、新しい配列に代入する

HTMLに渡す配列newArrayをつくるために、まずmの最大値（contents要素の個数）を取得しておく。mの値はMySQLクエリのDENSE_RANKによって決まっているので、配列の最後の要素におけるmの値を調べれば良い。

var last = jsonArray[jsonArray.length-1]; 
        //配列jsonArrayの最後の要素(JSONオブジェクト)を取り出す
console.log(last);

var m_max = last.m; //取り出したJSONオブジェクトlastのmの値
console.log("m_max=" + m_max);

◆結果：コンソールに表示されるlastとm_maxの値◆

また、特定のｍにおけるcontents、timeの値は、以下の関数を使って取得できる

array.filter(関数)
arrayの各要素に対して与えられた関数を呼び出し、この関数がtrueを返す要素を新しい配列として返す
array.find(関数)
配列arrayの各要素に対して与えられた関数を呼び出し、この関数がtrueを返す最初の要素を返す。
array.map(関数)
配列arrayのすべての要素に対して与えられた関数を呼び出し、その結果を新しい配列として返す

下記のコードでは、特定のmにおいてcontentsの値、timeの値（配列）を取得し、それらを要素とするオブジェクトnewJsonをつくり、さらにこれをnewArrayの要素に追加する。この作業をfor文で回して、mのすべての値で同じ作業を繰り返している。↓こんなイメージ↓

var newArray = [];
    for(let i=0; i<m_max; i++){
      var newJson = {};
      newJson.contents = jsonArray.find(o => o.m === i+1).contents;
                    //配列jsonArrayを検索してm=i+1を満たす最初の要素（JSONオブジェクト）を取り出し、
                    //contentsの値を取得する
      var timeList = jsonArray.filter(o => o.m === i+1).map(o => o.time);
                     //配列jsonArrayからm=i+1の要素（JSONオブジェクト）を配列で取り出し、
                     //その配列のすべての要素から、timeの値を配列timeListに取得する
      newJson.time = timeList;
      
      newArray.push(newJson);  //newArrayに配列要素として追加
    }
console.log(newArray);

◆結果：コンソールに表示されるnewArray◆

STEP.3 HTMLで作った表に、値を渡す

作成した配列newArrayの値を使って表をつくり、集計結果を可視化する。
今回はHTML tableを使って、シンプルな表を描画。

test_log.ejs

<html>
<--   省略   -->
    <body>
        <table>
          <% contentsAndTime.forEach(data => { %>
            <tr>
              <th>作業内容</th>
              <% for(let i=0; i<data.time.length; i++){ %>
                <th><%= i+1 %>回目</th> 
              <% } %>
            </tr>
            <tr>
              <td><%= data.contents %></td>
              <% for(let i=0; i<data.time.length; i++){ %>
                <td><%= data.time[i] %></td>  
              <% } %>
            </tr>
          <% }); %>
        </table>
    </body>
</html>

app.js

app.get('/test', (req, res) =>{connection.query(`
  SELECT
      DENSE_RANK() OVER (ORDER BY contents)  AS m, 
      contents, time,
      COUNT(*) OVER w AS n
  FROM
      test_log
  WINDOW
      w AS (
        PARTITION BY contents ORDER BY time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);`,
  (error, results) => {
    var jsonArray = JSON.parse(JSON.stringify(results)); 
    var last = jsonArray[jsonArray.length-1];
    var m_max = last.m;
    var newArray = [];
    
    for(let i=0; i<m_max; i++){
      var newJson = {};
      newJson.contents = jsonArray.find(o => o.m === i+1).contents;
      var timeList = jsonArray.filter(o => o.m === i+1).map(o => o.time);
      newJson.time = timeList;
      
      newArray.push(newJson);
    }
    
    res.render("test_log.ejs", {contentsAndTime : newArray});
  });  
})

◆結果：Webページ[…/test]に表示されるテーブル

【完成】要素ごとに異なるnで、n回目までのデータを集計・整理するコード例