"Below is temp_data table in Oracle database: TRANSACT_DT_TM LETTER 2023-01-01 00:00:00 K 2023-01 .."

jinxing RaqForum 41 No.
190 View • 1 Years ago

Group and Summarize data, and align to the specified sequence

Below is temp_data table in Oracle database:

TRANSACT_DT_TM	LETTER
2023-01-01 00:00:00	K
2023-01-02 00:00:00	K
2023-01-02 00:00:00	L
2023-01-03 00:00:00	Q
2024-01-02 00:00:00	Y
2024-01-03 00:00:00	B

LETTER is the foreign key field, which points to temp_ref table’s LETTER field, whose order is specified by LETTER_SEQ field in the same table.

LETTER	LETTER_SEQ
Y	10
B	20
Q	30
K	40
L	50

Task: Group temp_data table by the year and LETTER field, count records in each group, and align records to the cross product of the year and the LETTER; record CNT as null if no count value exists. Arrange years in the chronological order and the LETTER according to LETTER_SEQ. Below is the expected result:

YEAR	LETTER	CNT
2023	Y
2023	B
2023	Q	1
2023	K	2
2023	L	1
2024	Y	1
2024	B	1
2024	Q
2024	K
2024	L

Write the following SPL code:

	A
1	=orcl.query("select to_char(TRANSACT_DT_TM,'YYYY') , LETTER,count(1) from temp_data group by to_char(TRANSACT_DT_TM,'YYYY'), LETTER")
2	=orcl.query("select LETTER from temp_ref order by LETTER_SEQ")
3	=xjoin(A1.id(#1):YEAR;A2:LETTER).join(#1:#2,A1:#1:#2,#3:CNT)