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

墨蓝 2023-02-18 01:58 226阅读 0赞

### 文章目录 ###

*   *   *  一.基本需求
         *  二.解决思路
         *  三. 详细代码

### 一.基本需求 ###

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

表一:user\_api\_info  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2_size_16_color_FFFFFF_t_70]  
表二:templates  
![在这里插入图片描述][20200615132208858.png]  
返回数据如下图所示:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2_size_16_color_FFFFFF_t_70 1]  
echarts显示结果:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2_size_16_color_FFFFFF_t_70 2]

### 二.解决思路 ###

`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]' }
    ]

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

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

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

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

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

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

1.  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' }
    ]

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

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

1.  上面的结果已经是很像返回的结果了，但还可以根据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;
            }
        }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020061513224581.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2,size_16,color_FFFFFF,t_70
[20200615132208858.png]: https://img-blog.csdnimg.cn/20200615132208858.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200615132741313.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200615140144110.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2,size_16,color_FFFFFF,t_70