SQL & Database reader

KNIME Analytics Platform
1

Dear's,

Just need your help for a "simple" SQL reader....

With the table :

ID        Date

aa        1.2.2017

ab        1.3.2017

ac         1.2.2017

aa        1.6.2017

aa        1.3.2017

ab        1.4.2017

i must count how many ID appears, per month, for the first time (MinDate). Result should be :

1.2.2017 : 2  (aa & ac)

1.3.2017 : 1 (ab)

DB is a Microfoft SQL.....

Many thanks for your help

 

2

Hi MitchP,

For this purpose you can use the Database GroupBy node. First you can group for the dates and then in the aggregation tab you can set a concatenation for the ID column.

Hope that helps,

Best,

Vincenzo

3

Hi Vincenzo,

Many thanks, but I have to build it as SQL, for a new view in the DWH

Best,

Mitch

4

Hi MitchP,

Then, I think you can use the Database Query node for that purpose.

If you do not know which query statement to use, I would suggest to use first the Database GroupBy node, execute it, and then check the SQL statement generated by the GroupBy node available in the Database Connection Output -> Connection tab.

Hope that helps,

Best,

Vincenzo

 

5

Great...

 

Thank you Vincenzo

7

@Vincenzo, looking to understand how to do the unique concatenate in the Database GroupBy node - it does not seem to be visible. Do I need to use the parameters somehow?

8

Which database are you using? My guess is that the database that you are using does not support the unique concatenate aggregation method.

Cheers,
Vincenzo

9

Connecting to SQL Server… it seems it has the ability to do so in SQL, just not sure how whether possible / how to do this using the Database Groupby?

10

@supersharp, Database GroupBy works in the same way as the GroupBy node in KNIME.
You can find more details about GroupBy node in the following videos:
1- Basic Aggregations with the GroupBy node: https://www.youtube.com/watch?v=JQ-OWMt48ew
2- Advanced Aggregations with the GroupBy Node https://www.youtube.com/watch?v=qym8hqYiTxE

The “Manual Aggregation” tab in the configuration window of the Database GroupBy node allows you to change the aggregation method of more than one column. In order to do so select the columns to change, open the context menu with a right mouse click and select the aggregation method to use. In this case it would be unique concatenate.

Hope this helps! If not please feel free to share the workflow you are working on and the issue(s) you are facing! :slight_smile:

11

Thanks @Vincenzo.

I am very familiar with the regular GroupBy node, however the Database GroupBy does not appear to offer the same aggregation options.

For example, unique concatenate is just not an option that you can select in the Manual Aggregation tab of Database GroupBy:
image

vs. what is available in the regular GroupBy:

image
image695×329 14.9 KB

I’d like to concatenate unique values in the ExpType_Code, ideally in-database, as I am looking at 10 million rows, and the regular Groupby would take too long. Any other suggestions you can think of would be appreciated :slight_smile:

Thanks!

1 Like
12

Hi @supersharp

Thanks for clarifying.
It seems that SQL Server does not support the unique concatenate as aggregation function out of the box https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-2017

The concat function works on different columns https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-2017

Cheers,
Vincenzo

1 Like
13

We had a similar problem with SQL Server 2014, but there is a way to solve this:

Hope that helps! I just don’t know how the performance is on big tables.

Kind regards
Florian

2 Likes