sql - 如何在oracle sql中找到个连续的销售奖金

Question

我有以下带有示例数据的表定义。

表:target_bonus

branch   month    bonus
  1        1       100
  1        2       0
  1        3       200
  1        4       150
  1        5       175
  1        6       180
  1        7       125
  1        8       0
  1        9       0
  1        10      0
  1        11      125
  1        12      130
  2        1       0
  2        2       0
  2        3       200
  2        4       150
  2        5       175
  2        6       180
  2        7       125
  2        8       110
  2        9       105
  2        10      115
  2        11      125
  2        12      130

鉴于上表，我需要按月查找奖金不为零的N个或更多连续记录。例如，如果 N = 3，结果集将返回以下内容:

branch     month     bonus
  1        3       200
  1        4       150
  1        5       175
  1        6       180
  1        7       125
  2        3       200
  2        4       150
  2        5       175
  2        6       180
  2        7       125
  2        8       110
  2        9       105
  2        10      115
  2        11      125
  2        12      130

Answer 1

这建立在这个答案的基础上:https://stackoverflow.com/a/9977908/330315

基于这个技巧，我们需要首先生成一组可以使用的连续值。然后可以采取相同的方法:

with flagged as (
  select t.*,
         case
            when (bonus > 0) then row_number() over (partition by branch order by month) 
            else 0
          end as bonus_rn
  from target_bonus t
), numbered as (
  select f.*,
         bonus_rn - row_number() over (partition by branch order by month)  as grp
  from flagged f
), grouped as (
  select n.*,
         sum(grp) over (partition by branch order by month) as grp_nr
  from numbered n
), cons as (
  select g.*,
         count(*) over (partition by branch, grp_nr) as num_consecutive
  from grouped g
  where bonus > 0
)
select branch, month, bonus
from cons
where num_consecutive > 1 -- change here if you want 
order by branch, month;

以上可能可以简化，但我发现如果我可以检查这种方法的每一步的结果，调试会更容易。

另一种查询(更类似于链接答案中的查询)只会显示每个“间隔”的开始和结束月份:

with flagged as (
  select t.*,
         case
            when (bonus > 0) then row_number() over (partition by branch order by month) 
            else 0
          end as bonus_rn
  from target_bonus t
), numbered as (
  select f.*,
         bonus_rn - row_number() over (partition by branch order by month)  as grp
  from flagged f
), grouped as (
  select n.*,
         sum(grp) over (partition by branch order by month) as grp_nr
  from numbered n
)
select branch, 
       min(month) as start_month, 
       max(month) as end_month ,
       count(*) as num_consecutive
from grouped 
group by branch, grp_nr
having count(*) > 1 -- change here if you want 
order by branch, start_month;

对于大表，此解决方案不会很快。

SQLFiddle: http://sqlfiddle.com/#!4/90b4c/1