Chapter-25 : SQL Windows Function | Partition by ,CTE, Rank, Dense rank... With Example | MySQL
305 بار بازدید -
8 ماه پیش
-
Windows Function ,CTE , Partition
Windows Function ,CTE , Partition by, Rank, Dense rank In Sql With Example | MySQL
All about data
📺 YouTube : @all_about_data_
📷 Instagram : Instagram: allabout_data_
📺Google Drive : https://drive.google.com/drive/folder...
Windows functions in SQL are powerful tools for performing calculations and aggregations over a specific subset of rows within a result set, often referred to as a "window." They offer flexibility and granularity beyond traditional aggregation functions.
Key Concepts:
1. Rank, Dense Rank, and Row Number:
Rank: Assigns a rank to each row within a partition, skipping duplicate values (ties receive the same rank, causing a gap).
Dense Rank: Similar to Rank, but assigns consecutive ranks without skipping numbers.
Row Number: Assigns a unique sequential number to each row within a partition, starting from 1.
2. Partition By:
This clause divides the result set into logical groups based on specified columns. Each partition acts as a separate window for applying the window function.
3. Group By:
Similar to Partition By, it groups rows based on specific columns. However, it aggregates data for each group, returning one row per group with aggregate values.
4. CTE (Common Table Expression):
A temporary named result set that can be referenced in other parts of the query. It improves readability and modularity of complex queries.
Differences:
Here's how these terms differ:
Rank vs. Dense Rank: Both rank rows, but Rank skips numbers for ties, while Dense Rank assigns consecutive ranks.
Partition By vs. Group By: Both partition data, but Partition By allows applying window functions within each partition, while Group By aggregates data for each group, returning fewer rows.
Windows Function vs. Group By: Windows functions operate on individual rows within a window, allowing for detailed analysis. Group By aggregates data for entire groups, summarizing info.
Common Window Functions:
RANK(): Ranks rows within a window, skipping ties.
DENSE_RANK(): Ranks rows with consecutive numbers, ignoring ties.
ROW_NUMBER(): Assigns sequential numbers to each row within a window.
LEAD()/LAG(): Access data from rows before/after the current row within the window.
These functions offer powerful capabilities for ranking, ordering, calculating running totals, and analyzing data for specific subsets of rows within a larger dataset.
All about data
📺 YouTube : @all_about_data_
📷 Instagram : Instagram: allabout_data_
📺Google Drive : https://drive.google.com/drive/folder...
Windows functions in SQL are powerful tools for performing calculations and aggregations over a specific subset of rows within a result set, often referred to as a "window." They offer flexibility and granularity beyond traditional aggregation functions.
Key Concepts:
1. Rank, Dense Rank, and Row Number:
Rank: Assigns a rank to each row within a partition, skipping duplicate values (ties receive the same rank, causing a gap).
Dense Rank: Similar to Rank, but assigns consecutive ranks without skipping numbers.
Row Number: Assigns a unique sequential number to each row within a partition, starting from 1.
2. Partition By:
This clause divides the result set into logical groups based on specified columns. Each partition acts as a separate window for applying the window function.
3. Group By:
Similar to Partition By, it groups rows based on specific columns. However, it aggregates data for each group, returning one row per group with aggregate values.
4. CTE (Common Table Expression):
A temporary named result set that can be referenced in other parts of the query. It improves readability and modularity of complex queries.
Differences:
Here's how these terms differ:
Rank vs. Dense Rank: Both rank rows, but Rank skips numbers for ties, while Dense Rank assigns consecutive ranks.
Partition By vs. Group By: Both partition data, but Partition By allows applying window functions within each partition, while Group By aggregates data for each group, returning fewer rows.
Windows Function vs. Group By: Windows functions operate on individual rows within a window, allowing for detailed analysis. Group By aggregates data for entire groups, summarizing info.
Common Window Functions:
RANK(): Ranks rows within a window, skipping ties.
DENSE_RANK(): Ranks rows with consecutive numbers, ignoring ties.
ROW_NUMBER(): Assigns sequential numbers to each row within a window.
LEAD()/LAG(): Access data from rows before/after the current row within the window.
These functions offer powerful capabilities for ranking, ordering, calculating running totals, and analyzing data for specific subsets of rows within a larger dataset.
8 ماه پیش
در تاریخ 1402/09/03 منتشر شده
است.
305
بـار بازدید شده