3.3 Ordered grouping

Let’s assume there is a table ‘orders_news’ that contains the order information of each customer:

It is required to split one row of data into multiple rows based on the purchase quantity.

SPL

The news()is essentially a two-layer loop function. It first loops through A1, then loops through the first parameter product_name.split(“,”).len().

SQL

SELECT customer_id,
  TRIM(REGEXP_SUBSTR(order_date, '[^,]+', 1, LEVEL)) AS order_date,
  TRIM(REGEXP_SUBSTR(product_name, '[^,]+', 1, LEVEL)) AS product_name
FROM order_news
CONNECT BY LEVEL <= REGEXP_COUNT(product_name, ',') + 1
AND PRIOR customer_id = customer_id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

Python

Python does not provide the inverse grouping method, so it has to hard code.

4.1 Basic aggregation
Example codes for comparing SPL, SQL, and Python