MySQLにて特定レコードのみを抽出したい

ZabbixのDB(MySQL)からホスト毎の最新値を取得したいと考えております。
・Zabbix 5.0.17
・MySQL 8.0.27

下記クエリを実行するとホストかつ監視項目毎の過去全ての履歴が表示されます。
<クエリ>
SELECT
s.host,
i.name,
from_unixtime(h.clock) AS date,
h.value
FROM
items i
JOIN hosts s
ON i.hostid = s.hostid
JOIN history h
ON i.itemid = h.itemid
WHERE
s.available = 1
and
i.name like '%CPU使用率%'
ORDER BY s.host,date asc;

<クエリ実行結果>
[host,name,date,value]
host1, CPU使用率, 2021-11-18 18:56:05, 1%
host1, CPU使用率, 2021-11-18 18:59:05, 21%
host1, CPU使用率, 2021-11-18 19:02:05, 10%
host1, CPU使用率, 2021-11-18 19:05:05, 23%
host2, CPU使用率, 2021-11-18 18:56:05, 29%
host2, CPU使用率, 2021-11-18 18:59:05, 41%
host2, CPU使用率, 2021-11-18 19:02:05, 33%
host2, CPU使用率, 2021-11-18 19:05:05, 45%

上記結果から、最新日時(現在時刻を11/18 19:06とした場合)のレコードのみ抽出したいと考えております。
こうしたい場合、どういったクエリ文であれば期待する結果が得られますでしょうか。
<期待するクエリ実行結果>
[host,name,date,value]
host1, CPU使用率, 2021-11-18 19:05:05, 23%
host2, CPU使用率, 2021-11-18 19:05:03, 45%

お力添え頂けましたら幸いです。
よろしくお願いいたします。

No.3 ベストアンサー 回答者： kmee 回答日時： 2021/11/20 05:48

方法は大きく2つ

1.「ホスト毎の最新時刻を取得するクエリ」を作り、INNER JOIN で最新時刻の行だけ抽出する。
2. ウィンドウ関数ROW_NUMBER() で ホスト毎に行番号を付けたクエリを作り、行番号=1 だけ抽出する。
　※ ウィンドウ関数は古いMySQLでは対応していないが、8.0なら大丈夫なはず



環境が無いので詳細未確認ですが、2の例です

SELECT w.host,w.name,w.date,w.value
FROM (
SELECT
s.host,
i.name,
from_unixtime(h.clock) AS date,
h.value,
row_number() over (partitiion by s.host,i.name order by h.clock) as rno
FROM
items i
JOIN hosts s
ON i.hostid = s.hostid
JOIN history h
ON i.itemid = h.itemid
WHERE
s.available = 1
and
i.name like '%CPU使用率%'
) w
where w.rno=1
ORDER BY host,date asc;

この回答へのお礼

コメントありがとうございます。

教えて頂いた構文で期待する結果が得られました！！！！
（partition部分を少し修正しております）

仰せの通り、MySQL 8からwindow関数が使えるようになった模様です。
とても参考になりました、ありがとうございました！

以下実行クエリと結果です。

SELECT w.host,w.name,w.date,w.value
FROM (
SELECT
s.host,
i.name,
from_unixtime(h.clock) AS date,
h.value,
row_number() over (partition by s.host,i.name order by h.clock desc) as rno
FROM
items i
JOIN hosts s
ON i.hostid = s.hostid
JOIN history h
ON i.itemid = h.itemid
WHERE
s.available = 1
and
i.name like '%CPU使用率%'
) w
where w.rno=1;

<クエリ実行結果>
[host,name,date,value]
host1, CPU使用率, 2021-11-20 09:10:05, 23%
host2, CPU使用率, 2021-11-20 09:10:05, 45%


(話がそれますが)
historyテーブルに約16万レコード、itemsテーブルに約1万レコード、hostsテーブルに数百レコードの状況で、上記クエリを発行するとCPU負荷が上昇します(テスト環境だと10%程)
EXPLAINの実行結果を見ると、typeがALLであったりExtraにUsing temporaryとあるので、業務で使うと致命的かなと考えています。
こちらについては「MySQLクエリ改善について ～(続)MySQLにて特定レコードのみを抽出したい～」といったタイトルで別途質問をさせて頂こうと思います。

お礼日時：2021/11/20 09:40

No.2 回答者： naoiz 回答日時： 2021/11/19 10:35

ごめんなさい、順番が逆でした。

ただ、一番古いのを持ってきちゃうと思うので、どうしようかなぁ…ということで、DATEカラムが最大値をとるWHERE句を追加して、↓これでどうでしょう？

i.name like '%CPU使用率%'
AND MAX（`date`)
GROUP BY s.host
ORDER BY s.host,date desc

DATEのカラムにMAX関数効くのかなぁ…

この回答へのお礼

追記ありがとうございます。

今回はグループ関数エラーが発生していました。

SELECT
s.host AS host,
i.name,
from_unixtime(h.clock) AS date,
h.value
FROM
items i
JOIN hosts s
ON i.hostid = s.hostid
JOIN history h
ON i.itemid = h.itemid
WHERE
s.available = 1
and
i.name like '%CPU使用率%'
and
MAX(`date`)
GROUP BY host
ORDER BY host,date desc;
<実行結果>
ERROR 1111 (HY000): Invalid use of group function

お礼日時：2021/11/19 13:18

No.1 回答者： naoiz 回答日時： 2021/11/19 02:39

最新1件だけを取り出したいということであれば、最後を下記にしたらいかがでしょう。

ORDER BY s.host,date desc
GROUP BY s.host

検証してないので、期待値と違ったらごめんなさい。

この回答へのお礼

コメント頂きありがとうございます。

試してみたところ文法エラーとなりました。

SELECT
s.host AS host,
i.name,
from_unixtime(h.clock) AS date,
h.value
FROM
items i
JOIN hosts s
ON i.hostid = s.hostid
JOIN history h
ON i.itemid = h.itemid
WHERE
s.available = 1
and
i.name like '%CPU使用率%'
ORDER BY host,date desc
GROUP BY host;
<実行結果>
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY host' at line 17

お礼日時：2021/11/19 08:06