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

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

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 の部分は、欠けているデータですので、補完や追加処理が可能です。

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