最近数据分析有需求,分析运营活动短信用户,但是发送短信的用户是通过 JSON 字符串数组存储在一个 text 字段的。内容类似于:
["user1", "user2", "user3"....]
数据分析想分析这些用户,那么就需要 in 这些用户查询。自己手动拼 SQL 太蛋疼,而且好几万几十万的用户,拼成SQL,复制粘贴也够蛋疼的。那么可以考虑将这一行分割为多行,作为一个字段。
mysql.help_topic 是啥
网上的思路是利用 mysql.help_topic 这个记录表,这个表是存储 mysql 各种帮助文档目录的,主要因为他有一个从零开始自增的 id 字段,所以采用这张表作为帮助表。其实他不是用来干这个的。并且,有时候我们精简安装,或者是云服务里面的 mysql,他们的这张表里面的内容,是空的,所以我们不能靠这张表。
如何自己实现呢?
思路主要是如下,首先处理数据,将 JSON 字符串数组处理成:
user1,user2,user3
通过:
select replace(replace(replace(replace(a,'[',''),']',''), '"', ''),' ','') processed_data from 表
然后,我们通过substring_index函数,可以提取出user1,user2,user3这些用户 id。分别是:substring_index(substring_index(processed_data,',',1),',',-1),substring_index(substring_index(processed_data,',',2),',',-1),substring_index(substring_index(processed_data,',',3),',',-1).可以看出,如果我们能提供一个数字,这个数字从1开始,一直到,的个数 + 1,这样就能使用substring_index函数,将每个 userId 提取出来,也就是将数据转换成:
+-------------------+----+
| processed_data | id |
| user1,user2,user3 | 1 |
| user1,user2,user3 | 2 |
| user1,user2,user3 | 3 |
哪里有这么一张表呢?我们可以创建一个表,里面只有一列 id,从0或者1开始,这里我们从0开始,一直到你的,可能的最多个数,我们这里是 200 万。
+----+
| id |
+----+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
这样,通过 join 这张表,用 id < ,的个数为条件,就能得出上面的processed_data与idjoin 的数据。
最后的SQL:
SELECT
substring_index( substring_index( processed_data, ',', b.id + 1 ), ',',- 1 ) user_id
FROM
(
SELECT REPLACE
(
REPLACE ( REPLACE ( REPLACE (数据字段, '[', '' ), ']', '' ), '"', '' ),
' ',
''
) processed_data
FROM
表
) temp
JOIN help表 b ON b.id < ( length( temp.processed_data ) - length( REPLACE ( temp.processed_data, ',', '' ) ) + 1 )
其中的 help 表就是里面只有一列 id,从0或者1开始,这里我们从0开始,一直到你的,可能的最多个数的这张表