Hive开窗函数over()的使用（附案例）

Hive开窗函数案例：

• 准备工作：

  -- 准备数据
  ---建表并且加载数据
  create database db_2;
  
  use db_2;
  
  create table website_pv_info(
  cookieid string,
  createtime string, --day
  pv int
  )
  row format delimited fields terminated by ',';
  
  create table website_url_info (
  cookieid string,
  createtime string, --访问时间
  url string --访问页面
  )
  row format delimited fields terminated by ',';
  
  load data local inpath '/root/website_pv_info.txt'
  into table website_pv_info;
  load data local inpath '/root/website_url_info.txt'
  into table website_url_info;

• 开窗结合sum函数使用：

  --sum(...) over( )对表所有行求和
  select
      cookieid, createtime, pv,
      sum(pv) over() -- 61
  from website_pv_info
  ;
  
  --sum(...) over( order by ... ) 连续累积求和
  select
      cookieid, createtime, pv,
      sum(pv) over(order by cookieid, createtime)
  from website_pv_info
  ;
  
  --sum(...) over( partition by... ) 同组内所有行求和
  se