Talend community forum / How to implement RowNumber() Over(PARTITION BY Order by)

dylanMa · 2012-06-08 05:11:13

Tags: [context, database, delimited file, development, mssql, positional file, PostgreSQL]

Hi,

i have some data,ex,
Item# Price ParentItem
001 20 111
002 30 111
003 20 222
004 30 222

In MSSQL,my sql is

SELECT item,price,parentItem, index FROM (
select item,price,parentItem,ROW_NUMBER() OVER(PARTITION BY ParentItem ORDER BY Price ) AS index
) AS T
WHERE T.index = 1

the query output result is:
Item# Price ParentItem index
001 20 111 1
002 20 222 1

ParentItem the same just get the lowest price,Which component i can use?

Thx

Last edited by dylanMa (2012-06-08 05:11:41)

pedro · 2012-06-08 05:14:57

Hi

You might try to use tMssqlRow.
And create job like this.

tMssqlRow-->tParseRecordSet-->Output

Regards,
Pedro

dylanMa · 2012-06-08 05:17:52

pedro wrote:
Hi

You might try to use tMssqlRow.
And create job like this.

tMssqlRow-->tParseRecordSet-->Output

Regards,
Pedro

i store the data in disk files,i can't use they components

pedro · 2012-06-08 05:19:37

Hi

What do you mean 'i store the data in disk files'?

Regards,
Pedro

dylanMa · 2012-06-08 05:21:53

pedro wrote:
Hi

What do you mean 'i store the data in disk files'?

Regards,
Pedro

I use tMSSQLinput and store the query result in disk file, use tFileOutputMSPositional

dylanMa · 2012-06-08 07:07:54

pedro wrote:
Hi

Just extract the query from your file and save it into a context variable(let's say it called context.query).
Type context.query in the 'Query' text field of tMSSQLRow or tMSSQLInput.

Regards,
Pedro

i can't do this, i had upload my flow

i want to use Rownumber() in tUnite

, can u give me a demo?

Uploaded Images

pedro · 2012-06-08 07:25:09

Hi

Sorry. Could you explain your requirement more clearly?
Where is the source data? In DB or in a delimited file?
Or I may go wrong...

Regards,
Pedro

dylanMa · 2012-06-08 07:31:14

pedro wrote:
Hi

Sorry. Could you explain your requirement more clearly?
Or I may go wrong...

Regards,
Pedro

Okay, i get the data from disk file, and i want implement the function of "RowNumber() Over(PARTITION BY Order by)"
after tUnit compement, because "RowNumber() Over(PARTITION BY Order by)" is SQL function ,
so i don't konw how to do it in TOS

Uploaded Images

pedro · 2012-06-08 07:57:58

Hi

Got you.
In fact, your requirement is to get the min Price group by ParentItem from a disk file, right?

Just try tAggregateRow.

Regards,
Pedro

Uploaded Images

dylanMa · 2012-06-09 06:23:57

pedro wrote:
Hi

Got you.
In fact, your requirement is to get the min Price group by ParentItem from a disk file, right?

Just try tAggregateRow.

Regards,
Pedro

hi,

if i have multiple columns , but i just want aggregate on column , how can i do it?

Last edited by dylanMa (2012-06-09 06:26:40)

Joe · 2012-06-29 04:02:06

pedro wrote:
Hi

Got you.
In fact, your requirement is to get the min Price group by ParentItem from a disk file, right?

Just try tAggregateRow.

Regards,
Pedro

Hi Pedro

maybe not exactly like this. I needn't the operation of 'min()' on 'item'. about the function of row_number over(parttion by column_1,column_2 order by column_3),it just add another column which show the sequence in its own group. as following example.
1.orginal table:
Item# Price ParentItem
001 20 111
001 20 112
001 20 223
002 30 222
002 30 223
003 40 333
2.process:
row_number() over(parttion by Item,Price order by ParentItem) as Seq
ps: 'parttion by Item,Price ' means 'group by Item,Price '

3.so we get the result:
Item# Price ParentItem Seq
001 20 111 1
001 20 112 2
001 20 223 3
002 30 222 1
002 30 223 2
003 40 333 1

Any suggestion? It puzzles me long...

Thanks in advance.
Joe

pedro · 2012-06-29 08:03:17

Hi Joe

I create a job for you.
Hope this will help you.

Code in tJava

Code:

output_row.Item = input_row.Item;
output_row.Price = input_row.Price;
output_row.ParentItem = input_row.ParentItem;
if(Item_tMemorizeRows_1[1]!=Item_tMemorizeRows_1[0]){
output_row.seq = 1;
context.new1++;
}else{
output_row.seq = Numeric.sequence("s"+context.new1,2,1);
}

Besides, create a context variable in this job called new1 whose data type is int and default value it 0.

Regards,
Pedro

Uploaded Images

Joe · 2012-07-06 09:25:08

Hi pedro

Thanks for your advice.Although it works not that good for me , i just get seq values 1,and all be the same.I think there must be something wrong in my job,and i'll go on testing on it.

Joe

Joe · 2012-07-07 02:45:46

Hi pedro

I think there is a bug in this method. we can't be sure that data are read into tMemorizeRows order by Item.So the rows stored in tMemorizeRows cannot compare like this way. such as , we read
" 001 20 111 " first ,and then read
" 002 30 112 ",and then
" 001 20 114 " So,we the the seq value (fault)
" 001 20 111 1 "
" 001 20 114 1 "
" 002 30 112 1 "
But the right result should be:
" 001 20 111 1 "
" 001 20 114 2 "
" 002 30 112 1 "

Joe

pedro · 2012-07-09 06:57:43

Hi Joe

Why don't you use tSortRow between tFileInputDelmited and tMemorizeRow?
Then you can get rows order by Item.

Regards,
Pedro

Joe · 2012-07-11 09:18:41

Hi pedro

I did it,but not work. the value of seq i got were all 1.
absolutely as you did,and add tSortRow sort by Item and ParentItem.

intratek · 2012-07-20 09:13:35

I am also facing the same problem...

All the time for 580 rows I am getting the seq value as 1 and then there are next 4 lines as,

|1
|2
|3
|4

There is blank (no value) before '|'

Have you got the solution ??

Is there any problem reg context variable ??
Any other reason ??

Please help..

Regards

intratek · 2012-07-21 11:17:20

Hi..

It works perfect for tFixedFlowInput, but not with tFileInputDelimited or tFileInputExcel...

It means that tMemorizeRows and the code in tJavaRow are working..
Context variable is also working fine..

What might be the reason.

Thanks.

Announcement

Unanswered posts

#1 2012-06-08 05:11:13

How to implement RowNumber() Over(PARTITION BY Order by)

#2 2012-06-08 05:14:57

Re: How to implement RowNumber() Over(PARTITION BY Order by)

#3 2012-06-08 05:17:52

Re: How to implement RowNumber() Over(PARTITION BY Order by)

pedro wrote:

#4 2012-06-08 05:19:37

Re: How to implement RowNumber() Over(PARTITION BY Order by)

#5 2012-06-08 05:21:53

Re: How to implement RowNumber() Over(PARTITION BY Order by)

pedro wrote:

#6 2012-06-08 07:07:54

Re: How to implement RowNumber() Over(PARTITION BY Order by)

pedro wrote:

#7 2012-06-08 07:25:09

Re: How to implement RowNumber() Over(PARTITION BY Order by)

#8 2012-06-08 07:31:14

Re: How to implement RowNumber() Over(PARTITION BY Order by)

pedro wrote:

#9 2012-06-08 07:57:58

Re: How to implement RowNumber() Over(PARTITION BY Order by)

#10 2012-06-09 06:23:57

Re: How to implement RowNumber() Over(PARTITION BY Order by)

pedro wrote:

#11 2012-06-29 04:02:06

Re: How to implement RowNumber() Over(PARTITION BY Order by)

pedro wrote:

#12 2012-06-29 08:03:17

Re: How to implement RowNumber() Over(PARTITION BY Order by)

Code:

#13 2012-07-06 09:25:08

Re: How to implement RowNumber() Over(PARTITION BY Order by)

#14 2012-07-07 02:45:46

Re: How to implement RowNumber() Over(PARTITION BY Order by)

#15 2012-07-09 06:57:43

Re: How to implement RowNumber() Over(PARTITION BY Order by)

#16 2012-07-11 09:18:41

Re: How to implement RowNumber() Over(PARTITION BY Order by)

#17 2012-07-20 09:13:35

Re: How to implement RowNumber() Over(PARTITION BY Order by)

#18 2012-07-21 11:17:20

Re: How to implement RowNumber() Over(PARTITION BY Order by)

Board footer