LEADとLAG関数
CoddyのSQLジャーニー「基礎」セクションの一部。レッスン 61/72。
LEAD関数とLAG関数を使うと、n行前またはn行後の現在の行の値にアクセスできます。
たとえば、現在の行と1か月前の会社の収益の比率を計算したい場合は、前月の値を取り出せます。
| id | revenue | month |
| 1 | 532 | 5 |
| 2 | 492 | 6 |
| 3 | 393 | 7 |
| 4 | 723 | 8 |
SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY MONTH) as prev_month_revenue
FROM table1 ORDER BY idこれにより、次のテーブルが作成されます。
| id | revenue | prev_month_revenue |
| 1 | 532 | |
| 2 | 492 | 532 |
| 3 | 393 | 492 |
| 4 | 723 | 393 |
この方法で、prev_month_revenue/revenue の比率を計算できます。
代わりにLEAD関数を使うと、各行の翌月の売上を取得できます。
SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY id| id | revenue | next_month_revenue |
| 1 | 532 | 492 |
| 2 | 492 | 393 |
| 3 | 393 | 723 |
| 4 | 723 |
チャレンジ
簡単利用可能なテーブルと列:
air_conditioners:id,efficiency,strength,month
各エアコンの効率を、ひとつ前のエアコンの効率と比較します。
各エアコンの効率と、前に設置されたエアコン(idに基づく)の効率を表示するクエリを書きましょう。また、現在のエアコンと前のエアコンの効率差も計算します。結果をid、次にefficiencyの昇順で並べます。
出力列:
idefficiencyprevious_efficiency(LAGを使用)efficiency_difference(現在の効率 - 前の効率)
最後に、クエリをラップして、previous_efficiency(したがってefficiency_differenceも)が空、つまりNULLである最初の行を除外します。
SELECT * FROM (
-- Your query here
)
WHERE previous_efficiency IS NOT NULL自分で試してみよう
このレッスンには短いクイズがあります。レッスンを始めて解答し、進捗を記録しましょう。
基礎のすべてのレッスン
11ウィンドウ関数 パート1
ROW_NUMBER関数ORDER BY基準PARTITION BY基準PARTITIONとORDERLEADとLAG関数復習 - LEADとLAG復習 - 図復習 - ボックス自分で練習してみよう: SQLプレイグラウンド