指定日の時間毎に最多出現する項目から最新日時の～

質問

指定日の時間毎に最多出現する項目の中から最新日時を持った行のみを取得したいのですが
以下の内容では不要な行が出てしまいます。

SELECT SUBSTR(日時, 12, 2) AS 時間,
項目,
COUNT(項目) AS 項目件数,
MAX(日時) AS 最新日時
FROM Test
WHERE 日時 BETWEEN '2010-12-01 00:00:00' AND '2010-12-01 23:59:59'
GROUP BY SUBSTR(日時, 12, 2),
項目

※SUBSTR(日時, 12, 2)は日時から'時'(hour)を取得しています

サブクエリーを使用しないといけない様な気がするのですが
どなたか知恵をお貸しいただけないでしょうか？

【期待例】
日時 | 項目
--------------------
2010-12-01 00:00:00 | 1
2010-12-01 00:01:00 | 1
2010-12-01 00:02:00 | 1
2010-12-01 00:03:00 | 2
2010-12-01 00:04:00 | 2
2010-12-01 00:05:00 | 2
2010-12-01 00:06:00 | 3
2010-12-01 00:07:00 | 3
2010-12-01 00:08:00 | 0
2010-12-01 00:09:00 | 0
2010-12-01 01:00:00 | 4
2010-12-01 01:01:00 | 4
2010-12-01 01:02:00 | 5
↓
時間 | 項目 | 件数 | 最新日時
----------------------------------------
00 | 2 | 3 | 2010-12-01 00:05:00
01 | 4 | 2 | 2010-12-01 01:01:00

環境は【SqLite3】を使用しています。

回答No.1

yamada_g · ベストアンサー

あまりいい書き方ではないかもしれませんが・・・

http://ideone.com/uJHuJ

お礼

回答ありがとうございます。

ただ今SQLを確認中です。
提示した期待例はOKでしたが、
INNER JOINを利用したため
いくつかのテーブルを結合できず
0件となってしまいました。

各SELECTにWHERE区で期間の条件を加えると
期待した内容になるようですが
使い方は合っていますでしょうか？

※そもそもテーブルには分単位でINSERTし
60分×24時間×365日×N年という情報を
記述しなかった私に問題がありましたね。
申し訳ありません。

回答No.2

nora1962

とりあえず、中間回答ということで
SELECT T1.時間,T1.項目,T1.項目件数,
(SELECT MAX(日時) FROM Test WHERE Test.項目=T1.項目
And 日時 BETWEEN '2010-12-01 00:00:00' AND '2010-12-01 23:59:59' ) AS
最新日時
FROM
( SELECT SUBSTR(日時, 12, 2) AS 時間, 項目, COUNT(項目) AS 項目件数 FROM
Test
GROUP BY SUBSTR(日時, 12, 2),項目 ) AS T1 INNER JOIN
( SELECT 時間, MAX(項目件数) AS 最大件数
FROM ( SELECT SUBSTR(日時, 12, 2) AS 時間, 項目, COUNT(項目) AS 項目件数 FROM Test
GROUP BY SUBSTR(日時, 12, 2),項目 )
GROUP BY 時間 ) AS T2
ON T1.時間=T2.時間 AND T1.項目件数=T2.最大件数

問題は、「最多出現する項目」が複数あった場合ですね。
Oracle、DB2,Sqlserver,PosgresqlのようにWindow関数があると簡単ですが、Sqliteだと上記の結果を元にもう一度結合処理するぐらいしか思い当たりません。

お礼

回答ありがとうございます。

ただ今SQLを確認中です。
提示した期待例はOKでしたが、
テーブル結合に難があるようで
上手くデータが入らない箇所があるようです。
やはりWHERE句の条件が無いからでしょうか。

※そもそもテーブルには分単位でINSERTし
60分×24時間×365日×N年という情報を
記述しなかった私に問題がありましたね。
申し訳ありません。