tankerkoenig

script.sql

create table pricereport (
	date timestamp without time zone not null,
	fueltype character varying(6) not null,
	stationid uuid not null,
	price numeric(4,3) not null,
	change integer not null
);


create table prices (
	date timestamp without time zone not null,
	station_id uuid not null,
	diesel numeric(4,3) not null,
	e5 numeric(4,3) not null,
	e10 numeric(4,3) not null,
	dieselchange numeric(4,3) not null,
	e5change integer not null,
	e10change integer not null
);

COPY prices from PROGRAM 'awk "NR == 1 || FNR > 1" /users/ben/downloads/2022/*/*.csv' delimiter ',' csv header;


insert into pricereport (date, fueltype, stationid, price, change)
select date, 'diesel', station_id, diesel, dieselchange from prices
union all
select date, 'e5', station_id, e5, e5change from prices
union all
select date, 'e10', station_id, e10, e10change from prices;

create materialzed view pricereport_view as (
	select stationid, fueltype, date, price,
	  lag(date) over (partition by stationid, fueltype order by stationid asc, fueltype asc, date asc) as prevDate,
	  lag(price) over (partition by stationid, fueltype order by stationid asc, fueltype asc, date asc) as prevPrice,
	  lead(date) over (partition by stationid, fueltype order by stationid asc, fueltype asc, date asc) as nextDate,
	  lead(price) over (partition by stationid, fueltype order by stationid asc, fueltype asc, date asc) as nextPrice
	from pricereport 
);

create index pricereport_view_idx on public.pricereport_view using btree (stationid, fueltype, date);
vacuum(full, analyze, verbose) pricereport;

create index pricereport_idx on public.pricereport_view using btree (stationid, fueltype, date);
vacuum(full, analyze, verbose) pricereport;

explain (analyze, buffers);
select * from pricereport_view
where stationid = '51d4b6de-a095-1aa0-e100-80009459e03a' and fueltype = 'diesel' order by date asc;

explain (analyze, buffers);
select stationid, fueltype, date, price,
  lag(date) over (partition by stationid, fueltype order by stationid asc, fueltype asc, date asc) as prevDate,
  lag(price) over (partition by stationid, fueltype order by stationid asc, fueltype asc, date asc) as prevPrice,
  lead(date) over (partition by stationid, fueltype order by stationid asc, fueltype asc, date asc) as nextDate,
  lead(price) over (partition by stationid, fueltype order by stationid asc, fueltype asc, date asc) as nextPrice
from pricereport 
where stationid = '51d4b6de-a095-1aa0-e100-80009459e03a' and fueltype = 'diesel' order by date asc;