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

この記事では、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" を返します。

SELECT COALESCE(NULL, NULL, NULL,'intersystems', NULL,'sql')

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

CREATE TABLE EXPENSES(
    TDATE     DATE NOT NULL,
    EXPENSE1   NUMBER NULL,
    EXPENSE2   NUMBER NULL,
    EXPENSE3   NUMBER NULL,
    TTYPE  CHAR(30) NULL)

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

 INSERT INTO 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 ORDER BY 2   

#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のスキルを向上させ続けていきましょう。