Bài học theo mô-đun (4/4)
Thay đổi: LAG và LEAD
Shifts: LAG and LEAD
In sequential and time-based data, you often need to compare the current row with the one from the previous day. In "standard" SQL, doing so requires very dangerous (and very slow) self-joins.
The LAG() (previous row) and LEAD() (next row) functions solve exactly this problem with extreme agility and elegance.
Looking to the Past and the Future
SELECT
created_at,
total_amount,
LAG(total_amount) OVER(ORDER BY created_at) AS importo_ordine_precedente
FROM orders;In the example, each row will conveniently carry the amount produced by the same table, shifted forward by one row (backwards in time). We can easily subtract total_amount - LAG(...) to compute the difference or month-over-month percentage changes!
Likewise, LEAD() will pull the value from the row right below the current one.
From the 'products' table, extract 'id', 'launched_on', 'price' and add 'prev_price', i.e. the 'price' of the product logically considered the 'previous' one, in ascending 'launched_on' order.
Hiển thị gợi ý
Use LAG(price) OVER(ORDER BY launched_on ASC)
Giải pháp khả dụng sau 3 lần thử
How would you compute the rating jumps per individual user? Simulate it: from the 'reviews' table extract 'customer_id', 'created_at', 'rating' and 'next_rating' (the next review of the same customer over time). So partition by 'customer_id', order by 'created_at' and use the right window function to peek ahead.
Hiển thị gợi ý
LEAD() together with PARTITION BY and ORDER BY in the OVER.
Giải pháp khả dụng sau 3 lần thử