线索主表分段理解

其实他应该拆出来，混在一起了，自然特别麻烦。 不过将就也能看咯。

 获取线索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

-- 获取签单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 ;

理解 row_number  和 partition by 使用方法。

以及时间使用  ORDER BY allocation_time，没有倒序就是正常的时间。

时间递增性。2018到2019年。就是距离现在最晚的时候。

ORDER BY allocation_time DESC 。就是距离现在最近的。

SELECT  *
FROM    (
            SELECT  a.*
                    ,ROW_NUMBER() OVER(PARTITION BY clue_id ORDER BY allocation_time) AS asc_number
            FROM    o_jz_clue_t_allocation_record a
            WHERE   dt = '${bdp.system.bizdate}'
        ) a
WHERE   a.asc_number > 2
LIMIT   100