Aggregate Window Functions

The addition of the OVER clause in SQL Server was a great enhancement to the T-SQL language. Using the ranking functions has helped solve an array of problems in a very efficient manner. While there is a huge benefit of the ranking functions, it is often overlooked that the OVER clause supports now aggregate window functions. This means that the window aggregate function (SUM, AVG, COUNT, MIN, MAX) computes a value for each row from a result set derived from the window (partition). That opens an opportunity to solve problems using new methods.

Here are a couple examples to demonstrate using aggregate window functions with the OVER clause.

CREATE TABLE Loans (

  loan_nbr INT NOT NULL PRIMARY KEY,

  loan_date DATETIME NOT NULL,

  loan_amount DECIMAL(12, 2) NOT NULL

              DEFAULT 0.0,

  customer_nbr INT NOT NULL,

  loan_type CHAR(1) NOT NULL

            DEFAULT 'P'

            CHECK (loan_type IN ('P'-- personal

                                'B')) -- business

  );

 

INSERT INTO Loans

VALUES (1, '20080801', 12000.00, 2, 'P'),

       (2, '20080805', 15700.00, 1, 'B'),

       (3, '20080610', 12000.00, 3, 'P'),

       (4, '20080401', 5000.00, 1, 'P'),

       (5, '20080715', 25000.00, 4, 'B'),

       (6, '20080610', 25000.00, 5, 'P'),

       (7, '20080501', 1000.00, 6, 'P'),

       (8, '20080810', 6000.00, 7, 'B'),

       (9, '20080815', 2000.00, 8, 'B'),

       (10, '20080815', 1000.00, 9, 'P'),

       (11, '20080715', 5500.00, 10, 'P'),

       (12, '20080615', 1000.00, 11, 'B'),

       (13, '20080820', 6000.00, 12, 'P'),

       (14, '20080510', 28000.00, 6, 'B'),

       (15, '20080815', 2000.00, 10, 'P'),

       (16, '20080810', 1500.00, 8, 'P'),

       (17, '20080817', 10000.00, 10, 'B'),

       (18, '20080816', 2500.00, 9, 'P');  

 

-- Aggregate window functions with the OVER clause

SELECT loan_nbr, loan_type, loan_amount, customer_nbr,

       SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS total_customer_loans,

       AVG(loan_amount) OVER(PARTITION BY customer_nbr) AS avg_customer_loan_amt,

       MAX(loan_amount) OVER(PARTITION BY customer_nbr) AS max_customer_loan_amt,

       MIN(loan_amount) OVER(PARTITION BY customer_nbr) AS min_customer_loan_amt,

       COUNT(*) OVER(PARTITION BY customer_nbr) AS count_customer_loans

FROM Loans;

 

-- Calculate percent for current loan based on total customer loans

-- and total of all loans

SELECT loan_nbr, loan_type, loan_amount, customer_nbr,

       loan_amount /

       SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS percent_of_customer_loans,

       loan_amount /

       SUM(loan_amount) OVER() AS percent_of_all_loans

FROM Loans;

 

-- Get customers (and all their loans) with more than 2 loans

SELECT customer_nbr, loan_nbr, loan_amount, cnt

FROM (SELECT customer_nbr, loan_nbr, loan_amount,

            COUNT(*) OVER(PARTITION BY customer_nbr) AS cnt

      FROM Loans) AS L

WHERE cnt >= 2;

Resources:

OVER Clause
http://msdn.microsoft.com/en-us/library/ms189461.aspx

6 replies
  1. Anonymous
    Anonymous says:

    This is a great article!

    I have, however, another question (and maybe it is a material for a whole new post). How would you write a query which will return an aggregate data for every 3 consequent records per loan type? For example: in your table you have loans 1,3,4 as personal, then 6,7,10 and so on. The question is how to show an output which shows the average loan amount per every group of 3 consequent loans, according to their type. (The query should display the data of the entire table, records grouped by 3, and if the last group has less than 3, then still show the average of 2 or just one record.)

    Reply
  2. Plamen Ratchev
    Plamen Ratchev says:

    This is very easy to accomplish. Here is a query to demonstrate:

    SELECT loan_type, grp, AVG(loan_amount) AS avg_grp
    FROM (
    SELECT loan_nbr, loan_type, loan_amount, customer_nbr,
    (ROW_NUMBER() OVER(PARTITION BY loan_type ORDER BY loan_nbr) – 1) / 3 AS grp
    FROM Loans) AS L
    GROUP BY loan_type, grp;

    Essentially you use ROW_NUMBER partitioned by loan type and by integer division by 3 you create the groups of loans.

    Reply
  3. Mohammad
    Mohammad says:

    What is the advantage of this method (aggregate window function with OVER() clause) against the following method? performance? less complexity? shorter code?

    — Aggregate window functions with the OVER clause
    SELECT loan_nbr, loan_type, loan_amount, customer_nbr,
    SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS total_customer_loans,
    AVG(loan_amount) OVER(PARTITION BY customer_nbr) AS avg_customer_loan_amt,
    MAX(loan_amount) OVER(PARTITION BY customer_nbr) AS max_customer_loan_amt,
    MIN(loan_amount) OVER(PARTITION BY customer_nbr) AS min_customer_loan_amt,
    COUNT() OVER(PARTITION BY customer_nbr) AS count_customer_loans
    FROM Loans

    — without aggregate window function
    select loan_nbr, loan_type, loan_amount, V.customer_nbr,
    D.total_customer_loans,
    D.avg_customer_loan_amt,
    D.max_customer_loan_amt,
    D.min_customer_loan_amt,
    D.count_customer_loans
    from
    (
    select customer_nbr,
    SUM(loan_amount) as total_customer_loans,
    AVG(loan_amount) AS avg_customer_loan_amt,
    MAX(loan_amount) AS max_customer_loan_amt,
    MIN(loan_amount) AS min_customer_loan_amt,
    COUNT(
    ) AS count_customer_loans
    from loans
    group by customer_nbr
    )D
    join
    loans v
    on d.customer_nbr=v.customer_nbr

    Thanks.

    Reply
  4. Plamen Ratchev
    Plamen Ratchev says:

    Mohammad,

    The benefits of using the OVER clause are all of that: simple, more efficient, shorter and maintainable code. I do not see a reason to use a join with derived table with aggregates unless you are using SQL Server 2000.

    Reply
  5. Mohammad
    Mohammad says:

    >
    more efficient
    <<

    More efficient?! But I do not see the difference between two previous queries.

    See:
    /
    — Aggregate window functions with the OVER clause
    Table 'Worktable'. Scan count 3, logical reads 85
    Table 'Loans'. Scan count 1, logical reads 2

    — without aggregate window function
    Table 'Worktable'. Scan count 3, logical reads 85
    Table 'Loans'. Scan count 1, logical reads 2
    /

    Reply
  6. Plamen Ratchev
    Plamen Ratchev says:

    This is because the query optimizer is smart and generates almost the same execution plan in this case. But with different data, multiple tables, indexes, statistics, etc., it can differ.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.