Hive按日期范围关联两表实现acc_no层级月度折扣计算
Hive环境折扣金额月度统计查询
关联表结构说明
- 交易表
txn:存储多账户月度交易记录,所有交易数据按每月1日生成(如2019-01-01),核心字段包含txn_date、card_no、prod_catg、card_grp、美元计价的tot_bill,数据覆盖2018、2019、2020年多账户、多品类的模拟交易场景 - 映射表
mapping_table:存储不同产品服务对应的折扣率匹配规则,核心字段包含prod_serv_name、prod_catg、card_grp、disc_rate、start_date、end_date,定义每条折扣规则的有效时间范围
计算与统计规则
- 单笔交易折扣计算公式:
tot_disc_bill = tot_bill * disc_rate - 两表关联匹配逻辑:基于
prod_catg字段做等值关联,同时校验交易日期落在对应折扣规则的有效时间区间内 - 覆盖业务范围:
- 规则1:匹配
prod_serv_name = 'x'的折扣规则,统计2020-01-01至2020-12-31的交易对应折扣 - 规则2:匹配
prod_serv_name = 'y'的折扣规则,限定card_grp = 'Platinum'白金卡品类,统计2018-01-01至2018-12-31的交易对应折扣
- 规则1:匹配
- 统计输出维度:按
card_no层级、card_catg分类、覆盖全product_hieracy维度,聚合输出单账户月度总折扣金额total_disc_amount
可运行Hive查询代码
SELECT t.card_no, t.prod_catg AS card_catg, t.prod_catg AS product_hieracy, date_format(t.txn_date, 'yyyy-MM') AS stat_month, SUM(t.tot_bill * m.disc_rate) AS total_disc_amount FROM txn t INNER JOIN mapping_table m ON t.prod_catg = m.prod_catg -- 匹配折扣规则有效时间区间 AND t.txn_date >= m.start_date AND t.txn_date <= m.end_date WHERE -- 拼接两类业务统计范围 ( m.prod_serv_name = 'x' AND t.txn_date BETWEEN '2020-01-01' AND '2020-12-31' ) OR ( m.prod_serv_name = 'y' AND t.card_grp = 'Platinum' AND t.txn_date BETWEEN '2018-01-01' AND '2018-12-31' ) GROUP BY t.card_no, t.prod_catg, date_format(t.txn_date, 'yyyy-MM');
适配说明:因现有表结构无独立的
card_catg、product_hieracy字段,查询中直接以现有prod_catg字段取值承接对应维度,可满足全产品层级的统计要求;若后续需区分个人/企业客户群体的折扣规则,可在两表关联条件中新增对应客户群体字段的等值匹配逻辑即可。
内容的提问来源于stack exchange,提问作者asr_rocks
相关产品推荐
相关产品推荐

