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

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

# JSONデータを結合して、データ分析をしてみる
## 
* まず１つめのJSONデータは[Oracle Live Labs](https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/run-workshop?p210_wid=813&p210_wec=&session=111591414958196)内の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フライト当たりの平均遅延分数が多いことが分かります。
    ![画像1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3058002/3f152f6b-21ef-45e7-907c-518579d81d94.png)