四川長昕建設工程有限公司網(wǎng)站友情鏈接怎么連
這兩天有個數(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個用戶的預約序列。
id | user_id | item_name | department_name | reservation_status | reservation_time | create_time |
---|---|---|---|---|---|---|
1 | 123 | A | a | 1 | 1672588800000 | 1672586522444 |
簡要分析:
- 各用戶預約服務數(shù)不同,采用collect_list以數(shù)組形式存儲在一個字段中
- 部門和服務字段采用concat拼接,由于服務名稱存在標點符號,采用其他的標點符號進行拼接,便于后續(xù)拆分使用
- 要讓預約服務按照時間排序,即轉化為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ù)組元素遍歷比較 讓俺再想想 先這樣