"【Question】 i need help with this i try to think about resolve this but i cant find how resolve w .."

blackduckie RaqForum 28 No.
521 View • 5 Years ago

Group-based Transposition

SQL(179)

【Question】

i need help with this i try to think about resolve this but i cant find how resolve with mysql, I need if the users have record in the week, and show this result with this form:

	user 1	user 2	user 3
week 1	yes	no	yes
week 2	yes	yes	no
week 3	yes	no	yes
week 4	no	yes	yes
week 5	yes	no	no
week 6	yes	no	yes

I have table user and table record

TABLES	FIELDS
user	id
	name
record	id_user
	date

【Answer】

There are two tables: one is user table, the other is record table, which records a user activity in a certain date. The task is to compose records expressing whether or not a user has activities (record yes if a user has an activity and no if they haven’t any) and to display them in the desired form.

For this task, one user corresponds to multiple dates and there isn’t any summarization involved. Moreover, dates need to be dynamically converted to week numbers, for which we’ll check whether a user has any activity in this time period. So, in this case, the SQL way of transposing static columns is neither a satisfactory nor an easy solution. Yet there is a convenient way of doing this. That’s the use of SPL. Here’s the SPL script:

	A
1	=connect(“test”)
2	=A1.query(“select user.name,record.date,record.id_user from user, record where record.id_user=user.id”).run(date=interval@w(“2018-11-1”,date)+1)
3	=A2.group(id_user)
4	=A3.max(date).new(~:week,${A3.(“\“No\”:”+name).concat@c()})
5	=A3.run(~.run(A4(date).field(A3.#+1,“Yes”)))

A1: Connect to test database;

A2: Query user table and record table to match user name and record date, and to convert dates to corresponding week numbers;

A3: Group records by id_user field. Each group is a sequence of records made up of name, date and id_user;

A4: Find the biggest week number based on A2, and generate a new table sequence consisting of week column and user name columns. User name columns are dynamically generated according to A3’s groups. The initial values in all user name columns are set as “No”.

A5: Calculate the corresponding week number in A4 for A3’s each record in each group. The result is the nth record in A4, that is A4(n). Then replace the value in this record of the corresponding user in A3 with “Yes”.

A4’s result is what we want.