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

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

<img width=10%><img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/6320d966-dfe6-cd6a-d386-731a76b6060d.png" width=80% >


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

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/d8010ee6-f21d-2edd-9ee7-0d41ca78ad5b.png" width=60%>

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

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

https://qiita.com/Nya-san-H/items/bc13c87b461644b504d5

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

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

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

:arrow_down:**これがゴール**:arrow_down:
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/dc13f40e-b4fb-47cb-da1f-57124827c5b0.png">

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を振る**。

```sql
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で上記クエリを実行した結果◆**
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/7fc407a4-c34c-e2fb-e5d8-62843940bc69.png" width=50%>

### STEP.1-2 node.jsにデータを渡す
上記のクエリを`connection.query()`の第一引数にし、node.jsで集計結果を受け取る。
```javascript
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◆**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/e3026af0-4add-3931-8772-7971476b7cf3.png)

## STEP.2　HTML用のオブジェクト配列をつくる
受け取った配列から要素を取り出して、出力用の新しいオブジェクト配列をつくる。
配列の構造は下図のようなイメージ。key=timeのvalueには、複数の作業時刻を配列にして格納する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/31e16cf2-21fb-f236-19d9-e79639affae7.png)

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

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

- `JSON.stringify(object)`
JavaScriptオブジェクトを取得し、JSON 文字列に変換
- `JSON.parse()`
JSON 文字列を取得し、JavaScriptオブジェクトに変換
```javascript
var jsonArray = JSON.parse(JSON.stringify(results)); 
console.log(jsonArray);
```
**◆結果：コンソールに表示されるjsonArray◆**
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/40212746-11f0-a557-b3d4-d6a0cec8577b.png" width=80%>

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

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

```javascript
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の値◆**
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/d72cbfd9-ae9e-2154-ed25-ead52588a4a7.png" width=80%>


また、特定のｍにおけるcontents、timeの値は、以下の関数を使って取得できる
- `array.filter(関数)`
arrayの各要素に対して与えられた関数を呼び出し、**この関数がtrueを返す要素**を新しい配列として返す
- `array.find(関数)`
配列arrayの各要素に対して与えられた関数を呼び出し、**この関数がtrueを返す最初の要素**を返す。
- `array.map(関数)`
配列arrayのすべての要素に対して与えられた関数を呼び出し、その結果を**新しい配列**として返す

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/9c5373e6-0cfc-938e-2128-8c4f6dd86d59.png)

```javascript
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◆**
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/cb3f74e4-454b-7d95-bcff-ac94dd7ae840.png" width=80%>

## STEP.3 HTMLで作った表に、値を渡す
作成した配列newArrayの値を使って表をつくり、集計結果を可視化する。
今回はHTML tableを使って、シンプルな表を描画。
```html: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>
```

```javascript: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]に表示されるテーブル**
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/cbd85637-b72a-51e6-a50e-387eff0a87c5.png" width=80%>

# 【完成】要素ごとに異なるnで、n回目までのデータを集計・整理するコード例
```javascript:app.js
app.get('/test', (req, res) =>{connection.query(`
  SELECT
      DENSE_RANK() OVER (ORDER BY contents)  AS m, 
      contents, time, user,
      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)); 
    console.log(jsonArray);

    var last = jsonArray[jsonArray.length-1];
    var m_max = last.m;
    console.log(last);
    console.log("m_max = " + m_max);

    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 arrayByContents = jsonArray.filter(o => o.m === i+1);
                    //配列jsonArrayからm=i+1の要素（JSONオブジェクト）を配列で取り出し、配列arrayByContentsを作る
      var userList = arrayByContents.map(o => o.user);
      var timeList = arrayByContents.map(o => o.time);
                     //配列arrayByContentsのすべての要素からuserの値を配列userListに、
                     //timeの値を配列timeListに取得する
      newJson.user = userList;
      newJson.time = timeList;
                     //それぞれの配列をnewJsonの要素として代入
                     
      newArray.push(newJson);
    }
    res.render("test_log.ejs", {dataByContents : newArray});
  });  
})
```

**◆HTMLtableで描画した集計結果◆**
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3825151/5aed8ae7-11ae-042a-d6e8-5039184bc365.png" width=80%>