Geek Answers Handbook

How to find <N> sequential sales bonus in oracle sql

I have the following definition of a table with sample data.

Table: 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

Given the table above, I need to find N or more consecutive records by month, where the bonus is not equal to zero. For example, if N = 3, the result set will return the following:

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
+4
source share
2 answers

This is based on this answer: fooobar.com/questions/479070 / ...

Based on this trick, we need to first create a set of sequential values ​​that can be used. Then you can do the same approach:

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;

The above may be simplified, but it’s easier for me to debug if I can check the result of each step of this approach.

( , ) "":

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

+1

, :

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;
0

More articles:


All Articles