C. Langdon

 

Aggregates

Database platforms do a lot of things.  So many things, we sometimes lose sight of maximizing their capabilities.

One common operation is to aggregate data.  That is, combining data in similar groups and summarizing it by using various aggregate functions such as MIN and MAX.  For example:

SELECT MAX(OrderDate)
FROM Order
WHERE 1=1
  AND CustomerId = 'ACME'

In this post, we briefly discuss the MIN/MAX aggregate functions and how you might be able to use them beyond their simplest form. 

Common Query

We can use the MAX function on a table column and retrieve the maximum value within the column.  In the example below, the query will return the maximum OrderDate where CustomerId is 'ACME'.

SELECT MAX(OrderDate)
FROM Order
WHERE 1=1
  AND CustomerId = 'ACME'

Most of the time, we're wanting to process our data vertically when using aggregate functions. For example, aggregating the values in a column that results in the SUM(Quantity) or the SUM(Amount) or the MAX(OrderDate)

 CustomerIdOrderDateQuantityAmount
ACME2018-11-0110100
ACME2018-11-03550
ACME2018-11-051001000
Table - Order 

 

SELECT MAX(OrderDate)
FROM Order
WHERE 1=1
  AND CustomerId = 'ACME'

Result:
2018-11-05

SELECT SUM(Quantity) AS TotalOrders
FROM Order
WHERE 1=1
  AND CustomerId = 'ACME'

Result:
115

SELECT SUM(Amount) AS TotalRevenue
FROM Order
WHERE 1=1
  AND CustomerId = 'ACME'

Result:
1150

In all these queries, notice how we're aggregating the values of a column.  Wouldn't it be interesting if we could do similar functionality across multiple columns of a row?

Less Common Query

Let's assume an order has multiple processing dates.  When a customer submits an order, it is known as the OrderDate.  The order is then processed and shipped which is referred to as the ShipmentDate.  Once shipped, the order is invoiced and known as the InvoiceDate

OrderIdCustomerId OrderDateShipmentDateInvoiceDateQuantityAmount
1ACME2018-11-012018-11-102018-11-1210100
2ACME2018-11-032018-11-27NULL550
3ACME2018-11-05NULLNULL1001000
 Table - Order 

To evaluate the MAX processing date on an order, use MAX(OrderDate, ShipmentDate, InvoiceDate).

SELECT OrderId, MAX(OrderDate, ShipmentDate, InvoiceDate) AS MaxDate
FROM Order
WHERE 1=1
  AND CustomerId = 'ACME'
GROUP BY OrderId

 

Result:
OrderId    MaxDate
-------    -------
1          2018-11-12
2          2018-11-27
3          2018-11-05

Wrap It Up

It isn't everyday you'll need to use this functionality.  And, frankly, it will be on rare occasions.  Most of the time, half the battle in solving a problem is having a solution.  Don't lose sight on maximizing your aggregates.  It could one day help you solve a problem.

Comments


Comments are closed