Comparing the current and next rows in data warehousing

There is often a need for comparing the current row with the next row for different reasons in data warehousing strategies. Instead of cursoring through an entire table to compare values between current and next records, this technique is faster. Cursors are generally slower for large tables if you don't have any conditions to filter the records sets. In that case, if this technique is built into a cursor, you would have both values in the same row.

I have used this technique for implementing a clickstream data warehouse. One of the requirements was to analyze the time people spend on each page. As the source system was not capturing the time difference, this technique was easier. For a heavily hit web site (6-8 million hits a day was the one I worked with), the processing time is very minimal. This technique would still give you the correct result even in parallel query processing. This code will only work in Oracle from 8i onwards.

Here's the code. The in-line views should be controlled accordingly with the ORDER BY clause or DISTINCT key words.

SELECT AA.DATE_TIME CURRENT_ROW_TIME,
BB.DATE_TIME NEW_ROW_TIME
FROM
(
SELECT A.DATE_TIME,
ROWNUM ROWA 
FROM
(
SELECT DATE_TIME
FROM SOURCE.CLICK_SESSIONS
) A
)AA,
(
SELECT B.DATE_TIME, ROWNUM+1 ROWB
FROM
(
SELECT DATE_TIME
FROM SOURCE.CLICK_SESSIONS
) B
) BB
WHERE AA.ROWA = BB.ROWB(+);

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.