mysql中的列轉行
在作業中遇到的一個MySQL列轉行的統計：
場景
用戶訪問app時會跳出標簽選擇頁面讓用戶選擇喜歡的標簽，在資料庫中記錄的是陣列樣式的字串，資料樣式大致如下：
| id | user_id | like_tags | create_time |
|---|---|---|---|
| 1 | 1101 | [“八卦”,“數碼”,“財經”] | 2020-09-01 09:19:52 |
| 2 | 1102 | [“數碼”] | 2020-09-01 09:23:52 |
| 3 | 1103 | [“數碼”,“影視”] | 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然后子查詢的臨時表了
SELECT
SUBSTRING_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
FROM
user_tags
) a
LEFT JOIN (
SELECT
0 AS help_id UNION ALL
SELECT
1 UNION ALL
SELECT
2 UNION ALL
SELECT
3 UNION ALL
SELECT
4 UNION ALL
SELECT
5
) b
# 序號小于分隔符,的個數
ON b.help_id < ( LENGTH( a.clean_like_tags ) - LENGTH( REPLACE ( a.clean_like_tags, ',', '' ) ) + 1 )
GROUP BY
NAME;
結算結果
+------+------------+
| NAME | COUNT( 1 ) |
+------+------------+
| 八卦 | 1 |
| 影視 | 1 |
| 數碼 | 3 |
| 財經 | 1 |
+------+------------+
小結
業務人員需要資料，本來20分鐘可以做好的事情，當時花了1個多小時去處理，平時多積累一下這樣快速解決問題的小技巧，提升自己的效率，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/26907.html
標籤：其他