Разумны дом: Які будуецца графікі спажывання вады і электрычнасці ў Home Assistant

Кожны раз атрымліваючы плацёжку за электрычнасць і ваду я здзіўляюся - няўжо мая сям'я стооооолько спажывае? Ну так, у ваннай усталяваны цёплая падлога і бойлер, але ж яны ж не качагараць увесь час. Ваду таксама накшталт эканомім (хоць паплюхацца ў ваннай таксама каханы). Некалькі гадоў таму я ўжо падключыў лічыльнікі вады и электрычнасці да разумнай хаты, але на гэтым справа так і затрымалася. Да аналізу спажывання рукі дайшлі толькі зараз, пра што, уласна, вось гэты артыкул.

Нядаўна я перайшоў на Home Assistant у якасці сістэмы разумнай хаты. Адной з прычын была як раз магчымасць арганізаваць збор вялікай колькасці даных з магчымасцю зручнай пабудовы рознага роду графікаў.

Інфармацыя апісаная ў гэтым артыкуле не новая, усе гэтыя штукі пад рознымі падліўкамі ўжо былі апісаны ў Інтэрнэт. Але кожны артыкул, як правіла, апісвае толькі адзін падыход ці аспект. Параўноўваць усе гэтыя падыходы і выбіраць найбольш прыдатны прыйшлося самому. Артыкул усё роўна не дае вычарпальнай інфармацыі па зборы дадзеных, але з'яўляецца свайго роду канспектам таго як зрабіў я. Так што канструктыўная крытыка і прапановы па паляпшэнні вітаюцца.

Пастаноўка задачы

Такім чынам, мэта сённяшняга практыкавання ў тым, каб атрымаць прыгожыя графікі спажывання вады і электрычнасці:

• Пагадзінны за 2 дні
• Пасутачны за 2 тыдні
• (апцыянальна) панядзелак і памесячны

У гэтым нас пільнуюць некаторыя складанасці:

• Стандартныя кампаненты графікаў, як правіла, вельмі ўбогія. У лепшым выпадку можна пабудаваць лінейны графік па кропках.

  Калі добра пашукаць, то можна знайсці іншыя кампаненты, якія пашыраюць магчымасці стандартнага графіка. Для home assistant, у прынцыпе, нядрэнны і прыгожы кампанент mini-graph-card, але і ён некалькі абмежаваны:

  • Складана задаваць параметры слупкавага графіка на вялікіх прамежках (шырыня слупка задаецца ў долях гадзіны, а значыць прамежкі даўжэй за гадзіну будуць задавацца дробавымі лікамі).
  • Нельга на адзін графік дадаваць розныя сутнасці (напрыклад тэмпературу і вільготнасць, або слупковы графік сумясціць з лініяй)
• Мала таго, што home assistant па змаўчанні выкарыстоўвае самую прымітыўную базу дадзеных SQLite (а я, рукажоп, не здужаў усталёўку MySQL або Postgres), так і дадзеныя захоўваюцца не самай аптымальнай выявай. Так, напрыклад, пры кожнай змене кожнага нават самага дробнага лічбавага параметру параметра ў базу запісваецца велізарны json памерам каля кілабайта.

  {"entity_id": "sensor.water_cold_hourly", "old_state": {"entity_id": "sensor.water_cold_hourly", "state": "3", "attributes": {"source": "sensor.water_meter_cold", "status": "collecting", "last_period": "29", "last_reset": "2020-02-23T21:00:00.022246+02:00", "meter_period": "hourly", "unit_of_measurement": "l", "friendly_name": "water_cold_hourly", "icon": "mdi:counter"}, "last_changed": "2020-02-23T19:05:06.897604+00:00", "last_updated": "2020-02-23T19:05:06.897604+00:00", "context": {"id": "aafc8ca305ba4e49ad4c97f0eddd8893", "parent_id": null, "user_id": null}}, "new_state": {"entity_id": "sensor.water_cold_hourly", "state": "4", "attributes": {"source": "sensor.water_meter_cold", "status": "collecting", "last_period": "29", "last_reset": "2020-02-23T21:00:00.022246+02:00", "meter_period": "hourly", "unit_of_measurement": "l", "friendly_name": "water_cold_hourly", "icon": "mdi:counter"}, "last_changed": "2020-02-23T19:11:11.251545+00:00", "last_updated": "2020-02-23T19:11:11.251545+00:00", "context": {"id": "0de64b8af6f14bb9a419dcf3b200ef56", "parent_id": null, "user_id": null}}}

  Датчыкаў у мяне даволі шмат (тэмпературныя датчыкі ў кожным пакоі, лічыльнікі вады і электрычнасці), а некаторыя да таго ж генеруюць дастаткова шмат дадзеных. Так напрыклад толькі лічыльнік электрычнасці SDM220 генеруе каля дзясятка значэнняў кожныя 10-15 секунд, а такіх лічыльнікаў я бы жадаў усталяваць штук 8. А яшчэ ёсць цэлы пачак параметраў, якія вылічаюцца на аснове іншых датчыкаў. В.а. усе гэтыя значэнні лёгка могуць раздзімаць базу на 100-200 Мб штодня. Праз тыдзень сістэма будзе ледзь варочацца, а праз месяц здохне флешка (у выпадку тыповай усталёўкі home assistant на raspberry PI), а ўжо пра захоўванне дадзеных цэлы год і гаворкі быць не можа.

• Калі вам павезла, ваш лічыльнік сам умее лічыць спажыванне. Вы ў любы момант можаце звярнуцца да лічыльніка і спытаць якую гадзіну назапашанае значэнне спажывання. Як правіла ўсе лічыльнікі электрычнасці ў якіх ёсць лічбавы інтэрфейс (RS232/RS485/Modbus/Zigbee) падаюць такую ​​магчымасць.

  Горш калі прылада можа проста вымяраць нейкі імгненны параметр (напрыклад імгненную магутнасць ці ток), ці проста генераваць імпульсы кожныя X ват-гадзін ці літраў. Тады трэба думаць як і чым гэта інтэграваць і дзе назапашваць значэнне. Ёсць рызыка прапусціць чарговую справаздачу па якой-небудзь прычыне, ды і дакладнасць сістэмы ў цэлым выклікае пытанні. Можна, вядома, гэта ўсё даручыць сістэме разумнай хаты накшталт home assistant, але пункт пра колькасць запісаў у базе ніхто не адмяняў, ды і апытанне датчыкаў гушчару чым раз у секунду задаволіць не атрымаецца (абмежаванне архітэктуры home assistant).

Падыход 1

Спачатку паглядзім што падаецца home assistant са скрынкі. Вымярэнне спажывання за перыяд - вельмі запатрабаваная функцыянальнасць. Зразумела, яе даўным даўно рэалізавалі ў выглядзе спецыялізаванага кампанента – utility_meter.

Сутнасць кампанента ў тым, што ён усярэдзіне заводзіць зменную бягучае_назапашанае_значэнне, і скідае яе па заканчэнні зададзенага перыяду (гадзіна/тыдзень/месяц). Кампанент сам маніторыць уваходную зменную (значэнне якога-небудзь сэнсара), сам падпісваецца на змены значэння – вы проста атрымліваеце гатовы вынік. Апісваецца гэтая штука ўсяго некалькімі радкамі ў канфігурацыйным файле.

utility_meter:
  water_cold_hour_um:
    source: sensor.water_meter_cold
    cycle: hourly
  water_cold_day_um:
    source: sensor.water_meter_cold
    cycle: daily

Тут sensor.water_meter_cold гэтае бягучае значэнне лічыльніка ў літрах, якія я атрымліваю непасрэдна ад жалязякі па mqtt. Канструкцыя стварае 2 новых сэнсара water_cold_hour_um і water_cold_day_um, якія назапашваюць вартавыя і дзённыя паказанні, абнуляючы іх па заканчэнні перыяду. Вось графік гадзіннікавага акумулятара за паўдня.

Код гадзіннага і дзённага графікаў для lovelace-UI выглядае так:

      - type: history-graph
        title: 'Hourly water consumption using vars'
        hours_to_show: 48
        entities:
          - sensor.water_hour

      - type: history-graph
        title: 'Daily water consumption using vars'
        hours_to_show: 360
        entities:
          - sensor.water_day

Уласна, у гэтым алгарытме і крыецца праблема такога падыходу. Як я ўжо згадваў, на кожнае ўваходнае значэнне (бягучае сведчанне лічыльніка на кожны наступны літр) генеруецца па 1кб запісы ў базе. Кожны utility meter таксама генеруе па новым значэнні, якое таксама складаецца ў базу. Калі я жадаю збіраць вартавыя/дзённыя/тыднёвыя/месячныя паказанні, ды па некалькіх стаякам вады, ды яшчэ пачак электралічыльнікаў дадаць - гэта будзе вельмі шмат дадзеных. Ну дакладней дадзеных тое не шмат, але паколькі home assistant у базу піша кучу лішняй інфармацыі памер базы будзе расці як на дражджах. Баюся нават меркаваць памер базы для патыднёвых і памесячных графікаў.

Апроч гэтага utility meter сам па сабе не вырашае пастаўленую задачу. Графік значэнняў, якія выдае utility meter гэта манатонна нарастальная функцыя, якая скідаецца ў 0 кожную гадзіну. Нам жа патрэбен зразумелы для карыстальніка графік спажывання, колькі ж літраў было з'едзена за перыяд. Стандартны кампанент history-graph такога не ўмее, але нам можа дапамагчы вонкавы кампанент mini-graph-card.

Гэта код карткі для lovelace-UI:

      - aggregate_func: max
        entities:
          - color: var(--primary-color)
            entity: sensor.water_cold_hour_um
        group_by: hour
        hours_to_show: 48
        name: "Hourly water consumption aggregated by utility meter"
        points_per_hour: 1
        show:
          graph: bar
        type: 'custom:mini-graph-card'

Апроч стандартных налад накшталт імя сэнсара, тыпу графіка, колеры (стандартны аранжавы мне не спадабаўся) тут важна адзначыць 3 налады:

• group_by:hour — графік будзе генеравацца з выраўноўваннем слупкоў па пачатку гадзіны
• points_per_hour: 1 - адзін слупок на кожную гадзіну
• І самае важнае, aggregate_func: max - браць максімальнае значэнне ў межах кожнай гадзіны. Менавіта гэты параметр і ператварае пілападобны графік у слупкі.

На шэраг слупкоў злева не звяртайце ўвагі - гэтыя стандартныя паводзіны кампанента калі няма дадзеных. А дадзеных і не было - я толькі пару гадзін таму ўключыў збор дадзеных па utility meter толькі дзеля гэтага артыкула (свой бягучы падыход я раскажу крыху ніжэй).

На гэтым малюнку я хацеў паказаць, што часам адлюстраванне дадзеных нават працуе, і слупкі сапраўды адлюстроўваюць правільныя значэння. Толькі вось далёка не ўсё. Выдзелены слупок за прамежак з 11 да 12 раніцы чамусьці адлюстроўвае 19 літраў, хоць на зубастым графіку крыху вышэй за гэты ж самы перыяд з таго ж самага сэнсара бачым спажыванне 62 літра. Або баг, або рукі крывыя. А вось чаму адламаліся дадзеныя справа я пакуль не зразумеў - спажыванне там было ў норме, што таксама відаць па зубастым графіку.

Увогуле, праўдападобнасці гэтага гэтага падыходу мне дабіцца не ўдалося — графік амаль заўсёды паказвае нейкую ерась.

Аналагічны код для дзённага сэнсара.

      - aggregate_func: max
        entities:
          - color: var(--primary-color)
            entity: sensor.water_cold_day_um
        group_by: interval
        hours_to_show: 360
        name: "Daily water consumption aggregated by utility meter"
        points_per_hour: 0.0416666666
        show:
          graph: bar
        type: 'custom:mini-graph-card'

Звярніце ўвагу, што параметр group_by усталяваны ў значэнне interval, і руліць усім параметр points_per_hour. І ў гэтым заключаецца іншая праблема гэтага кампанента - points_per_hour добра працуе на графіках за гадзіну ці менш, але агідна на вялікіх прамежках. Так каб атрымаць адзін слупок за адзін дзень прыйшлося ўпісаць значэнне 1/24=0.04166666. Я ўжо не гавару пра тыднёвыя і месячныя графікі.

Падыход 2

Яшчэ толькі разбіраючыся з home assistant я натыкнуўся на вось гэтае відэа:

Таварыш збірае дадзеныя спажыванні з некалькіх відаў разетак Xiaomi. Задача ў яго крыху прасцей - проста выводзіць значэнне спажывання за сёння, учора і за месяц. Ніякіх графікаў не трэба.

Пакінем у баку развагі аб ручным інтэграванні імгненных значэнняў магутнасці – аб "дакладнасці" такога падыходу я ўжо пісаў вышэй. Не зразумела чаму ён не стаў выкарыстоўваць назапашаныя значэння спажывання, якія ўжо збіраюцца той жа разеткай. На мой погляд інтэграванне ўнутры жалязякі будзе працаваць лепш.

Ад відэа мы возьмем ідэю ручнога падліку спажывання за перыяд. У мужыка лічацца толькі значэнні за сёння і за ўчора, але мы пойдзем далей і паспрабуем намаляваць графік. Сутнасць прапанаванага метаду ў маім выпадку заключаецца ў наступным.

Завядзем зменную значэнне_у_пачатку_гадзіны, у якую запішам бягучыя паказанні лічыльніка
Па таймеры ў канцы гадзіны (ці ў пачатку наступнай) палічым розніцу паміж бягучым сведчаннем і запомненым у пачатку гадзіны. Гэтая розніца і будзе спажываннем за бягучую гадзіну - захаваем значэнне ў сэнсар, і ў будучыні будзем па гэтым значэнні будаваць графік.
Таксама трэба "абнуліць" зменную значэнне_у_пачатку_гадзіны запісаўшы туды бягучае значэнне лічыльніка.

Усё гэта можна зрабіць праз ж… сродкамі самога home assistant.

Кода давядзецца напісаць некалькі больш чым у папярэднім падыходзе. Для пачатку завядзем гэтыя самыя "пераменныя". З скрынкі ў нас няма сутнасці "пераменная", але можна скарыстацца паслугамі mqtt брокера. Будзем адпраўляць туды значэнні са сцягам retain=true - гэта захавае значэнне ўсярэдзіне брокера, і яго ў любы момант можна адтуль выдраць, нават пры перазагрузцы home assistant. Я зрабіў адразу вартавыя і дзённыя лічыльнікі.

- platform: mqtt
  state_topic: "test/water/hour"
  name: water_hour
  unit_of_measurement: l

- platform: mqtt
  state_topic: "test/water/hour_begin"
  name: water_hour_begin
  unit_of_measurement: l

- platform: mqtt
  state_topic: "test/water/day"
  name: water_day
  unit_of_measurement: l

- platform: mqtt
  state_topic: "test/water/day_begin"
  name: water_day_begin
  unit_of_measurement: l

Уся магія адбываецца ў аўтаматызацыі, якая запускаецца кожную гадзіну і кожную ноч адпаведна.

- id: water_new_hour
  alias: water_new_hour
  initial_state: true
  trigger:
    - platform: time_pattern
      minutes: 0
  action:
    - service: mqtt.publish
      data:
        topic: "test/water/hour"
        payload_template: >
          {{ (states.sensor.water_meter_cold.state|int) - (states.sensor.water_hour_begin.state|int) }}
        retain: true
    - service: mqtt.publish
      data:
        topic: "test/water/hour_begin"
        payload_template: >
          {{ states.sensor.water_meter_cold.state }}
        retain: true

- id: water_new_day
  alias: water_new_day
  initial_state: true
  trigger:
    - platform: time
      at: "00:00:00"
  action:
    - service: mqtt.publish
      data:
        topic: "test/water/day"
        payload_template: >
          {{ (states.sensor.water_meter_cold.state|int) - (states.sensor.water_day_begin.state|int) }}
        retain: true
    - service: mqtt.publish
      data:
        topic: "test/water/day_begin"
        payload_template: >
          {{ states.sensor.water_meter_cold.state }}
        retain: true

Абедзве аўтаматызацыі выконваюць 2 дзеянні:

• Вылічаюць значэнне за інтэрвал як розніцу паміж пачатковым і канчатковым значэннем
• Абнаўляюць базавае значэнне для наступнага інтэрвалу

Пабудова графікаў у дадзеным выпадку вырашаецца звычайным history-graph:

      - type: history-graph
        title: 'Hourly water consumption using vars'
        hours_to_show: 48
        entities:
          - sensor.water_hour

      - type: history-graph
        title: 'Daily water consumption using vars'
        hours_to_show: 360
        entities:
          - sensor.water_day

Выглядае гэта так:

У прынцыпе, гэта ўжо тое, што трэба. Плюсам дадзенага метаду з'яўляецца тое, што дадзеныя генеруюцца адзін раз за інтэрвал. Г.зн. усяго 24 запісы за суткі для гадзіннікавага графіка.

Нажаль, агульную праблему расце базы гэта ўсё роўна не вырашае. Калі я захачу графік памесячнага спажывання, то давядзецца захоўваць дадзеныя як мінімум за год. А паколькі home assistant дае толькі адну настройку працягласці захоўвання на ўсю базу, то гэта азначае, што УСЕ дадзеныя ў сістэме давядзецца захоўваць цэлы год. Напрыклад за год я спажываю 200 кубоў вады, а значыць гэта 200000 запісаў у базу. А калі ўлічыць яшчэ і іншыя сэнсары, то лічба ўвогуле непрыстойнай становіцца.

Падыход 3

Да шчасця, разумныя людзі ўжо вырашылі гэтую праблему напісаўшы базу дадзеных InfluxDB. Гэтая база адмысловай выявай аптымізаваная пад захоўванне time-based дадзеных і ідэальна падыходзіць для захоўвання значэнняў розных сэнсараў. Сістэма таксама падае SQL-падобную мову запытаў, які дазваляе выкалупваць з базы значэння, і потым іх агрэгаваць рознымі спосабамі. Нарэшце розныя дадзеныя можна захоўваць розны час. Напрыклад, часта якія змяняюцца паказанні накшталт тэмпературы ці вільготнасці можна захоўваць усяго пару тыдняў, тады як дзённыя паказанні спажывання вады можна захоўваць цэлы год.

Акрамя InfluxDB разумныя людзі таксама вынайшлі Grafana – сістэму малявання графікаў па дадзеных з InfluxDB. Графана ўмее маляваць розныя віды графікаў, дэталёва іх кастамізаваць, і, што самае галоўнае, гэтыя графікі можна "ўваткнуць" на lovelace-UI home assistant'а.

Натхняцца тут и тут. У артыкулах падрабязна апісаны працэс усталёўкі і падлучэнні InfluxDB і Grafana да home assistant. Я ж засяроджуся на рашэнні сваёй пэўнай задачы.

Такім чынам, перш за ўсё пачнем складаць значэнне лічыльніка ў influxDB. Кавалак канфігурацыі home assistant (у гэтым прыкладзе я буду забаўляцца не толькі лядоўні, але і гарачай вадой):

influxdb:
  host: localhost
  max_retries: 3
  default_measurement: state
  database: homeassistant
  include:
    entities:
      - sensor.water_meter_hot
      - sensor.water_meter_cold

Адключым захаванне гэтых жа самых дадзеных унутраную базу home assistant, каб не раздзімаць яе лішні раз:

recorder:
  purge_keep_days: 10
  purge_interval: 1
  exclude:
    entities:
      - sensor.water_meter_hot
      - sensor.water_meter_cold

Пяройдзем зараз у кансоль InfluxDB і наладзім нашу базу. У прыватнасці, трэба наладзіць колькі часу будуць захоўвацца тыя ці іншыя дадзеныя. Гэта рэгулюецца т.зв. retention policy - гэта падобна на базы дадзеных усярэдзіне асноўнай базы дадзеных, прычым кожная ўнутраная база мае свае налады. Па змаўчанні ўсе дадзеныя складаюцца ў retention policy пад назовам autogen, гэтыя дадзеныя будуць захоўваецца тыдзень. Я б хацеў, каб вартавыя дадзеныя захоўваліся месяц, тыднёвыя - год, а месячныя наогул ніколі не выдаляліся. Створым адпаведныя retention policy

CREATE RETENTION POLICY "month" ON "homeassistant" DURATION 30d REPLICATION 1
CREATE RETENTION POLICY "year" ON "homeassistant" DURATION 52w REPLICATION 1
CREATE RETENTION POLICY "infinite" ON "homeassistant" DURATION INF REPLICATION 1

Цяпер, уласна, галоўны трук - агрэгацыя дадзеных з дапамогай continuous query. Гэта механізм, які аўтаматычна запускае запыт праз зададзеныя прамежкі часу, агрэгуе дадзеныя па гэтым запыце, а вынік складае ў новае значэнне. Разбяром на прыкладзе (я пішу ў слупок для удобочитаемости, але на справе мне прыйшлося ўводзіць гэтую каманду адным радком)

CREATE CONTINUOUS QUERY cq_water_hourly ON homeassistant 
BEGIN 
  SELECT max(value) AS value 
  INTO homeassistant.month.water_meter_hour 
  FROM homeassistant.autogen.l 
  GROUP BY time(1h), entity_id fill(previous) 
END

Гэтая каманда:

• Стварае continuous query па імі cq_water_cold_hourly у базе homeassistant
• Запыт будзе выконвацца кожную гадзіну (time(1h))
• Запыт будзе выграбаць усе дадзеныя з measurement'а homeassistant.autogen.l (літры), у тым ліку паказанні халоднай і гарачай вады
• Агрэгаваныя дадзеныя будуць групавацца па entity_id, што створыць нам асобныя значэнні па халоднай і гарачай вадзе
• Паколькі лічыльнік літраў гэта манатонна нарастальная паслядоўнасць у рамках кожнай гадзіны трэба будзе браць максімальнае значэнне, таму агрэгацыя будзе праводзіцца функцыяй max(value)
• Новае значэнне будзе запісана ў homeassistant.month.water_meter_hour, дзе month гэтае імя retention policy са тэрмінам захоўвання ў месяц. Прычым дадзеныя па лядоўні і гарачай вадзе будуць раскіданыя ў асобныя запісы з адпаведным entity_id і значэннем у поле value

Уначы ці калі нікога няма дома спажывання вады няма, а адпаведна новых запісаў у homeassistant.autogen.l таксама няма. Каб не было пропускаў значэнняў у звычайных запытах можна выкарыстоўваць fill(previous). Гэта прымусіць InfluxDB выкарыстоўваць значэнне мінулай гадзіны.

Нажаль, у continuous query ёсць асаблівасць: трук fill(previous) не працуе і запісы проста не ствараюцца. Прычым гэта нейкая непераадольная праблема, якая абмяркоўваецца ўжо не першы год. З гэтай праблемай мы разбярэмся пазней, а fill(previous) у continuous query хай будзе – яно не мяшае.

Праверым што атрымалася (зразумела, трэба пачакаць пару гадзінак):

> select * from homeassistant.month.water_meter_hour group by entity_id
...
name: water_meter_hour
tags: entity_id=water_meter_cold
time                 value
----                 -----
...
2020-03-08T01:00:00Z 370511
2020-03-08T02:00:00Z 370513
2020-03-08T05:00:00Z 370527
2020-03-08T06:00:00Z 370605
2020-03-08T07:00:00Z 370635
2020-03-08T08:00:00Z 370699
2020-03-08T09:00:00Z 370761
2020-03-08T10:00:00Z 370767
2020-03-08T11:00:00Z 370810
2020-03-08T12:00:00Z 370818
2020-03-08T13:00:00Z 370827
2020-03-08T14:00:00Z 370849
2020-03-08T15:00:00Z 370921

Звярніце ўвагу, што значэнні ў базе захоўваюцца ў UTC, таму ў гэтым спісе адрозніваюцца на 3 гадзіны - значэнні за 7 раніцы ў выснове InfluxDB адпавядаюць значэнням за 10 раніцы на графіках вышэй. Таксама звярніце ўвагу, што паміж 2 і 5 раніцы запісаў проста няма - гэта тая самая асаблівасць continuous query.

Як бачыце, агрэгаванае значэнне таксама з'яўляецца манатонна нарастальнай паслядоўнасцю, толькі запісы ідуць радзей - раз у гадзіну. Але гэта не праблема - мы можам напісаць яшчэ адзін запыт, які будзе здабываць правільныя дадзеныя для графіка.

SELECT difference(max(value)) 
FROM homeassistant.month.water_meter_hour 
WHERE entity_id='water_meter_cold' and time >= now() -24h 
GROUP BY time(1h), entity_id 
fill(previous)

Расшыфрую:

• З базы homeassistant.month.water_meter_hour выцягнем дадзеныя для entity_id='water_meter_cold' за апошнія суткі (time >= now() -24h).
• Як я ўжо згадваў у паслядоўнасці homeassistant.month.water_meter_hour могуць адсутнічаць некаторыя запісы. Гэтыя дадзеныя мы згенеруем нанова, запусціўшы запыт з GROUP BY time(1h). На гэты раз fill(previous) спрацуе як трэба, згенераваўшы адсутнічаюць дадзеныя (функцыя возьме папярэдняе значэнне)
• Самае галоўнае ў гэтым запыце гэта функцыя difference, якая і палічыць розніцу паміж вартавымі адзнакамі. Сама па сабе яна не працуе і патрабуе якая агрэгуе функцыю. Няхай гэта будзе max() выкарыстаны раней.

Вынік выканання выглядае так

name: water_meter_hour
tags: entity_id=water_meter_cold
time                 difference
----                 ----------
...
2020-03-08T02:00:00Z 2
2020-03-08T03:00:00Z 0
2020-03-08T04:00:00Z 0
2020-03-08T05:00:00Z 14
2020-03-08T06:00:00Z 78
2020-03-08T07:00:00Z 30
2020-03-08T08:00:00Z 64
2020-03-08T09:00:00Z 62
2020-03-08T10:00:00Z 6
2020-03-08T11:00:00Z 43
2020-03-08T12:00:00Z 8
2020-03-08T13:00:00Z 9
2020-03-08T14:00:00Z 22
2020-03-08T15:00:00Z 72

З 2 да 5 раніцы (UTC) спажывання не было. Тым не менш запыт верне адно і тое ж значэнне спажывання дзякуючы fill(previous), а функцыя difference гэта значэнне адніме само з сябе і на вынахадзе атрымаем 0, што ўласна і патрабуецца.

Засталася справа за малым - пабудаваць графік. Для гэтага адкрыем Grafana, адкрыем які небудзь існуючы (ці створым новы) дашборд, створым новую панэль. Настройкі графікаў будуць такімі.

Я буду адлюстроўваць дадзеныя па халоднай і гарачай вадзе на адным графіцы. Запыт сапраўды такі ж як я апісаў вышэй.

Параметры адлюстравання задаюцца так. У мяне гэта будзе графік лініямі (lines), які ідзе прыступкамі (stairs). Параметр Stack я растлумачу крыху ніжэй. Там ніжэй яшчэ пара параметраў адлюстравання, але яны не так цікавыя.

Каб дадаць атрыманы графік у home assistant трэба:

• выйсці з рэжыму рэдагавання графіка. Чамусьці правільныя налады шарынгу графікаў прапануюцца толькі са старонкі дашборда
• Націснуць на трыкутнік каля імя графіка, у меню абраць share
• У якое адкрылася акне перайсці на ўкладку embed
• Прыбраць галачку current time range - часавы дыяпазон мы будзем задаваць праз URL
• Выбраць неабходную тэму. У маім выпадку гэта light
• Скапіяваць атрыманы URL у картку налад lovelace-UI

      - type: iframe
        id: graf_water_hourly
        url: "http://192.168.10.200:3000/d-solo/rZARemQWk/water?orgId=1&panelId=2&from=now-2d&to=now&theme=light"

Звернеце ўвагу што часавы дыяпазон (апошнія 2 дні) задаецца менавіта тут, а не ў наладах дашборда.

Выглядае графік вось так. Гарачую ваду я не за апошнія 2 дні не выкарыстоўваў, таму малюецца толькі графік халоднай вады.

Я так для сябе і не вырашыў які графік мне больш падабаецца, лініяй-прыступкай, ці рэальнымі слупкамі. Таму проста прывяду прыклад дзённага графіка спажывання, толькі на гэты раз слупкамі. Запыты будуюцца аналагічна вышэйапісаным. Параметры адлюстравання такія:

Выглядае гэты графік так:

Дык вось пра параметр Stack. У гэтым графіку слупок лядоўні воды малюецца па-над слупком гарачай. Агульная вышыня адпавядае сумарнаму спажыванню па халоднай і гарачай вадзе за перыяд.

Усе паказаныя графікі - дынамічныя. Можна навесці мышкай на цікавую кропку і паглядзець дэталі і значэнне ў канкрэтнай кропцы.

Нажаль без пары лыжак дзёгцю не абыйшлося. На слупковым графіку (у адрозненні ад графіка лініямі-прыступкамі) сярэдзіна слупка знаходзіцца не ў сярэдзіне сутак, а ў 00:00. Г.зн. левая палова слупка намаляваная на месцы папярэдняга дня. Дык вось графікі за суботу і нядзелю намаляваны крыху лявей, чым сіняватая зона. Пакуль я не прыдумаў як гэта перамагчы.

Іншая праблема заключаецца ў немагчымасці правільна працаваць з інтэрваламі ў месяц. Справа ў тым, што даўжыня гадзіны/дня/тыдня фіксаваная, а вось даўжыня месяца кожны раз розная. InfluxDB умее працаваць толькі з аднолькавымі інтэрваламі. Пакуль маіх мазгоў хапіла каб задаць фіксаваны інтэрвал у 30 дзён. Так, графік на працягу года крыху паплыве і слупкі не зусім дакладна будуць адпавядаць месяцам. Але паколькі мне гэта штука цікавая проста ў якасці паказометра, то я з гэтым ок.

Рашэнняў бачу як мінімум два:

• Забіць на памесячныя графікі і абмежавацца тыднёвымі. 52 тыднёвыя слупкі за год цалкам нядрэнна выглядаюць
• Само памесячнае спажыванне лічыць спосабам №2, а графану толькі для прыгожых графікаў выкарыстоўваць. Суцэль сабе дакладнае рашэнне атрымаецца. Можна нават накласці графікі за мінулы год для параўнання - графана і такое ўмее.

Заключэнне

Не ведаю чаму, але я цягнуся ад такога кшталту графікаў. Яны паказваюць што жыццё кіпіць і ўсё мяняецца. Учора было шмат, сёння мала, заўтра будзе як-небудзь яшчэ. Засталося папрацаваць з дамачадцамі на тэму спажывання. Але нават пры бягучых апетытах проста вялікая і незразумелая лічба ў плацёжцы ўжо ператвараецца ў дастаткова зразумелую карціну спажывання.

Нягледзячы на ​​амаль 20-гадовую кар'еру праграміста, з базамі дадзеных я практычна не перасякаўся. Таму ўстаноўка знешняй базы даных здавалася нечым такім разумным і незразумелым. Усё змяніла вышэйзгаданы артыкул - аказалася што прыкручванне падыходнага інструмента робіцца ў пару клікаў, а са спецыялізаваным інструментам задача пабудовы графікаў становіцца крыху прасцей.

У загалоўку я згадаў спажыванне электрычнасці. Нажаль у дадзены момант я не магу прывесці ніводнага графіка. Адзін лічыльнік SDM120 у мяне здох, а іншы глючыць пры звароце па Modbus. Зрэшты, на тэму дадзенага артыкула гэта ніяк не ўплывае - графікі будуць будуецца тым жа самым спосабам, як і для вады.

У гэтым артыкуле я прывёў тыя падыходы, якія выпрабаваў сам. Напэўна, ёсць яшчэ нейкія спосабы арганізацыі збору і візуалізацыі дадзеных, пра якія я не ведаю. Раскажыце мне аб гэтым у каментарах, мне будзе вельмі цікава. Буду рады канструктыўнай крытыцы і новым ідэям. Спадзяюся, выкладзены матэрыял таксама камусьці дапаможа.

Крыніца: habr.com