IRIS SQLクエリで取得した結果セットのランキング(順位)を算出する方法

弊社サポートセンターに、「IRIS SQLに Oracle の RANK() 関数のようものはありませんか？」というお問い合わせいただくことがあります。

IRIS2021.1以降のバージョンであれば、RANK() や ROW_NUMBER() などの ウィンドウ関数 がサポートされるようになりましたので、以下のように使用することができます。

// RANK() 関数
SELECT RANK() OVER (ORDER BY Age) as Ranking,Name,Age 
FROM Sample.Person 
WHERE Age > 60 order by Age
SQL
SQL

// ROW_NUMBER() 関数
SELECT ROW_NUMBER() OVER (ORDER BY Age) as Ranking,Name,Age 
FROM Sample.Person 
WHERE Age > 60 order by Age
SQL
SQL

IRIS2021.1より前のバージョンで同等のことを行いたい場合、ビュー ID (%VID) を使用してROW_NUMBER() 関数と同等のことが実現可能です。
%VID とは、ビューまたは FROM 節のサブクエリで返される各行に割り当てられる整数のIDです。
%VIDを使用することで、Order By クエリで並び替えた際に上位からIDを付与することが可能となります。
ただし、こちらの方法は同一値があった場合は同じ順位を付けることができないため、RANK() 関数ではなく、ROW_NUMBER() 関数と同等の機能となります。

実行例は以下のようになります。サブクエリには、TOP句を指定する必要があります。

// %VID を使用する方法
SELECT %vid as Ranking, * 
FROM (SELECT top all Name, Age
      FROM Sample.Person 
      WHERE Age > 60
      ORDER BY Age)
SQL
SQL

3rdパーティデータベース特有のコマンドをそのまま実行したい場合、直接パススルークエリを実行する方法もあります。

%Library.SQLGatewayConnection クラスを使用して直接クエリを実行する方法は以下のようになります。

  set dsn="OracleDB"
  set user="scott"
  set pass="tiger"
  // 接続
  set cn=##class(%SQLGatewayConnection).%New()
  set st=cn.Connect(dsn,user,pass)

  // SQL実行, Prepare第3引数に上記の接続変数を指定
  set rs=##class(%ResultSet).%New("%DynamicQueryGW:SQLGW")
  do rs.Prepare("select empno,sal,rank() over(order by sal) from emp",,cn)
  do rs.Execute()

  // 各行取得
  while rs.Next() {
    Write !,rs.GetData(1)," ",rs.GetData(2)," ",rs.GetData(3)
  }

  // 切断
  do cn.Disconnect()
ObjectScript
ObjectScript

詳細は以下のドキュメントをご覧ください。
プログラムによる SQL ゲートウェイの使用法

【関連トピック】
IRIS SQLでは OFFSET/LIMIT句のような機能をサポートしてますか？