Retrieving Previous and Next Rows for Rows Selected with WHERE Conditions Using SQL Window Functions

Problem Background and Scenario Analysis

In data processing, it is often necessary to analyze the context before and after specific elements in sequential data. For instance, in natural language processing or log analysis, we might have a table storing words or events in order and want to query the previous and next words when a particular term (e.g., "name") appears. This aids in understanding contextual relationships or pattern recognition.

Assume we have a table named words with the following structure:

CREATE TABLE words (
    id INT PRIMARY KEY,
    word VARCHAR(50)
);

The table stores tokenized results of the sentence "my name is Joseph and my father's name is Brian", with sample data as:

The goal is to query each occurrence of the word "name" and retrieve its previous and next words. The expected result should show two rows: the first corresponding to the first "name" (ID=2) with previous word "my" and next word "is"; the second corresponding to the second "name" (ID=8) with previous word "father's" and next word "is".

Core Solution: Using Window Functions

The best answer employs ANSI SQL standard window functions LAG and LEAD to solve this problem. These functions allow accessing rows before or after the current row in a result set without self-joins, enhancing efficiency and readability.

Here is the complete SQL query code:

SELECT prev_word, word, next_word
FROM (
    SELECT id, 
           LAG(word) OVER (ORDER BY id) AS prev_word,
           word,
           LEAD(word) OVER (ORDER BY id) AS next_word
    FROM words
) AS t
WHERE word = 'name';

Let's break down this query step by step:

1. Subquery Part: First, in the subquery, we use LAG(word) OVER (ORDER BY id) to get the previous word for each row, and LEAD(word) OVER (ORDER BY id) to get the next word. OVER (ORDER BY id) defines the window order, ensuring sorting by id in ascending order, which is crucial for sequential data.
2. Function Behavior: The LAG function returns the value from a row at a specified offset before the current row, with a default offset of 1 (i.e., the previous row). Similarly, LEAD returns the value from a row after the current row. For the first row, LAG returns NULL; for the last row, LEAD returns NULL.
3. Outer Query: The subquery is aliased as t, and the outer query selects the prev_word, word, and next_word columns, filtering with WHERE word = 'name' to return only rows where the word is "name".

Executing this query will output:

Technical Details and Extended Discussion

Window functions are powerful features introduced in the SQL:2003 standard, enabling computations over a window or partition of a result set. In this example, we used a simple ORDER BY clause to define the window order, but window functions also support PARTITION BY for computing previous and next rows within groups, such as processing text by sentence or paragraph.

Another answer proposed a more simplified query:

SELECT LAG(word) OVER (ORDER BY ID) AS PreviousWord,
       word,
       LEAD(word) OVER (ORDER BY ID) AS NextWord
FROM words;

This query returns previous and next words for all rows without filtering. While more concise, in practical applications, we often only care about rows meeting specific conditions, making the best answer's filtering approach more practical. Moreover, the best answer uses a subquery, which helps organize logic in complex queries, e.g., when filtering based on computed columns is needed.

Regarding compatibility, LAG and LEAD functions are supported in most modern database management systems (e.g., SQL Server, PostgreSQL, MySQL 8.0+, Oracle). If using older versions or specific databases, alternative methods like self-joins or user variables might be required, but these are often more complex and less performant.

Practical Applications and Best Practices

This technique is not limited to text analysis but applies to time-series data, log records, or any ordered dataset. For example, in analyzing user activity logs, one can query actions before and after specific events; in financial data, check trading volumes before and after price changes.

In actual coding, it is recommended to:

• Ensure the table has a clear sorting key (e.g., id or timestamp) to avoid result indeterminacy.
• Handle edge cases: return NULL when the previous or next row does not exist, which may require appropriate handling at the application layer.
• For large datasets, consider creating indexes on sorting keys to improve window function performance.

By mastering window functions, developers can write more efficient and readable SQL queries, easily handling contextual analysis in sequential data.