MySQL中的列转行 这次是真拯救了我,MySQL索引优化,explain讲得非常清楚了 Oracle快速入门(PLSQL编程) MySQL字符串拼接、截取 MySQL事务管理及存储引擎 《浪姐》万茜点赞宁静、郁可唯黑贴坐实?盗号者和程序员是背锅侠 程序员被公司辞退12天后,前领导要求回公司讲清代码,结果懵了 易语言大漠多线程foobar在游戏多窗口中时时显示输出信息 非科班,自学两年,复盘两个月,侥幸拿到头条、阿里offer 2020年全国数学建模大赛B题源代码以及模型的建立 (2) 2020年数学建模-校园供水系统智能管理 windows破解锁屏密码(亲测有效:再也不怕别人锁屏防你啦!) 清华大佬力荐的JVM学习路线+实战笔记+阿里真题,嚼碎吃透吊打面试官 打造多模块+高可用+高扩展Spring Cloud版分布式电商项目源码分享 Python爬虫入门教程 89-100 定个小目标,先用Python爬个一亿B站用户 五大分布式事务,你了解多少? 2020-09-12 九大核心专题,630页内容,熬夜23天吃透,我收割了3个大厂offer 防抖节流 防抖和节流 函数节流 debounce throttle 每天补充一点 一些零碎的小知识点 JavaScript作用域和作用域链知多少 01-jquery概述 简单概述JSONP原理 02-$符号-jquery与js相互转换 遇见 vue.js --------阿文的vue.js学习笔记(2)----- 基本使用 全国鞋服行业首个5G专项应用落地柒牌 自动搬运作业提升效率150% 大数据系统提高生产效率超10% [网络安全自学篇] 九十四.《Windows黑客编程技术详解》之提权技术(令牌权限提升和Bypass UAC) 鸿蒙OS 2.0 开源蹭热浅读 蚂蚁三面滑铁卢!遭分布式截胡,靠这些笔记潜修30天,挺进京东 【高并发】Redis如何助力高并发秒杀系统,看完这篇我彻底懂了!! K8s概述:几种集群方案的对比 Linux到底该怎么学?RHCA架构师整理了300页学习笔记 到了2020年,技术水平到底需要达到怎样的程度才能成为顶级的阿里P8架构师 Linux怎么学?一张思维导图带你深入Linux核心原理 金九银十首战告捷!凭借这份Alibaba爆款“面试宝典”成功斩获美团Offer 大数据杀熟:我投之以元宝,它报之以砍刀! “物联网加持”下的社区长啥样儿? 潘云鹤院士:大数据智能是人工智能2.0的核心组成部分 防小孩和老人走失,定位精度达1厘米?上海社区为先进物联网产品提供落地场景 技术老兵十年专攻MySQL编写了763页核心总结,90MySQL问题全解 【Jenkins自动化部署】Windows节点Apache+Django服务自动化构建 Mybatis 使用通用 mapper 正道的光!阿里爆款Jenkins+K8s笔记终于全网开源了 不要死磕Java并发了,阿里P7架构师带你深入剖析synchronized的实现原理 EtherNet/IP协议基础知识(Part 1) CGB2005-京淘13 思科 OSPF协议简单配置与分析 在一家公司呆了 10 年的程序员,最后都怎么了? 致力物联网芯片研发,奕斯伟计算获逾20亿元融资 Unity性能优化技巧
您的位置:首页 >运维 >

MySQL中的列转行

mysql中的列转行

在工作中遇到的一个MySQL列转行的统计:

场景

用户访问app时会跳出标签选择页面让用户选择喜欢的标签,在数据库中记录的是数组样式的字符串,数据样式大致如下:

iduser_idlike_tagscreate_time11101[“八卦”,“数码”,“财经”]2020-09-01 09:19:5221102[“数码”]2020-09-01 09:23:5231103[“数码”,“影视”]2020-09-01 09:29:52…………

要统计每个标签选择的人数有多少。

分析与解决

like_tags字段存放的是用户喜好的数据,这个问题是数组的字符串,需要列转行,然后进行统计。在网上搜了一下,MySQL没有数组的操作函数和explode函数,有些需要建存储过程和函数来进行统计,但生产上权限的限制问题没法随意建存储过程,而且这个统计看上去很简单。

大致分析了一下数据,进入app选取的标签不会超过8个,最多like_tags会有6个数据,利用一个临时表可以解决问题。

在个人电脑MySQL上建表测试一下

#创建表create table user_tags(id int,like_tags varchar(50));#添加数据insert into user_tags values (1,'["八卦","数码","财经"]');insert into user_tags values (2,'["数码"]');insert into user_tags values (3,'["数码","影视"]');

查询select * from user_tags验证插入的数据

+------+------------------------+| id | like_tags|+------+------------------------+|1 | ["八卦","数码","财经"] ||2 | ["数码"] ||3 | ["数码","影视"]|+------+------------------------+

本来想使用一下with as建临时表,发现不支持,就直接用union all然后子查询的临时表了

SELECTSUBSTRING_INDEX( SUBSTRING_INDEX( a.clean_like_tags, ',', b.help_id + 1 ), ',',- 1 ) AS NAME,COUNT( 1 ) FROM(SELECT REPLACE( REPLACE ( REPLACE ( like_tags, '[', '' ), ']', '' ), '"', '' ) AS clean_like_tags FROMuser_tags ) aLEFT JOIN (SELECT0 AS help_id UNION ALLSELECT1 UNION ALLSELECT2 UNION ALLSELECT3 UNION ALLSELECT4 UNION ALLSELECT5 ) b# 序号小于分隔符,的个数ON b.help_id < ( LENGTH( a.clean_like_tags ) - LENGTH( REPLACE ( a.clean_like_tags, ',', '' ) ) + 1 ) GROUP BYNAME;

结算结果

+------+------------+| NAME | COUNT( 1 ) |+------+------------+| 八卦 |1 || 影视 |1 || 数码 |3 || 财经 |1 |+------+------------+

小结

业务人员需要数据,本来20分钟可以做好的事情,当时花了1个多小时去处理,平时多积累一下这样快速解决问题的小技巧,提升自己的效率。

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。