Hive_保证concat_ws内部有序

最新推荐文章于 2023-09-21 11:08:08 发布

灿彬垂死挣扎ing

最新推荐文章于 2023-09-21 11:08:08 发布

阅读量1.2k

点赞数 1

文章标签： hive 大数据 hadoop

本文链接：https://blog.csdn.net/qq_38294275/article/details/125552145

版权

业务背景：同个库存组织下同颗料号：可能会被多个机型使用

备注：ratio_num = 当前机型使用该物料的数量/所有机型使用该物料的总数量，cnt_model：该物料一共被几个机型使用
在这里插入图片描述

我要什么：一行数据表示即可：库存组织+物料+各机型使用详情

备注：并且需要按使用情况，降序排序（看看是哪个冤大头=_=，用了最多…）
在这里插入图片描述

怎么做：多行，聚合，但怎么保证使用：concat_ws后，详情字段内部还是有序的呢？

先对配比（ratio_num）排序，得到序号123…
将序号和字段拼接起来
使用sort_array
再用concat_ws（多行聚合一行）
再用正则替换掉数字

发现不对劲… 因为如果序号在1-9，就没问题；但如果超过了10，因为是按字典序排序，则不是我们想要的降序了…
在这里插入图片描述

解决：将序号补齐，且固定为四位数字 & lpad(oid,4,‘0’)即可

最后：附上SQL

SELECT
     t1.period_tag -- 属于哪个时间段
    ,t1.ORGANIZATION_ID
    ,t1.ITEM_CODE
    ,t1.cnt_model -- 归属产品使用个数
    ,regexp_replace(concat_ws('；',sort_array(collect_set(concat_ws(':',cast(lpad(oid,4,'0') as string),t1.model_use_dtl)))),'\\d+\:','') as model_use_dtl
FROM(
    SELECT
         t1.period_tag -- 属于哪个时间段
        ,t1.ORGANIZATION_ID
        ,t1.ITEM_CODE
        ,t1.cnt_model -- 归属产品使用个数
        ,t1.ratio_num
        ,t1.model_use_dtl 
        ,t1.item_model_ratio -- 该机型使用占比（最大值）
        ,t1.oid -- 倒叙排序
    FROM(
        SELECT
             t1.period_tag -- 属于哪个时间段
            ,t1.ORGANIZATION_ID
            ,t1.ITEM_CODE
            ,t1.cnt_model -- 归属产品使用个数
            ,round(t1.item_model_ratio*100,2) ratio_num
            ,concat(t1.PRODUCT_MODEL,'：',concat(round(t1.item_model_ratio*100,2),'%')) model_use_dtl
            ,max(t1.item_model_ratio) item_model_ratio -- 该机型使用占比（最大值）
            ,dense_rank() over(partition by t1.period_tag,t1.organization_id,t1.ITEM_CODE order by cast(round(t1.item_model_ratio*100,2) as double) desc) oid
        FROM        DMA${db_para}.DMA_GROUP_ORG_ITEM_BELONG_TO_01_test t1
        where       organization_id = '1535' and item_code = '004.044.0053208'
        and         period_tag = '近3个月'
        GROUP BY
             t1.period_tag -- 属于哪个时间段
            ,t1.ORGANIZATION_ID
            ,t1.ITEM_CODE
            ,t1.cnt_model -- 归属产品使用个数
            ,round(t1.item_model_ratio*100,2)
            ,concat(t1.PRODUCT_MODEL,'：',concat(round(t1.item_model_ratio*100,2),'%'))
    )t1
)t1
GROUP BY
     t1.period_tag -- 属于哪个时间段
    ,t1.ORGANIZATION_ID
    ,t1.ITEM_CODE
    ,t1.cnt_model -- 归属产品使用个数