myql如何實現行轉列統計查詢

182次閱讀

共計 4905 個字符，預計需要花費 13 分鐘才能閱讀完成。

這篇文章主要介紹了 myql 如何實現行轉列統計查詢，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

1 原始數據
— —————————-
— Table structure for `t_bm_repeat_purchase`
— —————————-
DROP TABLE IF EXISTS `t_bm_repeat_purchase`;
CREATE TABLE `t_bm_repeat_purchase` (
`months` int(2) DEFAULT NULL COMMENT 月份 ,
`total` bigint(21) NOT NULL DEFAULT 0 COMMENT 查詢月份對應的下一個月后或幾個月后的購買用戶數 ,
`seq` bigint(20) DEFAULT NULL COMMENT 序列號 ,
`next_months` bigint(4) DEFAULT NULL COMMENT months 字段對應的第幾個月后，1 月后，2 月后，3 月后。。。
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

— —————————-
— Records of t_bm_repeat_purchase
— —————————-
INSERT INTO `t_bm_repeat_purchase` VALUES (1 , 1170 , 2 , 2
INSERT INTO `t_bm_repeat_purchase` VALUES (1 , 2144 , 2 , 3
INSERT INTO `t_bm_repeat_purchase` VALUES (1 , 1012 , 2 , 4
INSERT INTO `t_bm_repeat_purchase` VALUES (1 , 873 , 2 , 5
INSERT INTO `t_bm_repeat_purchase` VALUES (1 , 785 , 2 , 6
INSERT INTO `t_bm_repeat_purchase` VALUES (1 , 1008 , 2 , 7
INSERT INTO `t_bm_repeat_purchase` VALUES (1 , 773 , 2 , 8
INSERT INTO `t_bm_repeat_purchase` VALUES (2 , 1446 , 2 , 3
INSERT INTO `t_bm_repeat_purchase` VALUES (2 , 700 , 2 , 4
INSERT INTO `t_bm_repeat_purchase` VALUES (2 , 665 , 2 , 5
INSERT INTO `t_bm_repeat_purchase` VALUES (2 , 533 , 2 , 6
INSERT INTO `t_bm_repeat_purchase` VALUES (2 , 694 , 2 , 7
INSERT INTO `t_bm_repeat_purchase` VALUES (2 , 551 , 2 , 8
INSERT INTO `t_bm_repeat_purchase` VALUES (3 , 1530 , 2 , 4
INSERT INTO `t_bm_repeat_purchase` VALUES (3 , 1273 , 2 , 5
INSERT INTO `t_bm_repeat_purchase` VALUES (3 , 1062 , 2 , 6
INSERT INTO `t_bm_repeat_purchase` VALUES (3 , 1367 , 2 , 7
INSERT INTO `t_bm_repeat_purchase` VALUES (3 , 1044 , 2 , 8
INSERT INTO `t_bm_repeat_purchase` VALUES (4 , 1035 , 2 , 5
INSERT INTO `t_bm_repeat_purchase` VALUES (4 , 775 , 2 , 6
INSERT INTO `t_bm_repeat_purchase` VALUES (4 , 949 , 2 , 7
INSERT INTO `t_bm_repeat_purchase` VALUES (4 , 790 , 2 , 8
INSERT INTO `t_bm_repeat_purchase` VALUES (5 , 939 , 2 , 6
INSERT INTO `t_bm_repeat_purchase` VALUES (5 , 1304 , 2 , 7
INSERT INTO `t_bm_repeat_purchase` VALUES (5 , 1066 , 2 , 8
INSERT INTO `t_bm_repeat_purchase` VALUES (6 , 1110 , 2 , 7
INSERT INTO `t_bm_repeat_purchase` VALUES (6 , 899 , 2 , 8
INSERT INTO `t_bm_repeat_purchase` VALUES (7 , 1589 , 2 , 8

要變成

2 用動態查詢：

SET @EE=
set @str_tmp=
SELECT @EE:=CONCAT(@EE, SUM(IF(next_months=\ ,next_months, \ , ,total,null)) AS ,next_months, , ) as aa into @str_tmp
FROM (SELECT DISTINCT next_months FROM t_bm_repeat_purchase) A order by length(aa) desc limit 1;
SET @QQ=CONCAT(SELECT t_bm_repeat_purchase.months, ,left(@str_tmp,char_length(@str_tmp)-1), FROM t_bm_repeat_purchase GROUP BY months
PREPARE stmt FROM @QQ;
EXECUTE stmt ;
deallocate prepare stmt;

動態查詢結果：這不是最終我們想要的，舍棄這種查詢方法，因為前面為空的數據，還要將后面的數據整體向左平移

3 用靜態查詢

SELECT t.months,
IF(0 num,NULL,SUBSTRING_INDEX(total, , , 1)) AS 1 ,
IF(1 num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 2), , ,-1)) AS 2 , — 這個是算取第 1 個數
IF(2 num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 3) , , ,-1)) AS 3 , — 取第 2 個數
IF(3 num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 4) , , ,-1)) AS 4 , — 取第三個數
IF(4 num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 5) , , ,-1)) AS 5 ,
IF(5 num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 6) , , ,-1)) AS 6 ,
IF(6 num,NULL,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 7) , , ,-1)) AS 7 ,
IF(7 num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 8) , , ,-1)) AS 8 ,
IF(8 num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 9) , , ,-1)) AS 9 ,
IF(9 num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 10) , , ,-1)) AS 10 ,
IF(10 num,NULL, SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 11) , , ,-1)) AS 11
FROM
(SELECT a.months,
CHAR_LENGTH(GROUP_CONCAT(a.total))-CHAR_LENGTH(replace(GROUP_CONCAT(a.total), , , )) as num, — 這個是算每個月有幾個逗號
GROUP_CONCAT(a.total ORDER BY a.next_months) AS total
FROM t_bm_repeat_purchase a
GROUP BY a.months ) t;

SELECT a.months,
CHAR_LENGTH(GROUP_CONCAT(a.total))-CHAR_LENGTH(replace(GROUP_CONCAT(a.total), , , )) as num, — 這個是算每個月有幾個逗號
GROUP_CONCAT(a.total ORDER BY a.next_months) AS total
FROM t_bm_repeat_purchase a
GROUP BY a.months 這個語句下查詢結果：

對其進行優化

SELECT t.months,
IF(num =1,SUBSTRING_INDEX(total, , , 1),NULL) AS 1 ,
IF(num =2,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 2), , ,-1) ,NULL) AS 2 ,
IF(num =3,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 3) , , ,-1),NULL) AS 3 ,
IF(num =4,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 4) , , ,-1),NULL) AS 4 ,
IF(num =5,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 5) , , ,-1),NULL) AS 5 ,
IF(num =6,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 6) , , ,-1),NULL) AS 6 ,
IF(num =7,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 7) , , ,-1),NULL) AS 7 ,
IF(num =8,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 8) , , ,-1),NULL) AS 8 ,
IF(num =9,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 9) , , ,-1),NULL) AS 9 ,
IF(num =10,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 10) , , ,-1),NULL) AS 10 ,
IF(num =11,SUBSTRING_INDEX(SUBSTRING_INDEX(total, , , 11) , , ,-1),NULL) AS 11
FROM
(SELECT a.months,
COUNT(*) as num, — 這邊取每個月分別有多少個數據
GROUP_CONCAT(a.total ORDER BY a.next_months) AS total
FROM t_bm_repeat_purchase a
GROUP BY a.months) t;