I would like to calculate the total of a column by category without using the joiner node. My curiosity is how to execute this using column expressions to mimic the WINDOWs FUNCTIONS like SQL.
Yes, I know it should be possible to do this using Python or Java Snippet.
I’m studying SQL (Postgres), and since I use column expressions a lot, I was curious to know if it’s possible to do it through them or any node that doesn’t depend on the group loop.
In SQL (postgres)
it is possible to use do the function OVER () , with PARTITION ()
And achieve powerful results.
In this example I’m using “avg” instead “sum”. Is the sum all all values.
I could, group by any categories’ em bring the average, and bring the avg of the a column with a filter inside. Example: the sum of column where the year is greater than 2000.
Well, in Knime, I could do some “group loop”, and the result of the sum, store into a variable and print in the table, using joiner etc…
It’s possible to mimic, like SQL in one “simple” code?
I imagine that I will have to use group loop, or variable, joiner, but this post is for curiosity, learning.
Hi @Felipereis50 , I’m possibly not fully understanding the question, but it’s not a task I would expect Column Expressions to be particularly suitable for), but I wouldn’t use a loop for it either.
I would think the most straightforward way of providing similar output to the sql functions you describe would be with the GroupBy node rather than a group loop.
In the simple case, grouping by the “Category” column, and deriving sum(Value) and then joining the result back to category would achieve the first output, whilst in the second case, initially filtering rows on Year, then doing the equivalent GroupBy and Join as before would achieve the second output.
Mind you, if you really have the urge just to write SQL against your KNIME tables, … you could try out my KNIME Table Session Connector component, and its sister-component the KNIME Table Connector
and
Just attach to the table KNIME table and write your SQL (H2 syntax)… using your choice of the DB nodes
Wonderful.
I’ll definitely analyze the components. They must be very good (coming from you, even).
I’m also studying SQL, and I want to merge KNIME with other languages. (still have a lot to learn with KNIME) I have several goals to achieve in my studies.
