This step support following aggregation methods.
- Sum
- Average or Mean
- Median
- Minimum
- Maximum
- Standard deviation
- Percentile
- Number of values
- Number of distinct values
- Number of rows
- First non-null value
- Last non-null value
- First value null inclusive
- Last value null inclusive
- Cumulative sum for all rows
- Cumulative average for all rows
- Concatenate strings separated by comma
- Concatenate strings separated by a value.
Here are the commonly used options for this step.
- Group fields - The fields for Group by.
- Aggregates fields - Specify aggregate field names and aggregate methods.
- Include all rows - Output will contain all rows instead of just the aggregate rows
- Add a line number, Restart in each group - Option to be used along with "Include all rows"
- Always give back a result row - To make sure there is an output row in case of no input rows ( for count of 0 ).
Step wise illustration on how to use "Group By" step given below.
Step 1
Create a demo transformation.This demo transformation will read from a CSV input file which contain product information. "Group By" step will be used to aggregate product price based on product category. The input records need to be sorted to use Group BY. A sort step is used for this.
Here are the configurations for the CSV input and Sort step.
Step 2
Drag the "Group By" step into the canvas.Step 3
Set the group by field and aggregate type.Category is used as the group by field here for demo purposes. Sum, maximum, minimum, first value and last value selected as aggregate types.
Step 4
Drag the "Dummy" step into the canvas to complete the flow.Execution Log
trf_Demo_Aggregates - Transformation is pre-loaded.
Spoon - Transformation opened.
Spoon - Launching transformation [trf_Demo_Aggregates]...
Spoon - Started the transformation execution.
trf_Demo_Aggregates - Dispatching started for transformation [trf_Demo_Aggregates]
trf_Demo_Aggregates - Allocating Steps & StepData...
trf_Demo_Aggregates - Transformation is about to allocate step [CSV file input] of type [CsvInput]
CSV file input.0 - distribution activated
trf_Demo_Aggregates - Transformation is about to allocate step [Sort rows] of type [SortRows]
Sort rows.0 - distribution activated
trf_Demo_Aggregates - Transformation is about to allocate step [Group by] of type [GroupBy]
Group by.0 - distribution activated
Group by.0 - Starting allocation of buffers & new threads...
Sort rows.0 - The number of binary string to data type conversions done in this sort block is 0
Sort rows.0 - Finished processing (I=0, O=0, R=10000, W=10000, U=0, E=0)
Group by.0 - Finished processing (I=0, O=0, R=10000, W=26, U=0, E=0)
Dummy (do nothing).0 - Finished processing (I=0, O=0, R=26, W=26, U=0, E=0)
trf_Demo_Aggregates - Handling extension point for plugin with id 'DataRefineryTransFinishListener' and extension point id 'TransformationFinish'
Spoon - The transformation has finished!!
Include All rows - Disabled
Results preview where "Include all rows" option is disabled. Only aggregated rows will be there in the output, in this case one row per category since group by was done based on category.Include All rows - Enabled
Results preview where "Include all rows" option is enabled. All input rows will be there in the output, aggregated values such as sum_price, max_price e.t.c will be repeated for the same category in the output.Include All rows - Enabled with Line number
Results preview where "Include all rows" option is enabled and "Add line number" is enabled. All rows will be there in the output with a line number for each row.Always give back a result row
Add a filter step to the dummy transformation to filter out all input records.There will be no results rows if the "Always give back a result row" is disabled. Need to enable this to get output result row with Counts as 0 and other aggregate values as Null.
31 comments :
How does Percentile aggregation work?
Wow amazing i saw the article with execution models you had posted. It was such informative. Really its a wonderful article. Thank you for sharing and please keep update like this type of article because i want to learn more relevant to this topic.
Hadoop Training in Chennai
Great post! I am actually getting ready to across this information, It's very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
Informatica training in chennai
Great article. It helps to explain complicated things to people who are not competent and explicate why this is important.
dataroom software
Good post. I like your post, Thanks for sharing.
web design institute in chennai
this is really too useful and have more ideas from yours. keep sharing many techniques. eagerly waiting for your new blog and useful information. keep doing more.
Digital Marketing Course in Chennai
Dude, there are too many ads. I can't even search. It anonying...
Thank You for sharing your article, This is an interesting & informative blog. It is very useful for the developer like me. Kindly keep blogging. Besides that Wisen has established as Best Corporate Training in Chennai .
Nowadays JavaScript has tons of job opportunities on various vertical industry. Know more about JavaScript Framework Training visit Corporate Training Companies in India.
This post gives me detailed information about the technology. corporate training companies in india
Its interesting and informative. Thanks for your great information
Embedded Project Center in Chennai | Best Embedded Project Center in Velachery
Quite a useful post, I learned some new points here. Thanks admin please keep posting updates regularly to enlighten our knowledge.
ME/M.Tech Project Center in Chennai | ME/M.Tech Project Center in Velachery
Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site
Website Design and Development Company in Bangalore
ECommerce Web Design Company in bangalore
Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog.
Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a Front end developer learn from Javascript Online Training from India .
or learn thru JavaScript Online Training from India. Nowadays JavaScript has tons of job opportunities on various vertical industry. ES6 Training in Chennai
I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site.
python training in chennai
python course in chennai
python training in bangalore
I would assume that we use more than the eyes to gauge a person's feelings. Mouth. Body language. Even voice. You could at least have given us a face in this test.
Selenium training in Chennai | Selenium training institute in Chennai | Selenium course in Chennai
Selenium training in Bangalore | Selenium training institute in Bangalore | Selenium course in Bangalore
Java training in Chennai | Java training institute in Chennai | Java course in Chennai
Java training in Bangalore | Java training institute in Bangalore | Java course in Bangalore
Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
rpa training in Chennai | rpa training in bangalore | best rpa training in bangalore | rpa course in bangalore | rpa training institute in bangalore | rpa training in bangalore | rpa online training
This is a nice article here with some useful tips for those who are not used-to comment that frequently. Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.
Best Devops training in sholinganallur
Devops training in velachery
Devops training in annanagar
Devops training in tambaram
Thank you for allowing me to read it, welcome to the next in a recent article. And thanks for sharing the nice article, keep posting or updating news article.
Data Science training in rajaji nagar | Data Science Training in Bangalore
Data Science with Python training in chennai
Data Science training in electronic city
Data Science training in USA
Data science training in pune
Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.
Best AWS Training in Chennai | Amazon Web Services Training in Chennai
Learn Amazon Web Services Tutorial |Best AWS Tutorials For Beginners
Best AWS Interview Questions And Answers
Best AWS Training in Chennai | No.1 AWS Training Institute in Chennai Velachery, Tambaram, OMR
Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative. I can't wait to read lots of your posts.
Data science course in bangalore | Data Science training with placement in Bangalore
It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.
Most of ideas can be nice content.The people to give them a good shake to get your point and across the command.
Java training in Bangalore|best Java training in Bangalore
Do not think! Just come with us to BGAOC and play with us. best casino games come to us and win soon.
All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
microsoft azure training in bangalore
rpa training in bangalore
best rpa training in bangalore
rpa online training
Супер отличная гибкая світлодіодна стрічка на любой вкус и цвет, обычно покупаю в интернет магазине.
Home Mart is a site about Home Improvement, Furniture, Home Appliances and many more.
Check out the best
bedroom furniture nz
entertainment unit
shoe rack
I am really happy to say it’s an interesting post to read. I learn new information from your article, you are doing a great job, Keep it up.
mobile phone repair in Novi
iphone repair in Novi
cell phone repair in Novi
phone repair in Novi
tablet repair in Novi
ipad repair in Novi
mobile phone repair Novi
iphone repair Novi
cell phone repair Novi
phone repair Novi
Nice blog,I understood the topic very clearly,And want to study more like this.
Data Scientist Course
IEEE Final Year projects Project Center in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes. Final Year Project Domains for IT
JavaScript Training in Chennai
JavaScript Training in Chennai
The Angular Training covers a wide range of topics including Components, project projects for cse. Angular Training
Thanks for sharing this information Freyr Energy was founded on the principles of making solar energy affordable and accessible for everyone. In order to make adoption of solar energy a reality at the grass-root level, we have identified that consumer awareness, affordability and accessibility play an integral role. With our innovative platform, SunPro+, our extensive channel-partner network and our efficient service we ensure that these three factors are addressed to make sure your venture into solar energy is hassle-free. Best solar company in Hyderabad-freyr
Thanks for the efforts in writing the wonderful article.
php interview questions and answers
salesforce interview questions
blue prism interview questions
javascript interview questions
ethical hacking interview questions
networking interview questions and answers
Our Informatica Developer Training will provide you to learn the performance of ETL tasks with live classes. ITGuru Informatica Online Training also include projects
online informatica training| informatica course online
Thanks for sharing such a useful blog. Really! This Blog is very informative for us which contains a lot of information about the Writing. I like this post. SEO Cheltenham
Post a Comment