产品价格变动带来的结算金额处理逻辑 lead/lag函数的使用

背景:

计算公式:结算金额 = 产品单价(数值随时会变动) * 产品点击数

产品价格随时会更改,例如上午00:00:00点--->13:59:59 是单价 3元,下午 14:00:00-23:59:59 就改为4元。那么今天的结算金额 = 

[00:00:00点:13:59:59]的点击数 * 3元 + [14:00:00-23:59:59]的点击数 * 4元

一.sql(hive): 计算时间区间的产品价格表b

 select
    product_code,                                  -- 产品编码
    split(product_name, '#')[0] as product_name,   -- 产品名称
    product_type, -- 产品类型
    update_time,  -- 价格更新的时间
    price,        -- 价格
    lag(update_time, 1, '0000-00-00 00:00:00') over(partition by product_name, product_code order by update_time) last_time, -- 按产品分组，上一次更新时间
    lag(price, 1, 0) over(partition by product_name, product_code order by update_time) last_price, -- 按产品分组，上一次更新时的价格
    lead(update_time, 1, '9999-99-99 99:99:99') over(partition by product_name, product_code order by update_time) next_time, -- 按产品分组，下一次更新时间
    lead(price, 1, 0) over(partition by product_name, product_code order by update_time) next_price -- 按产品分组，下一次更新时的价格
from ods.ods_product_price_minutes -- 该表中的价格已去重，即每天的分区中同产品同价格的数据仅有一条
-- 需拿到近两天的价格，因若点击时间为今日最早的update价格时间之前，则需要取用前一天最后的价格来计算
where d >= date_format(date_sub('${d_minutes}', 10), 'yyyy-MM-dd')
and product_code = 'yiqihuabjzz'

结果 :【源数据有问题,可以不看下图.弄懂思路就好】

二.

   得到每个用户的点击时间明细表a

字段:1.产品编码 2.产品名称 3.用户手机号 4.点击时间(精确到时分秒)

三.  表a 和表b 关联.表a是左表，表b是右表.

关联条件on:产品名称 产品编码

筛选条件where :

1.表a的点击时间 > 表b的last_time

2.表a的点击时间 < 表b的next_time

3.表a的点击时间 > 表b的update_time (也就是点击时间要大于价格的更改时间)

四.根据产品编码/产品名称等维度group by