Dbs101_flippedclass4
Topic : Advanced Aggregation Functions
After the flipped class 4 was completed successfully, i had a better view on the advanced aggregation functions in SQL. The functions were like the data analysis equivalent of hidden superpowers, enabling us to explore our data at higher levels than before. It also offered a whole new level of insight and analysis, extending far beyond the basics like COUNT(), AVG(), SUM(),etc and at last , their uses were crucial for anyone trying to gain ideas from their data as it holds the key to unlocking the potential of large databases. The advanced aggregation functions include ranking, windowing, pivoting, rollup, and cube.
Ranking
For example, you’re competing in a race and want to see how you stand up among the other runners. The RANK() method in SQL is same to your personal racing scoreboard. Using the values of one or more columns, this gives each row in your result set an individual rank. When you want to compare each row to others in the group this method comes in hand. The ranking can be determined by using the OVER(ORDER BY…) clause. It’s like having a customized scoreboard for any group you want.
Pivoting
Converting data from a row-oriented form to a column-oriented form . To summarize data based on one or more columns, a cross-tabulation can be made using the PIVOT() method. It’s very helpful when writing reports that call for a complex information summary. CASE statements can be used to simulate the PIVOT() function in SQL dialects that do not support it.
Windowing
The OVER() clause introduced windowing methods, which allow the calculation of aggregates over an array of rows related to the current row. For tasks like calculating moving averages or total sums, this is particularly useful. To partition the result set, use the PARTITION BY and ORDER BY clauses in the OVER() clause. It will order each row within every partition. The portion of rows to be part of the calculation is further defined by the FRAME clause.
Rollup and Cube
It allows the creation of totals and grand totals across multiple dimensions. The GROUP BY clause’s one or more columns are successively removed by the ROLLUP() function to generate an order of subtotals. The grand total represents all possible combinations of the columns provided in the GROUP BY clause that are generated by the CUBE() function, on the other hand.
During the flipped class, we got our home groups and had a topic on pivoting. First, I had gone through the reading materials on the flipped class instructions and got the idea of the function and its uses. Next, I shared the ideas or understanding of the function and my members also did it too. Then, during the presentation I was the one who showed or explained the function with a practical giving my friends a better view on the pivoting function in SQL.