topic Re: how to generate row_number in tmap in Talend Studio

how to generate row_number in tmap

ffanali0804 — Fri, 15 Nov 2024 22:09:41 GMT

hello,

Is there a solution to use this row_number in a tMap?

SELECT AccountID,

UserTagID,

Status,

LogDate,

"row_number"() OVER (PARTITION BY UserTagID, AccountID, LogDate ORDER BY LogDate DESC) rn

FROM log_user_tag

Thank you so much,

Federico

Re: how to generate row_number in tmap

Anonymous — Fri, 27 Jan 2023 17:05:53 GMT

You can certainly add row numbers in the tMap, but if you are trying to replicate this query, why don't you just use this query in your DB component?

Re: how to generate row_number in tmap

Anonymous — Sun, 29 Jan 2023 14:47:53 GMT

Take a look at sequence of the Numeric routine: https://help.talend.com/r/en-US/8.0/studio-user-guide/numeric-routines

We can do a partition like this:

Numeric.sequence( row1.UserTagID+"__"+row1. AccountID+"__"+row1.LogDate , 1, 1)

ORDER BY I'm afraid is only possible if you add a tSortRow to your flow.

Re: how to generate row_number in tmap

ffanali0804 — Mon, 30 Jan 2023 10:54:07 GMT

I had actually tried this solution but I had doubts about the performance, so I wanted to compare it with the one with the tmap. If you confirm that they are similar then I would use the one you are proposing directly 🙂

thanks

Re: how to generate row_number in tmap

Anonymous — Mon, 30 Jan 2023 11:41:49 GMT

You will NEVER outperform a DB manipulating data like this, with any other application that has to receive the data, then sort it. This is what databases are for. If you have all of your data in a single DB, you may as well carry out any sorting or calculations that can be carried out in the DB, in the DB. Then send it to other applications to be further processed.

Re: how to generate row_number in tmap

Anonymous — Mon, 30 Jan 2023 11:48:06 GMT

Databases are made to run queries super fast. We're trying to maninpulate data that is not in the right order, etc. It can't be that efficient.