這兩天有個數(shù)據(jù)需求，記錄一下。 原始需求說明產品寫得很亂不清晰確認了半天無語死了（開始罵人），直接列轉換后的問題了

問題1：
現(xiàn)有一張辦事預約服務記錄表reservation_order，包含字段用戶id、服務名稱、服務所屬部門、預約狀態(tài)（1已成功 2已取消）、預約到場時間時間戳、預約申請創(chuàng)建時間時間戳等（簡單示例如下）?，F(xiàn)為了優(yōu)化預約服務指引，產品打算基于用戶預約記錄找出各辦事預約事項相關聯(lián)的業(yè)務線。篩選在2023年1月至2023年3月預約未取消數(shù)量大于等于2次的用戶，列出預約未取消數(shù)cnt、按照預約時間順序列出預約的各部門服務及時間列表item_detail_list, 根據(jù)預約未取消數(shù)量降序排序篩選前300個用戶的預約序列。

簡要分析：

1. 各用戶預約服務數(shù)不同，采用collect_list以數(shù)組形式存儲在一個字段中
2. 部門和服務字段采用concat拼接，由于服務名稱存在標點符號，采用其他的標點符號進行拼接，便于后續(xù)拆分使用
3. 要讓預約服務按照時間排序，即轉化為collect_list內部元素排序問題。采用distribute by 和sort by對元素進行分布和排序。對user_id進行分散分布到各個reduce，在各 reduce里進行sort by排序。（參考具體說明：Hive：distribute by與group by，order by與sort by , cluster by的區(qū)別

代碼如下：

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數(shù)據(jù)庫的時候發(fā)生錯誤。由于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,列出重合的業(yè)務線及重合次數(shù)。根據(jù)重合次數(shù)進行倒序排序，取前30條業(yè)務線。

簡要分析：（還沒嘗試用hsql寫，只想到用python的方法，由短到長排序后就用in或者把業(yè)務線先轉化為數(shù)組，然后再去數(shù)組元素遍歷比較 讓俺再想想 先這樣