NodeRed
node-red에서MySQL데이타읽어Chart표현function탐구
아이티제어1998
2022. 2. 22. 15:41
select 쿼리후 change 노드에서 Set "J" 를 선택하고
(
$series := [
{ "field": "data1", "label": "1A" },
{ "field": "data2", "label": "2B" },
{ "field": "data3", "label": "3C" },
{ "field": "data4", "label": "4D" },
{ "field": "data5", "label": "5E" }
];
$xaxis := "time"; /*시간필드명*/
[
{
"series": $series.label,
"data": $series.[
(
$yaxis := $.field;
$$.payload.{
"x": $lookup($, $xaxis),
"y": $lookup($, $yaxis)
}
)
]
}
]
)위와같이 넣으면 결과값이
"select time, data1, data2,data3, data4, data5 from TableName where time < {{msg.payload}} " 쿼리값의 결과값을 테이블에 표현이 아래와같이 된다.
to the value J:
---------------------------
(
$series := [ /* := 이것의 연산자를 기억하자~~ */
{ "field": "data1", "label": "1A" },
{ "field": "data2", "label": "2B" },
{ "field": "data3", "label": "3C" },
{ "field": "data4", "label": "4D" },
{ "field": "data5", "label": "5E" }
];/* $series 변수를 배열로 선언 : 내부적으로 본딩되도록 */
$xaxis := "time"; /*시간필드명 $xaxis 라는 변수는 time 필드명으로 지정 */
[
{
"series": $series.label, /* series라는 놈은 $series변수값의 label 이라는 항목값이 들어간다. */
"data": $series.[ /* data라는 놈은 $series변수값의 */
(
$yaxis := $.field; /* $series선언된곳의 field 즉 data1,data2 ,...인데 loop를 돌려 끝까징 */
$$.payload.{ /* $$.payload lookup 룩업 x: , y: 를 반복 지정한다. */
"x": $lookup($, $xaxis), "y": $lookup($, $yaxis)
}
)
]
}
]
)
-------------
//결과
[ { series: [ '1A', '2B', '3C', '4D', '5E' ],
data:
[ [ { x: 1645501494, y: 11 }, { x: 1645501576, y: 14 }, { x: 1645501645, y: 24 }, { x: 1645501718, y: 28 } ],
[ { x: 1645501494, y: 12 }, { x: 1645501576, y: 18 }, { x: 1645501645, y: 28 }, { x: 1645501718, y: 30 } ],
[ { x: 1645501494, y: 7 }, { x: 1645501576, y: 3 }, { x: 1645501645, y: 5 }, { x: 1645501718, y: 6 } ],
[ { x: 1645501494, y: 9 }, { x: 1645501576, y: 8 }, { x: 1645501645, y: 9 }, { x: 1645501718, y: 1 } ],
[ { x: 1645501494, y: 7 }, { x: 1645501576, y: 8 }, { x: 1645501645, y: 10 }, { x: 1645501718, y: 5 } ]
]
}
]
[debug:MyS결과]
[
{
time: 1645501494,
data1: 11,
data2: 12,
data3: 7,
data4: 9,
data5: 7
},
{
time: 1645501576,
data1: 14,
data2: 18,
data3: 3,
data4: 8,
data5: 8
},
{
time: 1645501645,
data1: 24,
data2: 28,
data3: 5,
data4: 9,
data5: 10
},
{
time: 1645501718,
data1: 28,
data2: 30,
data3: 6,
data4: 1,
data5: 5
}
]
전체 소스코드 flow.json
[
{
"id": "4fd8864728f45693",
"type": "tab",
"label": "Flow 3 MySQL2Chart",
"disabled": false,
"info": "",
"env": []
},
{
"id": "d205c5ce.1feca8",
"type": "ui_chart",
"z": "4fd8864728f45693",
"name": "24 hours data",
"group": "8880d363.148ac",
"order": 2,
"width": "0",
"height": "0",
"label": "Chart",
"chartType": "line",
"legend": "false",
"xformat": "HH:mm:ss",
"interpolate": "linear",
"nodata": "",
"dot": false,
"ymin": "",
"ymax": "",
"removeOlder": "24",
"removeOlderPoints": "",
"removeOlderUnit": "3600",
"cutout": 0,
"useOneColor": false,
"useUTC": false,
"colors": [
"#00e68c",
"#2d2da8",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"outputs": 1,
"useDifferentColor": false,
"className": "",
"x": 840,
"y": 300,
"wires": [
[]
]
},
{
"id": "2a63c806.ae4db8",
"type": "mysql",
"z": "4fd8864728f45693",
"mydb": "a844720c.608d6",
"name": "MYSQL t",
"x": 420,
"y": 320,
"wires": [
[
"3d0af460.41906c",
"a03b0066.3ff5a"
]
]
},
{
"id": "86ab4360.50c6c",
"type": "function",
"z": "4fd8864728f45693",
"name": "24Hors",
"func": "var timeE = msg.payload;\n\n//Restrict the query to pull the last 24hrs\n//of data instead of the whole db\nnode.log(\"timeE=\"+timeE);\nmsg.payload = (timeE - (1000*60*60*24));\n node.status({text:msg.payload});\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 100,
"y": 280,
"wires": [
[
"308dd6b1.2a193a"
]
]
},
{
"id": "11c8f8a2.d97147",
"type": "template",
"z": "4fd8864728f45693",
"name": "Format query 2",
"field": "topic",
"fieldType": "msg",
"format": "handlebars",
"syntax": "mustache",
"template": "SELECT\n CEILING(time/3600000)*3600000 AS timestamp,\n AVG(data1) AS `data1`,\n AVG(data2) AS `data2`\nFROM dbasename\nWHERE time > {{payload}}\nGROUP BY `timestamp`;",
"output": "str",
"x": 260,
"y": 360,
"wires": [
[]
]
},
{
"id": "3d0af460.41906c",
"type": "debug",
"z": "4fd8864728f45693",
"name": "MyS결과",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 630,
"y": 420,
"wires": []
},
{
"id": "272494b.eb3d36c",
"type": "inject",
"z": "4fd8864728f45693",
"name": "Timestamp",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": "",
"topic": "",
"payload": "",
"payloadType": "date",
"x": 95,
"y": 200,
"wires": [
[
"86ab4360.50c6c"
]
]
},
{
"id": "755e08a5.08de88",
"type": "comment",
"z": "4fd8864728f45693",
"name": "Flow to query database and format for chart",
"info": "",
"x": 190,
"y": 140,
"wires": []
},
{
"id": "433d9ca4.076774",
"type": "mysql",
"z": "4fd8864728f45693",
"mydb": "a844720c.608d6",
"name": "MYSQL t",
"x": 254,
"y": 80,
"wires": [
[]
]
},
{
"id": "d59a31e8.d3772",
"type": "template",
"z": "4fd8864728f45693",
"name": "Format data",
"field": "topic",
"fieldType": "msg",
"format": "handlebars",
"syntax": "mustache",
"template": "INSERT INTO `dbasename` (`data1`,`data2`,`time`) VALUES ({{data1}},{{data2}},{{time}})",
"output": "str",
"x": 101,
"y": 80,
"wires": [
[
"433d9ca4.076774"
]
]
},
{
"id": "2b71188f.3e2428",
"type": "comment",
"z": "4fd8864728f45693",
"name": "Flow to insert data into the database",
"info": "",
"x": 170,
"y": 30,
"wires": []
},
{
"id": "308dd6b1.2a193a",
"type": "template",
"z": "4fd8864728f45693",
"name": "Format query 1",
"field": "topic",
"fieldType": "msg",
"format": "handlebars",
"syntax": "mustache",
"template": "SELECT * FROM dbasename WHERE time > {{payload}} or 1",
"output": "str",
"x": 260,
"y": 280,
"wires": [
[
"2a63c806.ae4db8",
"1bc1be74c9902834"
]
]
},
{
"id": "a03b0066.3ff5a",
"type": "change",
"z": "4fd8864728f45693",
"name": "Format data",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "(\t $series := [\t { \"field\": \"data1\", \"label\": \"1A\" },\t { \"field\": \"data2\", \"label\": \"2B\" },\t { \"field\": \"data3\", \"label\": \"3C\" },\t { \"field\": \"data4\", \"label\": \"4D\" },\t { \"field\": \"data5\", \"label\": \"5E\" }\t ];\t $xaxis := \"time\"; /*시간필드명*/\t [\t {\t \"series\": $series.label,\t \"data\": $series.[\t (\t $yaxis := $.field;\t $$.payload.{\t \"x\": $lookup($, $xaxis),\t \"y\": $lookup($, $yaxis)\t }\t )\t ]\t }\t ]\t)",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 610,
"y": 320,
"wires": [
[
"d205c5ce.1feca8",
"02cdf32afd9ebc3b"
]
]
},
{
"id": "02cdf32afd9ebc3b",
"type": "debug",
"z": "4fd8864728f45693",
"name": "FMT결과",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 830,
"y": 340,
"wires": []
},
{
"id": "1bc1be74c9902834",
"type": "debug",
"z": "4fd8864728f45693",
"name": "",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 460,
"y": 200,
"wires": []
},
{
"id": "8880d363.148ac",
"type": "ui_group",
"name": "Thermostat demo",
"tab": "db58ad1a.e37a8",
"order": 2,
"disp": true,
"width": "6",
"collapse": false,
"className": ""
},
{
"id": "a844720c.608d6",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "test",
"tz": "",
"charset": "utf8",
"credentials": {}
},
{
"id": "db58ad1a.e37a8",
"type": "ui_tab",
"name": "Test stuff",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]