spark sql中collect_list + partition by的特殊用法

问题的提出

hive或者spark中collect_list一般是用来做分组后的合并，翻一下优快云上的博客，大部分都是写了它和group by连用的情况，而几乎没有和partition by连用的情况，因此本篇特定来讲collect_list + partition by的这个用法。

会发现这个问题也算是一个巧合，它来源于业务上的一个问题：如何求一个用户的续课次数，续课金额？

假如给定这样一个场景：有严格规定的两个课程期次序列：s1,s2,s3,s4和p1,p2,p3，我们规定满足如下条件才算是一个用户的续课：

1. 当课程属于同一课程序列时，必须遵循严格的期次顺序，且不得有重复。如 s1, s3, s4, s2，站在s1的角度看，续课了3次（s3,s4,s2）；站在s3的角度看续课了1次(s4)；
2. 当课程属于不同的课程系列时，只要后面跟的是不同的课程序列都算1次，重复值不算。如s2,p2,p1,s2，站在s2的角度看，续费了2次（p2,p1）；站在p2的角度看，续费了1次（s2）

解决思路

我们先来看一下这题的解决思路：

1. 如果我们先不考虑减少数据量，那么对于用户参加的每一次的课程而言，附加一列生成一个这个用户的全量有序的购课记录就好，只是要记录一下对应的这门课在整个序列中的位置，然后后面的续课期次和续课金额的计算如果实在是不能用sql解决，放在spark rdd里面也是能解决的；
2. 最好的方式肯定是对于每一个用户而言，附加一个该用户在这门课之后有序的选课记录，这里也有两种实现方式：1）自身关联这张购课表一次，找出每一个用户买了这门课之后的所有购课记录和购课金额；2）要是我们实在是不想自关联一次，那我们就要考虑一下如何用其他方式合理的解决这个问题（没想出来这种咋解决）

其实最早在处理的时候，使用collect_list + partition by 本来是想使用了第一种方法的（实在是不想搞自关联，觉得数据量有点太大了），但是没想到误打误撞发现了collect_list一个比较神奇的地方，下章说~

实际上如何解决

好，我们开始考虑如何用上面提出的第一种方案来解决这个问题：

之前不是打算给每一列附加一个全量的购课序吗？那我们就来这么做：
考虑到每一条用户的购课信息必须保留当前购课的期次，时间金额等，我们肯定不能用group by这种操作，考虑行内的分组partition by，又要组成一个序列，那么collect_list肯定不能跑了，那么我们为每一行这么附加一列：

SELECT
         user_id,
         term_id ,
         pay_ime, --这个用来确定当前购买的这一门课处于选课序列中的第几个
         concat(parent_type,'-',sequence) AS current_category,--enroll的序列是啥
         collect_list(concat(parent_type,'-',sequence,'|',pay_time,'|',pay_amount)) OVER (PARTITION BY buyer_id ORDER BY pay_time ASC ) AS category_list --用来确定购课期次序列，购课时间序列和金额等

好，我们现在以为在新列能出现一个用户购课按照升序排列的所有购课记录对不对？

没这样做之前我也是这样想的，然而，它会出现这样的结果：

user_id	term_id	pay_time	current_category	category_list
1	s2	1	s2	s2
1	s3	2	s3	s2,s3
1	s4	3	s4	s2,s3,s4

发现没，实际上collect_list在这种用法下并没有给出一个用户全部的选课序，而是在时间升序的条件下给出了购买时间小于等于这一条课程的购课时间的所有排序，我们当然不想要这种结果，下面这种形式才是我们希望的：

user_id	term_id	pay_time	current_category	category_list
1	s2	1	s2	s2,s3,s4
1	s3	2	s3	s3,s4
1	s4	3	s4	s4

那么如何才能变成我们想要的这种情形呢？

虽然不能完全一摸一样，但是我们可以搞一个变种（因为实际上我们只需要每一个用户购买某一门课后的其他课程期次，不一定是list类型的，我们可以让它有多条），如果只是变种，那就很简单了，我们只需要让其变成一个降序排列就好了：

SELECT
         user_id,
         term_id ,
         pay_ime, --这个用来确定当前购买的这一门课处于选课序列中的第几个
         concat(parent_type,'-',sequence) AS current_category,--enroll的序列是啥
         collect_list(concat(parent_type,'-',sequence,'|',pay_time,'|',pay_amount)) OVER (PARTITION BY buyer_id ORDER BY pay_time desc ) AS category_list --用来确定购课期次序列，购课时间序列和金额等

这样出来的结果其实是这样的：

user_id	term_id	pay_time	current_category	category_list
1	s2	1	s2	s4,s3,s2
1	s3	2	s3	s4,s3
1	s4	3	s4	s4

瞧，它是一个降序的！拿到的都是这条用户购课之后的课程，只是反序排了而已，这样也没关系，我们后续可以用lateral view explode 把这个东西展开，过滤后再进行计算！

这样，我们原来遇到的最大的问题就解决了，至于怎么去计算续课期次和金额的问题 count函数和sum函数中使用if就可以搞定啦～

collect_list的这个用法不知道有没有官方的文档啥的，如果有小伙伴看到这个求发个链接，感谢感谢～