SQL汇总

查询商户ID下点击用户的设备号:

select
date, uid, aid as adid, idfa as IDFA, uuid as IMEI, did AS deviceid, count(1) clickcnt from default.adstatsdaily
where date='${date}'
and advertiser
id='66212824406' and ((mergetype=2 and classify!=8) or (mllabel in('click') and classify=8 and imagemode=5) or (label in('click','activityclick','feedplay','open') and classify=8 and imagemode!=5)) group by date, uid,aid, idfa,uuid,did

查询广告ID下点击用户的IMEI/IDFA:

select
userid, adid, imei, idfa, reqid, timestatmp, os, rit, dt, host, ip, ua from
default.ad
sendidfadaily where
date = "20171017" and ad_id = 71767822816

查询地理位置信息

SELECT
* FROM
growth.mcloudpushlocation_trace WHERE
did IN ('39768884240', '38791451222', '37464421065') and date between '20171005'and '20171208'

查询广告投放时用户所在地 SELECT
a.date, a.aid, a.uid, a.did, a.cityid, a.gct, a.ict, a.ip, a.at, a.label, b.name, b.provincecode, b.provincename FROM(
SELECT aid, uid, did, city
id, gct, ict, ip, date, at, label from adstatsdaily where aid = '75814758533' and did in ('39768884240', '38791451222', '37464421065') and label in ('send', 'click', 'show', 'convert') and date in ('20171203', '20171204', '20171205') ) a LEFT JOIN ( SELECT code, name, provincecode, provincename FROM default.dimcity ) b ON a.cityid = b.code

查询以激活为目标的计划的设备号信息 select *
from adtrackurlhourly
where ad
id in ('79132310207')
and datehour>='2017120700'
and datehour<='2017121300'

  查询API调用情况

select
params['appid'] AS appid,
params['aduserid'] AS aduserid,
params['path'] AS path,
params['statuscode'] AS statuscode,
params['method'] AS method

from originlog.eventloghourly
where date BETWEEN '20171101' and '20171201'
and app='adplatform'
and event = 'open
apiuserlog'
limit 19

API广告主调用次数

select
params['appid'] AS appid,
params['aduserid'] AS aduserid,
COUNT(*)

from originlog.eventlog_hourly

where date BETWEEN '20161101' and '20171201'
and app='adplatform'
and event = 'openapiuser_log'

GROUP BY params['appid'],params['aduser_id']

查询广告主对应消耗(adods.biadstatall表一个分区对应一天数据)

SELECT advertiserid,advertisername,sum(cost)
from adods.biadstatall
WHERE pdate BETWEEN '2017-11-01' AND '2017-12-01'
AND date BETWEEN '2017-11-01' AND '2017-12-01'
AND advertiser
id IN(53659036154,6181638866)
GROUP BY advertiserid,advertisername

查询广告主消耗(adbidw.adstatall表一个分区对应之前的所有数据)

SELECT advertiserid,advertisername,sum(cost)
from adbidw.adstatall
WHERE pdate = '20171201'
AND date BETWEEN '2017-11-01' AND '2017-12-01'
AND advertiser
id IN(53659036154,6181638866)
GROUP BY advertiserid,advertisername

总消耗

SELECT sum(cost)

from adbidw.adstatall

WHERE p_date = '20180102'
AND date BETWEEN '2017-12-01' AND '2018-01-01'

查询广告主某时间段日志

SELECT *,fromunixtime(CAST (createtime as bigint),'yyyy-MM-dd HH:mm:ss ') AS new_time

FROM adbidw.optlog
WHERE p
date = '20180111'
AND fromunixtime(CAST (createtime as bigint),'yyyy-MM-dd HH:mm:ss ') BETWEEN '2018-01-09 09:49:00' AND '2018-01-09 09:52:00'
AND advertiser_id = '73882865850'

希望日志显示对应的计划名称和广告组名称可以用下面的join:

SELECT
b.adid,b.adname,b.campaignid,c.campaignname,a.newtime,a.operatorid,a.operatorstaffid,a.advertiserid
FROM
( SELECT object
id,fromunixtime(CAST (createtime as bigint),'yyyy-MM-dd HH:mm:ss ') AS newtime,operatorid,operatorstaffid,advertiser_id

FROM adbidw.optlog
WHERE p
date = '20180111'
AND fromunixtime(CAST (createtime as bigint),'yyyy-MM-dd HH:mm:ss ') BETWEEN '2018-01-09 09:49:00' AND '2018-01-09 09:52:00'
AND advertiser_id = '73882865850'
)a

LEFT JOIN
( SELECT adid,adname,campaignid FROM addim.dimad WHERE pdate='20180111' )b ON a.objectid=b.adid

LEFT JOIN
( SELECT campaignid,campaignname FROM adbidw.campaign WHERE pdate='20180111' )c ON b.campaignid=c.campaign_id

字段说明: operator_id:操作人ID(如果是管理员则是工号)
operator_staff_id:操作人角色(0是管理员)

查询计划对应的广告组ID和名称

SELECT
a.advertiserid,a.adid,a.adname,a.campaignid,b.campaignname
FROM
( SELECT ad
id,adname,advertiserid,campaignid FROM addim.dimad WHERE adid IN (,84333706280,84333511712) and pdate='20180111' )a LEFT JOIN
( SELECT campaign
id,campaignname FROM adbidw.campaign WHERE pdate='20180111' )b ON a.campaignid=b.campaignid

  查询SMB账号用户登录次数(PV)

select
cs1 as 广告主 id, substr(cs3, 17, length(cs3) -16) as 广告主名称, CASE substr(cs4, 17, length(cs4) -16) WHEN '0' THEN '普通广告主' WHEN '1' THEN '代理商' WHEN '2' THEN '虚拟广告主' WHEN '3' THEN '管理员' WHEN '4' THEN '内部广告主' WHEN '6' THEN '二级代理商' WHEN '7' THEN '头条号文章作者' WHEN '8' THEN '头条自建DSP广告主' WHEN '11' THEN '代理商系统账号' WHEN '12' THEN '广告主系统账号' WHEN '13' THEN '账户管家账号' WHEN '14' THEN '火山账户' ELSE '其他' END as 广告主角色, CASE cs5 WHEN 'userself:' THEN '是' else '否' END as 是否自己登陆, count(cs1) as pv from
ad
growingio.adsitepage where
date = 20180103 and substr(cs8, 17, length(cs8) -16) = 18 group by
cs1, substr(cs3, 17, length(cs3) -16), CASE substr(cs4, 17, length(cs4) -16) WHEN '0' THEN '普通广告主' WHEN '1' THEN '代理商' WHEN '2' THEN '虚拟广告主' WHEN '3' THEN '管理员' WHEN '4' THEN '内部广告主' WHEN '6' THEN '二级代理商' WHEN '7' THEN '头条号文章作者' WHEN '8' THEN '头条自建DSP广告主' WHEN '11' THEN '代理商系统账号' WHEN '12' THEN '广告主系统账号' WHEN '13' THEN '账户管家账号' WHEN '14' THEN '火山账户' ELSE '其他' END, CASE cs5 WHEN 'user_self:' THEN '是' else '否' END

查询用户登录情况(数据有误,可以学习语句)

select
cs1 as 广告主 id, substr(cs3, 17, length(cs3) -16) as 广告主名称, CASE substr(cs4, 17, length(cs4) -16) WHEN '0' THEN '普通广告主' WHEN '1' THEN '代理商' WHEN '2' THEN '虚拟广告主' WHEN '3' THEN '管理员' WHEN '4' THEN '内部广告主' WHEN '6' THEN '二级代理商' WHEN '7' THEN '头条号文章作者' WHEN '8' THEN '头条自建DSP广告主' WHEN '11' THEN '代理商系统账号' WHEN '12' THEN '广告主系统账号' WHEN '13' THEN '账户管家账号' WHEN '14' THEN '火山账户' ELSE '其他' END as 广告主角色, CASE WHEN cs5 = 'userself:true' THEN '是' ELSE '否' END as 是否自己登陆, CASE substr(cs6, 11, length(cs6) -10) WHEN '0' THEN '普通广告主' WHEN '1' THEN '代理商' WHEN '2' THEN '虚拟广告主' WHEN '3' THEN '管理员' WHEN '4' THEN '内部广告主' WHEN '6' THEN '二级代理商' WHEN '7' THEN '头条号文章作者' WHEN '8' THEN '头条自建DSP广告主' WHEN '11' THEN '代理商系统账号' WHEN '12' THEN '广告主系统账号' WHEN '13' THEN '账户管家账号' WHEN '14' THEN '火山账户' ELSE '其他' END as 代理人角色, CASE substr(cs8, 17, length(cs8) -16) WHEN '11' THEN 'KA直客' WHEN '12' THEN '北上广本地直客' WHEN '13' THEN '区域直客' WHEN '14' THEN '分公司直客' WHEN '15' THEN '网服直客' WHEN '16' THEN '内部客户' WHEN '17' THEN 'LA直客' WHEN '18' THEN 'SMB直客' WHEN '253' THEN 'DSP' WHEN '254' THEN '游戏' WHEN '31' THEN 'KA虚客' WHEN '37' THEN 'LA虚客' WHEN '38' THEN 'SMB虚客' WHEN '255' THEN '虚拟广告主' WHEN '51' THEN '自助客户' ELSE '其他' END as 客户类型 from
ad
growingio.adsitepage where
date = 20171228

  查询表: 查看收入表:adods.biadstatall API使用表:originlog.eventloghourly
广告计划所在广告组:ad
dim.dim_ad

字段含义: idfa:IOS的IDFA号
uuid:安卓的IMEI号
advertiserid:广告主ID
req
id:广告请求的唯一标识
gct:根据gps信息获取的城市id
i
ct:根据ip信息获取的城市id
at:当前log落地时的timestamp
ot:表示send时间
aid:广告计划ID
cid:广告创意ID
uid:用户ID
did:设备号(通过算法将IMEI/IDFA处理出的一串数字,作为头条用户唯一标识)
label:(send、show、click)

default.adrequestlogdaily中 an 字段
news_article_lite --代表头条Lite,app
id=35
explorearticle --appid=26
livestream --appid=1112 火山
newsarticle --代表头条主端,appid=13
opennews --appid=34
toutiaowap --appid=25
toutiaoweb --appid=24
videoarticle --appid=32
jokeessay --appid=7

一张表的日期格式是 'yyyymmdd', 另一张的pdate格式是'yyyy-mm-dd', 如果想用date join,应该怎么join这两种不一样的日期格式呢? 答:利用 FROMUNIXTIME( UNIXTIMESTAMP(date,'yyyy-MM-dd'),'yyyyMMdd')

时间戳转格式: 转天级:and fromunixtime(CAST (createtime as bigint),'yyyyMMdd')>'20170101'

转小时:and fromunixtime(CAST (createtime as bigint),'yyyy-MM-dd HH:mm:ss ') > '2017-01-01 18:12:34'