開発者の皆さん、こんにちは！

この記事では、Muhammad Waseem さんが（US開発者コミュニティに）投稿された「SQLのスキルを次のレベルに引き上げることのできる5つの便利なSQL関数」の記事についてご紹介します。

✅ SQLに関わらず、IRIS／Caché全般で日頃利用されている便利な機能、使い方、関数などなどありましたら、ぜひコミュニティで共有いただければと思います。

✅ 現在「技術文書ライティングコンテスト」開催中です！ 🎁 参加賞／特賞 🏆ありますので、ぜひチャレンジしてみてください！

以下、Muhammad さんの記事です。

この記事では、5つの便利なSQL関数の説明を実行例と共にご紹介します👇

• COALESCE
• RANK
• DENSE_RANK
• ROW_NUMBER
• Function to Get Running Totals

まずは、COALESCE関数から始めてみましょう

#COALESCE

COALESCE関数は表現式をのリストを左から右に順番に評価し、NULLではない最初の表現式の値を返します。すべての表現式がNULLの場合、NULLが返ります。

以下の例文では、Nullではない最初の値である "intersystems" を返します。

SELECTCOALESCE(NULL, NULL, NULL,'intersystems', NULL,'sql')

以下例のテーブルを作成します。

CREATETABLE EXPENSES(
    TDATE     DATENOTNULL,
    EXPENSE1   NUMBERNULL,
    EXPENSE2   NUMBERNULL,
    EXPENSE3   NUMBERNULL,
    TTYPE  CHAR(30) NULL)

関数のテストをするため、以下のようにダミーデータを登録します。

 INSERTINTO sqluser.expenses (tdate, expense1,expense2,expense3,ttype )  
  SELECT {d'2023-01-01'}, 500,400,NULL,'Present'
  UNION ALL
  SELECT {d'2023-01-01'}, NULL,50,30,'SuperMarket'
  UNION ALL 
  SELECT {d'2023-01-01'}, NULL,NULL,30,'Clothes' 
  UNION ALL
  SELECT {d'2023-01-02'}, NULL,50,30 ,'Present'
  UNION ALL
  SELECT {d'2023-01-02'}, 300,500,NULL,'SuperMarket'
  UNION ALL 
  SELECT {d'2023-01-02'}, NULL,400,NULL,'Clothes'   
  UNION ALL
  SELECT {d'2023-01-03'}, NULL,NULL,350 ,'Present'
  UNION ALL
  SELECT {d'2023-01-03'}, 500,NULL,NULL,'SuperMarket'
  UNION ALL 
  SELECT {d'2023-01-04'}, 200,100,NULL,'Clothes'
  UNION ALL
  SELECT {d'2023-01-06'}, NULL,NULL,100,'SuperMarket'
  UNION ALL 
  SELECT {d'2023-01-06'}, NULL,100,NULL,'Clothes'  

データを確認してみます。

テストデータの作成が終わったら、COALESCE関数を使用して、EXPENSE1、EXPENSE2、EXPENSE3のカラムからNULLではない最初の値を取得してみましょう。

SELECT TDATE,
COALESCE(EXPENSE1,EXPENSE2,EXPENSE3),
TTYPE
FROM sqluser.expenses ORDERBY2   

#RANK vs DENSE_RANK vs ROW_NUMBER 関数

• RANK()— 同じウィドウフレーム内の各行に1から始まるランキングを表す整数を割り当てます。ウィンドウ関数フィールドに同じ値を含む複数の行が存在する場合、ランキングの整数に重複値を含めることができます。
• ROW_NUMBER() — 同じウィンドウフレーム内の各行に１から始まる一意な連続した整数を割り当てます。ウィンドウ関数フィールドに同じ値を含む複数の行が存在する場合、各行に一意の連続した整数が割り当てられます。
• DENSE_RANK() — 重複したランク（順位）があってもその後の順位は飛ばさない関数。

SQLでは、行にランキングの整数値を割り当てる方法がいくつかあります。先ほどと同じ例をもう１度考えてみましょう。

以下実行例では、どこに一番お金をかけているかを調べています。様々な方法があります。ROW_NUMBER() 、RANK()、DENSE_RANK()をすべて使用できます。この3つの関数を使用して前述したテーブルを順番に並べます。次のクエリを使用して３つの関数の主な違いを確認してみましょう。

クエリは以下の通りです。

3つの関数の主な違いは、同じランキングになった場合の扱い方です。違いは以下の通りです。

• ROW_NUMBER()- 1から始まる各行に一意な番号を返します。同値の場合、2番目の基準が定義されていなければ、任意に番号を割り当てます。
• RANK()- 1から始まる各行の一意な番号を返しますが、同値の場合は同じ番号を割り当てます。重複した順位には「ずれ」が生じます。
• DENSE_RANK() - 重複したランク（順位）があってもその後の順位は飛ばさない関数。

#合計の計算

合計の計算は、特に成長を視覚化する場合に最も便利なウィンドウ関数の１つです。ウィンドウ関数の SUM()を使用して、累積集計を計算することができます。

これを行うためには、集約関数 SUM() 使用して変数を合計します。以下の例ではTDATEカラムを順番に並べて合計を算出しています。

実行例は以下の通りです。

上記結果の通り、日付が経過するにつれ、使用された金額の累積合計を確認できます（結果の「Window_3」カラムが累積集計の結果です）。

まとめ

SQLは素晴らしいです。上記例で使用した関数は、データ分析、データサイエンス、その他データに関連するあらゆる分野で役に立つでしょう。だからこそ、SQLのスキルを向上させ続けていきましょう。