Grouping Data Through Patterns
Kim Berg Hansen · Apress eBooks · 2020
Grouping data with a group by clause requires you to find one or more values that are the same in those rows you want to belong to the same group. Often that is simply some columns or just as often a calculation on some columns. Sometimes, though, the condition that tells you a row belongs to a group is not simply a condition you can calculate using only values from that row itself, but the condition is on how the row relates to other rows. A condition to group by could, for example, be that all rows with consecutive sequential values should be grouped – when a gap in the sequence is found, a new group is started. This requires a calculation across rows, which often can be handled by analytic functions – but sometimes not. This can be solved by utilizing that pattern matching with one row per match does an implicit group by.