Timestreamに記録したスマートメーター情報を利用して、市場連携価格の電気料金を試算してみた

Amazon Timestream を利用して、スマートメータのログから30分毎の電気使用量を求め、 市場連携価格の電気代の計算を試みました。
2021.06.17

AWSチームのすずきです。

時系列データベースの Amazon Timestream に登録したスマートメータの計測情報と、JPEXのエリアプライスを利用して、市場価格に連携した電気料金の試算を試みる機会がありましたので、紹介させていただきます。

ソース

スマートメーター

Nature Remo E Lite で取得したスマートメーター情報を、AWS Lambda を利用して Amazon Timestreamに記録しました。

データサンプル

SELECT
    time,
    measure_name,
    measure_value::bigint
FROM
    "smartmeter"."xxx"
WHERE
    time >= '2021-06-13 20:00:00.000000000'
AND time < '2021-06-13 23:00:00.000000000'
AND id = 'zzz'
ORDER BY
    time
time measure_name measure_value::bigint
2021-06-13 20:00:14.000000000  normal_direction_cumulative_electric_energy 336804
2021-06-13 20:00:14.000000000 measured_instantaneous 559
2021-06-13 20:01:14.000000000 normal_direction_cumulative_electric_energy 336806
2021-06-13 20:01:14.000000000 measured_instantaneous 555
2021-06-13 20:02:14.000000000 normal_direction_cumulative_electric_energy 336808
2021-06-13 20:02:14.000000000 measured_instantaneous 551
  • measured_instantaneous : 積算電力量計測値(正方向)
  • normal_direction_cumulative_electric_energy : 瞬時電力計測値

JPEXスポット市場価格

JEPXが公開するCSV、AWS Lambda を利用して Amazon Timestreamに記録しました。

データサンプル

SELECT
    *
FROM
    "smartmeter"."spot_trading_results"
WHERE
    time >= '2021-06-13 20:00:00.000000000'
AND time < '2021-06-13 23:00:00.000000000'
AND area = 'tepco'
AND measure_name = 'area_price'
area measure_value::double measure_name time
tepco 5.85 area_price 2021-06-13 20:00:00.000000000
tepco 5.93 area_price 2021-06-13 20:30:00.000000000
tepco 5.81 area_price 2021-06-13 21:00:00.000000000
tepco 8 area_price 2021-06-13 21:30:00.000000000
tepco 7.22 area_price 2021-06-13 22:00:00.000000000
tepco 6.17 area_price 2021-06-13 22:30:00.000000000

Timestream処理

Timestreamの BIN関数を利用、30分単位の集計を試みました。

瞬間電力計測値

瞬間電力計測値の集計(最小値、最大値、平均値、パーセンタイル値、標準偏差)を試みました。

SELECT
    BIN(time, 30m) AS binned_timestamp,
    MIN(
        measure_value::bigint
    ) AS min_energy,
    MAX(
        measure_value::bigint
    ) AS max_energy,
    MAX(
        measure_value::bigint
    ) - MIN(
        measure_value::bigint
    ) AS max_min MAX(
        measure_value::bigint
    ) - MIN(
        measure_value::bigint
    ) AS max_minus_min,
    COUNT(1) AS count_records
FROM
    "smartmeter"."xxx"
WHERE
    measure_name = 'normal_direction_cumulative_electric_energy'
AND time >= '2021-06-13 20:00:00.000000000'
AND time < '2021-06-13 23:00:00.000000000'
AND id = 'zzz'
GROUP BY
    id,
    BIN(time, 30m)
ORDER BY
    binned_timestamp DESC
binned_timestamp min_value max_value avg_value p90_value stddev_value count_records
2021-06-13 20:00:00.000000000 155 725 489.4 558 114.4 29
2021-06-13 20:30:00.000000000 439 513 477.9 502 25.5 30
2021-06-13 21:00:00.000000000 468 499 481.4 498 9.7 28
2021-06-13 21:30:00.000000000 432 501 468.5 491 25.4 29
2021-06-13 22:00:00.000000000 477 486 481.1 485 2.6 27
2021-06-13 22:30:00.000000000 456 502 478.5 498 11.8 28

積算電力量計測値

積算電力量の集計(最小値、最大値)を取得より、対象期間中の消費電力を求めました。

SELECT
    BIN(time, 30m) AS binned_timestamp,
    min(measure_value::bigint) as min_energy,
    max(measure_value::bigint) as max_energy,
    max(measure_value::bigint) - min(measure_value::bigint) as max_min
    max(measure_value::bigint) - min(measure_value::bigint) as max_minus_min,
    count(1) as count_records
FROM
    "smartmeter"."xxx"
WHERE
    measure_name = 'normal_direction_cumulative_electric_energy'
AND time >= '2021-06-13 20:00:00.000000000' 
AND time < '2021-06-13 23:00:00.000000000'
and id = 'zzz'
GROUP BY
    id,BIN(time, 30m)
order by binned_timestamp desc
binned_timestamp min_energy max_energy max_min count_records
2021-06-13 20:00:00.000000000 336804 336852 48 29
2021-06-13 20:30:00.000000000 336854 336900 46 30
2021-06-13 21:00:00.000000000 336900 336948 48 28
2021-06-13 21:30:00.000000000 336950 336994 44 29
2021-06-13 22:00:00.000000000 336996 337042 46 27
2021-06-13 22:30:00.000000000 337044 337090 46 28

同一時間枠の最小値と最大値の差分では無視できない漏れが発生したため、 サブクエリを利用して直前の時間枠で測定した最大値との差を求め、電力消費量(kWh)を求めました。

SELECT
    a.binned_timestamp,
    b.previous,
    a.max_energy,
    a.max_energy - b.previous AS previous_max(a.max_energy - b.previous) * 0.005 AS power_consumption
FROM
    (
        SELECT
            id,
            BIN(time, 30m) AS binned_timestamp,
            MIN(
                measure_value::bigint
            ) AS min_energy,
            MAX(
                measure_value::bigint
            ) AS max_energy,
            COUNT(1) AS count_records
        FROM
            "smartmeter"."xxx"
        WHERE
            measure_name = 'normal_direction_cumulative_electric_energy'
        AND time >= '2021-06-13 20:00:00.000000000'
        AND time < '2021-06-13 23:00:00.000000000'
        AND id = 'zzz'
        GROUP BY
            id,
            BIN(time, 30m)
    ) a,
    (
        SELECT
            id,
            BIN(time, 30m) AS start_timestamp,
            MAX(
                measure_value::bigint
            ) AS previous
        FROM
            "smartmeter"."xxx"
        WHERE
            measure_name = 'normal_direction_cumulative_electric_energy'
        AND time >= '2021-06-13 20:00:00.000000000'
        AND time < '2021-06-13 23:00:00.000000000'
        GROUP BY
            id,
            BIN(time, 30m)
    ) b
WHERE
    a.id = b.id
AND a.binned_timestamp = b.start_timestamp + 30m
ORDER BY
    a.binned_timestamp
binned_timestamp previous_energy max_energy power_consumption 電力使用量(kWh)
2021-06-13 20:00:00.000000000 336802 336852 50 0.25
2021-06-13 20:30:00.000000000 336852 336900 48 0.24
2021-06-13 21:00:00.000000000 336900 336948 48 0.24
2021-06-13 21:30:00.000000000 336948 336994 46 0.23
2021-06-13 22:00:00.000000000 336994 337042 48 0.24
2021-06-13 22:30:00.000000000 337042 337090 48 0.24

サブクエリを利用、JPEXのスポット市場価格(エリアプライス)を結合しました。

SELECT
    a.binned_timestamp,
    (a.max_energy - b.previous) * 0.005 AS power_consumption,
    c.area_price_repco
FROM
    (
        SELECT
            id,
            BIN(time, 30m) AS binned_timestamp,
            MIN(
                measure_value::bigint
            ) AS min_energy,
            MAX(
                measure_value::bigint
            ) AS max_energy,
            COUNT(1) AS count_records
        FROM
            "smartmeter"."xxx"
        WHERE
            measure_name = 'normal_direction_cumulative_electric_energy'
        AND time >= '2021-06-13 20:00:00.000000000'
        AND time < '2021-06-13 23:00:00.000000000'
        AND id = 'zzz'
        GROUP BY
            id,
            BIN(time, 30m)
    ) a,
    (
        SELECT
            id,
            BIN(time, 30m) AS start_timestamp,
            MAX(
                measure_value::bigint
            ) AS previous
        FROM
            "smartmeter"."xxx"
        WHERE
            measure_name = 'normal_direction_cumulative_electric_energy'
        AND time >= '2021-06-13 19:30:00.000000000'
        AND time < '2021-06-13 23:00:00.000000000'
        GROUP BY
            id,
            BIN(time, 30m)
    ) b,
    (
        SELECT
            measure_value::double AS area_price_repco,
            time
        FROM
            "smartmeter"."spot_trading_results"
        WHERE
            time >= '2021-06-13 20:00:00.000000000'
        AND time < '2021-06-13 23:00:00.000000000'
        AND area = 'tepco'
        AND measure_name = 'area_price'
    ) c
WHERE
    a.id = b.id
AND a.binned_timestamp = c.time
AND a.binned_timestamp = b.start_timestamp + 30m
ORDER BY
    a.binned_timestamp
binned_timestamp power_consumption area_price_repco
2021-06-13 20:00:00.000000000 0.25 5.85
2021-06-13 20:30:00.000000000 0.24 5.93
2021-06-13 21:00:00.000000000 0.24 5.81
2021-06-13 21:30:00.000000000 0.23 8
2021-06-13 22:00:00.000000000 0.24 7.22
2021-06-13 22:30:00.000000000 0.24 6.17

従量料金計算

NatureでんきのスマートプランのWebページで案内されている、 電気市場価格に連動した従量料金の計算を試みてみました。

  • 電力購入料

日本卸電力市場(JEPX)にて供給エリアごとにその前日に決定されるエリアプライス。

  • 託送料金

東京エリア:7.45円 (1kWhにつき)

  • 事業運営費

4.5円 (1kWhにつき)

SELECT
    round(SUM(power_consumption), 2) AS sum_power_consumption,
    round(SUM(purchase_fee), 2) AS sum_purchase_fee,
    round(SUM(operating_fee), 2) AS sum_operating_fee,
    round(SUM(consignment_fee), 2) AS sum_consignment_fee,
    round(SUM(purchase_fee) + SUM(operating_fee) + SUM(consignment_fee), 2) as sum_total,
    round(AVG(area_price_repco), 2) AS avg_area_price_repco
FROM
    (
        SELECT
            a.binned_timestamp,
            (a.max_energy - b.previous) * 0.005 AS power_consumption,
            c.area_price_repco,
            (a.max_energy - b.previous) * 0.005 * c.area_price_repco AS purchase_fee,
            (a.max_energy - b.previous) * 0.005 * 4.5 AS operating_fee,
            (a.max_energy - b.previous) * 0.005 * 7.45 AS consignment_fee
        FROM
            (
                SELECT
                    id,
                    BIN(time, 30m) AS binned_timestamp,
                    MIN(
                        measure_value::bigint
                    ) AS min_energy,
                    MAX(
                        measure_value::bigint
                    ) AS max_energy,
                    COUNT(1) AS count_records
                FROM
                    "smartmeter"."xxx"
                WHERE
                    measure_name = 'normal_direction_cumulative_electric_energy'
                AND time >= ago(15d)
                GROUP BY
                    id,
                    BIN(time, 30m)
            ) a,
            (
                SELECT
                    id,
                    BIN(time, 30m) AS start_timestamp,
                    MAX(
                        measure_value::bigint
                    ) AS previous
                FROM
                    "smartmeter"."xxx"
                WHERE
                    measure_name = 'normal_direction_cumulative_electric_energy'
                AND time >= ago(15d)
                GROUP BY
                    id,
                    BIN(time, 30m)
            ) b,
            (
                SELECT
                    measure_value::double AS area_price_repco,
                    time
                FROM
                    "smartmeter"."spot_trading_results"
                WHERE
                    time >= ago(15d)
                AND area = 'tepco'
                AND measure_name = 'area_price'
            ) c
        WHERE
            a.id = b.id
        AND a.binned_timestamp = c.time
        AND a.binned_timestamp = b.start_timestamp + 30m
        ORDER BY
            a.binned_timestamp
    ) d
電気使用量(kWh) 電力調達料 事業運営費 託送料金 合計
100.96 705.6 454.32 752.15 1912.08

まとめ

Amazon Timestream を利用して、30分毎の電気使用量を求め、その時点の市場価格から従量課金の計算が可能な事を確認できました。

実際の電気代の計算を行うためには、基準時間に合わせたスマートメーター計測値の調整や、有効桁数が一巡した場合の処理なども必要になると思われますが、 実測値を利用した速報集計などを行う場合には、Amazon Timestream をお試し頂ければと思います。