
REPLACE INTO pr_ref_monthly(
  pr_ref_monthly_no          /* PR参考データ月次NO */
  ,server_no                 /* サーバNO */
  ,pr_ref_year               /* 年 */
  ,pr_ref_month              /* 年月 */

  ,irradiance                /* アレイ平行面日射量 */
  ,irradiance_exclude        /* アレイ平行面日射量_除外適用 */
  ,amb_temp                  /* 月間平均気温 */
  ,amb_temp_exclude          /* 月間平均気温_除外適用 */
  ,mod_temp                  /* 月間平均モジュール温度 */
  ,mod_temp_exclude          /* 月間平均モジュール温度_除外適用 */
  ,irr_weighted_temp         /* アレイ平行面日射量重みづけ平均モジュール温度 */
  ,irr_weighted_temp_exclude /* アレイ平行面日射量重みづけ平均モジュール温度_除外適用 */
  ,ac_energy                 /* 売電電力量 */
  ,ac_energy_exclude         /* 売電電力量_除外適用 */
)
SELECT
  NULL pr_ref_monthly_no   /* PR参考データ月次NO */
  ,:server_no1 server_no   /* サーバNO */
  ,rf.year pr_ref_year     /* 年 */
  ,rf.month pr_ref_month   /* 年月 */

  ,IFNULL(irradiance.value, 0) irradiance     /* アレイ平行面日射量 */
  ,IFNULL(irradiance_exclude.value, 0) 
    irradiance_exclude                        /* アレイ平行面日射量_除外適用 */

  ,IFNULL(amb_temp.value, 0) amb_temp         /* 月間平均気温 */
  ,IFNULL(amb_temp_exclude.value, 0) 
    amb_temp_exclude                          /* 月間平均気温_除外適用 */

  ,IFNULL(mod_temp.value, 0) mod_temp         /* 月間平均モジュール温度 */
  ,IFNULL(mod_temp_exclude.value, 0) 
    mod_temp_exclude                          /* 月間平均モジュール温度_除外適用 */

  ,IFNULL(irr_weighted_temp.value, 0) 
    irr_weighted_temp                         /* アレイ平行面日射量重みづけ平均モジュール温度 */
  ,IFNULL(irr_weighted_temp_exclude.value, 0) 
    irr_weighted_temp_exclude                 /* アレイ平行面日射量重みづけ平均モジュール温度_除外適用 */

  ,IFNULL(ac_energy.value, 0) ac_energy       /* 売電電力量 */
  ,IFNULL(ac_energy_exclude.value, 0) 
    ac_energy_exclude                         /* 売電電力量_除外適用 */
FROM (
  SELECT 
    0 + :year year 
    ,0 + :month month
) rf


LEFT OUTER JOIN (
    /* アレイ平行面日射量 */
    SELECT 
      DATE_FORMAT(dv.datetime, '%Y%m') month
      ,SUM(dv.value) value
    FROM (
      /* 時間別平均 */
      SELECT 
        DATE_FORMAT(dv.pr_data_ts, '%Y-%m-%d %H') datetime
        ,AVG(dv.pr_data_value) value
      FROM pr_data_value dv      /* PR基礎データ値 */
      INNER JOIN pr_data_item it /* PR基礎データ項目 */
        ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
        AND it.server_no = :server_no2
        AND it.pr_data_item_id = 'irradiance' /* アレイ平行面日射量 */
      GROUP BY server_no, DATE_FORMAT(dv.pr_data_ts, '%Y-%m-%d %H')
    ) dv
    GROUP BY DATE_FORMAT(dv.datetime, '%Y%m')
  ) irradiance
  ON irradiance.month = rf.month



LEFT OUTER JOIN (
    /* アレイ平行面日射量 除外考慮 */
    SELECT 
      DATE_FORMAT(dv.datetime, '%Y%m') month
      ,SUM(dv.value) value
    FROM (
      /* 時間別平均 */
      SELECT 
        DATE_FORMAT(dv.pr_data_ts, '%Y-%m-%d %H') datetime
        ,AVG(dv.pr_data_value) value
      FROM pr_data_value dv      /* PR基礎データ値 */
      INNER JOIN pr_data_item it /* PR基礎データ項目 */
        ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
        AND it.server_no = :server_no3
        AND it.pr_data_item_id = 'irradiance' /* アレイ平行面日射量 */
      WHERE NOT EXISTS (
        SELECT 1 FROM pr_exclude_condition cd
        WHERE dv.pr_data_ts >= cd.pr_exclude_start
          AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE
      )
      GROUP BY server_no, DATE_FORMAT(dv.pr_data_ts, '%Y-%m-%d %H')
    ) dv
    GROUP BY DATE_FORMAT(dv.datetime, '%Y%m')
  ) irradiance_exclude
  ON irradiance_exclude.month = rf.month




LEFT OUTER JOIN (
    /* 月間平均気温 */
    SELECT 
      DATE_FORMAT(dv.pr_data_ts, '%Y%m') month
      ,AVG(dv.pr_data_value) value
    FROM pr_data_value dv      /* PR基礎データ値 */
    INNER JOIN pr_data_item it /* PR基礎データ項目 */
      ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
      AND it.server_no = :server_no4
      AND it.pr_data_item_id = 'amb_temp' /* 気温 */
    GROUP BY DATE_FORMAT(dv.pr_data_ts, '%Y%m')
  ) amb_temp
  ON amb_temp.month = rf.month


LEFT OUTER JOIN (
    /* 月間平均気温 除外考慮 */
    SELECT 
      DATE_FORMAT(dv.pr_data_ts, '%Y%m') month
      ,AVG(dv.pr_data_value) value
    FROM pr_data_value dv      /* PR基礎データ値 */
    INNER JOIN pr_data_item it /* PR基礎データ項目 */
      ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
      AND it.server_no = :server_no5
      AND it.pr_data_item_id = 'amb_temp' /* 気温 */
    WHERE NOT EXISTS (
      SELECT 1 FROM pr_exclude_condition cd
      WHERE dv.pr_data_ts >= cd.pr_exclude_start
        AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE
    )
    GROUP BY DATE_FORMAT(dv.pr_data_ts, '%Y%m')
  ) amb_temp_exclude
  ON amb_temp_exclude.month = rf.month




LEFT OUTER JOIN (
    /* 月間平均モジュール温度 */
    SELECT 
      DATE_FORMAT(dv.pr_data_ts, '%Y%m') month
      ,AVG(dv.pr_data_value) value
    FROM pr_data_value dv      /* PR基礎データ値 */
    INNER JOIN pr_data_item it /* PR基礎データ項目 */
      ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
      AND it.server_no = :server_no6
      AND it.pr_data_item_id = 'mod_temp' /* モジュール温度 */
    GROUP BY DATE_FORMAT(dv.pr_data_ts, '%Y%m')
  ) mod_temp
  ON mod_temp.month = rf.month


LEFT OUTER JOIN (
    /* 月間平均モジュール温度 除外考慮 */
    SELECT 
      DATE_FORMAT(dv.pr_data_ts, '%Y%m') month
      ,AVG(dv.pr_data_value) value
    FROM pr_data_value dv      /* PR基礎データ値 */
    INNER JOIN pr_data_item it /* PR基礎データ項目 */
      ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
      AND it.server_no = :server_no7
      AND it.pr_data_item_id = 'mod_temp' /* モジュール温度 */
    WHERE NOT EXISTS (
      SELECT 1 FROM pr_exclude_condition cd
      WHERE dv.pr_data_ts >= cd.pr_exclude_start
        AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE
    )
    GROUP BY DATE_FORMAT(dv.pr_data_ts, '%Y%m')
  ) mod_temp_exclude
  ON mod_temp_exclude.month = rf.month




LEFT OUTER JOIN (
    /* アレイ平行面日射量重みづけ平均モジュール温度 */
    SELECT 
      DATE_FORMAT(mod_temp.ts, '%Y%m') month
      ,SUM(irr.value * mod_temp.value) / SUM(mod_temp.value) AS value
    FROM 
      (
        SELECT
          dv.pr_data_ts ts
          ,dv.pr_data_value value
        FROM pr_data_value dv      /* PR基礎データ値 */
        INNER JOIN pr_data_item it /* PR基礎データ項目 */
          ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
          AND it.server_no = :server_no8
          AND it.pr_data_item_id = 'mod_temp' /* モジュール温度 */
      ) mod_temp
      , (
        SELECT
          dv.pr_data_ts ts
          ,dv.pr_data_value value
        FROM pr_data_value dv      /* PR基礎データ値 */
        INNER JOIN pr_data_item it /* PR基礎データ項目 */
          ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
          AND it.server_no = :server_no9
          AND it.pr_data_item_id = 'irradiance' /* アレイ平行面日射量 */
      ) irr
    WHERE irr.ts = mod_temp.ts
    GROUP BY DATE_FORMAT(mod_temp.ts, '%Y%m')
  ) irr_weighted_temp
  ON irr_weighted_temp.month = rf.month


LEFT OUTER JOIN (
    /* アレイ平行面日射量重みづけ平均モジュール温度 除外考慮 */
    SELECT 
      DATE_FORMAT(mod_temp.ts, '%Y%m') month
      ,SUM(irr.value * mod_temp.value) / SUM(mod_temp.value) AS value
    FROM 
      (
        SELECT
          dv.pr_data_ts ts
          ,dv.pr_data_value value
        FROM pr_data_value dv      /* PR基礎データ値 */
        INNER JOIN pr_data_item it /* PR基礎データ項目 */
          ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
          AND it.server_no = :server_no10
          AND it.pr_data_item_id = 'mod_temp' /* モジュール温度 */
        WHERE NOT EXISTS (
          SELECT 1 FROM pr_exclude_condition cd
          WHERE dv.pr_data_ts >= cd.pr_exclude_start
            AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE
        )
      ) mod_temp
      , (
        SELECT
          dv.pr_data_ts ts
          ,dv.pr_data_value value
        FROM pr_data_value dv      /* PR基礎データ値 */
        INNER JOIN pr_data_item it /* PR基礎データ項目 */
          ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
          AND it.server_no = :server_no11
          AND it.pr_data_item_id = 'irradiance' /* アレイ平行面日射量 */
        WHERE NOT EXISTS (
          SELECT 1 FROM pr_exclude_condition cd
          WHERE dv.pr_data_ts >= cd.pr_exclude_start
            AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE
        )
      ) irr
    WHERE irr.ts = mod_temp.ts
    GROUP BY DATE_FORMAT(mod_temp.ts, '%Y%m')
  ) irr_weighted_temp_exclude
  ON irr_weighted_temp_exclude.month = rf.month




LEFT OUTER JOIN (
    /* 売電電力量 */
    SELECT 
      DATE_FORMAT(dv.pr_data_ts, '%Y%m') month
      ,SUM(dv.pr_data_value) value
    FROM pr_data_value dv      /* PR基礎データ値 */
    INNER JOIN pr_data_item it /* PR基礎データ項目 */
      ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
      AND it.server_no = :server_no12
      AND it.pr_data_item_id = 'ac_energy_total' /* AC電力量合計 */
    GROUP BY DATE_FORMAT(dv.pr_data_ts, '%Y%m')
  ) ac_energy
  ON ac_energy.month = rf.month


LEFT OUTER JOIN (
    /* 売電電力量 除外考慮 */
    SELECT 
      DATE_FORMAT(dv.pr_data_ts, '%Y%m') month
      ,SUM(dv.pr_data_value) value
    FROM pr_data_value dv      /* PR基礎データ値 */
    INNER JOIN pr_data_item it /* PR基礎データ項目 */
      ON it.pr_data_item_no = dv.pr_data_item_no /* PR基礎データ項目No */
      AND it.server_no = :server_no13
      AND it.pr_data_item_id = 'ac_energy_total' /* AC電力量合計 */
    WHERE NOT EXISTS (
      SELECT 1 FROM pr_exclude_condition cd
      WHERE dv.pr_data_ts >= cd.pr_exclude_start
        AND dv.pr_data_ts < cd.pr_exclude_end + INTERVAL 1 MINUTE
    )
    GROUP BY DATE_FORMAT(dv.pr_data_ts, '%Y%m')
  ) ac_energy_exclude
  ON ac_energy_exclude.month = rf.month
