Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

--https://stackoverflow.com/q/78204363/5298879
--SQL Query to average multiple data and compare with one data point to check offset

--I am new to SQL so please bear with me. 
--In one of my tables I have a series of inverter voltage values, 
--written every minute. I am displaying the data on GRAFANA 
--and would like to find anomalous points, if any. 
--A really simple solution for me now is to get the average of all voltages 
--at a specific time, and see whether 1 value is off by "a lot".

--Some background: Using postresql, and the columns "timestamp", "value", "name" 
--name is the identifier and right now the inverters are in the format 
--'INVX_INVERTER_SG_Branch VoltageY' 
--where Y = {1,3,5,7,9,11,13,15,17,19,21,23} and X = {01-18}.
select setseed(.42);

create table st_values("timestamp", "value", "name") as 
select 'today'::timestamp+n*'1 minute'::interval,
       (2*random())^3,
       format('INV%s_INVERTER_SG_Branch Voltage%s',lpad(x::text,2,'0'),y)
from generate_series(1,18,1)x,
     generate_series(1,23,2)y,
     generate_series(1,700)n;

select * from st_values tablesample bernoulli(.42)repeatable(.42)
order by "timestamp" limit 16;
create index tnv_idx on st_values ("timestamp","name")
  include("value")
  with(fillfactor=100);


setseed






SELECT 1



SELECT 151200




timestamp
value
name


2024-03-23 00:02:00
6.707207629391543
INV10_INVERTER_SG_Branch Voltage15

2024-03-23 00:04:00
0.2165854378283646
INV16_INVERTER_SG_Branch Voltage5

2024-03-23 00:06:00
0.0012182924942929373
INV11_INVERTER_SG_Branch Voltage9

2024-03-23 00:07:00
0.6768534194102499
INV09_INVERTER_SG_Branch Voltage19

2024-03-23 00:09:00
0.2907072230437511
INV06_INVERTER_SG_Branch Voltage3

2024-03-23 00:09:00
1.3618524145407915
INV03_INVERTER_SG_Branch Voltage23

2024-03-23 00:13:00
0.03309596412063995
INV09_INVERTER_SG_Branch Voltage23

2024-03-23 00:14:00
0.24989200485469099
INV12_INVERTER_SG_Branch Voltage5

2024-03-23 00:14:00
0.5135056910779185
INV01_INVERTER_SG_Branch Voltage11

2024-03-23 00:16:00
0.13517425939443212
INV08_INVERTER_SG_Branch Voltage9

2024-03-23 00:20:00
2.657070883085778
INV18_INVERTER_SG_Branch Voltage13

2024-03-23 00:22:00
3.592830362401637
INV15_INVERTER_SG_Branch Voltage3

2024-03-23 00:26:00
3.382074211594285
INV15_INVERTER_SG_Branch Voltage13

2024-03-23 00:28:00
0.10588884732543108
INV16_INVERTER_SG_Branch Voltage3

2024-03-23 00:28:00
0.96926796908084
INV09_INVERTER_SG_Branch Voltage5

2024-03-23 00:28:00
0.09788700606805915
INV02_INVERTER_SG_Branch Voltage1



SELECT 16



CREATE INDEX


cluster st_values using tnv_idx;

CLUSTER


vacuum analyze st_values;

VACUUM


--The first temporary query works as expected, 
--it produces a table of timestamps and average values of all combinations at that time.
--In the second SELECT query I am getting bullshit values. 
--There is no other data with name containing 'SG_Branch Voltage' on the DB Table. 
--Our friendly chatgpt is spitting garbage.

--Ideally the result will show me timestamps and values for anomalous points. 
--I shall then plot those points on the graph. 
--This will simply be an indicator of the point being not as expected.
explain analyze verbose
WITH average AS (
    SELECT "timestamp",
            AVG(value) AS avg_value
    FROM st_values
    WHERE "timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
      AND "name" LIKE 'INV%_INVERTER_SG_Branch Voltage%'
    GROUP BY "timestamp" )
SELECT v."timestamp",
       v.value
       --,av.avg_value
       --,ABS(v.value - av.avg_value) / av.avg_value as deviation
FROM st_values v
JOIN average av USING("timestamp")
WHERE v."timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
  AND v."name" LIKE 'INV%_INVERTER_SG_Branch Voltage%'
  AND ABS(v.value - av.avg_value) / av.avg_value > 0.3 
ORDER BY v."timestamp"--, deviation desc
--LIMIT 24;
--The code above is (semi?) not working. 
--I am not sure how to debug this. 
--This yields points that are truly anomalous (i.e. lie on one of my lines) 
--but some that are (fake?) not lying on any lines.


QUERY PLAN


Nested Loop  (cost=5267.71..15095.28 rows=50395 width=16) (actual time=129.233..193.761 rows=131941 loops=1)

  Output: v."timestamp", v.value

  ->  Finalize GroupAggregate  (cost=5267.28..5360.03 rows=700 width=16) (actual time=129.205..130.349 rows=700 loops=1)

        Output: st_values."timestamp", avg(st_values.value)

        Group Key: st_values."timestamp"

        ->  Gather Merge  (cost=5267.28..5347.78 rows=700 width=40) (actual time=129.199..129.640 rows=722 loops=1)

              Output: st_values."timestamp", (PARTIAL avg(st_values.value))

              Workers Planned: 1

              Workers Launched: 1

              ->  Sort  (cost=4267.27..4269.02 rows=700 width=40) (actual time=121.045..121.095 rows=361 loops=2)

                    Output: st_values."timestamp", (PARTIAL avg(st_values.value))

                    Sort Key: st_values."timestamp"

                    Sort Method: quicksort  Memory: 53kB

                    Worker 0:  actual time=121.962..121.980 rows=351 loops=1

                      Sort Method: quicksort  Memory: 52kB

                    ->  Partial HashAggregate  (cost=4227.19..4234.19 rows=700 width=40) (actual time=120.881..120.939 rows=361 loops=2)

                          Output: st_values."timestamp", PARTIAL avg(st_values.value)

                          Group Key: st_values."timestamp"

                          Batches: 1  Memory Usage: 105kB

                          Worker 0:  actual time=121.784..121.842 rows=351 loops=1

                            Batches: 1  Memory Usage: 105kB

                          ->  Parallel Seq Scan on public.st_values  (cost=0.00..3782.53 rows=88932 width=16) (actual time=0.014..87.500 rows=75600 loops=2)

                                Output: st_values."timestamp", st_values.value, st_values.name

                                Filter: ((st_values.name ~~ 'INV%_INVERTER_SG_Branch Voltage%'::text) AND (st_values."timestamp" <= now()) AND (st_values."timestamp" >= (now() - '1 day'::interval)))

                                Worker 0:  actual time=0.017..87.052 rows=73697 loops=1

  ->  Index Only Scan using tnv_idx on public.st_values v  (cost=0.43..13.19 rows=72 width=16) (actual time=0.005..0.073 rows=188 loops=700)

        Output: v."timestamp", v.name, v.value

        Index Cond: ((v."timestamp" = st_values."timestamp") AND (v."timestamp" >= (now() - '1 day'::interval)) AND (v."timestamp" <= now()))

        Filter: ((v.name ~~ 'INV%_INVERTER_SG_Branch Voltage%'::text) AND ((abs((v.value - (avg(st_values.value)))) / (avg(st_values.value))) > '0.3'::double precision))

        Rows Removed by Filter: 28

        Heap Fetches: 0

Planning Time: 2.398 ms

Execution Time: 199.040 ms



EXPLAIN


--https://stackoverflow.com/a/78205056/5298879
--faster
explain analyze verbose
SELECT "timestamp",value FROM (
    SELECT "timestamp",
           value,
           --avg(value)over w1 as avg_value,
           ABS(value - avg(value)over w1) / avg(value)over w1 as deviation
    FROM st_values
    WHERE "timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
      AND "name" LIKE 'INV%_INVERTER_SG_Branch Voltage%'
    WINDOW w1 AS (PARTITION BY "timestamp") ) AS subq
WHERE .3 < deviation
ORDER BY "timestamp"--, deviation desc
--LIMIT 24;


QUERY PLAN


Subquery Scan on subq  (cost=0.43..13885.90 rows=50395 width=16) (actual time=0.177..135.712 rows=131941 loops=1)

  Output: subq."timestamp", subq.value

  Filter: ('0.3'::double precision < subq.deviation)

  Rows Removed by Filter: 19259

  ->  WindowAgg  (cost=0.43..11996.09 rows=151185 width=24) (actual time=0.177..120.490 rows=151200 loops=1)

        Output: st_values."timestamp", st_values.value, (abs((st_values.value - avg(st_values.value) OVER (?))) / avg(st_values.value) OVER (?))

        ->  Index Only Scan using tnv_idx on public.st_values  (cost=0.43..8594.43 rows=151185 width=16) (actual time=0.039..56.341 rows=151200 loops=1)

              Output: st_values."timestamp", st_values.value

              Index Cond: ((st_values."timestamp" >= (now() - '1 day'::interval)) AND (st_values."timestamp" <= now()))

              Filter: (st_values.name ~~ 'INV%_INVERTER_SG_Branch Voltage%'::text)

              Heap Fetches: 0

Planning Time: 0.192 ms

Execution Time: 140.222 ms



EXPLAIN


--same output
SELECT "timestamp",value FROM (
    SELECT "timestamp",
           value,
           --avg(value)over w1 as avg_value,
           ABS(value - avg(value)over w1) / avg(value)over w1 as deviation
    FROM st_values
    WHERE "timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
      AND "name" LIKE 'INV%_INVERTER_SG_Branch Voltage%'
    WINDOW w1 AS (PARTITION BY "timestamp") ) AS subq
WHERE .3 < deviation
ORDER BY "timestamp", deviation desc
LIMIT 12;

WITH average AS (
    SELECT "timestamp",
            AVG(value) AS avg_value
    FROM st_values
    WHERE "timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
      AND "name" LIKE 'INV%_INVERTER_SG_Branch Voltage%'
    GROUP BY "timestamp" )
SELECT v."timestamp",
       v.value
FROM st_values v
JOIN average av USING("timestamp")
WHERE v."timestamp" BETWEEN NOW() - INTERVAL '1 day' AND NOW()
  AND v."name" LIKE 'INV%_INVERTER_SG_Branch Voltage%'
  AND ABS(v.value - av.avg_value) / av.avg_value > 0.3 
ORDER BY v."timestamp", ABS(v.value - av.avg_value) / av.avg_value desc
LIMIT 12;


timestamp
value


2024-03-23 00:01:00
7.959279784878713

2024-03-23 00:01:00
7.93500162028675

2024-03-23 00:01:00
7.9144565265141145

2024-03-23 00:01:00
7.889473874222754

2024-03-23 00:01:00
7.847757420600278

2024-03-23 00:01:00
7.73249819182747

2024-03-23 00:01:00
7.481158571729608

2024-03-23 00:01:00
7.458994989004545

2024-03-23 00:01:00
7.44782339029418

2024-03-23 00:01:00
7.35278788273252

2024-03-23 00:01:00
7.2889980794600655

2024-03-23 00:01:00
7.27298351523426



SELECT 12




timestamp
value


2024-03-23 00:01:00
7.959279784878713

2024-03-23 00:01:00
7.93500162028675

2024-03-23 00:01:00
7.9144565265141145

2024-03-23 00:01:00
7.889473874222754

2024-03-23 00:01:00
7.847757420600278

2024-03-23 00:01:00
7.73249819182747

2024-03-23 00:01:00
7.481158571729608

2024-03-23 00:01:00
7.458994989004545

2024-03-23 00:01:00
7.44782339029418

2024-03-23 00:01:00
7.35278788273252

2024-03-23 00:01:00
7.2889980794600655

2024-03-23 00:01:00
7.27298351523426



SELECT 12


select left(name,5),
       count(distinct name),
       array_agg(distinct regexp_substr(name,'Voltage(\d*$)'))
from st_values
where "name" SIMILAR TO 
'INV(0[1-9]|1[0-8])\_INVERTER\_SG\_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)' 
ESCAPE '\'
group by 1;


left
count
array_agg


INV01
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV02
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV03
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV04
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV05
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV06
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV07
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV08
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV09
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV10
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV11
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV12
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV13
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV14
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV15
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV16
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV17
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV18
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}



SELECT 18


select left(name,5),
       count(distinct name),
       array_agg(distinct regexp_substr(name,'Voltage(\d*$)'))
from st_values
where "name" ~
'INV(01|02|03|04|05|06|07|08|09|10|11|12|13|14|15|16|17|18)_INVERTER_SG_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)' 
group by 1;


left
count
array_agg


INV01
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV02
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV03
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV04
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV05
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV06
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV07
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV08
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV09
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV10
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV11
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV12
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV13
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV14
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV15
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV16
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV17
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV18
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}



SELECT 18


with examples(sample) as (values
  ('INV01_INVERTER_SG_Branch Voltage1'),
  ('INV012INVERTERXSGXBranch Voltage3') ),
  patterns(pattern) as (values
  ('INV(0[1-9]|1[0-8])_INVERTER_SG_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)'),
  ('INV(0[1-9]|1[0-8])\_INVERTER\_SG\_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)') ) 
select sample ~ pattern as "~", 
       sample similar to pattern as "similar to", 
       sample similar to pattern escape '\' as "similar to..escape", 
       sample,
       pattern
from examples,patterns;


~
similar to
similar to..escape
sample
pattern


t
t
t
INV01_INVERTER_SG_Branch Voltage1
INV(0[1-9]|1[0-8])_INVERTER_SG_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)

t
t
t
INV01_INVERTER_SG_Branch Voltage1
INV(0[1-9]|1[0-8])\_INVERTER\_SG\_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)

f
t
t
INV012INVERTERXSGXBranch Voltage3
INV(0[1-9]|1[0-8])_INVERTER_SG_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)

f
f
f
INV012INVERTERXSGXBranch Voltage3
INV(0[1-9]|1[0-8])\_INVERTER\_SG\_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)



SELECT 4


select left(name,5),
       count(distinct name),
       array_agg(distinct regexp_substr(name,'Voltage(\d*$)'))
from st_values
where "name" ~
'INV(0[1-9]|1[0-8])_INVERTER_SG_Branch Voltage(1|3|5|7|9|11|13|15|17|19|21|23)' 
group by 1;


left
count
array_agg


INV01
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV02
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV03
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV04
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV05
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV06
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV07
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV08
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV09
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV10
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV11
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV12
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV13
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV14
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV15
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV16
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV17
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

INV18
12
{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}



SELECT 18

timestamp	value	name
2024-03-23 00:02:00	6.707207629391543	INV10_INVERTER_SG_Branch Voltage15
2024-03-23 00:04:00	0.2165854378283646	INV16_INVERTER_SG_Branch Voltage5
2024-03-23 00:06:00	0.0012182924942929373	INV11_INVERTER_SG_Branch Voltage9
2024-03-23 00:07:00	0.6768534194102499	INV09_INVERTER_SG_Branch Voltage19
2024-03-23 00:09:00	0.2907072230437511	INV06_INVERTER_SG_Branch Voltage3
2024-03-23 00:09:00	1.3618524145407915	INV03_INVERTER_SG_Branch Voltage23
2024-03-23 00:13:00	0.03309596412063995	INV09_INVERTER_SG_Branch Voltage23
2024-03-23 00:14:00	0.24989200485469099	INV12_INVERTER_SG_Branch Voltage5
2024-03-23 00:14:00	0.5135056910779185	INV01_INVERTER_SG_Branch Voltage11
2024-03-23 00:16:00	0.13517425939443212	INV08_INVERTER_SG_Branch Voltage9
2024-03-23 00:20:00	2.657070883085778	INV18_INVERTER_SG_Branch Voltage13
2024-03-23 00:22:00	3.592830362401637	INV15_INVERTER_SG_Branch Voltage3
2024-03-23 00:26:00	3.382074211594285	INV15_INVERTER_SG_Branch Voltage13
2024-03-23 00:28:00	0.10588884732543108	INV16_INVERTER_SG_Branch Voltage3
2024-03-23 00:28:00	0.96926796908084	INV09_INVERTER_SG_Branch Voltage5
2024-03-23 00:28:00	0.09788700606805915	INV02_INVERTER_SG_Branch Voltage1

timestamp	value
2024-03-23 00:01:00	7.959279784878713
2024-03-23 00:01:00	7.93500162028675
2024-03-23 00:01:00	7.9144565265141145
2024-03-23 00:01:00	7.889473874222754
2024-03-23 00:01:00	7.847757420600278
2024-03-23 00:01:00	7.73249819182747
2024-03-23 00:01:00	7.481158571729608
2024-03-23 00:01:00	7.458994989004545
2024-03-23 00:01:00	7.44782339029418
2024-03-23 00:01:00	7.35278788273252
2024-03-23 00:01:00	7.2889980794600655
2024-03-23 00:01:00	7.27298351523426

left	count	array_agg
INV01	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV02	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV03	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV04	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV05	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV06	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV07	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV08	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV09	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV10	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV11	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV12	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV13	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV14	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV15	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV16	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV17	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}
INV18	12	{Voltage1,Voltage11,Voltage13,Voltage15,Voltage17,Voltage19,Voltage21,Voltage23,Voltage3,Voltage5,Voltage7,Voltage9}

~	similar to	similar to..escape	sample	pattern
t	t	t	INV01_INVERTER_SG_Branch Voltage1	INV(0[1-9]\|1[0-8])_INVERTER_SG_Branch Voltage(1\|3\|5\|7\|9\|11\|13\|15\|17\|19\|21\|23)
t	t	t	INV01_INVERTER_SG_Branch Voltage1	INV(0[1-9]\|1[0-8])\_INVERTER\_SG\_Branch Voltage(1\|3\|5\|7\|9\|11\|13\|15\|17\|19\|21\|23)
f	t	t	INV012INVERTERXSGXBranch Voltage3	INV(0[1-9]\|1[0-8])_INVERTER_SG_Branch Voltage(1\|3\|5\|7\|9\|11\|13\|15\|17\|19\|21\|23)
f	f	f	INV012INVERTERXSGXBranch Voltage3	INV(0[1-9]\|1[0-8])\_INVERTER\_SG\_Branch Voltage(1\|3\|5\|7\|9\|11\|13\|15\|17\|19\|21\|23)