faq/5/172

クエリで並べ替えた後行番号を表示するには †

メッセージ †

ID date      name
-------------------
1  09-11-01  A
2  09-08-21  B
3  09-08-21  C
4  09-11-13  D

を以下のようにdateで並べ替え、順に行番号を表示したいのですが、

NO date      name
-----------------
1  09-08-21  B
2  09-08-21  C
3  09-11-01  A
4  09-11-13  D

サブクエリで count(*) でdateの小さいものをcountすると B, Cが同じ行番号になってしまいますので目的に合いません。SQLポケットリファレンスではROW_NUMBERという関数がありますが、HSQLDBでは使えないのか、エラーになります。

よろしくおねがいします。

---

サンプルの提供は可能でしょうか †

M.Kamataki (2009-11-23 11:14:20 (月))

作成中のクエリーを含めたサンプルのODBファイルを提供可能でしょうか。より多くの方が問題を共有できるので、回答が期待できます。

なお、HSQLDBで利用できる関数は以下のページで確認できます。

http://hsqldb.org/doc/guide/ch09.html

サンプルファイルを添付しました †

guni (2009-11-23 12:05:33 (月))

M.Kamatakiさん、お世話になります。
サンプルファイルを添付しました。
query1が私なりに作ったものですが、重複無くNOをつけたいのですが、うまくいきません。

添削しました †

M.Kamataki (2009-11-23 16:25:52 (月))

date、nameフィールドで並べ替えたいということですよね。この場合は、2つのフィールドをまとめて比較すればよいので、CONCAT関数で文字列として結合してしまいました。したがって並べ替えもIDフィールドではなくnameフィールドに変更しています。以下ではどうでしょう。

SELECT 
(select count(*)+1 from "table1" as a1
where CONCAT(a1."date", a1."name") < CONCAT("table1"."date","table1"."name")) as "NO",
"ID", "date", "name" FROM "table1"
ORDER BY "date", "name" ;

結果は下図のようになります。



ただ、サンプルのようにdate、nameフィールドを合わせてユニークだった場合が前提条件なので、この条件から外れるデータだと、このクエリーではうまく番号付けできないです。その場合は、date、nameに加え、IDフィールドもCONCATすれば、番号付けできるでしょう。（この部分、訂正）

ちょっと強引かもしれないので、他にも実現できる方法を募集ですね。

訂正 †

M.Kamataki (2009-11-23 17:02:06 (月))

MySQLのCONCAT関数は3つ以上の引数を結合できるようですが、HSQLDBを含めそれ以外のDBのCONCAT関数の引数は2つまでです。以下のクエリーのように、date、IDフィールドをCONCATし、並べ替えをdate、ID、nameの順にするとうまくいくんじゃないかと思います。

SELECT 
(select count(*) from "table1" as a1
where
CONCAT(a1."date", a1."ID") < CONCAT("table1"."date","table1"."ID")) +1
as "NO",
"ID", "date", "name" FROM "table1"
ORDER BY "date" ,"ID", "name" ;

下図のサンプルで検証してみました。

3つのフィールドをCONCAT †

M.Kamataki (2009-11-23 17:42:05 (月))

CONCATするフィールドが3つある場合は、次のようにCONCAT関数をネストして使えば実現できました。

CONCAT( CONCAT(a1."date", a1."name"), a1."ID" ) < CONCAT( CONCAT("table1"."date","table1"."name"), "table1"."ID" )

並べ替えの指定は

ORDER BY "date", "name", "ID"

として、下図のサンプルのように、date、nameフィールドの順に並べ替えできました。ふぅ。

うまくできました †

guni (2009-11-23 18:18:33 (月))

M.Kamataki さん、ありがとうごさいました。

これ、初心者にはとても考え付かないことでした。

他にも実現できる方法 †

M.Kamataki (2009-11-24 00:22:54 (火))

以下のクエリーでも faq5_172result3.jpg と同じ結果になります。

SELECT 
(select count(*)
from "table1" as a1
where
a1."date" < "table1"."date"
or (
a1."date" = "table1"."date" and a1."name" < "table1"."name"
or (
a1."name" = "table1"."name" and a1."ID" < "table1"."ID"
) )
) +1 as "NO",
"ID", "date", "name"
FROM "table1"
ORDER BY "date", "name", "ID" ;

WHERE句に複数の条件を指定しています。たぶん、理論的に正しいのはこちらの方法だと思います。SQLの奥深さを感じますねぇ。

参考：
順位を求めるｓｑｌ文について
http://oshiete1.goo.ne.jp/qa367708.html

どこかに落とし穴がないことを祈ってます。

無題 †

tani (2009-11-24 10:52:29 (火))

なんかデータ量が増えるとすごい時間がかかりそうなSQLなので、ナンバリング部分はマクロとかのプログラムから後でくっつけてやったほうが良いのではないかな、とか思いました。

いったん完了にします †

M.Kamataki (2009-11-24 13:49:17 (火))

いずれにしても、データベース内容によって判断が必要でしょう。このあたりはデータベースのご担当の方にお任せします。

いったん完了にしますが、情報があれば追記もOKです。

HSQLDBの採番機能 †

M.Kamataki (2009-11-24 18:27:20 (火))

SEQUENCEを使うと、プライマリキーのフィールドとは別に番号付けしたフィールドをSQLで実現できるようです。しかしながら、プライマリキーの昇順にのみ対応した番号付けになるようです。別テーブルにINSERT INTOすると良いかも。

また「0」から始まりますし、SQLを実行するたびに番号は増えていきます。

SEQUENCEの説明はHSQLDBサイトの以下のページ、

http://hsqldb.sourceforge.net/doc/guide/ch02.html#N104E1

日本語の簡単な説明は以下です。

http://www.dzeta.jp/tech/index.cgi?%A5%D7%A5%ED%A5%B0%A5%E9%A5%DF%A5%F3%A5%B0tips#l7

HSQLDBの採番機能 †

M.Kamataki (2009-11-24 21:01:50 (火))

＞また「0」から始まりますし、SQLを実行するたびに番号は増えていきます。

以下のコマンド（「1」からの番号付けの例）でリセットできました。

ALTER SEQUENCE sequence_name RESTART WITH 1 ;

いったんCREATE SEQUENCEした sequence_name は、DROPしない限りデータベースに保持されるようです。

HSQLDBの採番機能 †

M.Kamataki (2009-11-24 21:09:53 (火))

サンプルの test1.odb で、並べ替えを行ったビューを作成し、そのビューを基に以下のようなSQLで、1からの番号付けが行えました。

SELECT
NEXT VALUE FOR sequence_name as NO ,
"date" , "name"
FROM "v_table1" ;

有益な情報ありがとうございます †

ike@九州 (2009-11-26 10:06:27 (木))

Base ドキュメントを開いた状態で実行すると結果が表示されます
(5000件で瞬時でした)

'シーケンスを利用したクエリをGUI表示する
Sub OpenQuery
 oDoc = ThisComponent
 oDataSource = oDoc.DataSource
 oCon = oDataSource.getConnection("","")
 oStmt = oCon.createStatement()
 oStmt.EscapeProcessing = False
 sTableName = "table1"       'テーブル名
 sSeqName = "myseq"          'シーケンスの任意な名前
 sViewName = "v_table1"      'ビューの任意な名前
 sQueryName = "シーケンスクエリー"  'クエリの任意な名前    
 sSQL = "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES ;"
 bSeq = False
 '既存シーケンス名の情報取得
 oResult = oStmt.executequery(sSQL)
 While oResult.next()
  if oResult.getString(1) = sSeqName then bSeq = True
 Wend
 bView = False
 '既存ビュー名の情報取得  
 sSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.SYSTEM_VIEWS ;"
 oResult = oStmt.executequery(sSQL)
 While oResult.next()
  if oResult.getString(1) = sViewName then bView = True
 Wend  
 'シーケンスが有ればリセット、無ければ作成
 IF bSeq then  
  sSQL = "ALTER SEQUENCE """ & sSeqName & """ RESTART WITH 1;"
  oStmt.execute(sSQL)
 Else
  sSQL = "CREATE SEQUENCE """ & sSeqName & """ START WITH 1;"
  oStmt.execute(sSQL)
 End If
 'ビューがあれば削除
 If bView then
  sSQL = "DROP VIEW """ & sViewName & """ ;"
  oStmt.execute(sSQL)
 End if
 'ビューの作成
 sSQL = "CREATE VIEW """ & sViewName &_
 """ AS SELECT * FROM """ & sTableName & """ ORDER BY ""date"" ASC, ""name"" ASC, ""ID"" ASC ;"
 oStmt.execute(sSQL)  
 'クエリが有ればリセット、無ければ作成
 oQueryDefs = oDataSource.getQueryDefinitions()
 sSQL = "SELECT NEXT VALUE FOR """ & sSeqName &_
  """ as ""NO"" ,""date"" , ""name"" FROM """ & sViewName & """ ;"  
 If  oQueryDefs.hasByName(sQueryName) then
  oQuery = oQueryDefs.getByName(sQueryName)
  oQuery.Command = sSQL
  oQuery.EscapeProcessing = False 'SQL直接実行に設定
 Else
  oNewQueryDef = oQueryDefs.createInstance()
  oNewQueryDef.Command = sSQL
  oNewQueryDef.EscapeProcessing = False 'SQL直接実行に設定
  oQueryDefs.insertByName(sQueryName, oNewQueryDef)  
 End IF

 oCon.close() 'マクロによる接続を切断

 TableRefresh(oDoc.getURL) 'テーブル更新(Base による接続確立)
 wait 100
 oController = oDoc.getCurrentController()
 'Base による接続を利用してクエリを開く(True にすると編集で開きます)   
 oController.loadComponent(com.sun.star.sdb.CommandType.QUERY, sQueryName, False)
End Sub

Sub TableRefresh(sURL)
 On Error Goto Errorhundler
  oComponents = StarDesktop.Components
  oComponentsEnum = oComponents.createEnumeration()
   While oComponentsEnum.hasMoreElements()
    oComponent = oComponentsEnum.nextElement()
    If HasUnoInterfaces(oComponent, "com.sun.star.frame.XModel") then
      If oComponent.getURL = sURL then
        oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
	     oFrame = oComponent.getCurrentController().getFrame()
        oDisp.executeDispatch(oFrame,".uno:DBViewTables", "", 0, Array())
        oDisp.executeDispatch(oFrame,".uno:DBRefreshTables", "", 0, Array())           
      End if
    end if   
   Wend
 Errorhundler:
End Sub

＃11/27修正しました

ありがとうございます †

M.Kamataki (2009-11-26 18:24:55 (木))

ike@九州さん、どうもありがとうございます。システムテーブルを参考にされているので、faq/5/179「HSQLDBにはどのようなシステムテーブルがありますか」に簡単なシステムテーブルの表示方法を紹介しました。

p.s. もしかするとODBファイルに影響するかもしれません。わたしだけかもしれませんが、テーブルにアクセスできなくなりました。一度、ZIPアーカイブとして展開し、そのまま再度ZIP圧縮してテーブルにアクセス可能となりました。たぶん大丈夫だとは思います。。。 なぜか、soffice.bin プロセスが残っているのが原因でした。プロセスを殺してから、再度、OpenOffice.orgを起動し、データベースファイルを開くとテーブルにアクセスできました。その後、マクロを実行してもOpenOffice.org終了後、プロセスが残ることは、今のところありません。（11/26 20:00 頃追記）

再現しましたか… †

ike@九州 (2009-11-27 08:10:22 (金))

最初に私もそうなりました。
原因は同じくsoffice.bin プロセスが残っているものでした。
同一ファイルで一度きりで、後は何度やっても再発しないので、模索中の出来事だと思ってました。

やはり何処かの記述に問題あるのかもしれませんね。
上記マクロについて、皆様は参考程度に留めて置いて下さいませ。

検索しても情報が殆ど無い部分のマクロについては常に模索中です
各データベースのフロントエンドとして Base が普及することが法人等で OOo の普及加速に繋がるような気がしてます。

Re: 再現しましたか… †

M.Kamataki (2009-11-27 11:29:24 (金))

オリジナルの test1.odb をコピーしてもう一度、新規にマクロを加えて確認してみましたが、今回は再現しませんでした。

関係ないかもしれませんが、マクロ側で対処するなら、以下のコード（データベースコネクションのクローズ）をプロシージャの最後に加えるぐらいですかね。

oCon.close()

原因が想像できるので、対処のしやすいことは確かです。

同じ結論になりました。 †

ike@九州 (2009-11-27 11:59:21 (金))

幾つか試行したところ、同じ結論になりました。
マクロによる接続はマクロにて必ず終了しておく必要があるようですね。

Baseドキュメントを開いて直ぐの状態では、HSQLDB との接続はまだされてないようです。
そのまま、ツール＞マクロ＞マクロを実行　だとクエリをGUIで開くコードが必ずエラーをはきました。

ロックファイルが作成される操作をした時に Base は HSQLDB と接続確立がされるようで、その後ではマクロでクエリをGUI表示できます。

ので、テーブルの更新をさせて一石二鳥の動作を含めました。
これで、既存のドキュメント上で初実行しても問題は再現しなくなりました。
有難うございました。

wait入れました †

M.Kamataki (2009-11-27 14:23:29 (金))

ike@九州さん、たびたびありがとうございます。

＞Baseドキュメントを開いて直ぐの状態では、HSQLDB との接続はまだされてないようです。

はい、そうです。わたしは、テーブルカテゴリを表示（DBとコネクト）してから、マクロを実行していたので、幸いにもエラーにはなりませんでした。

環境にもよると思うんですが、クエリー表示のタイミングが早すぎることもあるようなので、コードのうちテーブルリフレッシュのあとに wait を入れてみました。これで、上記のマニュアル作業時と同じようにテーブルカテゴリの表示を待ってクエリーのGUI表示となりました。

SQLアタマアカデミー †

M.Kamataki (2009-11-28 21:06:52 (土))

SQLについて、技術評論社さんのWebサイトに面白い記事がありました。SQLによる番号付けが第1回のテーマになっています。以下です。

SQLアタマアカデミー
http://gihyo.jp/dev/serial/01/sql_academy2/000101

関連フィールドを文字列として連結する方法も載っていました。わたしが最初に紹介した方法もそれほど外しているわけではないようです。

Basicなどの手続き型言語に対して、SQLを「集合指向」の言語と紹介しています。

無題 †

ike@九州 (2009-11-30 09:02:15 (月))

wait についてナイス記述だと思いました。

文字連結SQLのみでの番号付けで、数値、IDを変更して正しく並び替えが出来ない現象がでたので、桁を合わせて並び替えを実施していたのですが5000件位で実行時間が数分間掛かりました。

条件やデータ量が増えると tani さんご指摘の通りでした。
煩雑に使用する場合はマクロに頼るしかないですけれど、65536以下ならCalcに並び替えクエリをドラッグドロップで番号付けされますね　orz

順位付け †

M.Kamataki (2009-11-30 10:28:10 (月))

たぶん「並べ替えをして、番号を付けたい」というのは、順位付けをしたいということもあると思います。その場合、意味がある順位まであらかじめ絞っておくという方法があります。

HSQLDBの場合は、SELECT文にLIMIT句（要「SQLコマンドを直接実行」）を加えると制御できます。例えば上記マクロの「CREATE VIEW」文の最後に「LIMIT 30 OFFSET 0」を加えると上位30番目までのレコードに絞り込めます。0番目からなら「OFFSET 0」は省略可能で、任意の順番からなら「0」の数値を変えます[*]。

「COUNT(*)」すると、1+2+3+4…というように数えるレコード数が増えていくので5000行だと12,502,500行（(1+5000)×5000÷2）になるのかな。

* LIMIT句などSELECT順による制御はいろいろ用法があるみたいなので以下のドキュメントで要確認
http://hsqldb.org/doc/guide/ch09.html

お名前:
題名: