Strom Data Source

%%sql
CREATE OR REPLACE VIEW fail AS
SELECT * FROM sqlite_scan('{{latest_file}}', 'meter');
SELECT * FROM fail;

Meters table

%%sql
SELECT 
    _id,
    decode(name)::STRING,
    decode(units)::STRING,
    decode(comment)::STRING,
    decode(vsf)::STRING,
    decode(tsf)::STRING,
    decode(cost)::STRING,
    decode(fcost)::STRING,
    decode(invert)::STRING,
    decode(vmsetup)::STRING,
    decode(type)::STRING,
    decode(currency)::STRING,
    decode(scaling)::STRING,
    decode(phyunits)::STRING,
    decode(bidir)::STRING,
    decode(prod)::STRING
FROM sqlite_scan('{{latest_file}}', 'meter')
;

Strom data

%%sql 

SELECT 
  meterid, 
  -- Blob Functions, because most columns get read as blob
  -- https://duckdb.org/docs/sql/functions/blob
  decode(date)::DATETIME AS date, 
  decode(value)::INT AS value,
  decode(comment)::STRING AS comment,
  decode(color)::STRING AS color,
  decode(cost)::STRING AS cost,
  decode(vcor)::STRING AS vcor,
  decode(ccor)::STRING AS ccor,
  decode(first)::STRING AS first
FROM sqlite_scan('{{latest_file}}', 'reading') 
WHERE meterid = 1
;

%%sql 

toy << 
WITH strom_sqlite AS (
  SELECT 
    meterid, 
    -- Blob Functions, because most columns get read as blob
    -- https://duckdb.org/docs/sql/functions/blob
    decode(date)::DATETIME AS date, 
    decode(value)::INT AS value,
    decode(first)::INT AS first
  FROM sqlite_scan('{{latest_file}}', 'reading') 
  WHERE meterid = 1
)
SELECT *,
date_sub('minute', lag(date, 1, '2020-11-30 00:00:00') over(order by date), date) AS minutes, 
value * (1/(1-first)) - lag(value, 1, 12160) over(order by date) AS consumption,
1.0 * consumption / minutes AS cm,
24.0 * 60.0 * consumption / minutes AS consumption_day_equivalent
FROM strom_sqlite
ORDER BY date
;

Strom data

%%sql 

ATTACH '{{latest_file}}' AS ecas (TYPE SQLITE);
USE ecas;

%%sql 

SELECT * FROM reading;

%%sql 


UPDATE reading
SET meterid = 1
WHERE meterid = 16;

%%sql 


UPDATE reading
SET meterid = 3
WHERE meterid = 17;

%%sql 


UPDATE reading
SET meterid = 2
WHERE meterid = 18;

%%sql

CREATE OR REPLACE TABLE normalstrom_minute_nulls AS
WITH minutes_table AS (
    SELECT UNNEST(generate_series(ts[1], ts[2], interval 1 minute)) AS minute
    FROM (VALUES (
    [(SELECT MIN(date) FROM normalstrom), (SELECT MAX(DATE) FROM normalstrom)]
    )) t(ts)
)
SELECT * 
FROM minutes_table
LEFT JOIN normalstrom
ON minutes_table.minute = normalstrom.date
;

SELECT * FROM normalstrom_minute_nulls ORDER BY minute;

%%sql

CREATE OR REPLACE TABLE normalstrom_minute AS
SELECT
    minute,
    date,
    value,
    minutes,
    consumption,
    FIRST_VALUE(cm IGNORE NULLS) OVER(
    ORDER BY minute ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 
    ) AS cm
FROM normalstrom_minute_nulls t1
ORDER BY t1.minute
;

%%sql

SELECT * FROM strom_per_day WHERE date >= '2021-01-01' AND date <= '2021-12-31';

SELECT MAX(date) FROM strom_per_day;

%%sql 

SELECT 
  meterid, 
  decode(date)::DATETIME AS date, 
  decode(value)::INT AS value,
  decode(first)::STRING AS first
FROM sqlite_scan('{{latest_file}}', 'reading') 
WHERE meterid = 2
AND date >= '2023-05-21'
AND date <= '2023-05-31'
ORDER BY date
;