弊社サポートセンターに、「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
SQLSQL
Ranking | Name | Age |
1 | Townsend,Neil W. | 61 |
1 | Murray,Terry J. | 61 |
3 | Huff,Patrick B. | 67 |
4 | Rotterman,Umberto A. | 72 |
5 | Quine,Imelda D. | 75 |
6 | McCormick,Imelda S. | 80 |
7 | Roentgen,Vincent Q. | 81 |
8 | Ueckert,Terry Q. | 85 |
9 | Perez,Ted P. | 97 |
// ROW_NUMBER() 関数
SELECT ROW_NUMBER() OVER (ORDER BY Age) as Ranking,Name,Age
FROM Sample.Person
WHERE Age > 60 order by Age
SQLSQL
Ranking | Name | Age |
1 | Townsend,Neil W. | 61 |
2 | Murray,Terry J. | 61 |
3 | Huff,Patrick B. | 67 |
4 | Rotterman,Umberto A. | 72 |
5 | Quine,Imelda D. | 75 |
6 | McCormick,Imelda S. | 80 |
7 | Roentgen,Vincent Q. | 81 |
8 | Ueckert,Terry Q. | 85 |
9 | Perez,Ted P. | 97 |
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)
SQLSQL
Ranking | Name | Age |
1 | Townsend,Neil W. | 61 |
2 | Murray,Terry J. | 61 |
3 | Huff,Patrick B. | 67 |
4 | Rotterman,Umberto A. | 72 |
5 | Quine,Imelda D. | 75 |
6 | McCormick,Imelda S. | 80 |
7 | Roentgen,Vincent Q. | 81 |
8 | Ueckert,Terry Q. | 85 |
9 | Perez,Ted P. | 97 |
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()
ObjectScriptObjectScript
詳細は以下のドキュメントをご覧ください。
プログラムによる SQL ゲートウェイの使用法