【Snowflake】【SAS】SASのFIRST.BY関数・LAST.BY関数をSnowflakeに書き換える方法

目的

SASのFIRST.BY関数・LAST.BY関数をSnowflakeで書き換える方法をご紹介します。

FIRST.BY関数・LAST.BY関数とは

SASのFIRST.BY関数とLAST.BY関数は、BYステートメントに指定したカラムの各グループの先頭行・末尾行を判定するための関数です。

端的に言うと、以下の通りです。
・FIRST.variable（変数を指定）：そのグループの最初の行なら1、それ以外は0が付与されます
・LAST.variable（変数を指定）：そのグループの最後の行なら1、それ以外は0が付与されます

注意点としては、以下の2点が挙げられます。
・BYステートメントで指定された変数に対して使うこと
・PROC SORTで事前に並べ替えておく必要がある（BY変数順に）

テストデータ

ある商品における、店舗ごとの売上金額の表があります。
こちらの表において、売上金額が最高額の店舗と、最低額の店舗にフラグを付与していきます。

SASコード

proc sort data=DT1;
   by "商品コード"n "店舗名"n "売上金額（万円）"n;
run;

data DT2;
   set DT1;
   by "商品コード"n "店舗名"n "売上金額（万円）"n;

   length FLG1 FLG2 $1.;
   if  first."商品コード"n = 1 then FLG1 = "Y";
   if  last."商品コード"n = 1 then FLG2 = "Y";
run;

SASコード解説

まず最初に、商品コード、店舗名、売上金額の順で並び替えを行います。（デフォルトで昇順に並び替えられます。）

proc sort data=DT1;
   by "商品コード"n "店舗名"n "売上金額（万円）"n;
run;

次に、first.by関数、last.by関数を使用して、
商品コードでグループ分けした時の最初のレコードに「Y」を付与したFLG1列と、最後のレコードに「Y」を付与したFLG2列を追加します。

data DT2;
   set DT1;
   by "商品コード"n "店舗名"n "売上金額（万円）"n;

   length FLG1 FLG2 $1.;
   if  first."商品コード"n = 1 then FLG1 = "Y";
   if  last."商品コード"n = 1 then FLG2 = "Y";
run;

出来上がったデータはこちらになります。

商品コード「1」においては、
最低売上のA店・100万円のレコードのFLG1列に「Y」が、
最高売上のB店・150万円のレコードのFLG2列に「Y」が入っているのが分かります。

また商品コード「2」においては、
最低売上のB店・70万円のレコードのFLG1列に「Y」が、
最高売上のA店・300万円のレコードFLG2列に「Y」が入っているのが分かります。

Snowflakeに書き換えるには

上記をSnowflakeで書き換えるには、ROW_NUMBER()関数を使います。

ROW_NUMBER()関数とは

SnowflakeのROW_NUMBER()関数は、指定した並び順で各行に一意の連番を付けるウィンドウ関数です。
端的に言うと、以下の通りです。
・並び順に基づいて1から順に番号を振る
・同順位でも重複せず、すべての行に一意な番号が付く

主な用途として、以下の2点が挙げられます。
・グループ内の1番目のデータを抽出したいとき
・重複排除（重複のうち最初の1件だけを残す）などに使う

Snowflakeコード

CREATE TABLE DT2
AS
SELECT "商品コード", "店舗名", "売上金額（万円）",
ROW_NUMBER() OVER(
PARTITION BY "商品コード" ORDER BY "売上金額（万円）") AS FLG1,
ROW_NUMBER() OVER(
PARTITION BY "商品コード" ORDER BY "売上金額（万円）" DESC) AS FLG2
FROM DT1;

Snowflakeコード解説

まず以下の部分で、商品コード毎に売上金額の昇順で連番を振るFLG1列を作成します。

ROW_NUMBER() OVER(
PARTITION BY "商品コード" ORDER BY "売上金額（万円）") AS FLG1

次に以下の部分で、商品コード毎に売上金額の降順で連番を振るFLG2列を作成します。

ROW_NUMBER() OVER(
PARTITION BY "商品コード" ORDER BY "売上金額（万円）" DESC) AS FLG2,

これにより、以下のようなデータが作成されます。

少し分かりづらいかもしれませんが、
商品コード「1」においては、
最低売上のA店・100万円のレコードのFLG1列に「1」が、
最高売上のB店・150万円のレコードのFLG2列に「1」が入っているのが分かります。

また商品コード「2」においては、
最低売上のB店・70万円のレコードのFLG1列に「1」が、
最高売上のA店・300万円のレコードのFLG2列に「1」が入っているのが分かります。

このように、それぞれの商品の最低売上、最高売上のレコードを特定することが出来ました。

まとめ

SASのFIRST.BY関数・LAST.BY関数はそれぞれグループ毎の
最初（FIRST.BY関数）と最後（LAST.BY関数）のレコードを特定するための関数です。

これをSnowflakeで書き換えるには、ROW_NUMBER()関数を使います。
PARTITION BY句にFIRSTもしくはLASTの後に指定されているカラムを指定し、ORDER BY句にグループ分け内で順番付けを行いたい対象であるカラムを指定したうえで昇順もしくは降順で並び替えて連番を振ることでFIRST.BY関数・LAST.BY関数を再現することが可能です。

最後に

本記事ではSASのFIRST.BY関数・LAST.BY関数をSnowflakeに書き換える方法を説明しました。
SASからSnowflakeへコードを書き換える記事はそう多くないと思うので今後もこのような記事を投稿できればと思っております。

関連サイト

宣伝

株式会社ジールはMicrosoft Azure やAWS（Amazon Web Services）、Google Cloud Platform（GCP）、Oracle Cloud Infrastructure（OCI）など、多彩なクラウドプラットフォーム構築・運用の知見を有しています。そのため、複数のクラウドサービスを組み合わせるマルチクラウド環境においても、ベンダーロックインを回避し、お客様のご要望に応じた最適なクラウドプラットフォームの構築・運用への対応が可能です。
https://www.zdh.co.jp/products-services/cloud-data/