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

106 阅读 0 评论 70 点赞

我是靠谱客的博主微笑摩托，这篇文章主要介绍mysql统计数组字符串格式内元素出现的次数，现在分享给大家，希望可以做个参考。

文章目录

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

一.基本需求

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

表一:user_api_info
在这里插入图片描述
表二:templates

返回数据如下图所示:

echarts显示结果:

二.解决思路

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

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

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[
 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
2
3
4
5
6
7
[
 42, 42, 42, 42, 43, 43, 44,
 44, 45, 42, 42, 42, 42, 42,
 42, 42, 50, 50, 50, 50, 50,
 46
]

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

复制代码

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

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

复制代码

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

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

复制代码

1
2
3
4
5
6
7
8
9
[
 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
2
3
4
5
6
7
8
9
[
 { templateName: '合同模板', usedCount: 1 },
 { templateName: '新增模板12312', usedCount: 1 },
 { templateName: '模板1', usedCount: 5 },
 { templateName: '模板121', usedCount: 11 },
 { templateName: '模板2', usedCount: 2 },
 { templateName: '模板3', usedCount: 2 }
]

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

复制代码

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

三. 详细代码

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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;
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
  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;
        }
    }