dcsimg
Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


advertisement
 

Mastering Top-N and OLAP Queries in DB2-5 : Page 5


advertisement

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


A Wider RANGE of Functions
Now we come to a more powerful feature—the ability to perform column functions on a moving range of rows. You can use any column function, and it will be applied to the records:

  • Grouped in the PARTITION BY clause, if present
  • Limited to a subset of rows using a ROWS or RANGE clause, if specified, where the range operates in the ORDER given.
The easiest way to see this is by example. Listing 8 shows the SUM, AVG, and MAX functions used to fill a range of different roles. If you run the query on our sample data you'll see some patterns emerge among the different clients.

Listing 8. A Wider Range of Functions

SELECT CLIENT, MONTHEND, SALEVOL, -- Total sales ever SUM(SALEVOL) OVER () AS TOTALSALES, --Year-to-date cumulative total SUM(SALEVOL) OVER( PARTITION BY CLIENT, YEAR(MONTHEND) ORDER BY MONTHEND ROWS UNBOUNDED PRECEDING) AS YTD, --All-time cumulative total across all --clients prior to this month SUM(SALEVOL) OVER( ORDER BY MONTHEND ROWS UNBOUNDED PRECEDING) AS ALLYTD, --Moving 3-month average AVG(SALEVOL) OVER( PARTITION BY CLIENT ORDER BY MONTHEND ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MOVAVGDIF, --Difference to last year's moving avg SALEVOL - AVG(SALEVOL) OVER( PARTITION BY CLIENT ORDER BY MONTHEND ROWS BETWEEN 13 PRECEDING AND 11 PRECEDING) AS MOVPREVDIF, --Greatest volume by any client for the --current month MAX(SALEVOL) OVER( PARTITION BY MONTHEND) AS MONTHMAX, --Percentage of total for month SMALLINT(SALEVOL * 100 / SUM(SALEVOL) OVER (PARTITION BY MONTHEND)) AS THISPCT, --Number of months where this client's sales -- are within +/- 5 units of this month COUNT(*) OVER( PARTITION BY CLIENT ORDER BY SALEVOL RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING) AS NUMSAME FROM MYSALES ORDER BY CLIENT, MONTHEND

You can use RANGE in place of ROWS, except that RANGE operates only on a single numeric column, and compares the values in the column rather than the number of rows. (See the NUMSAME calculation near the end of Listing 8 above).

That's only the beginning of the possibilities. It's important to remember that OLAP queries can place a heavy load on a database, and sometimes the whole data set must be processed even to return a small number of results. That said, you don't have to transfer large amounts of data to other programs, or worse, across a network, to get the results.


Greg Nash is an integration engineer with a manufacturing company.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date