260 lines
7.1 KiB
SQL
260 lines
7.1 KiB
SQL
|
|
|
|
USE SBFspot;
|
|
|
|
CREATE Table Config (
|
|
`Key` varchar(32),
|
|
`Value` varchar(200),
|
|
PRIMARY KEY (`Key`)
|
|
);
|
|
|
|
INSERT INTO Config VALUES('SchemaVersion','2');
|
|
|
|
CREATE Table Inverters (
|
|
Serial int unsigned NOT NULL,
|
|
Name varchar(32),
|
|
Type varchar(32),
|
|
SW_Version varchar(32),
|
|
TimeStamp int,
|
|
TotalPac int,
|
|
EToday bigint,
|
|
ETotal bigint,
|
|
OperatingTime double,
|
|
FeedInTime double,
|
|
Status varchar(10),
|
|
GridRelay varchar(10),
|
|
Temperature float,
|
|
PRIMARY KEY (Serial)
|
|
);
|
|
|
|
CREATE View vwInverters AS
|
|
Select Serial,
|
|
Name,Type,SW_Version,
|
|
From_UnixTime(TimeStamp) AS TimeStamp,
|
|
TotalPac,
|
|
EToday,ETotal,
|
|
OperatingTime,FeedInTime,
|
|
Status,GridRelay,
|
|
Temperature
|
|
FROM Inverters;
|
|
|
|
CREATE Table SpotData (
|
|
TimeStamp int NOT NULL,
|
|
Serial int unsigned NOT NULL,
|
|
Pdc1 int, Pdc2 int,
|
|
Idc1 float, Idc2 float,
|
|
Udc1 float, Udc2 float,
|
|
Pac1 int, Pac2 int, Pac3 int,
|
|
Iac1 float, Iac2 float, Iac3 float,
|
|
Uac1 float, Uac2 float, Uac3 float,
|
|
EToday bigint, ETotal bigint,
|
|
Frequency float,
|
|
OperatingTime double,
|
|
FeedInTime double,
|
|
BT_Signal float,
|
|
Status varchar(10),
|
|
GridRelay varchar(10),
|
|
Temperature float,
|
|
PRIMARY KEY (TimeStamp, Serial)
|
|
);
|
|
|
|
-- Fix 02-MAY-2016 See Issue 150
|
|
CREATE View vwSpotData AS
|
|
Select From_UnixTime(Dat.TimeStamp) AS TimeStamp,
|
|
From_UnixTime(CASE WHEN (Dat.TimeStamp % 300) < 150
|
|
THEN Dat.TimeStamp - (Dat.TimeStamp % 300)
|
|
ELSE Dat.TimeStamp - (Dat.TimeStamp % 300) + 300
|
|
END) AS Nearest5min,
|
|
Inv.Name,
|
|
Inv.Type,
|
|
Dat.Serial,
|
|
Pdc1, Pdc2,
|
|
Idc1, Idc2,
|
|
Udc1, Udc2,
|
|
Pac1, Pac2, Pac3,
|
|
Iac1, Iac2, Iac3,
|
|
Uac1, Uac2, Uac3,
|
|
Pdc1+Pdc2 AS PdcTot,
|
|
Pac1+Pac2+Pac3 AS PacTot,
|
|
CASE WHEN Pdc1+Pdc2 = 0 THEN
|
|
0
|
|
ELSE
|
|
CASE WHEN Pdc1+Pdc2>Pac1+Pac2+Pac3 THEN
|
|
ROUND((Pac1+Pac2+Pac3)/(Pdc1+Pdc2)*100,1)
|
|
ELSE
|
|
100.0
|
|
END
|
|
END AS Efficiency,
|
|
Dat.EToday,
|
|
Dat.ETotal,
|
|
Frequency,
|
|
Dat.OperatingTime,
|
|
Dat.FeedInTime,
|
|
ROUND(BT_Signal,1) AS BT_Signal,
|
|
Dat.Status,
|
|
Dat.GridRelay,
|
|
ROUND(Dat.Temperature,1) AS Temperature
|
|
FROM SpotData Dat
|
|
INNER JOIN Inverters Inv ON Dat.Serial=Inv.Serial;
|
|
|
|
CREATE Table DayData (
|
|
TimeStamp int NOT NULL,
|
|
Serial int unsigned NOT NULL,
|
|
TotalYield bigint,
|
|
Power bigint,
|
|
PVoutput int(1),
|
|
PRIMARY KEY (TimeStamp, Serial)
|
|
);
|
|
|
|
CREATE View vwDayData AS
|
|
select From_UnixTime(Dat.TimeStamp) AS TimeStamp,
|
|
Inv.Name, Inv.Type, Dat.Serial,
|
|
TotalYield,
|
|
Power,
|
|
PVOutput FROM DayData Dat
|
|
INNER JOIN Inverters Inv ON Dat.Serial=Inv.Serial
|
|
ORDER BY Dat.Timestamp Desc;
|
|
|
|
CREATE Table MonthData (
|
|
TimeStamp int NOT NULL,
|
|
Serial int unsigned NOT NULL,
|
|
TotalYield bigint,
|
|
DayYield bigint,
|
|
PRIMARY KEY (TimeStamp, Serial)
|
|
);
|
|
|
|
CREATE View vwMonthData AS
|
|
select convert_tz(from_unixtime(Dat.TimeStamp), 'SYSTEM', '+00:00') AS TimeStamp,
|
|
Inv.Name, Inv.Type, Dat.Serial,
|
|
TotalYield, DayYield FROM MonthData Dat
|
|
INNER JOIN Inverters Inv ON Dat.Serial=Inv.Serial
|
|
ORDER BY Dat.Timestamp Desc;
|
|
|
|
CREATE Table EventData (
|
|
EntryID int unsigned,
|
|
TimeStamp int NOT NULL,
|
|
Serial int unsigned NOT NULL,
|
|
SusyID smallint unsigned,
|
|
EventCode int unsigned,
|
|
EventType varchar(32),
|
|
Category varchar(32),
|
|
EventGroup varchar(32),
|
|
Tag varchar(200),
|
|
OldValue varchar(64),
|
|
NewValue varchar(64),
|
|
UserGroup varchar(10),
|
|
PRIMARY KEY (Serial, EntryID)
|
|
);
|
|
|
|
CREATE View vwEventData AS
|
|
select From_UnixTime(Dat.TimeStamp) AS TimeStamp,
|
|
Inv.Name, Inv.Type, Dat.Serial,
|
|
SusyID, EntryID,
|
|
EventCode, EventType,
|
|
Category, EventGroup, Tag,
|
|
OldValue, NewValue,
|
|
UserGroup FROM EventData Dat
|
|
INNER JOIN Inverters Inv ON Dat.Serial=Inv.Serial
|
|
ORDER BY EntryID Desc;
|
|
|
|
CREATE Table Consumption (
|
|
TimeStamp int NOT NULL,
|
|
EnergyUsed int,
|
|
PowerUsed int,
|
|
PRIMARY KEY (TimeStamp)
|
|
);
|
|
|
|
CREATE VIEW vwConsumption AS
|
|
SELECT From_UnixTime(TimeStamp) As Timestamp,
|
|
From_UnixTime(CASE WHEN (TimeStamp % 300) < 150
|
|
THEN TimeStamp - (TimeStamp % 300)
|
|
ELSE TimeStamp - (TimeStamp % 300) + 300
|
|
END) AS Nearest5min,
|
|
EnergyUsed,
|
|
PowerUsed
|
|
FROM Consumption;
|
|
|
|
-- Fix 02-MAY-2016 See Issue 150
|
|
CREATE VIEW vwAvgConsumption AS
|
|
SELECT Nearest5min,
|
|
cast(avg(EnergyUsed) As decimal(9)) As EnergyUsed,
|
|
cast(avg(PowerUsed) As decimal(9)) As PowerUsed
|
|
FROM vwConsumption
|
|
GROUP BY Nearest5Min;
|
|
|
|
CREATE VIEW vwAvgSpotData AS
|
|
SELECT nearest5min,
|
|
serial,
|
|
cast(avg(Pdc1) as decimal(9)) AS Pdc1,
|
|
cast(avg(Pdc2) as decimal(9)) AS Pdc2,
|
|
cast(avg(Idc1) as decimal(9,3)) AS Idc1,
|
|
cast(avg(Idc2) as decimal(9,3)) AS Idc2,
|
|
cast(avg(Udc1) as decimal(9,2)) AS Udc1,
|
|
cast(avg(Udc2) as decimal(9,2)) AS Udc2,
|
|
cast(avg(Pac1) as decimal(9)) AS Pac1,
|
|
cast(avg(Pac2) as decimal(9)) AS Pac2,
|
|
cast(avg(Pac3) as decimal(9)) AS Pac3,
|
|
cast(avg(Iac1) as decimal(9,3)) AS Iac1,
|
|
cast(avg(Iac2) as decimal(9,3)) AS Iac2,
|
|
cast(avg(Iac3) as decimal(9,3)) AS Iac3,
|
|
cast(avg(Uac1) as decimal(9,2)) AS Uac1,
|
|
cast(avg(Uac2) as decimal(9,2)) AS Uac2,
|
|
cast(avg(Uac3) as decimal(9,2)) AS Uac3,
|
|
cast(avg(Temperature) as decimal(9,2)) AS Temperature
|
|
FROM vwSpotData
|
|
GROUP BY serial, nearest5min;
|
|
|
|
CREATE VIEW vwPvoData AS
|
|
SELECT dd.Timestamp,
|
|
dd.Name,
|
|
dd.Type,
|
|
dd.Serial,
|
|
dd.TotalYield AS V1,
|
|
dd.Power AS V2,
|
|
cons.EnergyUsed AS V3,
|
|
cons.PowerUsed AS V4,
|
|
spot.Temperature AS V5,
|
|
spot.Uac1 AS V6,
|
|
NULL AS V7,
|
|
NULL AS V8,
|
|
NULL AS V9,
|
|
NULL AS V10,
|
|
NULL AS V11,
|
|
NULL AS V12,
|
|
dd.PVoutput
|
|
FROM vwDayData AS dd
|
|
LEFT JOIN vwAvgSpotData AS spot
|
|
ON dd.Serial = spot.Serial AND dd.Timestamp = spot.Nearest5min
|
|
LEFT JOIN vwAvgConsumption AS cons
|
|
ON dd.Timestamp = cons.Nearest5min
|
|
ORDER BY dd.Timestamp DESC;
|
|
|
|
-- Fix 09-JAN-2017 See Issue 54: SQL Support for battery inverters
|
|
CREATE TABLE SpotDataX (
|
|
`TimeStamp` int NOT NULL,
|
|
`Serial` int unsigned NOT NULL,
|
|
`Key` int NOT NULL,
|
|
`Value` int,
|
|
PRIMARY KEY (
|
|
`TimeStamp` ASC,
|
|
`Serial` ASC,
|
|
`Key`
|
|
)
|
|
);
|
|
|
|
DROP VIEW IF EXISTS vwBatteryData;
|
|
|
|
CREATE VIEW vwBatteryData AS
|
|
SELECT FROM_UNIXTIME(sdx.`TimeStamp` - (sdx.`TimeStamp` % 300)) AS `5min`,
|
|
sdx.`Serial`,
|
|
inv.`Name`,
|
|
AVG(CASE WHEN `Key` = 10586 THEN `Value` END) AS ChaStatus,
|
|
AVG(CASE WHEN `Key` = 18779 THEN CAST(`Value` AS DECIMAL(10,1)) / 10 END) AS Temperature,
|
|
AVG(CASE WHEN `Key` = 18781 THEN CAST(`Value` AS DECIMAL(10,3)) / 1000 END) AS ChaCurrent,
|
|
AVG(CASE WHEN `Key` = 18780 THEN CAST(`Value` AS DECIMAL(10,2)) / 100 END) AS ChaVoltage,
|
|
AVG(CASE WHEN `Key` = 17974 THEN `Value` END) AS GridMsTotWOut,
|
|
AVG(CASE WHEN `Key` = 17975 THEN `Value` END) AS GridMsTotWIn
|
|
FROM SpotDataX AS sdx
|
|
INNER JOIN
|
|
Inverters AS inv ON sdx.Serial = inv.`Serial`
|
|
GROUP BY `5min`;
|