线索主表分段理解

 获取线索id，以及线索标签。

use jz_daojia;
SELECT  a.id
        ,CONCAT_WS(‘,‘, COLLECT_SET(b.dict_value)) AS clue_labels
FROM    (
            SELECT  a.id
                    ,b.clue_label
            FROM    (
                        SELECT  a.id
                                ,GET_JSON_OBJECT(a.prop, ‘$.busSnapshotDto.clueLable‘) clue_label
                        FROM    o_jz_clue_t_sale_clue a
                        WHERE   dt = ‘${bdp.system.bizdate}‘
                    ) a
            LATERAL VIEW EXPLODE(SPLIT(clue_label, ‘,‘)) b AS clue_label
        ) a LEFT
JOIN    (
            SELECT  *
            FROM    o_jz_clue_t_dict
            WHERE   dt = ‘${bdp.system.bizdate}‘
            AND     type IN (‘target_unconform_label‘, ‘clue_label‘,‘target_conform_label‘)
        ) b
ON      a.clue_label = b.dict_key
GROUP BY a.id;

SELECT  a.*
        ,GET_JSON_OBJECT(a.prop, ‘$.busSnapshotDto.clueLable‘) clue_label
        ,GET_JSON_OBJECT(
            GET_JSON_OBJECT(prop,‘$.busSnapshotDto‘)
            ,‘$.childBrithDay‘
        ) AS childbrithday
        ,CASE    WHEN a.category_id = 205 THEN ‘月嫂‘
                 WHEN a.category_id = 270 THEN ‘育儿嫂‘
                 WHEN a.category_id = 212 THEN ‘保姆‘ 
                 ELSE ‘其他‘ 
         END AS category_name
FROM    o_jz_clue_t_sale_clue a
WHERE   dt = ‘${bdp.system.bizdate}‘
AND     delete_flag = 0
AND     source_id != 100000680
LIMIT  10 

-- 获取签单id，签单人姓名，部门id

SELECT order_id ,follow_id AS interviewer_id ,follow_name AS interviewer_name ,follow_office_id FROM ( SELECT order_id ,follow_id ,follow_name ,follow_office_id ,ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY create_time DESC) row_num FROM o_jz_order_t_order_follow WHERE dt = ‘${bdp.system.bizdate}‘ AND follow_role = 1 --跟进角色为 归属角色  ) a WHERE row_num = 1 LIMIT 10 ;