数据业务札记03：行转列进阶-包含字符串数据的行列转换
在实际业务中，我们常会采用sum （case when） group by 的语法来实现行转列。行转列的好处在于对逻辑主键进行关联时，我们可以大大压缩关联后的行数，
仅用数列字段换来多行记录。而常规的行转列中的聚合函数sum仅能处理字段为数值类型的数据。如果我们需要对字符串类型数据进行行转列处理，则需要一定
的技巧。
首先我们来看一个常规的数值类型行转列的例子：
我们有一张学生各科成绩的表
SELECT userid, SUM( CASE subjectName WHEN '语文' THEN score ELSE 0 END ) '语文', SUM( CASE subjectName WHEN '数学' THEN score ELSE 0 END ) '数学', SUM( CASE subjectName WHEN '英语' THEN score ELSE 0 END ) '英语', SUM( CASE subjectName WHEN '政治' THEN score ELSE 0 END ) '政治' FROM tb_score GROUP BY userid;
行转列结果如下：
可以看到这个例子中，我们需要转列的记录是数值型的成绩，字段名则是有限的科目名称。下面我们来看一个更复杂的例子，我们目前有一个记录客户id，
物品id，物品价格，价格排序的表，该表记录的是客户物品价格前五的记录。
我们现在要对物品名称、物品价格进行转列。类似的，对于数值类型的物品价格，我们可以用sum （case when） group by 的语法进行处理。但是对于
字符串类型的物品id，sum函数则无法进行处理。下面先展示对物品价格进行行转列处理的代码和结果：
select cu_id, sum(case when pricerank=1 then item_price else 0 end) rank1_price, sum(case when pricerank=2 then item_price else 0 end) rank2_price, sum(case when pricerank=3 then item_price else 0 end) rank3_price, sum(case when pricerank=4 then item_price else 0 end) rank4_price, sum(case when pricerank=5 then item_price else 0 end) rank5_price from (select cu_id,item_code,item_price,row_number()over(partition by cu_id order by item_price desc) as pricerank from cust_price_info) a group by cu_id
行转列结果：
下面，我们采用一点小技巧，在聚合函数中，我们可以找到max、min是可以兼容字符串数据、数值型数据的。因此这里我们考虑用max来替换sum函数。
下面是具体代码：
select cu_id, max(case when pricerank=1 then item_code else '0' end) rank1_item_code, sum(case when pricerank=1 then item_price else 0 end) rank1_price, max(case when pricerank=2 then item_code else '0' end) rank2_item_code, sum(case when pricerank=2 then item_price else 0 end) rank2_price, max(case when pricerank=3 then item_code else '0' end) rank3_item_code, sum(case when pricerank=3 then item_price else 0 end) rank3_price, max(case when pricerank=4 then item_code else '0' end) rank4_item_code, sum(case when pricerank=4 then item_price else 0 end) rank4_price, max(case when pricerank=5 then item_code else '0' end) rank5_item_code, sum(case when pricerank=5 then item_price else 0 end) rank5_price from (select cu_id,item_code,item_price,row_number()over(partition by cu_id order by item_price desc) as pricerank from cust_price_info) a group by cu_id可以看到，使用max来对字符串类型的物品代码进行处理时，我们是先限定了物品价格的排名，然后如果物品代码比字符串‘0’大，那么max函数返回的就是给定
物品价格排名后，物品代码的结果。值得注意的是，虽然min函数也具有兼容字符串类型和数值类型数据的功能，但在上述代码设定下，给定物品价格的排名，任意
字符串都将大于字符串’0‘，因此最后返回的结果也总会是’0‘，最后将得不到我们需要的结果。除非把字符串零改为一个非常大的字符串，例如’99999999999‘，但是
这样处理下我们会浪费不少内存。因此还是建议是哟max（case when ... then...else ’0‘ end）group by 的用法来实现字符串数据的行转列处理。
下面是我们使用上述逻辑实现的带字符串数据的行转列处理结果。
希望小小的分享可以给大家带来便利~
网易有道公司氛围 38人发布
