Timestreamに記録したスマートメーター情報を利用して、市場連携価格の電気料金を試算してみた
Amazon Timestream を利用して、スマートメータのログから30分毎の電気使用量を求め、 市場連携価格の電気代の計算を試みました。
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 をお試し頂ければと思います。
