JSONデータ同士を結合して、データ分析をしてみる

本記事で行うこと

関連性のある2つのJSONデータをもとに表を2つAutonomous Databaseに作成し、その2つの表を結合操作することで簡単なデータ分析を行ってみたいと思います。

JSONデータを結合して、データ分析をしてみる

• まず１つめのJSONデータはOracle Live Labs内のLab4からダウンロードできるものを使います。

• 手順に沿って、AirportDelays.jsonをダウンロードし、Autonomous Databaseにロードします。

• このデータはアメリカの空港の場所や、2003~2016年の飛行機の発着数、遅延の分数などのサンプルデータが格納されています。

• 2つめのJSONデータを作成し、Autonomous Database内に表として格納します。

• 表を作成します。

  CREATE TABLE weather_json_data (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- 自動増加 ID
  json_data CLOB CHECK (json_data IS JSON) -- JSON 格納用カラム
  );
  

• weather_json_data表にサンプルのデータをINSERTします。

  
  INSERT INTO weather_json_data (json_data)
  VALUES ('
  [
      {
          "AirportCode": "ATL",
          "Year": 2003,
          "Month": 6,
          "AverageTemperature": 27.3,
          "TotalPrecipitation": 95.2,
          "AverageWindSpeed": 15.5
      },
      {
          "AirportCode": "BOS",
          "Year": 2003,
          "Month": 6,
          "AverageTemperature": 22.1,
          "TotalPrecipitation": 120.4,
          "AverageWindSpeed": 13.5
      },
      {
          "AirportCode": "BWI",
          "Year": 2003,
          "Month": 6,
          "AverageTemperature": 24.8,
          "TotalPrecipitation": 110.3,
          "AverageWindSpeed": 12.7
      },
      {
          "AirportCode": "CLT",
          "Year": 2003,
          "Month": 6,
          "AverageTemperature": 26.4,
          "TotalPrecipitation": 85.6,
          "AverageWindSpeed": 10.9
      },
      {
          "AirportCode": "DCA",
          "Year": 2003,
          "Month": 6,
          "AverageTemperature": 25.2,
          "TotalPrecipitation": 99.8,
          "AverageWindSpeed": 14.1
      }
  ]');
  
  

• 2003年6月の複数の空港があるエリアの平均気温、降水量、平均風速のデータを登録しました。このデータはダミーです。

• AirportDelays表と、作成したweather_json_data表を結合してデータ分析をしてみます。

  SET LINESIZE 200  
  SET PAGESIZE 50  
  SET NULL 'NULL'
  COLUMN AirportCode FORMAT A6
  COLUMN Year FORMAT 9999
  COLUMN Month FORMAT 99
  COLUMN Avg_Temperature FORMAT 999.9
  COLUMN Precipitation FORMAT 999.9
  COLUMN Avg_WindSpeed FORMAT 999.9
  COLUMN Total_Flights FORMAT 99999999
  COLUMN Total_Minutes_Delayed FORMAT 99999999
  COLUMN Avg_Delay_Per_Flight FORMAT 999.9
  
  SELECT 
      d.AirportCode,
      TO_NUMBER(JSON_VALUE(d.Time, '$.Year')) AS Year,
      TO_NUMBER(JSON_VALUE(d.Time, '$.Month')) AS Month,
      ROUND(AVG(w.AverageTemperature), 1) AS Avg_Temperature,  
      ROUND(AVG(w.TotalPrecipitation), 1) AS Precipitation, 
      ROUND(AVG(w.AverageWindSpeed), 1) AS Avg_WindSpeed,       
      SUM(TO_NUMBER(JSON_VALUE(d.Statistics, '$.Flights.Total'))) AS Total_Flights,  
      SUM(TO_NUMBER(JSON_VALUE(d.Statistics, '$."Minutes Delayed"."Total"'))) AS Total_Minutes_Delayed, 
      ROUND(SUM(TO_NUMBER(JSON_VALUE(d.Statistics, '$."Minutes Delayed"."Total"'))) 
            / NULLIF(SUM(TO_NUMBER(JSON_VALUE(d.Statistics, '$.Flights.Total'))), 0), 1) AS Avg_Delay_Per_Flight 
  FROM airportdelays d
  LEFT JOIN (
      SELECT 
          jt.AirportCode, 
          jt.Year, 
          jt.Month, 
          jt.AverageTemperature, 
          jt.TotalPrecipitation, 
          jt.AverageWindSpeed
      FROM weather_json_data w,
          JSON_TABLE(
              w.json_data, '$[*]' 
              COLUMNS (
                  AirportCode VARCHAR2(10) PATH '$.AirportCode',
                  Year NUMBER PATH '$.Year',
                  Month NUMBER PATH '$.Month',
                  AverageTemperature NUMBER(5,2) PATH '$.AverageTemperature',
                  TotalPrecipitation NUMBER(6,2) PATH '$.TotalPrecipitation',
                  AverageWindSpeed NUMBER(5,2) PATH '$.AverageWindSpeed'
              )
          ) jt
  ) w
  ON UPPER(d.AirportCode) = UPPER(w.AirportCode)
  AND TO_NUMBER(JSON_VALUE(d.Time, '$.Year')) = w.Year
  AND TO_NUMBER(JSON_VALUE(d.Time, '$.Month')) = w.Month
  WHERE w.AverageTemperature IS NOT NULL  
  GROUP BY d.AirportCode, TO_NUMBER(JSON_VALUE(d.Time, '$.Year')), TO_NUMBER(JSON_VALUE(d.Time, '$.Month'))
  ORDER BY Year, Month, Avg_Delay_Per_Flight DESC;
  
  

結果を確認すると、風速が速いエリアにある空港ほど、1フライト当たりの平均遅延分数が多いことが分かります。