这篇文章主要讲解了“hive ETL业绩报表sql怎么写”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“hive ETL业绩报表sql怎么写”吧！
为永登等地区用户提供了全套网页设计制作服务，及永登网站建设行业解决方案。主营业务为
成都网站建设、网站设计、永登网站设计，以传统方式定制建设网站，并提供域名空间备案等一条龙服务，秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求，就会得到认可，从而选择与我们长期合作。这样，我们也可以走得更远！
-- 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怎么写这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
另外有需要云服务器可以了解下创新互联scvps.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
网站名称：hiveETL业绩报表sql怎么写-创新互联
文章分享：
http://cdkjz.cn/article/coijss.html
