通过排列 ID 的分区识别年度快照上的最新记录

Question

我有如下所示的场景，并希望根据排列ID，登录日期设置指标。如果用户在日历年中多次登录网站，则最近的记录需要设置 Y else N。我还需要像底部两行一样设置指标。.(表示去年最近访问1121221/13/2017需要设置"Y"，如果用户在下一年1/12/2018访问，则"Y"(

在此处输入图像描述

Answer 1

这里有一个选项。它有什么作用？

• CTE TEST是一些示例行。注意 ARRANGE_ID = 999 ，其日期为 2017 年和 2019 年(这意味着没有连续年份，因此 2019 年的日期应得到指标"N"。不过，你没有说，如果2019年还有另一个日期会发生什么;它们都会得到"N"，还是最大登录日期仍然会得到"Y"？
• INTER CTE 使用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>