hot_group.sql

CREATE TABLE IF NOT EXISTS group_hot_coeff_20141217
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' as
SELECT
    group_active_member_num.group_id
    ,group_active_member_num.active_member_num / (group_member_num.member_num + 10)
FROM
(
SELECT
    group_id AS group_id
    ,COUNT(*) AS active_member_num
FROM (
    SELECT
        group_id
        ,member_id
        ,active_date
        ,active_num
    FROM (
        SELECT 
            group_id
            ,member_id
            ,active_map
        FROM ml_group_active_status LATERAL VIEW
        EXPLODE(member_active_days_num_map) active_1 AS member_id, active_map
        ) member_1 LATERAL VIEW EXPLODE(active_map) active_2 AS active_date, active_num
    ) member_2 
WHERE active_num > 0 GROUP BY group_id 
) AS group_active_member_num             
JOIN                                     
(                                        
SELECT                                   
    group_id AS group_id                         
    ,COUNT(member_id) AS member_num      
FROM (
    SELECT
        group_id
        ,member_id
        ,active_date
        ,active_num
    FROM (
        SELECT
            group_id
            ,member_id
            ,active_map
        FROM ml_group_active_status LATERAL VIEW
        EXPLODE(member_active_days_num_map) active_3 AS member_id, active_map
        ) member_3 LATERAL VIEW EXPLODE(active_map) active_4 AS active_date, active_num
    ) member_4
GROUP BY group_id
) AS group_member_num
on group_active_member_num.group_id = group_member_num.group_id
;