OLAP Functions in iSeries SQL for Advanced Data Analysis: A Focus on Window Functions

In the realm of data analysis and management on IBM iSeries systems, SQL offers a powerful suite of tools designed to enhance the efficiency and depth of data interrogation.

Among these tools, OLAP (Online Analytical Processing) functions stand out for their ability to perform complex analytical queries directly within the SQL environment.

This post delves into the specialized category of OLAP functions known as Window Functions, which provide a sophisticated mechanism for accessing and analyzing data points across a dataset without the need for cumbersome self-joins.

Window Functions enable analysts and developers to perform intricate data comparisons and analyses with greater simplicity and elegance.

By defining a “window” or subset of rows in a result set, these functions allow for the execution of calculations across these rows, offering insights that are both broad and deeply contextual within the data landscape.

Among the most pivotal Window Functions are:

• LEAD and LAG: These functions are instrumental in navigating temporal or sequential data, allowing users to access data from a subsequent row (LEAD) or a previous row (LAG) with unparalleled ease. This capability is essential for trend analysis, forecasting, and examining data progressions over time without the complexity of self-joins.
• FIRST_VALUE and LAST_VALUE: By enabling direct access to the first or last value within a specified window, these functions facilitate analyses that require understanding the initiation or conclusion of a dataset’s value range. Such insights are crucial for identifying starting points, endpoints, and shifts in data trends.
• NTH_VALUE(): This function extends the versatility of window functions by fetching the value of a specified column at the nth position within the window, offering precise control over data point selection within a dataset. It opens up possibilities for pinpoint analyses and comparative studies across varied data segments.

The adoption of Window Functions in iSeries SQL not only streamlines complex data analysis tasks but also significantly enhances the capability to draw meaningful conclusions from large and diverse datasets.

By leveraging these functions, professionals can unlock a higher level of analytical precision and efficiency, pushing the boundaries of what can be achieved in data analysis on the IBM iSeries platform.

This post aims to provide a comprehensive understanding of these powerful OLAP functions, illustrating their potential through examples and best practices, to empower users in harnessing their full analytical capabilities within the iSeries SQL environment.

Below are examples that highlight the use of OLAP functions in iSeries SQL, focusing on the LEAD, LAG, FIRST_VALUE, LAST_VALUE, and NTH_VALUE() window functions. We will create an example dataset to test these functions using a Common Table Expression (CTE) with a VALUES statement.

This approach simulates a practical scenario, providing a clear context for the application of these functions and allows us to directly apply our window functions on an example dataset, without the need to have access to a real database.

Example 1: LEAD ans LAG OLAP Functions

In this example, we will explore the advanced use of OLAP functions in SQL, specifically on an iSeries system, to analyze sales data and discover interesting patterns in customer purchasing behaviors.

OLAP functions, such as LEAD, LAG, allow us to examine transactions sequentially, highlighting the frequency of sales and changes in sales amounts between consecutive transactions.

We will use a Common Table Expression (CTE) to simulate a dataset of sales transactions, containing information such as transaction ID, customer ID, transaction date, and sales amount.

Through the following query, we will demonstrate how to calculate the difference in days between consecutive transactions for each customer, the percentage change in sales amount from one transaction to the next, and how to access previous and subsequent sales amounts.

The goal is to provide detailed insights into customer purchasing behavior and transaction frequency using powerful data analytics tools integrated into SQL iSeries.

Let’s see the query in action!

WITH
  SalesData (TrnID, CustID, TrnDate, SaleAmount) AS (
    VALUES
        (1,  'C1', DATE('2022-01-03'), 100)
      , (2,  'C1', DATE('2022-02-06'), 150)
      , (3,  'C1', DATE('2022-03-06'), 120)
      , (4,  'C1', DATE('2022-05-29'), 170)
      , (5,  'C1', DATE('2022-07-11'), 200)
      , (6,  'C1', DATE('2022-10-12'), 100)
      , (7,  'C2', DATE('2022-01-14'), 150)
      , (8,  'C2', DATE('2022-01-16'), 120)
      , (9,  'C2', DATE('2022-01-16'), 170)
      , (10, 'C2', DATE('2022-01-17'), 200)
      , (11, 'C2', DATE('2022-01-22'), 170)
      , (12, 'C2', DATE('2022-01-26'), 200)
)  -- select * from SalesData;
SELECT TrnID "Trn"
      , CustID "Cust"
      , TrnDate "Date"
      , SaleAmount "Amount"
      , DAYS(TrnDate) - 
           DAYS(LAG(TrnDate) OVER (PARTITION BY CustID
                ORDER BY TrnDate)) "DaysPrev"
      , DAYS(LEAD(TrnDate) OVER (PARTITION BY CustID
             ORDER BY TrnDate)) - DAYS(TrnDate)
             "DaysNext"
      , LAG(SaleAmount) OVER (PARTITION BY CustID
                ORDER BY TrnDate) "PrevAmount"
      , 100 * DECIMAL(DECIMAL(SaleAmount, 10, 4)
        / DECIMAL(LAG(SaleAmount)
                     OVER (PARTITION BY CustID
                     ORDER BY TrnDate), 10, 3),
                 10, 4) "PercAmount"
      , LEAD(SaleAmount) OVER (PARTITION BY CustID
                ORDER BY TrnDate) "NextAmount"
  FROM  SalesData;

Result:

Detailed explanation of Query

Our SQL query utilizes OLAP functions LEAD and LAG, and it calculates the difference in days between consecutive transaction dates (TrnDate) for each customer (CustID), as well as the percentage change in SaleAmount between consecutive transactions.

Let’s break down what our query does:

1. WITH SalesData: Defines a Common Table Expression (CTE) named SalesData that simulates a table of sales data with columns for transaction ID (TrnID), customer ID (CustID), transaction date (TrnDate), and sale amount (SaleAmount). This temporary data set is used for the demonstration of the window functions.
2. SELECT Clause: Retrieves various pieces of information for each transaction:
   • TrnID as “Trn”,
   • CustID as “Cust”,
   • TrnDate as “Date”,
   • SaleAmount as “Amount”.
3. DAYS(TrnDate) – DAYS(LAG(TrnDate)…) “DaysPrev”: Calculates the difference in days between the current transaction date and the previous transaction date for the same customer. This shows how many days have passed since the last transaction for each customer.
4. DAYS(LEAD(TrnDate)…) – DAYS(TrnDate) “DaysNext”: Calculates the difference in days between the next transaction date and the current transaction date for the same customer. This indicates how many days will pass until the next transaction for each customer.
5. LAG(SaleAmount) OVER… “PrevAmount”: Retrieves the sale amount from the previous transaction for the same customer. This allows you to compare the current sale amount to the previous one directly.
6. 100 * DECIMAL(…) “PercAmount”: Calculates the percentage change in sale amount between the current transaction and the previous transaction for the same customer. This is a measure of how much the sale amount has increased or decreased in percentage terms from one transaction to the next.
7. LEAD(SaleAmount) OVER… “NextAmount”: Retrieves the sale amount for the next transaction for the same customer. Similar to “PrevAmount”, but for the subsequent transaction.

Summary

This query effectively demonstrates the power of window functions in SQL for performing complex analyses, such as calculating differences and percentage changes between rows in a partitioned dataset without needing to join the table to itself.

It’s a very useful approach for analyzing time series data, customer transaction patterns, or any scenario where you need to compare records in sequence.

Example 2: FIRST_VALUE, LAST_VALUE and NTH_VALUE()  OLAP Functions

To demonstrate the usage of FIRST_VALUE, LAST_VALUE, and NTH_VALUE() window functions in iSeries SQL, let’s create a scenario where these functions can be applied meaningfully.

We’ll simulate a sales dataset using a Common Table Expression (CTE) with VALUES to prepare the data for testing.

This setup will help us explore these functions by analyzing sales transactions, identifying patterns, or extracting specific insights without the need for self-joins.

Scenario: Analyzing Monthly Sales Data

Let’s assume we have a dataset representing monthly sales amounts for different products over a year. We want to find the first, last, and a specific month’s sales amount (e.g., the sixth month) for each product within this dataset.

Let’s see the query in action!

WITH MonthlySales (ProductID, Month, SalesAmount) AS (
    VALUES
        ('P1', '2022-01', 500), ('P1', '2022-02', 450)
      , ('P1', '2022-03', 600), ('P1', '2022-04', 550)
      , ('P1', '2022-05', 650), ('P1', '2022-06', 700)
      , ('P1', '2022-07', 550), ('P1', '2022-08', 480)
      , ('P1', '2022-09', 610), ('P1', '2022-10', 550)
      , ('P1', '2022-11', 660), ('P1', '2022-12', 720)
      , ('P2', '2022-01', 380), ('P2', '2022-02', 450)
      , ('P2', '2022-03', 600), ('P2', '2022-04', 550)
      , ('P2', '2022-05', 650), ('P2', '2022-06', 660)
      , ('P2', '2022-07', 550), ('P2', '2022-08', 480)
      , ('P2', '2022-09', 610), ('P2', '2022-10', 550)
      , ('P2', '2022-11', 660), ('P2', '2022-12', 620)
) 
SELECT distinct
    ProductID "ProductID"
  , SUM(SalesAmount) OVER (PARTITION BY ProductID) "TotalAmount"
  , FIRST_VALUE(SalesAmount)
          OVER (PARTITION BY ProductID
          ORDER BY Month
               ROWS BETWEEN UNBOUNDED PRECEDING
               AND UNBOUNDED FOLLOWING)
          AS "FirstSaleAmount"
  , LAST_VALUE(SalesAmount)
          OVER (PARTITION BY ProductID
          ORDER BY Month
          ROWS BETWEEN UNBOUNDED PRECEDING
               AND UNBOUNDED FOLLOWING)
          AS "LastSaleAmount"
  , NTH_VALUE(SalesAmount, 6)
          OVER (PARTITION BY ProductID
          ORDER BY Month
          ROWS BETWEEN UNBOUNDED PRECEDING
               AND UNBOUNDED FOLLOWING)
          AS "SixthMonthSales"
FROM MonthlySales
order by 1;

Result:

Detailed explanation of Query

This SQL query utilizes a Common Table Expression (CTE) and several window functions to analyze sales data for the products over a year.

Here’s a detailed breakdown of each component and function used in the query:

Common Table Expression (CTE): MonthlySales

• WITH MonthlySales (ProductID, Month, SalesAmount) AS (…): Defines a CTE named MonthlySales. This temporary result set includes three columns: ProductID for the product identifier, Month for the month of the sale, and SalesAmount for the amount sold in that month. The VALUES clause following this definition populates the CTE with sales data for products P1 and P2 across various months in 2022.

SELECT Statement

• SELECT distinct: This command selects unique rows based on the combination of values in the selected columns. It ensures that each ProductID appears only once in the output, accompanied by the calculated metrics for total sales amount, first sale amount, last sale amount, and sixth month’s sales amount.

Window Functions

The query uses window functions to calculate specific metrics for each product. Window functions perform calculations across a set of table rows that are somehow related to the current row.

1. SUM(SalesAmount) OVER (PARTITION BY ProductID): Calculates the total sales amount for each product (ProductID). The PARTITION BY ProductID clause ensures that the sum is computed separately for each product.
2. FIRST_VALUE(SalesAmount) OVER (PARTITION BY ProductID ORDER BY Month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING): Determines the first sale amount within each product’s partition, ordered by month. The window frame specified by ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures the function considers all rows in the partition from start to end. However, since FIRST_VALUE inherently looks at the first value in the ordered sequence, the frame specification is technically unnecessary for its operation but is syntactically required by some SQL dialects.
3. LAST_VALUE(SalesAmount) OVER (…): Finds the last sale amount for each product, using a similar partitioning and ordering clause as FIRST_VALUE. The key difference is in its application: LAST_VALUE looks at the last value in the sequence. The ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame is crucial here to ensure the function correctly considers the entire partition’s range, especially in databases that require explicit frame specification to return the truly last value across the entire sequence.
4. NTH_VALUE(SalesAmount, 6) OVER (…): Retrieves the sales amount for the sixth month for each product. The function is partitioned by ProductID and ordered by Month, similar to the previous functions. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is specified, though for NTH_VALUE, the primary concern is the correct ordering and the specific position (the 6th) within that order.

FROM Clause

• FROM MonthlySales: Indicates that the data for this query comes from the MonthlySales CTE defined at the beginning.

ORDER BY Clause

• ORDER BY 1: Orders the results by the first column in the SELECT list, which is ProductID. This ensures that the output is sorted alphabetically or numerically based on the ProductID values.

Summary

The query is structured to provide a comprehensive view of sales data for each product, calculating the total sales amount, and identifying the first, last, and sixth month’s sales amounts using window functions.

These functions allow for advanced calculations within each ProductID partition while maintaining readability and efficiency in the SQL statement. The use of DISTINCT and ORDER BY clauses ensures that the output is concise and well-organized.

Conclusions

These examples illustrate the power of window functions in SQL for iSeries, enabling complex data analysis without the need for self-joins.

By using a CTE to simulate a real dataset, we’ve shown practical applications of LEAD, LAG, FIRST_VALUE, LAST_VALUE, and NTH_VALUE() functions to access specific data points, analyze trends, and derive insights from sequential data.

These capabilities are invaluable for data analysts and developers working with iSeries SQL, offering a flexible and efficient approach to data analysis.

Please note, as always, that actual implementation and functionality may vary depending on the version of the iSeries operating system and DB2 database.

It is always good practice to refer to the official IBM documentation for more precise and detailed information.

Useful references

• Data Science: opportunities and face challenges

• OLAP Functions in Data Science: A Modern Approach to Business Intelligence

• Harnessing the Power of Data Science and SQL with OLAP Functions on IBM iSeries