Junior — Middle

How can you implement shifting of selected data when executing an SQL query?

sobes.tech AI

Answer from AI

Window functions such as LAG() and LEAD() are often used in SQL to implement data shifts. They allow retrieving the value from the previous or next row relative to the current one, which can be considered a data shift.

Example of shifting values down by one row:

SELECT
  id,
  value,
  LAG(value, 1) OVER (ORDER BY id) AS shifted_value
FROM table_name;

Here, shifted_value will contain the value from the previous row in id order. Similarly, LEAD() can be used to shift upward.

For a simpler implementation of shifting, subqueries with JOIN or OFFSET in some DBMSs can be used, but window functions are the most convenient and efficient method.

Previous Next