"[url] Let’s assume there is a table ‘orders_news’ that contains the order information of each cu .."

mars RaqForum 25 No.
70 View • 1 Months ago

3.4 Inverse grouping

example codes for comparing spl sql and python(23)

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

customer_id	order_date	product_name
1	2022-01-01,2022-01-02,2022-01-03	Product A,Product B,Product C
2	2022-01-01,2022-01-02	Product D,Product E

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

SPL

	A
1	=file(“order_news.txt”).import@t()
2	=A1.news(product_name.split(“,”).len();customer_id,date(order_date.split(“,”)(#)):order_date,product_name.split(“,”)(#):product_name)

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;