这篇文章主要讲解了“hive ETL业绩报表sql怎么写”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“hive ETL业绩报表sql怎么写”吧！
创新互联建站主营方山网站建设的网络公司,主营网站建设方案,app软件开发,方山h5小程序定制开发搭建,方山网站营销推广欢迎方山等地区企业咨询
-- case4 -- --========== rates ==========-- app0 1 app1 2 app2 2 app3 3 app4 3 app5 3 app6 5 app7 5 app8 5 app9 5 CREATE EXTERNAL TABLE rates ( app_name STRING , star_rates STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/tmp/db/rates'; create table app_ranks as select app_name as app , star_rates as stars , NTILE(3) OVER (ORDER BY star_rates DESC) as nt , row_number() OVER (ORDER BY star_rates DESC) as rn , rank() OVER (ORDER BY star_rates DESC) as rk , dense_rank() OVER (ORDER BY star_rates DESC) as drk , CUME_DIST() OVER (ORDER BY star_rates) as cd , PERCENT_RANK() OVER (ORDER BY star_rates) as pr from rates order by stars desc ; select app, stars, cd, sum(cd) OVER (PARTITION BY stars ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) from app_ranks; select app, stars, cd, sum(cd) OVER (PARTITION BY stars ORDER BY rn ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) from app_ranks; select app, stars, cd, sum(cd) OVER (PARTITION BY stars ORDER BY rn ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) from app_ranks; select app, stars, rn, lead(rn) OVER (PARTITION BY stars ORDER BY rn), lag(rn) OVER (PARTITION BY stars ORDER BY rn) from app_ranks; --========== visitors ==========-- d001 201301 101 d002 201301 102 d003 201301 103 d001 201302 111 d002 201302 112 d003 201302 113 d001 201303 121 d002 201303 122 d003 201303 123 d001 201304 131 d002 201304 132 d003 201304 133 d001 201305 141 d002 201305 142 d003 201305 143 d001 201306 151 d002 201306 152 d003 201306 153 d001 201307 201 d002 201307 202 d003 201307 203 d001 201308 211 d002 201308 212 d003 201308 213 d001 201309 221 d002 201309 222 d003 201309 223 d001 201310 231 d002 201310 232 d003 201310 233 d001 201311 241 d002 201311 242 d003 201311 243 d001 201312 301 d002 201312 302 d003 201312 303 d001 201401 301 d002 201401 302 d003 201401 303 d001 201402 211 d002 201402 212 d003 201402 213 d001 201403 271 d002 201403 272 d003 201403 273 d001 201404 331 d002 201404 332 d003 201404 333 d001 201405 351 d002 201405 352 d003 201405 353 CREATE EXTERNAL TABLE visitors ( domain STRING , month STRING , visitor STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/tmp/db/visitors'; select * from visitors where domain = 'd001'; select domain , month , visitor , first_value(visitor) OVER (PARTITION BY domain ORDER BY month DESC) , last_value(visitor) OVER (PARTITION BY domain ORDER BY month DESC) , lead(visitor) OVER (PARTITION BY domain ORDER BY month DESC) , lag(visitor) OVER (PARTITION BY domain ORDER BY month DESC) from visitors where domain = 'd001'; select domain , month , visitor , first_value(visitor) OVER (PARTITION BY domain ORDER BY month DESC) , last_value(visitor) OVER (PARTITION BY domain ORDER BY month DESC) , lead(visitor, 1, 0) OVER (PARTITION BY domain ORDER BY month DESC) , lag(visitor, 1, 0) OVER (PARTITION BY domain ORDER BY month DESC) , lead(visitor, 12, 0) OVER (PARTITION BY domain ORDER BY month DESC) , lag(visitor, 12, 0) OVER (PARTITION BY domain ORDER BY month DESC) from visitors where domain = 'd001'; create table visitors_report as select domain , month , visitor , lead(visitor, 1, 0) OVER (PARTITION BY domain ORDER BY month DESC) as last_mon , visitor - lead(visitor, 1, 0) OVER (PARTITION BY domain ORDER BY month DESC) as delta_mon , lead(visitor, 12, 0) OVER (PARTITION BY domain ORDER BY month DESC) as last_year , visitor - lead(visitor, 12, 0) OVER (PARTITION BY domain ORDER BY month DESC) as delta_year from visitors ; select * from visitors_report where domain = 'd001' and month > '2014'; select month , domain , visitor , last_mon , last_year from visitors_report where (domain = 'd001' or domain = 'd002') and month > '2014' order by month desc, domain asc limit 100; select month , domain , visitor , max(visitor) OVER (PARTITION BY month) as max_visitors , min(visitor) OVER (PARTITION BY month) as min_visitors from visitors where month > '2014' order by month desc, domain asc; select * from ( select month , domain , visitor , max(visitor) OVER (PARTITION BY domain ORDER BY month DESC ROWS BETWEEN CURRENT ROW AND 12 FOLLOWING) as max_visitors_last_12_mon , min(visitor) OVER (PARTITION BY domain ORDER BY month DESC ROWS BETWEEN CURRENT ROW AND 12 FOLLOWING) as min_visitors_last_12_mon from visitors ) v where month > '20131' order by month desc, domain asc;
感谢各位的阅读，以上就是“hive ETL业绩报表sql怎么写”的内容了，经过本文的学习后，相信大家对hive ETL业绩报表sql怎么写这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
