[hive SQL] 预约业务线

这两天有个数据需求，记录一下。 原始需求说明产品写得很乱不清晰确认了半天无语死了（开始骂人），直接列转换后的问题了

问题1：
现有一张办事预约服务记录表reservation_order，包含字段用户id、服务名称、服务所属部门、预约状态（1已成功 2已取消）、预约到场时间时间戳、预约申请创建时间时间戳等（简单示例如下）。现为了优化预约服务指引，产品打算基于用户预约记录找出各办事预约事项相关联的业务线。筛选在2023年1月至2023年3月预约未取消数量大于等于2次的用户，列出预约未取消数cnt、按照预约时间顺序列出预约的各部门服务及时间列表item_detail_list, 根据预约未取消数量降序排序筛选前300个用户的预约序列。

id	user_id	item_name	department_name	reservation_status	reservation_time	create_time
1	123	A	a	1	1672588800000	1672586522444

简要分析：

1. 各用户预约服务数不同，采用collect_list以数组形式存储在一个字段中
2. 部门和服务字段采用concat拼接，由于服务名称存在标点符号，采用其他的标点符号进行拼接，便于后续拆分使用
3. 要让预约服务按照时间排序，即转化为collect_list内部元素排序问题。采用distribute by 和sort by对元素进行分布和排序。对user_id进行分散分布到各个reduce，在各 reduce里进行sort by排序。（参考具体说明：Hive：distribute by与group by，order by与sort by , cluster by的区别

代码如下：

select user_id, item_detail, item_list, cnt 
from
(select user_id, collect_list(concat(item,':',create_time)) as item_detail, collect_list(item) as item_list, count(1) as cnt 
from
(select user_id, concat(department_name,'#',item_name) as item,
from_unixtime(cast((reservation_time+28800000)/1000 as bigint), 'yyyy-MM-dd HH:mm:ss') as create_time
from reservation_order
where reservation_time >= 1672502400000
and reservation_time < 1680278400000
and reservation_status != '2'
distribute by user_id
sort by user_id, create_time)a
group by user_id)b
where cnt >= 2
order by cnt desc
limit 300;

遇到新问题及改进
在把这张表推送到mysql数据库的时候发生错误。由于collect_list得到的结果是array类型的，mysql没有对应的类型，平台试图对item_list字段进行cast as string操作但是报错失败，无法直接做cast。

• 新问题：如何把array类型转化为string类型
• 解决方法：采用concat_ws对array类型字段按元素拆分再用分隔符连接起来（由于item_name的值有包含逗号分号，就采用了其他的）

代码修改如下：

select user_id, concat_ws('|',item_detail) as item_detail, concat_ws('|',item_list) as item_list, cnt 
from
(select user_id, collect_list(concat(item,':',create_time)) as item_detail, collect_list(item) as item_list, count(1) as cnt 
from
(select user_id, concat(department_name,'#',item_name) as item,
from_unixtime(cast((reservation_time+28800000)/1000 as bigint), 'yyyy-MM-dd HH:mm:ss') as create_time
from reservation_order
where reservation_time >= 1672502400000
and reservation_time < 1680278400000
and reserve_status != '2'
distribute by user_id
sort by user_id, create_time)a
group by user_id)b
where cnt >= 2
order by cnt desc
limit 300;

---

问题2：
基于上一阶段得到的item_list,列出重合的业务线及重合次数。根据重合次数进行倒序排序，取前30条业务线。

简要分析：（还没尝试用hsql写，只想到用python的方法，由短到长排序后就用in或者把业务线先转化为数组，然后再去数组元素遍历比较 让俺再想想 先这样