-- 通过地理位置过滤出特定高校的学生
-- 查询20140301前的地理信息数据，请将log.mobile_data_location替换为log.mobile_data_location_oldtb
-- v0.1北京大学20131101上报的终端
SELECT DISTINCT
    uuid
FROM
    log.mobile_data_location
WHERE
    dt = '20131101'
    AND marslat > 39.98714 AND marslat < 39.996889
    AND marslng > 116.304849 AND marslng < 116.315235

-- 测试
SELECT
    *
FROM
    (SELECT
        uuid,
        (case
            when marslat > 39.98714 AND marslat < 39.996889 AND marslng > 116.304849 AND marslng < 116.315235
            then 'PKU'
            when acos(sin(marslat*pi/180)*sin(40.001137*pi/180) + cos(marslat*pi/180)*cos(40.001137*pi/180)*cos((marslng-116.327741)*pi/180)) * 6371004 <= 300
            then 'THU'
         else 'NULL'
         end
        ) univ
     FROM
        log.mobile_data_location
     WHERE
        dt >= '20131101' AND dt <= '20131107'
    ) mdl
WHERE
    mdl.univ != 'NULL'

-- 找出出现在指定高校区域的设备及其在一周内的出现天数
SELECT
    mdl.uuid,
    mdl.univ,
    count(*) aprtimes
FROM
    (SELECT distinct
        uuid,
        (case
            when marslat > 39.98714 AND marslat < 39.996889 AND marslng > 116.304849 AND marslng < 116.315235
            then 'PKU'
            when marslat > 30.259491 AND marslat < 30.26898 AND marslng > 120.122359 AND marslng < 120.124891
            then 'ZJU'
            when marslat > 30.632197 AND marslat < 30.633397 AND marslng > 104.078482 AND marslng < 104.083031
            then 'SCU'
            when acos(sin(marslat*pi()/180)*sin(40.001137*pi()/180) + cos(marslat*pi()/180)*cos(40.001137*pi()/180)*cos((marslng-116.327741)*pi()/180)) * 6371004 <= 300
            then 'THU'
            when acos(sin(marslat*pi()/180)*sin(30.541111*pi()/180) + cos(marslat*pi()/180)*cos(30.541111*pi()/180)*cos((marslng-114.361922)*pi()/180)) * 6371004 <= 300
            then 'WHU'
            when acos(sin(marslat*pi()/180)*sin(31.297978*pi()/180) + cos(marslat*pi()/180)*cos(31.297978*pi()/180)*cos((marslng-121.500632)*pi()/180)) * 6371004 <= 300
            then 'FDU'
            when acos(sin(marslat*pi()/180)*sin(24.438973*pi()/180) + cos(marslat*pi()/180)*cos(24.438973*pi()/180)*cos((marslng-118.097788)*pi()/180)) * 6371004 <= 300
            then 'XMU'
         else 'NULL'
         end
        ) univ,
        dt
     FROM
        log.mobile_data_location
    WHERE
        dt >= '20131101' AND dt <= '20131107'
    ) mdl
WHERE
    mdl.univ != 'NULL'
group by
    mdl.univ, mdl.uuid

-- 选出学生群体的uuid和userid
SELECT
    uub.userid,
    uub.uuid,
    t.univ
FROM
(
    SELECT
        mdl.uuid,
        mdl.univ,
        count(*) aprtimes
    FROM
        (SELECT distinct
            uuid,
            (case
                -- 选学校模块

             else 'NULL'
             end
            ) univ,
            dt
         FROM
            log.mobile_data_location
        WHERE
            dt >= '20131101' AND dt <= '20131107'
        ) mdl
   WHERE
        mdl.univ != 'NULL'
    group by
        mdl.univ, mdl.uuid
) t
join
(
    SELECT
        userid,
        uuid
    FROM
        mart_mobile.user_uuid_base
) uub
on
    t.aprtimes >= 2 AND t.uuid = uub.uuid

-- 找出指定高校学生的userid，根据userid与微博用户信息进行交叉验证
SELECT
    loc_stu.userid,
    loc_stu.uuid,
    weibo.university,
    weibo.occupation
FROM
(
    SELECT
        uub.userid,
        uub.uuid,
        t.univ
    FROM
    (
        SELECT
            mdl.uuid,
            mdl.univ,
            count(*) aprtimes
        FROM
            (SELECT distinct
                uuid,
                (case
                    -- 选学校模块

                 else 'NULL'
                 end
                ) univ,
                dt
             FROM
                log.mobile_data_location
            WHERE
                dt >= '20131101' AND dt <= '20131107'
            ) mdl
       WHERE
            mdl.univ != 'NULL'
        group by
            mdl.univ, mdl.uuid
    ) t
    join
    (
        SELECT
            userid,
            uuid
        FROM
            mart_mobile.user_uuid_base
    ) uub
    ON
        t.aprtimes >= 2 AND t.uuid = uub.uuid
) loc_stu
join
(
    SELECT
        userid,
        university,
        occupation
    FROM
        ba_ups.user_weibo_profile
) weibo
ON
    loc_stu.userid = weibo.userid

-- 选学校模块
-- 武汉
when acos(sin(marslat*pi()/180)*sin(30.541111*pi()/180) + cos(marslat*pi()/180)*cos(30.541111*pi()/180)*cos((marslng-114.361922)*pi()/180)) * 6371004 <= 300
then 'WHU'
when acos(sin(marslat*pi()/180)*sin(30.512776*pi()/180) + cos(marslat*pi()/180)*cos(30.512776*pi()/180)*cos((marslng-114.412261)*pi()/180)) * 6371004 <= 300
then 'HUST'
when acos(sin(marslat*pi()/180)*sin(30.521316*pi()/180) + cos(marslat*pi()/180)*cos(30.521316*pi()/180)*cos((marslng-114.354712)*pi()/180)) * 6371004 <= 400
then 'WUT&CCNU'
-- 上海
when acos(sin(marslat*pi()/180)*sin(31.297978*pi()/180) + cos(marslat*pi()/180)*cos(31.297978*pi()/180)*cos((marslng-121.500632)*pi()/180)) * 6371004 <= 200
then 'FDU'
when acos(sin(marslat*pi()/180)*sin(31.142988*pi()/180) + cos(marslat*pi()/180)*cos(31.142988*pi()/180)*cos((marslng-121.42198)*pi()/180)) * 6371004 <= 200
then 'ECUST'
when acos(sin(marslat*pi()/180)*sin(31.199559*pi()/180) + cos(marslat*pi()/180)*cos(31.199559*pi()/180)*cos((marslng-121.433598)*pi()/180)) * 6371004 <= 200
then 'SJTU'
when acos(sin(marslat*pi()/180)*sin(31.317144*pi()/180) + cos(marslat*pi()/180)*cos(31.317144*pi()/180)*cos((marslng-121.39364)*pi()/180)) * 6371004 <= 200
then 'SHU'
-- 北京
when marslat > 39.98714 AND marslat < 39.996889 AND marslng > 116.304849 AND marslng < 116.315235
then 'PKU'
when acos(sin(marslat*pi()/180)*sin(40.001137*pi()/180) + cos(marslat*pi()/180)*cos(40.001137*pi()/180)*cos((marslng-116.327741)*pi()/180)) * 6371004 <= 300
then 'THU'
when marslat > 39.958988 AND marslat < 39.964679 AND marslng > 116.355296 AND marslng < 116.370403
then 'BNU&BUPT'
when marslat > 39.954317 AND marslat < 39.962064 AND marslng > 116.309806 AND marslng < 116.316737
then 'BIT&BFSU'
when acos(sin(marslat*pi()/180)*sin(39.951257*pi()/180) + cos(marslat*pi()/180)*cos(39.951257*pi()/180)*cos((marslng-116.320599)*pi()/180)) * 6371004 <= 200
then 'CUN'
when acos(sin(marslat*pi()/180)*sin(39.959482*pi()/180) + cos(marslat*pi()/180)*cos(39.959482*pi()/180)*cos((marslng-116.342121)*pi()/180)) * 6371004 <= 150
then 'CUFE'
when acos(sin(marslat*pi()/180)*sin(39.966241*pi()/180) + cos(marslat*pi()/180)*cos(39.966241*pi()/180)*cos((marslng-116.351391)*pi()/180)) * 6371004 <= 150
then 'CPUL'
when acos(sin(marslat*pi()/180)*sin(39.954383*pi()/180) + cos(marslat*pi()/180)*cos(39.954383*pi()/180)*cos((marslng-116.345426)*pi()/180)) * 6371004 <= 150
then 'BJTU'
-- 西安
when marslat > 34.242006 AND marslat < 34.250733 AND marslng > 108.979887 AND marslng < 108.987311
then 'XJTU'
when marslat > 34.264513 AND marslat < 34.266481 AND marslng > 108.995851 AND marslng < 109.00216
then 'NPU'
when acos(sin(marslat*pi()/180)*sin(34.231075*pi()/180) + cos(marslat*pi()/180)*cos(34.231075*pi()/180)*cos((marslng-108.916599)*pi()/180)) * 6371004 <= 150
then 'XDU'
when marslat > 34.150533 AND marslat < 34.157369 AND marslng > 108.88631 AND marslng < 108.896416
then 'SXNU'
when marslat > 34.24644 AND marslat < 34.250325 AND marslng > 108.923839 AND marslng < 108.931478
then 'NPU'
-- 广州大学城
when acos(sin(marslat*pi()/180)*sin(23.04573*pi()/180) + cos(marslat*pi()/180)*cos(23.04573*pi()/180)*cos((marslng-113.38181)*pi()/180)) * 6371004 <= 1000
then 'GZUNIV'
when acos(sin(marslat*pi()/180)*sin(23.064289*pi()/180) + cos(marslat*pi()/180)*cos(23.064289*pi()/180)*cos((marslng-113.395028)*pi()/180)) * 6371004 <= 1000
then 'GZUNIV'