FrontPage/IT/MW/PostgreSQL/SQL の変更点

• 追加された行はこの色です。
• 削除された行はこの色です。
• FrontPage/IT/MW/PostgreSQL/SQL へ行く。
• FrontPage/IT/MW/PostgreSQL/SQL の差分を削除

#author("2024-10-05T22:20:31+09:00","default:admin","admin")
#author("2024-10-05T22:26:06+09:00","default:admin","admin")
#navi(../)
#Contents
------------------------
PostgreSQLで、メモリマスタテーブルとCPUマスタテーブルがそれぞれ異なる時間が歯抜けしている状況に対応するには、以下の手順でSQLクエリを構築します。~

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

**ステップ 1: すべての時間と pcname, day の組み合わせを生成 [#jee165e0]
まず、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のデータを時間マスタと結合 [#l76815c8]
次に、上記の 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: 欠けているデータを確認 [#o4e9abe9]
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;
**1分ごとにデータを整えるSQL文 [#rbe44024]
 WITH data_with_timestamp AS (
     SELECT 
         pcname,
         date || ' ' || time AS timestamp_str,  -- Combine date and time
         total,
         avail
     FROM your_table
 )
 SELECT DISTINCT ON (pcname, date_trunc('minute', to_timestamp(timestamp_str, 'YYYY/MM/DD HH24:MI:SS')))
     pcname,
     to_timestamp(timestamp_str, 'YYYY/MM/DD HH24:MI:SS') AS full_timestamp,
     total,
     avail
 FROM data_with_timestamp
 ORDER BY pcname, date_trunc('minute', to_timestamp(timestamp_str, 'YYYY/MM/DD HH24:MI:SS')), full_timestamp DESC;



**Aテーブルのpcnameを利用してBテーブルに対しインサートするPLSQL文 [#ocf2eb4f]
 DO $$
 DECLARE
     rec RECORD;
 BEGIN
     -- Loop through each unique pcname
     FOR rec IN 
         SELECT DISTINCT pcname FROM your_source_table
     LOOP
         -- Insert the latest data per minute for each pcname
         INSERT INTO your_target_table (pcname, date, time, total, avail)
         SELECT DISTINCT ON (pcname, date_trunc('minute', to_timestamp(date || ' ' || time, 'YYYY/MM/DD HH24:MI:SS')))
             pcname,
             date,
             time,
             total,
             avail
         FROM your_source_table
         WHERE pcname = rec.pcname  -- Filter by current pcname in the loop
         ORDER BY date_trunc('minute', to_timestamp(date || ' ' || time, 'YYYY/MM/DD HH24:MI:SS')), 
                  to_timestamp(date || ' ' || time, 'YYYY/MM/DD HH24:MI:SS') DESC;
     END LOOP;
 END $$;

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

もしさらにデータを補完したい場合、デフォルト値を使用するか、追加データを挿入するロジックを実装することができます。
**情報ソース [#x6fcbc0f]
-[[ChatGPT>https://openai.com/ja-JP/chatgpt/]]