質問: 10分毎の温度と、取得時間が格納されているdbテーブルから SQLで日毎の最大温度と時間を抽出するクエリ文を作りたいです。 DBテーブル(temp_miniminutely)の構成はこんなかんじです。 観測時刻は10分づつ刻んでレコードが存在します。 温度 | 観測時刻 | --------+--------------------+ 26.0 | 202003011750 | 初歩としてこのような処理のsqlを書きました。 以下のsqlで20200301 ``` SELECT max(温度) as '日間最高気温', '日間最高気温観測時刻' as '観測時刻' from temp_miniminutely WHERE 観測時刻 LIKE '20200301%'; ``` このようにして、日間最高気温と日間最高気温観測時刻 を温度と観測時刻から割り出したいのですが 問題としまして、 1.10分刻みの中から1日の間を指定して毎日の最高気温を繰り返し抽出するのによい繰り返し文が見つからない。、 2.max(温度)の時と同列の観測時刻を抽出する方法がわからない。 この点につまづいています。 解る方いましたら助言お願いいたします
回答: 質問文に、 ``` WHERE 観測時刻 LIKE '20200301%'; ``` とありますので、「観測時刻」は TIMESTAMP 型ではなく VACHAR 型か CHAR 型であると思われます。ここでは `CHAR(12)` としておきます。 ``` CREATE TABLE temp_miniminutely ( 温度 REAL NOT NULL, 観測時刻 CHAR(12) NOT NULL PRIMARY KEY ); ``` 「日間」なので、`SUBSTR(観測時刻, 1, 8)` でグルーピングします。最高気温に対応する時刻(日間最高気温観測時刻)は自己結合(self join)して求めます。 ``` SELECT 日間最高気温, 観測時刻 AS 日間最高気温観測時刻 FROM temp_miniminutely a INNER JOIN ( SELECT SUBSTR(観測時刻, 1, 8) AS t, MAX(温度) AS 日間最高気温 FROM temp_miniminutely GROUP BY t ) b ON SUBSTR(a.観測時刻, 1, 8) = b.t AND a.温度 = b.日間最高気温; ``` 問題になるかもしれない事としては、最高気温が複数の時刻で記録されている場合です。例えば、`2020/8/1 12:00` と `12:10` に同じ最高気温 33°C が記録されている場合には、それらが全て抽出される事になります。 ``` 日間最高気温 | 日間最高気温観測時刻 --------------+---------------------- 16 | 202003011750 20 | 202004011750 33 | 202008011200 33 | 202008011210 ```