vlambda博客
学习文章列表

mysql按照特定字符拆分字段为多行和多列的产品销量拆分案例

SELECT 
 user_type,
 order_products
FROM 
 `商品运营案例`
WHERE
 DATE_FORMAT(order_time,"%Y/%m")="2020/08";

mysql按照特定字符拆分字段为多行和多列的产品销量拆分案例这是什么鬼,原来生产环境中order_products记录的是:产品编码(销量);产品编码(销量);...,那就是要把每单的产品和销量差分开来,然后再汇总。

先看怎么拆分产品和数量吧

有那么一个函数叫SUBSTRING_INDEX()

  • 语法:substring_index("待截取有用部分的字符串","截取数据依据的字符",截取依据的字符的位置N[正常从前往后,若为-1则是从后往前截取依据的字符后面的内容])

有一个mysql的存放参考文档的表mysql.help_topic

select * from mysql.help_topicmysql按照特定字符拆分字段为多行和多列的产品销量拆分案例

拆分产品思路

  1. 按照分号拆分,有多少个分号呢,(length(a.order_products)-length(REPLACE(a.order_products,';',''))) ,即总长度-去除分号的长度。
  2. 每个分号都要拆分一次,也就是位置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"

mysql按照特定字符拆分字段为多行和多列的产品销量拆分案例结果如上,为什么两个分号有6个结果呢,其实我们的一个中文状态下的分号长度是3导致。

  1. 截取每个分号的最后一个产品编号和数量,这样每行就只有唯一不重复的一个产品编号和数量了,当然还有就是最后一个字符是分号的怎会产生空值,所以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)<>""

mysql按照特定字符拆分字段为多行和多列的产品销量拆分案例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"
mysql按照特定字符拆分字段为多行和多列的产品销量拆分案例

接下来就是分类汇总

需求:帮我查一下新客户和老客户分别在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.客户类型
mysql按照特定字符拆分字段为多行和多列的产品销量拆分案例


更多数据分析与运营知识
干货在此,随时学习!