SQL：MySQL实现侧视图效果

在Hive中，我们通常会用到UDTF函数和侧视图来实现对多标签字符串的拆解和膨胀，以实现列转行的功能。但是在目前我所使用的MySQL 8.0版本中并没有支持这项功能，因为该需求本是面向数据分析应用的，并不适合于MySQL面向于事务处理的系统。

最近想要分析片单记录中各国影片的分布占比，但是有的影片会同时属于多个国家，我通过逗号分隔的字符串来记录到了一个字段中，于是开始研究如何在MySQL中也实现类似于侧视图的效果。

确定膨胀个数
首先需要对”国家”字段按照逗号进行切分，并将切分以后的国家join到对应的片名上去。由于国家名我们不好直接定位，但是逗号我们可以很准确的定位到，如果逗号的个数为N，可见原始的数据膨胀到了N+1条。在拓展到其他场景时，逗号也可以变为分号或其他任意用于分隔数据的符号。
为了确定国家的个数，可以使用字符串替换函数将逗号去除掉，再比较替换前后字符串长度的变化量：

1	LENGTH(x.country) - LENGTH(REPLACE(x.country, ',', '')) + 1

对数据进行膨胀
在确定了膨胀个数以后，我们可以通过非等值连接的方式进行数据膨胀。这里需要使用到一个记录了1～N的辅助表，该表可以提前准备好，也可以借用MySQL中的系统表mysql.help_topic。该表的help_topic_id字段包含了0～699的数列，已经足够本场景下使用了。

help_topic_id	name
0	HELP_DATE
1	HELP_VERSION
…	…
699	MERGE

非等值连接代码如下

SELECT   
    x.video_name,
    x.video_country,
    y.help_topic_id
FROM dim_notiondb_video_list_info AS x  
JOIN mysql.help_topic y  
ON y.help_topic_id < (LENGTH(x.country) - LENGTH(REPLACE(x.country, ',', '')) + 1)

video_name	country	help_topic_id
凡人修仙转	中国	0
海蒂和爷爷	德国,瑞士	0
海蒂和爷爷	德国,瑞士	1
何以为家	黎巴嫩,法国,美国	0
何以为家	黎巴嫩,法国,美国	1
何以为家	黎巴嫩,法国,美国	2

可见每一条数据都根据国家个数被复制了多份，并打上了对应的id标签。

切分指定位置的字符串

最后一步我们只需要根据每条数据对应的id标签切割出指定位置的国家名称即可。这里我们使用substring_index(str, delim, count) 函数来拆分字段，该函数会将字符串str按照delim进行切割，然后保留第第count个分隔符的左边的全部内容。如果count是负数，自会从右边开始数起。
这里需要对特定国名进行切割需要调用两次substring_index函数，第一次先得到该国名及其左边的所有字符串，然后再截取出该字符串右边最后一个国名即可。

1	SUBSTRING_INDEX(SUBSTRING_INDEX(x.country, ',', y.help_topic_id + 1), ',', -1)

完整的代码实现

SELECT   
    x.video_name,  
    SUBSTRING_INDEX(SUBSTRING_INDEX(x.country, ',', y.help_topic_id + 1), ',', -1) AS 'country'
FROM dim_notiondb_video_list_info AS x  
JOIN mysql.help_topic y  
ON y.help_topic_id < (LENGTH(x.country) - LENGTH(REPLACE(x.country, ',', '')) + 1)

本案例可以归结为行列转换的一种，更多有关行列转换的内容可以查看：MySQL中的行转列和列转行 - 墨天轮 (modb.pro)