如何在Oracle SQL中查找<N>个连续销售奖金

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

- empoy

---

你为找到解决方案做了什么？ - user4399775

Answer 1

这是基于这个答案的: https://dev59.com/92kw5IYBdhLWcg3wUI7x#9977908 基于这个技巧，我们需要先生成一组连续的可用值。然后可以采取相同的方法:

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

- user330315

---

对于小的N，你可以使用LEAD/LAG。 - Falco

Answer 2

或许这可以帮助你：

SELECT x.*
  FROM(SELECT branch, e, f
         FROM(SELECT branch,
                     LEAD(month, 1, 13) OVER (PARTITION BY branch ORDER BY month) - month - 1 AS d,
                     month + 1 AS e,
                     LEAD(month, 1, 13) OVER (PARTITION BY branch ORDER BY month) - 1 AS f
                FROM(SELECT DISTINCT
                            branch, 0 AS month, 0 AS bonus
                       FROM target_bonus x
                      UNION
                     SELECT branch, month, bonus
                       FROM target_bonus x
                    )
               WHERE bonus = 0
             )        
        WHERE d >= 3 -- This is your N
      ) y
  JOIN target_bonus x
    ON x.branch = y.branch
   AND x.month BETWEEN y.e AND y.f
 ORDER
    BY x.branch, x.month; 

- DirkNM

---

谢谢您的回复，DirkNM先生...但是查询获取了所有数据...我只想获取连续有奖金的月份，比如样例中获取3个连续有奖金的月份，第1、2个月没有奖金，第3、4、5个月有50的奖金，第6、7个月没有奖金，第8、9、10个月有100的奖金等等...我只想获取这3个连续有奖金的月份，所以结果将是第3、4和5个月...第8、9和10个月。 - empoy

@empoy 上面的语句返回了你在问题中提供的预期结果，请参见http://sqlfiddle.com/#!4/df390/1。 - DirkNM

@a_horse_with_no_name.... 你的第一个查询就是我需要的....问题是我正在使用较旧版本的Oracle 6i表格和报告...有什么解决方法可以在Oracle 6i版本中使用查询...提前致谢... - empoy

感谢您的查询...我尝试了一下，它可以工作...还有一件事，我只想获取前<N>个连续月份...让N=3，例如1月到3月有奖金，4月到5月没有奖金，6月到8月有奖金，9月到10月没有奖金，其余月份有奖金...所以结果将是...1月到3月...因为这是第一个连续的。 - empoy