FIRST_VALUE 関数と LAST_VALUE 関数の例

FIRST_VALUE 関数と LAST_VALUE 関数は、ウィンドウの最初と最後のローの値を返します。これらの関数を使用すると、セルフジョインを使わずにクエリで複数のローの値に一度にアクセスできます。

この 2 つの関数は、ウィンドウに使用する必要があるので、他の Window 集合関数とは異なります。また、これらの関数では IGNORE NULLS 句を使用できる点も、他の Window 集合関数と異なります。IGNORE NULLS を指定すると、式の最初または最後の NULL 以外の値が返されます。指定しなければ、最初または最後の値が返されます。

例 1：グループの最初のエントリ

FIRST_VALUE 関数を使用して、一定の順序で並んでいる値グループの最初のエントリを取り出すことができます。次のクエリは、各注文について、注文の最初の品目の ProductID、つまり各注文で LineID が最も小さい品目の ProductID を返します。

クエリでは、DISTINCT キーワードを使用して重複を削除しています。このキーワードを指定しなかった場合、各注文の各品目について重複するローが返されます。

SELECT DISTINCT ID,
FIRST_VALUE( ProductID ) OVER ( PARTITION BY ID ORDER BY LineID )
FROM SalesOrderItems
ORDER BY ID;

例 2：最大売り上げに対する割合

FIRST_VALUE 関数の一般的な使用方法として、各ローの値を、現在のグループ内の最大値または最小値と比較できます。次のクエリは、各営業担当者の総売り上げを計算してから、その総売り上げと、同じ製品の最大総売り上げを比較します。結果は、最大総売り上げのパーセントで表されます。

SELECT s.ProductID AS prod_id, o.SalesRepresentative AS sales_rep,
    SUM( s.Quantity * p.UnitPrice ) AS total_sales,
    100 * total_sales / ( FIRST_VALUE( SUM( s.Quantity * p.UnitPrice ) )
                          OVER Sales_Window ) AS total_sales_percentage
  FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
  GROUP BY o.SalesRepresentative, s.ProductID    
    WINDOW Sales_Window AS ( PARTITION BY s.ProductID 
                             ORDER BY SUM( s.Quantity * p.UnitPrice ) DESC )
  ORDER BY s.ProductID;

例 3：NULL 値を設定することによるデータの高密度化

FIRST_VALUE 関数と LAST_VALUE 関数は、データの密度を高めた後で、NULL の代わりに値を設定したい場合に便利です。たとえば、1 日の総売り上げが最も高い営業担当者が表彰されるとします。次のクエリは、2001 年 4 月の第 1 週にトップ成績を上げた担当者を表示します。

SELECT v.OrderDate, v.SalesRepresentative AS rep_of_the_day
FROM ( SELECT o.SalesRepresentative, o.OrderDate,
              RANK() OVER ( PARTITION BY o.OrderDate
                            ORDER BY SUM( s.Quantity * 
                              p.UnitPrice ) DESC ) AS sales_ranking
       FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
       GROUP BY o.SalesRepresentative, o.OrderDate ) AS v
WHERE v.sales_ranking = 1
AND v.OrderDate BETWEEN '2001-04-01' AND '2001-04-07'
ORDER BY v.OrderDate;

このクエリは、次の結果を返します。

OrderDate	rep_of_the_day
2001-04-01	949
2001-04-02	856
2001-04-05	902
2001-04-06	467
2001-04-07	299

ただし、売り上げがなかった日については結果は返されません。次のクエリは、データの密度を高め、売り上げがなかった日のレコードも作成されるようにします。また、LAST_VALUE 関数を使用して、表彰がなかった日には、成績順位に入れ替わりがあるまで最後にトップ成績を獲得した者の ID を NULL 値の代わりに rep_of_the_day に表示させています。

SELECT d.dense_order_date,
                   LAST_VALUE( v.SalesRepresentative IGNORE NULLS )
                       OVER ( ORDER BY d.dense_order_date )
                       AS rep_of_the_day
FROM ( SELECT o.SalesRepresentative, o.OrderDate,
              RANK() OVER ( PARTITION BY o.OrderDate
                            ORDER BY SUM( s.Quantity *
                             p.UnitPrice ) DESC ) AS sales_ranking
       FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
       GROUP BY o.SalesRepresentative, o.OrderDate ) AS v
RIGHT OUTER JOIN ( SELECT DATEADD( day, row_num, '2001-04-01' )
                        AS dense_order_date
                   FROM sa_rowgenerator( 0, 6 )) AS d 
ON v.OrderDate = d.dense_order_date AND sales_ranking = 1
ORDER BY d.dense_order_date;

このクエリは、次の結果を返します。

dense_order_date	rep_of_the_day
2001-04-01	949
2001-04-02	856
2001-04-03	856
2001-04-04	856
2001-04-05	902
2001-04-06	467
2001-04-07	299

前のクエリからの派生テーブル v を、対象日をすべて含む派生テーブル d にジョインすると、1 日ごとにローができます。ただし、この外部ジョインでは、売り上げがなかった日の SalesRepresentative カラムには NULL が含まれます。LAST_VALUE 関数を使用すると、この問題を解決できます。特定のローの rep_of_the_day を、対応する日までの SalesRepresentative の最後の NULL 以外の値と定義します。

参照

DocCommentXchange で意見交換できます