连续不低于

2021-11-01 · 989 words · 2 minute read 生活

随着前两个月被抓去做原型设计1,接着又被抓去做测试,最近被抓去做数据开发了……

开发过程中遇到了一个小问题,大致场景是:

公司中每个客户都有一个等级,客户等级总会发生变更,这些等级中C(Common)代表普通等级,S(Silver)代表银卡等级,G(Golden)代表金卡等级,P(Platinum)代表铂金卡等级,等级顺序依次是C<S<G<P,求每个客户连续不低于银卡等级的时间、连续不低于金卡等级的时间、连续不低于铂金卡等级的时间?

list_id customer_id old_level new_level insert_time
1 123456 C S 2010/10/14 01:11:51
2 123456 S G 2011/02/19 08:47:49
3 123456 G C 2014/11/11 11:45:36
4 123456 C S 2015/11/11 10:55:01
5 123456 S G 2016/11/11 09:55:32
6 123456 G P 2019/11/11 12:11:11

上面这个表格中的数据是我瞎编的一个例子,客户ID为123456的这位客户在2010年成为银卡客户,在2011年升级为金卡客户,又在2014年降级成为普通客户,随后又在2015年重新升级为银卡客户,其后客户等级保持连续不低于银卡等级,所以该客户连续不低于银卡等级的时间是2015/11/11

这个小问题是要在ORACLE中写SQL解决。最开始我困在“连续”这个词中,解题思路想得极其复杂,寻思着把代表等级的C/S/G/P等字母替换成可以直接比较大小的数字,每个客户按等级变更的时间顺序排序,然后把排序得到的序号作为父节点去用递归函数写条件依次判断……哈哈,我自己也觉得这个问题被我想复杂了,所以当时也没有直接按原来的思路去做,而是先放着干别的去了。过了两天,在把一些贼难、贼麻烦、贼耗时间的问题弄得差不多以后,才回过头来再处理这个问题。

可是我还是想不到简单解法,于是就把这个问题拿去问了同事小花,小花给了我一个很简单的思路:把“不连续”的变更记录直接去掉,然后对时间取最小值。

select t.customer_id, min(ch.insert_time)
  from t_base t  --确定数据范围的基础表,瞎编的表名
  left join t_vip_change ch on t.customer_id = ch.customer_id  --t_vip_change也是瞎编的表名
 where ch.new_level in ('S', 'G', 'P')
   and not exists (select '1'
          from t_vip_change ch2
         where ch2.customer_id = ch.customer_id
           and ch2.new_level not in ('S', 'G', 'P')
           and ch2.insert_time > ch.insert_time)
 group by t.customer_id;

小花说正是因为她本来就不会递归那些东西,所以解题思路才倾向于越简单越好,而我正是因为会那些复杂方法,才会想得太复杂。正所谓,乱花渐欲迷人眼2,浅草才能没马蹄。


  1. 主要是做可视化页面的原型设计,就是用R中的flexdashboard包+echarts4r包画图,偶尔用kableExtra包做表格。
  2. 乱花渐欲迷人眼的个人定义是,简单问题却想得过于复杂,难以透过现象看清本质,容易执迷于问题的表象而忽略根本。