雨不停,天气晴
2022-03-23 14:40
采纳率: 50%
浏览 29

hql语句的优化,广告方面的计算 hive hql 数据库

这个是根据如下表写的,计算出不同广告主的广告的点击率 展示,点击

CREATE TABLE ODS_GG_QL(
`ID` string COMMENT 'ID',
`USER_ID` string COMMENT '用户账号',
`DEVIICE_ID` string COMMENT '设备id',
`create_time` string COMMENT '时间',
`APP_ID` string COMMENT 'APPID',
`ADSPACE_CODE` string COMMENt '广告位',
`MATE_CODE` string COMMENT '广告素材编码',
`operate_type` string COMMENT '交互',
`source` string COMMENT '来源',
`IP` string COMMENT 'IP',
`ADS_CODE` string COMMENT '所属广告主',
`SHOW_TIME` string COMMENT '展示时间'
);
语句如下(怎么优化?)
```sql
create table ggz_yhxw
as 
select t1.c1,t2.c2,t3.c3
from
(select count(*) c1 from ods_gg_ql where operate_type='show' and create_time ='2022-02-19' group by ADS_CODe) t1,
(select count(*) c2 from ods_gg_ql where operate_type='clic' and create_time ='2022-02-19' group by ADS_CODe) t2,
(select
onefh.a1/oneqq.b1 c3
from
(select count(*)b1 from ods_gg_ql where operate_type='show' and create_time ='2022-02-19' group by ADS_CODe) as oneqq,
(select count(*)a1 from ods_gg_ql where operate_type='clic' and create_time ='2022-02-19' group by ADS_CODe) as onefh) t3;

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

3条回答 默认 最新

相关推荐 更多相似问题