hive分析函数

遇到以下问题如何解决？

1. 查询客户各个日期的历史累积购买金额
2. 查询每个客户第一次(首购)或前N次购买记录
3. 查询每个客户最后一次购买记录
4. 某活动推广后,每天的累积数据

查询客户每天的历史累积购买金额

历史累积

hive 语法：

SELECT
  userid user_id,
  pay_datekey pay_datekey,
  pay_amount pay_amount,
  SUM(pay_amount) OVER (PARTITION BY userid ORDER BY pay_datekey  ROWS BETWEENUNBOUNDED PRECEDING AND CURRENT ROW ) accumulate_pay_amount
FROM 
  order_table

 

窗口分析函数

OVER(PARTITION BY COLUMN ROWS BETWEEN ..)

ROWS ((CURRENT ROW) | (UNBOUNDED | [num]) PRECEDING) AND (UNBOUNDED | [num]) FOLLOWING

1. PRECEDING:往前
2. FOLLOWING:往后
3. CURRENT ROW:当前行
4. UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED

FOLLOWING:表示到后面的终点

如果不指定ROWS BETWEEN,默认为从起点到当前值

SUM(pay_amount) OVER (PARTITION BY userid ORDER BY pay_datekey ROWS BETWEEN 2 PRECEDING AND 0 FOLLOWING)

聚合函数

除了可以计算到当前行的sum聚合函数之外,还支持以下 标准聚合函数

• COUNT
• MIN
• MAX
• AVG

比如计算:截止某天历史平均购买交易额、最小购买金额等。

序列函数

为每一行加一个序号

1. row_number()
2. rank()
3. dense_rank()
4. percent_rank() 分组内当前行的RANK值-1/分组内总行数-1
5. ntile(n) 将数据集分成 n片,返回分片号
6. cume_dist 小于等于当前值的行数和分组内总行数的比值

效果

序列函数

SELECT 
    user_id,
    pay_datekey,
    pay_amount,
    ROW_NUMBER() OVER (PARTITION BY userid ORDER BY pay_datekey ASC)
    row_number,
    RANK() OVER (PARTITION BY userid ORDER BY pay_datekey ASC) rank, DENSE_RANK() OVER (PARTITION BY userid ORDER BY pay_datekey ASC) dense_rank, PERCENT_RANK() OVER (PARTITION BY userid ORDER BY pay_datekey ASC)
    percent_rank,
    NTILE(2) OVER (PARTITION BY userid ORDER BY pay_datekey ASC) ntile
FROM 
    order_detail

 

 

LAG

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值
(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)

LEAD

与LAG相反,LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值. 第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值
(当往下第n行为NULL时候,取默认值,如不指定,则为NULL

FIRST_VALUE

取分组内排序后,第一个值.应用场景,如求第一次购买日期。

LAST_VALUE

取分组内排序后,最后一个值.应用场景,如求第一次购买日期