Skip to content

Instantly share code, notes, and snippets.

@ytjia

ytjia/loc_stu.sql

Created March 9, 2015 05:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ytjia/a41782e43a03de08250a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ytjia/a41782e43a03de08250a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
loc_stu.sql
-- 通过地理位置过滤出特定高校的学生
-- 查询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'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment