时间戳

首页 > 解决方案 > Identify Most Recent Record on Yearly Snapshot by partition of Arrange Id

oracle - Identify Most Recent Record on Yearly Snapshot by partition of Arrange Id

问题描述

I have Scenario Like below and want set Indicator based on Arrange Id, Login Date.. If User login website multiple time in Calendar Year then Most recent record need to set Y else N. Also I need to set Indicator like Bottom two rows as well.. ( Means 1121221 Accessed on last year recent 12/13/2017 need to set 'Y' and if user accessed in next immediate year 1/12/2018 then 'Y' )

enter image description here

标签： oracleplsqloracle-data-integrator

解决方案

这是一种选择。它有什么作用？

  • CTE是TEST一些示例行。注意ARRANGE_ID = 999，它的日期是 2017 年和 2019 年（这意味着没有连续的年份，所以 2019 年的日期应该得到指示符“N”。不过，你没有说如果 2019 年还有另一个日期会发生什么; 他们俩会得到'N'，还是最大登录日期仍然会得到'Y'？
  • INTERCTE 使用分析函数查找该MAX年的最大登录日期和LAG返回上一个登录日期的分析函数（以便我可以检查这些年份是否连续）
  • 最终查询用于CASE查找某些行是否满足使指标等于“Y”的条件

干得好：

SQL> with test (arrange_id, login_date) as
  2    (select 234, date '2017-02-18' from dual union all
  3     select 234, date '2017-04-13' from dual union all
  4     select 234, date '2017-11-14' from dual union all
  5     select 234, date '2018-01-14' from dual union all
  6     select 234, date '2018-09-11' from dual union all
  7     select 234, date '2019-04-02' from dual union all
  8     select 234, date '2019-05-18' from dual union all
  9     select 112, date '2017-02-23' from dual union all
 10     select 112, date '2017-12-13' from dual union all
 11     select 112, date '2018-01-12' from dual union all
 12     select 999, date '2017-01-01' from dual union all
 13     select 999, date '2017-05-25' from dual union all
 14     select 999, date '2019-01-01' from dual
 15    ),
 16  inter as
 17    (select arrange_id,
 18       login_date,
 19       max(login_date) over
 20         (partition by arrange_id, extract (year from login_date)) maxdate,
 21       lag(login_date) over (partition by arrange_id order by login_date) prev_date
 22     from test
 23    )
 24  select arrange_id,
 25    login_date,
 26    case when login_date = maxdate and
 27              extract(year from login_date) - extract(year from prev_date) <= 1 then 'Y'
 28         else 'N'
 29    end indicator
 30  from inter
 31  order by arrange_id, login_date;

ARRANGE_ID LOGIN_DATE I
---------- ---------- -
       112 02/23/2017 N
       112 12/13/2017 Y  -- Y because it is MAX in 2017
       112 01/12/2018 Y  -- Y because it is MAX in 2018 and 2018 follows 2017
       234 02/18/2017 N
       234 04/13/2017 N
       234 11/14/2017 Y  -- Y because it is MAX in 2017
       234 01/14/2018 N
       234 09/11/2018 Y  -- Y because it is MAX in 2018 and 2018 follows 2017
       234 04/02/2019 N
       234 05/18/2019 Y  -- Y because it is MAX in 2019 and 2019 follows 2018
       999 01/01/2017 N
       999 05/25/2017 Y  -- Y because it is MAX in 2017
       999 01/01/2019 N  -- N because it is MAX in 2019, but 2019 doesn't follow 2017

13 rows selected.

SQL>

推荐阅读