☰

GnuPlot - 数据中的多个时间序列，绘制单独的线条

tag-icon • tag-icon gnuplot
GnuPlot - 数据中的多个时间序列，绘制单独的线条

我有一个 SQLite3 数据库，其中包含类似以下的数据

time_index      Circuit         dateTimeRecorded                activepower
404503          GPOs            2018-05-30 00:03:50             224.57
404503          Grid            2018-05-30 00:03:50             4.34
404503          Shed            2018-05-30 00:03:50             24.86
404504          GPOs            2018-05-30 00:08:51             223.89
404504          Grid            2018-05-30 00:08:51             5.69
404504          Shed            2018-05-30 00:08:51             25.25
404505          GPOs            2018-05-30 00:13:52             247.23
404505          Grid            2018-05-30 00:13:52             3.81
404505          Shed            2018-05-30 00:13:52             24.43
404506          GPOs            2018-05-30 00:18:52             223.19
404506          Grid            2018-05-30 00:18:52             4.58
404506          Shed            2018-05-30 00:18:52             25.11
404507          GPOs            2018-05-30 00:23:52             222.57
404507          Grid            2018-05-30 00:23:52             4.01
404507          Shed            2018-05-30 00:23:52             24.86

我设法得到了一张图，但所有 3 条电路都合并成一条线。我想我需要以某种方式过滤并绘制 3 次。

问题是数据是垂直的，而不是水平的。

我考虑在 SQL 中参数化 where 子句，但无法弄清楚如何使用 sqlite3 命令行来做到这一点。

我想我可以有 3 个单独的 SQL 文件，但我发现这有点丑陋。

目前进展如下...

set xdata time
set timefmt "%Y-%m-%d %H:%M:%S"
show xrange
set format x "%H:00"
#set datafile separator "   "
#set key autotitle columnhead
#
# Currently plotting all 3 sensors, working on how to plot for each sensor.
#
plot '< sqlite3 /tmp/usb_database.db ".read PlotElectricityUsage.sql"'  \
    using 3:5 \
    with lines \
    title "Electricity Usage"

pause -1 "Hit return to continue"

其中SQL文件如下

.header on
.separator "    "
SELECT "DATA"."time_index",
       Case   
          When EUI64 = '000D6F0005A5BE9D' then 'Grid'
          When EUI64 = '000D6F0005A5D77E' then 'GPOs'
          When EUI64 = '000D6F0005A5BCAE' then 'Shed'
          Else '????'
       End as "Circuit",
       datetime("DATA"."TimeStamp", 'unixepoch', 'localtime') as "dateTimeRecorded",
       "DATA"."activepower"
FROM "DATA" 
WHERE "DATA"."devicetype" = 15 
   AND "DATA"."voltage" > 0 
   AND "dateTimeRecorded" BETWEEN date('now', 'start of day') 
                                    AND date('now', 'start of day', '+1 day') 
   AND "dateTimeRecorded" > Datetime('2018-03-09 13:00:00')
 ORDER BY "Circuit",
      "DATA"."time_index" ASC
;

任何帮助都将不胜感激。

答案1

使用awk：

plot '< awk ''$2=="GPOs"'' /tmp/usb_database.db' u 1:5 w lp

相关内容