topic Re: Row_number over partition by in App Development

Row_number over partition by

ASJ — Mon, 04 Sep 2023 05:46:18 GMT

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

Re: Row_number over partition by

zhaofeng — Mon, 04 Sep 2023 06:21:56 GMT

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;

Re: Row_number over partition by

ASJ — Tue, 05 Sep 2023 10:23:15 GMT

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, -

Re: Row_number over partition by

zhaofeng — Wed, 06 Sep 2023 03:15:01 GMT

Try this