Inverse Grouping in SPL
【Question】
I need a list of user IDs (course_user_ids) that is currently stored in a single field of a larger table.
I have a table called courses that contains course information with course_id and course_students as such:
-----------------------------------------------------------
| course_id | course_students |
----------------------------------------------------------
| 1 | a:3:{i:0;i:12345;i:1;i:22345;i:2;i:323456;} |
-----------------------------------------------------------
| 2 | a:32:{…} |
-----------------------------------------------------------
The course_students part contains 3 chunks of information:
1. The number of students (a:3:{…) -- not needed
2. The order/key for the array of each student ({i:0;… i:1;… i:2; …}) -- also not needed
3. The course_user_id(i:12345; … i:22345;… i:32345;)
I only need the course_user_id and the original course_id, resulting in a new table that I can use for joins/subqueries like this:
------------------------------
| course_id | course_user_id |
------------------------------
| 1 | 12345 |
------------------------------
| 1 | 22345 |
------------------------------
| 1 | 323456 |
------------------------------
(Ideally able to continue to break out values for other course_ids and course_user_ids, but not a priority:)
| … | … |
------------------------------
| 2 | … |
------------------------------
| 2 | … |
------------------------------
| 97 | … |
------------------------------
| 97 | … |
------------------------------
| … | … |
------------------------------
Note: the course_user_id can vary in length (some are 5 digits, some are 6)
Any ideas would be much appreciated!
Below is a solution, but I don’t think it’s a good one。
select c.course_id,u.user_id
from
courses c
join users u
on u.user_id=if(instr(c.course_students,concat(“:”,u.user_id,“;”))>0,u.user_id,c.course_students)
【Answer】
First, parse strings surrounded by the braces; then get the desired strings from positions of even numbers; last, generate records according to the sequence of strings. This is inverse grouping and it’s hard to do it in SQL. You can easily implement it in SPL. Here’s SPL code:
A |
|
1 |
$select course_id,course_students from tb |
2 |
=A1.run(course_students=course_students.split@b(";").step(2,2).(~.split(":")(2))) |
3 |
=A2.news(course_students;A2.course_id,~:course_user_id) |
A2: Get a sequence of substrings by splitting each course_students value; the SPL split() function splits a string to generate a sequence; step(2,2) function gets members from positions that are even numbers;
A3: Generate a new table sequence according to A2’s sequence.
You can call the SPL script in another application via esProc JDBC. For details, see How to Call an SPL Script in Java