モジュール 7 · レッスン 3 / 4コース内の 27/57~12 min

モジュールのレッスン (3/4)

LAG、LEAD、および累計

LAG と LEAD は、以下にアクセスできる 2 つの特別なウィンドウ関数です。同じパーティションの 他の行: 前の行 (LAG) またはウィンドウの ORDER BY に従って、次のもの (LEAD) を選択します。彼らは、 行間の比較 (デルタ、変動、間隔) のための頼りになるツールそして、SUM/AVG/COUNT OVER と組み合わせて、累計合計を求めます。

LAG: 前の行

SQL

LAG(colonna)            OVER (PARTITION BY p ORDER BY o)
LAG(colonna, n)         OVER (PARTITION BY p ORDER BY o)   -- n righe indietro
LAG(colonna, n, default) OVER (PARTITION BY p ORDER BY o)  -- default se non c'è

例: 各注文について、「同じ人による前回の注文からの日数」顧客「:

SQL

SELECT id,
       customer_id,
       ordered_at,
       ordered_at - LAG(ordered_at) OVER (
         PARTITION BY customer_id ORDER BY ordered_at
       ) AS since_previous_order
FROM   orders
ORDER BY customer_id, ordered_at;

各顧客の最初の注文には since_previous_order = NULL が含まれています。パーティションには「前」はありません。

LEAD: 次の行

LAG のミラー。典型的: 「次のイベントまでの時間」、「差分」次のバージョンでは」。

SQL

SELECT id, ordered_at,
       LEAD(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at)
         - ordered_at AS to_next
FROM   orders;

累計: OVER を含む集計

集計関数 (SUM、AVG、COUNT、MAX、MIN) は次のようになります。累積 OVER (… ORDER BY …) と組み合わせた場合:

SQL

SELECT order_id,
       total,
       SUM(total) OVER (
         PARTITION BY customer_id
         ORDER BY     ordered_at
       ) AS running_total
FROM   …;

SUM は行ごとに、パーティション内のすべての行の合計を計算します。そのポイント、ウィンドウの ORDER BY に従っています。結果は、古典的な「ランニングバランス」。

試してみてください

運動#sql.m7.l3.e1

試行回数: 0読み込み中…

注文ごとに、id、customer_id、ordered_at、および、ordered_at で注文された同じ顧客の現在の注文と前の注文の間の丸日数 (FLOOR を含む) である days_from_previous 列を表示します。以前の注文がない場合は NULL。 customer_id と requested_at で注文します。

エディターを読み込み中…

ヒントを表示

LAG(owned_at) OVER (PARTITION BY customer_id ORDER BY requested_at) は、顧客の以前の注文のタイムスタンプを返します。減算して間隔を取得し、日数に変換します。

3 回の試行後に解決策が利用可能になります

復習演習

運動#sql.m7.l3.e2