FrontPage/IT/MW/PostgreSQL/SQL の履歴(No.2)

• 履歴一覧
• 差分 を表示
• 現在との差分 を表示
• ソース を表示
• FrontPage/IT/MW/PostgreSQL/SQL へ行く。
  • 1 (2024-09-24 (火) 11:26:31)
  • 2 (2024-09-24 (火) 11:30:15)

• Prev
• Next
• FrontPage/IT/MW/PostgreSQL

PostgreSQLで、メモリマスタテーブルとCPUマスタテーブルがそれぞれ異なる時間が歯抜けしている状況に対応するには、以下の手順でSQLクエリを構築します。

テーブル構成†

• 時間マスタテーブル (time_master): 全ての時間が格納されている。
  • time (時間)
• メモリマスタテーブル (memory_master): メモリ使用率を格納。
  • pcname (PC名)
  • day (日付)
  • time (時間)
  • used (使用率)
• CPUマスタテーブル (cpu_master): CPU使用率を格納。
  • pcname (PC名)
  • day (日付)
  • time (時間)
  • used (使用率)

ステップ 1: すべての時間と pcname, day の組み合わせを生成†

まず、pcname と day のすべての組み合わせに対して、時間のリストを組み合わせるために、CROSS JOIN を利用します。

sql

-- メモリマスターとCPUマスターのすべての `pcname` と `day` を統合
WITH all_times AS (
    SELECT DISTINCT pcname, day FROM memory_master
    UNION
    SELECT DISTINCT pcname, day FROM cpu_master
)
-- すべての時間と組み合わせる
SELECT a.pcname, a.day, t.time
FROM all_times a
CROSS JOIN time_master t;

ステップ 2: メモリとCPUのデータを時間マスタと結合†

次に、上記の pcname, day, time の組み合わせに対して、memory_master と cpu_master のデータを LEFT JOIN して、それぞれの時間で使用率のデータを補完します。

sql

WITH all_times AS (
    SELECT DISTINCT pcname, day FROM memory_master
    UNION
    SELECT DISTINCT pcname, day FROM cpu_master
)
-- すべての時間と組み合わせる
SELECT a.pcname, a.day, t.time, m.used AS memory_used, c.used AS cpu_used
FROM all_times a
CROSS JOIN time_master t
LEFT JOIN memory_master m ON a.pcname = m.pcname AND a.day = m.day AND t.time = m.time
LEFT JOIN cpu_master c ON a.pcname = c.pcname AND a.day = c.day AND t.time = c.time;

このクエリにより、全ての pcname, day, time の組み合わせに対して、memory_master と cpu_master のデータを結合し、両テーブルで欠けている部分が NULL で表示されます。

ステップ 3: 欠けているデータを確認†

memory_master または cpu_master のデータが欠けている場合、そのレコードの memory_used または cpu_used が NULL となります。これを確認するために、NULL の行だけをフィルタリングします。

sql

WITH all_times AS (
    SELECT DISTINCT pcname, day FROM memory_master
    UNION
    SELECT DISTINCT pcname, day FROM cpu_master
)
-- 欠けているデータを確認
SELECT a.pcname, a.day, t.time, m.used AS memory_used, c.used AS cpu_used
FROM all_times a
CROSS JOIN time_master t
LEFT JOIN memory_master m ON a.pcname = m.pcname AND a.day = m.day AND t.time = m.time
LEFT JOIN cpu_master c ON a.pcname = c.pcname AND a.day = c.day AND t.time = c.time
WHERE m.used IS NULL OR c.used IS NULL;

まとめ†

このクエリでは、memory_master と cpu_master のデータがそれぞれ欠けている時間を特定できます。また、memory_used や cpu_used が NULL の部分は、欠けているデータですので、補完や追加処理が可能です。

もしさらにデータを補完したい場合、デフォルト値を使用するか、追加データを挿入するロジックを実装することができます。

情報ソース†

ChatGPT