Oracle多个案例陈述

Question

我正在尝试使用case语句创建2个标志。

Flag1 = event_per_month 
Flag2 = event_per_day 

下面是样本数据

我期待以每天和每月的ID发生创建两个标志。

EVENT_PER_DAY逻辑：如果给定的id和msg不止一次在同一天重复，那么event_per_day标志将变为重复事件（id和msg的组合）的“T”，否则它将保持“T”。

EVENT_PER_MONTH逻辑：如果给定的id和msg在同一个月中重复多次，那么event_per_month标志将变为重复事件（id和msg的组合）的“T”，否则它将保持“T”。

我使用了case语句，但是这里的问题是我有日期以及月份列，这使得它很复杂。

这里是预期的结果

id error_date error_month error category event_per_day event_per_month 
123 15-May-15 12:01:01 May-15 msg1 F F 
123 15-May-15 12:20:00 May-15 msg1 T T 
123 17-May-15 22:00:00 May-15 msg2 F F 
124 17-May-15 20:00:00 May-15 msg2 T T 
123 18-May-15 12:00:00 May-15 msg3 F F 
123 19-May-15 19:00:00 May-15 msg3 F F 
123 20-May-15 12:00:00 May-15 msg4 F F 
123 20-May-15 17:00:00 May-15 msg4 T T 
123 22-May-15 12:00:00 May-15 msg4 F F 
123 23-May-15 12:00:00 May-15 msg4 F F 
123 12-Aug-15 18:00:00 Aug-15 msg1 F F 
123 13-Aug-15 12:00:00 Aug-15 msg2 F F 
123 14-Aug-15 12:00:00 Aug-15 msg3 F F 
123 15-Aug-15 12:00:00 Aug-15 msg4 F F 
123 16-Aug-15 12:00:00 Aug-15 msg1 F T 
123 17-Aug-15 12:00:00 Aug-15 msg1 F T 
123 18-Aug-15 12:00:00 Aug-15 msg2 F T 
123 19-Aug-15 12:00:00 Aug-15 msg3 F T 
123 20-Aug-15 12:00:00 Aug-15 msg4 F T 
123 21-Aug-15 12:00:00 Aug-15 msg3 F T 
890 15-May-15 12:01:01 May-15 msg1 F F 
890 15-May-15 12:20:00 May-15 msg1 T T 
890 17-May-15 22:00:00 May-15 msg2 F F 
890 17-May-15 20:00:00 May-15 msg2 T T 
890 18-May-15 12:00:00 May-15 msg3 F F 
890 19-May-15 19:00:00 May-15 msg3 F F 
890 20-May-15 12:00:00 May-15 msg4 F F 
890 20-May-15 17:00:00 May-15 msg4 T T 
890 22-May-15 12:00:00 May-15 msg4 F F 
890 23-May-15 12:00:00 May-15 msg4 F F 
890 12-Aug-15 18:00:00 Aug-15 msg1 F F 
890 13-Aug-15 12:00:00 Aug-15 msg2 F F 
890 14-Aug-15 12:00:00 Aug-15 msg3 F F 
890 15-Aug-15 12:00:00 Aug-15 msg4 F F 
890 16-Aug-15 12:00:00 Aug-15 msg1 F T 
890 17-Aug-15 12:00:00 Aug-15 msg1 F T 
890 18-Aug-15 12:00:00 Aug-15 msg2 F T 
890 19-Aug-15 12:00:00 Aug-15 msg3 F T 
890 20-Aug-15 12:00:00 Aug-15 msg4 F T 
890 21-Aug-15 12:00:00 Aug-15 msg3 F T 

Answer 1

我想你想要分析功能：

select t.*, 
     (case when row_number() over (partition by trunc(error_date), msg 
            order by error_date 
            ) = 1 
      then 'F' else 'T' 
     end) as event_per_day, 
     (case when row_number() over (partition by error_month, msg 
            order by error_date 
            ) = 1 
      then 'F' else 'T' 
     end) as event_per_month 
from table t; 

编辑：

上面的查询应该罚款，但也许使用子查询帮助：

select t.*, 
     (case when seqnum_day = 1 then 'F' else 'T' end) as event_per_day, 
     (case when seqnum_mon = 1 then 'F' else 'T' end) as event_per_month 
from (select t.*, 
      row_number() over (partition by trunc(error_date), msg 
           order by error_date) as seqnum_day, 
      row_number() over (partition by error_month, msg 
           order by error_date) as seqnum_mon 
     from table t 
    ) t;