Row_number over partition by

[ASJ]

hello ,

I have inline table like 

LOAD * INLINE [
x, 201112, a1
x, 201212, a2
x, 201312, a2
x, 201412, a3
y, 201112, a3
y, 201212, a2
y, 201312, a2
y, 201412, a3
z, 201112, a4
z, 201212, a4
z, 201312, a3
z, 201412, a3

];

I would like to give row number for each row according to matno and operationdate.

The result would like 

id, date, code, rownum
x, 201112, a1, 1
x, 201212, a2, 2
x, 201312, a2, 2
x, 201412, a3, 3
y, 201112, a3, 1
y, 201212, a2, 2
y, 201312, a2, 2
y, 201412, a3, 3
z, 201112, a4, 1
z, 201212, a4, 1
z, 201312, a3, 2
z, 201412, a3, 2

autonumber() too slow TT

Thanks in advance 

[zhaofeng · Accepted solution]

Try this

T1:
LOAD * INLINE [
A,B,C
x, 201112, a1
x, 201212, a2
x, 201312, a2
x, 201412, a3
y, 201112, a3
y, 201212, a2
y, 201312, a2
y, 201412, a3
z, 201112, a4
z, 201212, a4
z, 201312, a3
z, 201412, a3
];

noconcatenate

T2:
LOAD
    *
Resident T1
order by A,B,C asc
;


T3:
LOAD
    if(A<>previous(A),1,
    if(A=previous(A) and C<>previous(C),peek('rownumber')+1,
    if(A=previous(A) and C=previous(C),peek('rownumber')))) as rownumber,
    *
Resident T2;

Drop Tables T1,T2;

[ASJ]

Thank you.
Can I ask you more questions?

How about the following results?

id, date, code, rownum
x, 201112, a1, 1
x, 201212, a2, 2
x, 201312, a2, -
x, 201412, a3, 3
y, 201112, a3, 1
y, 201212, a2, 2
y, 201312, a2, -
y, 201412, a3, 3
z, 201112, a4, 1
z, 201212, a4, -
z, 201312, a3, 2
z, 201412, a3, -

[zhaofeng · Accepted solution]

Try this

T1:
LOAD * INLINE [
A,B,C
x, 201112, a1
x, 201212, a2
x, 201312, a2
x, 201412, a3
y, 201112, a3
y, 201212, a2
y, 201312, a2
y, 201412, a3
z, 201112, a4
z, 201212, a4
z, 201312, a3
z, 201412, a3
];

noconcatenate

T2:
LOAD
    *
Resident T1
order by A,B,C asc
;


T3:
LOAD
    if(A<>previous(A),1,
    if(A=previous(A) and C<>previous(C),peek('rownumber')+1,
    if(A=previous(A) and C=previous(C),peek('rownumber')))) as rownumber,
    *
Resident T2;

noconcatenate

T4:
Load
    A,
    B,
    C,
    IF(A=previous(A) and rownumber=previous(rownumber),null(),rownumber) as rownumber
Resident T3;

Drop Tables T1,T2,T3;