mysql统计数组字符串格式内元素出现的次数

一.基本需求

基本需求:有两张表user_api_info和templates如下图。现在需要根据templatedUsed这列的数据，统计每个template的使用量，供echarts显示。

表一:user_api_info

表二:templates

返回数据如下图所示:

echarts显示结果:

二.解决思路

templatedUsed这列是varchar类型，所以看上去是数组，实际上存的还是字符串(存的时候使用的是JSON.stringfy(arr))。
感觉以纯sql语句解决不了，于是我想出了一个比较巧妙的方式:

1. sql语句查询出templatedUsed这列全部的数据

[
 TextRow { templatesUsed: '[42]' },
 TextRow { templatesUsed: '[42,42]' },
 TextRow { templatesUsed: '[42,43]' },
 TextRow { templatesUsed: '[43,44]' },
 TextRow { templatesUsed: '[44,45]' },
 TextRow { templatesUsed: '[42,42]' },
 TextRow { templatesUsed: '[42,42]' },
 TextRow { templatesUsed: '[42,42]' },
 TextRow { templatesUsed: '[42]' },
 TextRow { templatesUsed: '[50,50,50,50,50]' },
 TextRow { templatesUsed: '[46]' }
]

2. 将每一个数组字符串转为数组，再将这些小数组拼接为一个大的数组。

[
 42, 42, 42, 42, 43, 43, 44,
 44, 45, 42, 42, 42, 42, 42,
 42, 42, 50, 50, 50, 50, 50,
 46
]

3. 统计数组内每个数值出现的次数

{ '42': 11, '43': 2, '44': 2, '45': 1, '46': 1, '50': 5 }

4. 取出这个对象的所有key，拼接为数组

[ '42', '43', '44', '45', '46', '50' ]

5. sql语句查出这个数组对应的模板名字

[
 TextRow { id: 45, name: '合同模板' },
 TextRow { id: 46, name: '新增模板12312' },
 TextRow { id: 50, name: '模板1' },
 TextRow { id: 42, name: '模板121' },
 TextRow { id: 43, name: '模板2' },
 TextRow { id: 44, name: '模板3' }
]

6. 将上面模板名称和次数重新组合

[
 { templateName: '合同模板', usedCount: 1 },
 { templateName: '新增模板12312', usedCount: 1 },
 { templateName: '模板1', usedCount: 5 },
 { templateName: '模板121', usedCount: 11 },
 { templateName: '模板2', usedCount: 2 },
 { templateName: '模板3', usedCount: 2 }
]

7. 上面的结果已经是很像返回的结果了，但还可以根据usedCount排序，返回一个有序数组

[
 { templateName: '合同模板', usedCount: 1 },
 { templateName: '新增模板12312', usedCount: 1 },
 { templateName: '模板2', usedCount: 2 },
 { templateName: '模板3', usedCount: 2 },
 { templateName: '模板1', usedCount: 5 },
 { templateName: '模板121', usedCount: 11 }
]

三. 详细代码

class Util {
    //统计数组内每个数值出现的次数
    arrNumber(arr) {
        var arr_number = {};
        for (var i = 0; i < arr.length; i++) {
            if (arr_number[arr[i]]) {
                arr_number[arr[i]]++
            }
            else {
                arr_number[arr[i]] = 1;
            }
        }
        return arr_number;
    }
    
    //根据数组内对象的某个属性排序
    compare(property){
        return function(a,b){
            var value1 = a[property];
            var value2 = b[property];
            return value1 - value2;
        }
    }
}
module.exports = exports = new Util();

  async getTemplatesUsedInfo(ctx) {
        let conn;
        const res = { ...ctx.errCode.SUCCESS };
        try {
            conn = await ctx.db.getConnection();
             //1.sql语句查询出templatedUsed这列全部的数据
            const result = await ctx.db.query(conn, `SELECT templatesUsed FROM bill_segmentation_ocr.user_api_info`);
            var arr = [];
            var templatesIds = [];
            var templatesUsedInfo = [];
            //2.将每一个数组字符串转为数组，再将这些小数组拼接为一个大的数组。
            for (let item of result) {
                arr = arr.concat(JSON.parse(item.templatesUsed))
            }
            //3.统计数组内每个数值出现的次数
            arr = util.arrNumber(arr);
            //4.取出这个对象的所有key，拼接为数组
            for (let key in arr) {
                templatesIds.push(key);
            }
            //5.sql语句查出这个数组对应的模板名字
            const result1 = await ctx.db.query(conn, `select id,name from templates where id in (?)`, [templatesIds]);
            //6.将上面模板名称和次数重新组合
            for (let i = 0; i < templatesIds.length; i++) {
                let obj = {};
                obj.templateName = result1[i].name
                obj.usedCount = arr[result1[i].id]
                templatesUsedInfo.push(obj)
            }
            //7.根据usedCount排序，返回一个有序数组
            templatesUsedInfo.sort(util.compare('usedCount'))
            console.log(templatesUsedInfo)
            res.data = { "templatesUsedInfo": templatesUsedInfo }
        } catch (error) {
            ctx.logger.error(error);
            res = { ...ctx.errCode.INTERNAL_SERVER_ERROR };
            if (ctx.app.env === 'dev') {
                res.data = error.toString();
            }
        } finally {
            ctx.db.releaseConnection(conn);
            ctx.body = res;
        }
    }