mysql按照特定字符拆分字段为多行和多列的产品销量拆分案例
SELECT
user_type,
order_products
FROM
`商品运营案例`
WHERE
DATE_FORMAT(order_time,"%Y/%m")="2020/08";
这是什么鬼,原来生产环境中order_products记录的是:产品编码(销量);产品编码(销量);...,那就是要把每单的产品和销量差分开来,然后再汇总。
先看怎么拆分产品和数量吧
有那么一个函数叫SUBSTRING_INDEX()
-
语法:substring_index("待截取有用部分的字符串","截取数据依据的字符",截取依据的字符的位置N[正常从前往后,若为-1则是从后往前截取依据的字符后面的内容])
有一个mysql的存放参考文档的表mysql.help_topic
select * from mysql.help_topic
拆分产品思路
-
按照分号拆分,有多少个分号呢,(length(a.order_products)-length(REPLACE(a.order_products,';',''))) ,即总长度-去除分号的长度。 -
每个分号都要拆分一次,也就是位置1拆分1次,位置2拆分1次,...,
SELECT
a.user_type as 客户类型,
a.order_products as 订单产品,
b.help_topic_id+1,
SUBSTRING_INDEX(a.order_products, ';', b.help_topic_id+1)
FROM
商品运营案例 AS a
JOIN
mysql.help_topic AS b
ON
b.help_topic_id<(length(a.order_products)-length(REPLACE(a.order_products,';','')))
WHERE
DATE_FORMAT(order_time,"%Y/%m")="2020/08"
结果如上,为什么两个分号有6个结果呢,其实我们的一个中文状态下的分号长度是3导致。
-
截取每个分号的最后一个产品编号和数量,这样每行就只有唯一不重复的一个产品编号和数量了,当然还有就是最后一个字符是分号的怎会产生空值,所以where条件中应该去除这部分。
SELECT
a.user_type as 客户类型,
a.order_products as 订单产品,
b.help_topic_id+1,
SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1),';',-1) AS 产品编号_num
FROM
商品运营案例 AS a
JOIN
mysql.help_topic AS b
ON
b.help_topic_id<(length(a.order_products)-length(REPLACE(a.order_products,';','')))
WHERE
DATE_FORMAT(order_time,"%Y/%m")="2020/08"
AND
SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1),';',-1)<>""
4. 接下来就是要把小括号中的产品和数量差分为两列。其中产品编号我们再来一次SUBSTRING_INDEX(产品编号_num,"(",1);产品数量等于replace(SUBSTRING_INDEX(产品编号_num,"(",-1),")","")
拆分产品的完整代码如下
SELECT
a.user_type as 客户类型,
a.order_products as 订单产品,
-- b.help_topic_id+1,
SUBSTRING_INDEX(
SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1)
,';',-1) AS 产品编号_num,
SUBSTRING_INDEX(
SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1),';',-1)
,"(",1) AS 产品编号,
replace(
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1),';',-1),"(",-1)
,")","") AS 产品数量
FROM
商品运营案例 AS a
JOIN
mysql.help_topic AS b
ON
b.help_topic_id<(length(a.order_products)-length(REPLACE(a.order_products,';','')))
WHERE
SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1),';',-1)<>""
AND
DATE_FORMAT(order_time,"%Y/%m")="2020/08"
接下来就是分类汇总
需求:帮我查一下新客户和老客户分别在8月份买了多少A-J产品
SELECT
T1.客户类型,
COUNT(产品编号) AS 订单数,
SUM(产品数量) AS 产品数量
FROM
(
SELECT
a.user_type as 客户类型,
a.order_products as 订单产品,
-- b.help_topic_id+1,
SUBSTRING_INDEX(
SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1)
,';',-1) AS 产品编号_num,
SUBSTRING_INDEX(
SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1),';',-1)
,"(",1) AS 产品编号,
replace(
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1),';',-1),"(",-1)
,")","") AS 产品数量
FROM
商品运营案例 AS a
JOIN
mysql.help_topic AS b
ON
b.help_topic_id<(length(a.order_products)-length(REPLACE(a.order_products,';','')))
WHERE
SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1),';',-1)<>""
AND
DATE_FORMAT(order_time,"%Y/%m")="2020/08"
AND
SUBSTRING_INDEX(
SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, ';',b.help_topic_id+1),';',-1)
,"(",1) = "A-J"
) AS T1
GROUP BY
T1.客户类型