如何获得第三份报告来合并客户和订单数据

Question

我有一个关于保留率的问题
我有两张表，包括客户数据和订单数据。

经销商为d+---------+-----------+--------------+--------------------+|ID |设置_DT |恢复_DT |本地安装_DT|+---------+-----------+--------------+--------------------+|C111111|2018/1/1|Null | Null||C111112|2015/12/9|2018/10/25|2018/10-25||C111113|2018/10/1|空|空||C111114|2018/10/6|2018/12/14|2018/12-14|+---------+-----------+--------------+--------------------+

ORDER为o，请注意数据仅供参考。。。+---------+----------+-----+|ID|ORD_DT|OAL|+---------+----------+-----+|C111111|2018/1/1|112||C111111|2018/1/1|100||C111111|2018/1/1|472||C111111|2018/1/1|452||C111111|2018/1/1|248||C111111|2018/1/1|996|+---------+----------+-----+

我心目中创建保留率报告的第三张表+---------+-----------+-----------+---------------+-----------+|ID|APP_MON|ORDER_MON|TimeDiff(MON)|TTL AMT|+---------+-----------+-----------+---------------+-----------+|C111111|2018/1/1|2018/1|1|-|25443||C111111|2018/1/1|2018/2/1|1|7610||C111111|2018/1/1|2018/3/1|2|20180||C111111|2018/1/1|2018/4/1|3|22265||C111111|2018/1/1|2018/5/1|4|34118||C111111|2018/1/1|2018/6/1|5|19523||C111111|2018/1/1|2018/7/1|6|20220||C111111|2018/1/1|2018/8/1|7|2006||C111111|2018/1/1|2018/9/1|8|15813||C111111|2018/1/1|2018/10/1|9|16733||C111111|2018/1/1|2018/11/1|10|20973||C111112|2018/10/1|2017/11/1|-|516||C111112|2018/10/1|2018/10/1 |-|1||C111113|2018/10/1|空|-|空||C111114|2018/12/1|空|-|空|+---------+-----------+-----------+---------------+-----------+定义：-APP_MON：客户加入的月份，是从[d.SETUP_DT]、[d.REINSTATE_DT]和[d.LOCAL_INSTATE_DT'开始的最长日期-ORD_MON：客户购买的月份，是订单日期月份的开始日期-TimeDiff：APP_MON和ORD_MON之间的月份持续时间，例如，如果A的ODR_MON为2018/1/1，而A的APP_MON为2018/2/1，则持续时间为1。-TTL_AMT：客户在相关订单日期月份购买的总订单金额

我试图从第三个表中获取数据。但我运行了下面的代码，速度很慢。。。我需要一种更有效的方法，因为我有数百万的数据。。。谢谢

Answer 1

我认为您不需要使用unpivot。要获取最新日期，只需使用greatest()函数即可。

此解决方案有两个子查询，一个子查询用于计算每个新客户的app_mon，另一个子查询则用于计算最近两年内下订单的所有客户的最早订单日期。这可能不是最具表现力的方法，但你的首要任务应该是获得正确的结果；一旦你有了，你可以调整它，如果必要：

with cust as 
(
select d.dist_id as id
, greatest(d.setup_dt, d.reinstate_dt, d.local_reinstate_dt) as app_mo 
from mjensen_dev.gc_distributor d
where d.setup_dt >= date '2017-01-01'
or d.reinstate_dt >= date '2017-01-01'
or d.local_reinstate_dt >= date '2017-01-01'
) , ord as 
(
select o.dist_id as id
, min(o.ord_dt) as ord_mon 
, sum(o.oal) as ord_amt
from gc_orders o
where o.ord_dt >= date '2017-01-01'
group by o.dist_id
, trunc(o.ord_dt,'mm')
)
select cust.dist_id as id
, cust.app_mon
, ord.ord_mon
, floor(months_between(ord.ord_mon, cust.app_mon ) as mon_diff
, sum(o.oal) as ord_amt
from cust
inner join gc_orders o on cust.id = o.dist_id
order by 1, 2
/

你可能想调整一下我对mon_diff的计算。此计算将2018/2/1-2018/1/1视为一个月的差异。因为我觉得奇怪的是，在加入当天下订单的客户的mon_diff为1而不是零。但是，如果您的业务规则声明是正确的，则需要在计算中添加1。同样，我没有将trunc()包括在日期处理中，但您可能希望恢复它。