Oracle SQL :从Join中的日期返回数周

Question

我有一个表，其中有几年的交易，其中还添加了一年的一周数字列(1-53)。我希望将表连接回自身，并汇总每个年份/周组合，即从年份/周返回12周的行。例如，如果我有2018第15周，我想总结2018年的第5周到第14周。

我不能仅仅看周数之间的差异，因为有很多年。

是否有函数或方法允许此联接条件？

Answer 1

我认为下面这样一种机制，将log表和connect by level <= 53连接在一起可能会有所帮助：

  with log_mytable( transaction_time ) as
  (
   select timestamp'2019-04-08 14:53:23' from dual union all
   select timestamp'2018-04-04 08:25:16' from dual union all
   select timestamp'2018-04-03 12:11:05' from dual union all
   select timestamp'2018-03-03 18:05:06' from dual union all
   select timestamp'2018-03-03 17:15:46' from dual union all 
   select timestamp'2018-03-03 14:05:36' from dual union all 
   select timestamp'2018-02-06 23:05:42' from dual union all 
   select timestamp'2018-01-15 03:24:31' from dual union all 
   select timestamp'2018-01-01 00:15:27' from dual
  ), t as 
   (
   select to_char(transaction_time,'yyyyiw') as transaction_time, 
          substr('&i_week',-2)- lvl + 1 as flag, -- '&i_week' = 201814 whenever prompted
          max(to_char(transaction_time,'yyyyiw')) over (order by lvl desc) 
                                             as max_transaction_time
     from log_mytable   
     right join ( select level as lvl from dual connect by level <= 53 )
       on lvl = substr(to_char(transaction_time,'yyyyiw'),-2) 
      and to_char(transaction_time,'yyyyiw') <= '&i_week'      
   ) 
  select max_transaction_time - flag + 1 as "Week", 
         count(t.transaction_time) as "Count" 
    from t 
   where flag between 1 and 12
   group by max_transaction_time - flag + 1
   order by "Week" desc;

   Week     Count
   201814   2
   201813   0
   201812   0
   201811   0
   201810   0
   201809   3
   201808   0
   201807   0
   201806   1
   201805   0
   201804   0
   201803   1

Answer 2

我不太确定您的专栏是如何定义的，但我尝试过解决您的问题。

with data(year, week, value) as(
SELECT 2018  year
     , Level week
     , 1     value
FROM DUAL
CONNECT BY LEVEL <= 53
union all
SELECT 2017  year
     , Level week
     , 1     value
FROM DUAL
CONNECT BY LEVEL <= 53
) 
select 'Sum values 12 weeks before ' || :year ||' '||:week, sum(value) from data
where 1=1
and (year = :year 
and week < :week
and week >= :week -12)
or ( year =:year -1
and week >= case when :week -12 <=0 then (:week -12 + 53) end)