Best Usage of NTILE function in SQL Server and other RDBMSs
SQL Server NTILE() is a window function that distributes rows of an ordered partition right into a unique quantity of approximately same partition or group or buckets. The use of the function is described with a real time problem scenario. Problem Scenario: An agency is doing unique sort of promotional events on 1st of each month. The administration wanted to peer, which promotional event has benefited the organization in each region. USE dbTestGO CREATE TABLE…
Analytical & Window Functions
Please refer to my previous post, in which schema and data for EMP and DEPT tables available. The topic of this post is SQL Server Analytical and Window Functions. Analytic functions calculate an aggregate based on a group of rows. Unlike aggregate functions, however, analytic functions can return multiple rows for each group. Use analytic…
Aggregate Functions in Analytic Context
The functions SUM, AVG, COUNT, MIN, and MAX are well-known aggregate functions that we use every day. They are to compute/summarize the multiple rows by grouping them and provide a single summary value. However, when it comes to analysis, the aggregate functions carry out twofold responsibility: The same aggregate function which computes on each row…
TOP, LIMIT, ROWNUM vs DENSE_RANK
What would you do if you were asked to identify top-ten products based on their prices? In SQL Server, using a TOP clause with a specified number of records with descending order of price? In MySQL and Impala, using a LIMIT clause with a specified number of records with descending order of price? Basically, TOP…
Big Data & SQL 