Oracle SQL - 复杂连接:检查四个值之一是否匹配

Question

我正在尝试根据另一个表中是否存在现有值在表中获取一个新列，并且我对如何解决这个问题有点受阻。我有以下两个表：

Table1
ID | Week | Year
Table2
ID | Week | Year | Counter

表 1 包含每个给定周的一组可用 ID。每次对 ID 执行操作时，表 2 中的计数器都会增加。如果未执行任何操作，则 ID 在表 2 中没有条目。此外，表 2 可能包含表 1 中不可用的 ID。

我现在需要检查表1中的所有ID，如果在过去四周内发生了任何操作，换句话说，如果表2中给定周的条目存在，并将其作为Y/N添加到结果表中。因此，如果表 1 中的 ID 与第 47 周相关联，则需要检查表 2 中同一 ID 的周值 47、46、45 和 44。

Example
Table1
ID | Week | Year
-----------------
01 |   47 | 2017
02 |   47 | 2017
Table2
ID | Week | Year | Counter
----------------------------
01 |   45 | 2017 | 5
01 |   43 | 2017 | 2
02 |   43 | 2017 | 7
ResultsTable
ID | Week | Year | Active in past 4 weeks
------------------------------------------
01 |   47 | 2017 | Y
02 |   47 | 2017 | N

我不确定如何进行此操作。任何帮助，不胜感激。谢谢！

Answer 1

以下情况如何：

select  distinct a1.id,
        a1.week,
        a1.year,
        case
          when a2.id is not null
            then 'Y'
            else 'N'
        end active_in_past_4_weeks
from    table1 a1
left outer join table2 a2 on a1.id = a2.id and trunc(to_date(a1.year,'yyyy') + a1.week * 7,'IW') - 21 <= trunc(to_date(a2.year,'yyyy') + a2.week * 7,'IW');

转换为联接日期应该有助于更改年份。

Answer 2

我建议WEEK/YEAR列转换为DATE。最好将它们添加为虚拟列。

CREATE OR REPLACE FUNCTION ISOWeekDate(WEEK INTEGER, YEAR INTEGER) RETURN DATE DETERMINISTIC IS
    res DATE;
BEGIN
    IF WEEK > 53 OR WEEK < 1 THEN
        RAISE VALUE_ERROR;      
    END IF;
    res := NEXT_DAY(TO_DATE( YEAR || '0104', 'YYYYMMDD' ) - INTERVAL '7' DAY, 'MONDAY') + ( WEEK - 1 ) * 7;
    IF TO_CHAR(res, 'fmIYYY') = YEAR THEN
        RETURN res;
    ELSE
        RAISE VALUE_ERROR;
    END IF;
END ISOWeekDate;

然后连接将更简单：

select distinct t1.ID ,t1.Week ,t1.Year,
    ISOWeekDate(t1.Week ,t1.Year),
    case when t2.id is not null then 'Y' else 'N' end active 
from table1 t1 
   left join table2 t2 on t1.id = t2.id 
      and ISOWeekDate(t1.Week ,t1.Year) between ISOWeekDate(t2.Week, t2.Year)-3*7 and ISOWeekDate(t2.Week, t2.Year)