Optimizing Row Splitting in Oracle SQL Using Recursive Common Table Expressions

Oracle SQL: Splitting Rows to Fill Maximum Quantity with Reference Articles

In this article, we will explore how to split rows in a table based on a specific condition and fill the maximum quantity for each group. We will use Oracle SQL and provide an example of how to achieve this using a Common Table Expression (CTE) with recursive queries.

Problem Statement

Suppose we have a list of articles with their corresponding quantities and maximum values. We want to split these rows into groups based on the article name, filling the maximum quantity for each group while maintaining the reference relationship between main articles and add-ons. The goal is to find a way to distribute the total quantity among the bins (or groups) created by this splitting process.

Solution Overview

To solve this problem, we will use a recursive Common Table Expression (CTE) in Oracle SQL. This CTE will calculate the bin tags for grouping, effective quantities, and other necessary values. We will then select data from the CTE to get the final results.

Recursive CTE with Oracle SQL

WITH split_bins (pos, ref_pos, article, quantity, maxquantity, bin_tag, bin_tag2, effective_quantity, prev_quantity,effective_name, ratio) AS (
  -- First static iteration    
  SELECT pos,
         ref_pos,
         article,
         quantity,
-- Calculate the max-quantity
         COALESCE(
           maxquantity, CONNECT_BY_ROOT maxquantity * quantity / CONNECT_BY_ROOT quantity
        ) AS maxquantity,
-- Calculate the bin_tag for grouping
         FLOOR(
           COALESCE(
             SUM(quantity) OVER (
               PARTITION BY article
               ORDER BY pos
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
             ),
             0
           )
           / COALESCE(
               maxquantity, CONNECT_BY_ROOT maxquantity * quantity / CONNECT_BY_ROOT quantity
             )
         ) + 1 AS bin_tag,
-- Calculate the bin_tag for grouping supplements to correct bin
         FLOOR(
           COALESCE(
             SUM(quantity) OVER (
               PARTITION BY article, pos
               ORDER BY pos
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
             ),
             0
           )
           / COALESCE(
               maxquantity, CONNECT_BY_ROOT maxquantity * quantity / CONNECT_BY_ROOT quantity
             )
         ) + 1 AS bin_tag2,
-- Calculate the effective quantity
         LEAST(
            COALESCE(
              maxquantity, CONNECT_BY_ROOT maxquantity * quantity / CONNECT_BY_ROOT quantity
            )
           - MOD(
               COALESCE(
                 SUM(quantity) OVER (
                   PARTITION BY article
                   ORDER BY pos
                   ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                 ),
                 0
               ),
               COALESCE(
          maxquantity, CONNECT_BY_ROOT maxquantity * quantity / CONNECT_BY_ROOT quantity
        )
             ),
           quantity
         ) AS effective_quantity,
-- Previously used quantity (start with zero)      
         0  AS prev_quantity,
-- Propagate the referenced article to the referencing articles    
     CONNECT_BY_ROOT article AS effective_name, 
-- Calculate the ratio of main articles and addons (just dev)    
     quantity / CONNECT_BY_ROOT quantity AS ratio 
  FROM 
    articles START WITH ref_pos IS NULL CONNECT BY PRIOR pos = ref_pos
  -- Second to n iteration    
  UNION ALL
--(pos, ref_pos, article, quantity, maxquantity, bin_tag, effective_quantity, prev_quantity,effective_name, ratio)
  SELECT pos,
         ref_pos,
         article,
         quantity,
         maxquantity,
-- Increase the identifier    
         bin_tag + 1 AS bin_tag,
         bin_tag2 + 1 AS bin_tag2,
-- Calculate the current effective_quantity    
         LEAST(
           quantity - prev_quantity - effective_quantity,
           maxquantity
         ) AS effective_quantity,
-- Calculate the prev_quantity for next iteration    
         prev_quantity + effective_quantity AS prev_quantity,
         effective_name, 
         ratio 
  FROM   split_bins
  WHERE  prev_quantity + effective_quantity < quantity
)
-- Final select data from with-clause    
SELECT pos, ref_pos, article, quantity, maxquantity, bin_tag, bin_tag2,effective_quantity, prev_quantity,effective_name, ratio,
       effective_name||'_limit_'||connect_by_root(bin_tag) AS id
FROM   split_bins START WITH ref_pos IS NULL CONNECT BY PRIOR pos = pos AND PRIOR bin_tag2=bin_tag2
ORDER BY pos, bin_tag;

Explanation

This CTE works as follows:

First Static Iteration: In the first part of the recursive query, we select the initial values for each row in the table. We calculate the maxquantity, bin_tag, and effective_quantity based on these values.
Second to N Iteration: For subsequent iterations, we update the values calculated in the previous iteration and add new rows as necessary.

The final SELECT statement retrieves the desired output from the CTE.

Benefits

This approach has several benefits:

It allows us to split rows based on a specific condition while filling the maximum quantity for each group.
The recursive CTE provides an efficient way to solve this problem, especially when dealing with large datasets.
By using a Common Table Expression (CTE), we can avoid subquerying and improve performance.

Conclusion

In conclusion, the provided Oracle SQL code demonstrates how to use a recursive CTE to split rows based on a specific condition while filling the maximum quantity for each group. This approach provides an efficient way to solve this problem and offers several benefits over traditional methods.

Last modified on 2025-04-09