-- Explain: Einfach bzw. Varianten
explain 
--explain analyze
--explain analyze verbose
--explain (analyze, buffers, format json)
select operator, name
from osm_austria_point
where shop='computer'




-- Korrelierten Index nutzen
explain analyze
select name, shop, operator, brand
from osm_austria_point
where brand = 'BMW'
--  and shop in('car', 'car_parts', 'car_repair')













-- Index ist nicht selektiv genug
explain analyze
select name, shop, operator, brand
from osm_austria_point
where shop = 'computer'
--where shop <> 'computer'









-- <- Folien




-- Index wegen Funktionsaufrufs nicht nutzbar
explain analyze
select name, shop, operator, brand
from osm_austria_point
-- where name = 'Eurospar'
where lower(name) = 'eurospar';



-- Functional index auf lower(name)
--create index osm_austria_point_lower_name_idx on osm_austria_point (lower(name));

-- Nutzung des funktionalen Index
explain analyze
select name, shop, operator, brand
from osm_austria_point
where lower(name) = 'eurospar';
-- => Index wird genutzt

-- Aufräumen
--drop index osm_austria_point_lower_name_idx;



-- <- Folien





-- Geometrie-Abfrage mit Contains: Was ist im Landkreis München?

--explain analyze
select vwe.ident, vwe.name, vwe.verwaltungseinheittyp
from verwaltungseinheit mü
inner join verwaltungseinheit vwe on ST_Contains(mü.geometry, vwe.geometry)
where mü.name = 'München'
  and mü.verwaltungseinheittyp = 'LKR'
order by verwaltungseinheittyp desc, name;

-- Geometrie-Abfrage mit Touches: Was grenzt an die Gemeinde München an?

-- explain analyze
select vwe.ident, vwe.kurzbezeichnung, vwe.name, vwe.verwaltungseinheittyp
from verwaltungseinheit mü
inner join verwaltungseinheit vwe on ST_Touches(vwe.geometry, mü.geometry)
where mü.name = 'München'
  and mü.verwaltungseinheittyp = 'GEM'
order by verwaltungseinheittyp desc, name;


-- Größe der Gemeinden im Landkreis München in km²

select vwe.ident, vwe.name, vwe.verwaltungseinheittyp,
    st_area(vwe.geometry) / 1000000 as fläche_km2
from verwaltungseinheit mü
inner join verwaltungseinheit vwe on ST_Contains(mü.geometry, vwe.geometry)
where mü.name = 'München'
  and mü.verwaltungseinheittyp = 'LKR'
order by verwaltungseinheittyp desc, fläche_km2 desc;


-- Länge der gemeinsamen Grenze der Landkreise München und Dachau

select da.ident, da.name, da.verwaltungseinheittyp, 
    ST_Length(ST_AsText(ST_Intersection(mü.geometry, da.geometry))) as länge_gemeinsame_grenze
from verwaltungseinheit mü
inner join verwaltungseinheit da on da.name = 'Dachau' and da.verwaltungseinheittyp = 'LKR'
where mü.name = 'München'
  and mü.verwaltungseinheittyp = 'LKR';


-- Welche Gemeinden sind näher als 50 km von Passau entfernt?

-- Langsam, durchsucht die gesamte Tabelle und errechnet jede Distanz
select gem.name, round(ST_Distance(pa.geometry, gem.geometry) / 1000) as entfernung
from verwaltungseinheit pa
inner join verwaltungseinheit gem on ST_Distance(pa.geometry, gem.geometry) < 50000 
                                 and gem.verwaltungseinheittyp = 'GEM'
where pa.name = 'Passau'
  and pa.verwaltungseinheittyp = 'GEM'
order by entfernung;



-- Schneller mit der Funktion ST_DWithin => Index wird genutzt
--explain analyze
select gem.name, round(ST_Distance(pa.geometry, gem.geometry) / 1000) as entfernung
from verwaltungseinheit pa
inner join verwaltungseinheit gem on ST_DWithin(pa.geometry, gem.geometry, 50000)
                                    and gem.verwaltungseinheittyp = 'GEM'
where pa.name = 'Passau'
  and pa.verwaltungseinheittyp = 'GEM'
order by entfernung




- -<- Folien














-- CTE-Einführung: Auswahl einer Untergruppe
-- explain analyze
with supermarket as (
  select *
  from osm_austria_point
  where shop = 'supermarket'
)
select operator, count(*) as opcount
from supermarket 
group by operator
order by opcount desc








-- Kleine Ergebnismengen aus großen Tabellen, Join
--explain analyze
with streets as (
   select name as street
   from osm_austria_roads
   where highway in ('primary', 'secondary', 'residential')
     and name is not null
),
shops as (
   select name as shop, "addr:street" as street, "addr:housenumber" as housenr
   from osm_austria_point
   where shop is not null
     and name is not null
     and "addr:street" is not null
),
shopstreets as (
   select shop, sh.street, housenr
   from shops sh
   inner join streets st on lower(sh.street) = lower(st.street)
)
select street, count(shop) as shops,
   min(housenr) as min_housenr, max(housenr) as max_housenr
from shopstreets
group by street
order by shops desc






-- <- Folien













-- Recursive CTE: Tabelle mit Hierarchie
drop table if exists recursive_test;
create table recursive_test (
	id integer primary key,
	parent_id integer,
	name text
);
insert into recursive_test values (1, null, 'Root 1');
insert into recursive_test values (2, 1, 'Child 1/1');
insert into recursive_test values (3, 2, 'Child 1/1/1');
insert into recursive_test values (4, 1, 'Child 1/2');
insert into recursive_test values (5, 1, 'Child 1/3');
insert into recursive_test values (11, null, 'Root 2');
insert into recursive_test values (12, 11, 'Child 2/1');

select * from recursive_test;


-- Rekursive Abfrage
with recursive structure as (
  -- Wurzelelemente ohne Parent, die Tiefe ist 1
	select id, name, cast('' as text) as parent_name, name as full_path,
		1 as depth
	from recursive_test
	where (parent_id is null or parent_id = id)
	union
  -- Unterelemente haben einen Parent
	select rt.id, rt.name, p.name as parent_name, p.full_path || ': ' || rt.name,
		p.depth + 1 as depth
	from recursive_test rt
  -- Join mit der Parent-ID
	inner join structure p on rt.parent_id = p.id 
   -- Zur Sicherheit die Tiefe begrenzen
	where p.depth <= 100
)
select *
from structure
order by full_path






-- Falsche Eintragung, die auf sich selbst zeigt
insert into recursive_test values (13, 13, 'Broken');

delete from recursive_test where name = 'Broken';






-- <- Folien




-- Window Functions: Verkäufe pro Tag, Mittelwert, Vergleich mit Mittelwert
with sales_per_day as (
   select datum, sum(sales) as sales
   from advsql_sales
   where datum between '2018-06-01' and '2018-06-30'
   group by datum
)
select *,
    -- Mittelwert der Verkäufe
    round(avg(sales) over (), 1) as avgsales,
    -- Tageswert im Vergleich mit dem Mittelwert absolut
    round(sales - avg(sales) over (), 1) as avg_comparison,
    -- Relativer Vergleich mit dem Mittelwert in Prozent
    round((sales - avg(sales) over ()) / avg(sales) over () * 100, 1) as comparison_pct
from sales_per_day
order by datum










-- Window Functions: Monatlich Mittelwert, Min, Max
with sales_per_day as (
   select datum, extract(month from datum) as month, sum(sales) as sales
   from advsql_sales
   where datum between '2018-05-20' and '2018-06-10'
   group by datum
)
select datum, sales,
	round(avg(sales) over (partition by month), 1) as avg_sales_month,
	min(sales) over (partition by month) as min_sales_month,
	max(sales) over (partition by month) as max_sales_month
from sales_per_day
order by datum










-- Window Functions: Kumulierte Summe
with sales_per_day as (
   select datum, extract(month from datum) as month, sum(sales) as sales
   from advsql_sales
   where extract(year from datum) = 2018
   group by datum
)
select datum, sales,
  -- Kumulierte Summe des jeweiligen Monats, startet im nächsten Monat neu
	sum(sales) over (partition by month order by datum) as month_cum_sum,
	-- Kumulierte Summe des Jahres
	sum(sales) over (order by datum) as year_cum_sum
from sales_per_day
order by datum













-- Window Functions: RANK und ROW_NUMBER
with sales_per_day as (
   select datum, extract(month from datum) as month, sum(sales) as sales
   from advsql_sales
   where extract(year from datum) = 2018
   group by datum
)
select month, datum, sales,
	-- Rang im Monat (bei Gleichstand wird die nächste Zahl ausgelassen)
	RANK() over (partition by month order by sales desc) as month_rank,
	-- Rang im Monat (keine Zahl ausgelassen)
	DENSE_RANK() over (partition by month order by sales desc) as month_dense_rank,
	-- Eindeutige Nummerierung innerhalb des Monats
	ROW_NUMBER() over (partition by month order by sales desc) as month_rownr,
	-- Rang im ganzen Jahr
	rank() over (order by sales desc) as year_rank
from sales_per_day
order by month, month_rank










-- So wurden früher Duplikate gesucht
select operator, name, count(osm_id) as dups
from osm_austria_point
where shop='computer'
  and name is not null
group by operator, name
having count(osm_id) > 1
order by dups desc, operator, name

-- Und dann Schritt für Schritt die einzelnen Kombinationen abfragen







-- Heute machen wir das so
with computershops as (
   select *,
      count(osm_id) over (partition by name) as dups,
      min(osm_id) over (partition by name) as minid
   from osm_austria_point
   where shop = 'computer'
     and name is not null
)
select osm_id, shop, name, dups, "addr:street"
from computershops
where dups > 1
order by dups desc, name




-- Beispiel für Löschung von unerwünschten Duplikaten
with computershops as (
    -- Like before
)
delete from osm_austria_point
where osm_id in (
    select osm_id
    from computershops
    where dups > 1
      and osm_id <> minid
)











-- Window functions: ABC-Analyse
-- A: Die ersten 70 % der verkauften Produkte, B: bis 90 %, C: Rest
with shops as (
  select p.name as shop, p.osm_id,
    sum(s.sales) as shopsales
  from advsql_sales s
  inner join osm_austria_point p using (osm_id)
  group by p.name, p.osm_id
),
shops_abc as (
  select shop, shopsales,
    -- Rang dieser Filiale unter allen Filialen
    rank() over (order by shopsales desc) as rang,
    -- Kumulierte Summe, absteigend sortiert
    sum(shopsales) over (order by shopsales desc) as cum_sum,
    -- Gesamtsumme
    --sum(shopsales) over () as overall_sum,
    -- Gruppen-Limits für A und B
    sum(shopsales) over () * 0.7 as group_a,
    sum(shopsales) over () * 0.9 as group_b,
    -- ABC-Klassifizierung
    case when sum(shopsales) over (order by shopsales desc) / sum(shopsales) over () <= 0.7 then 'A'
         when sum(shopsales) over (order by shopsales desc) / sum(shopsales) over () <= 0.9 then 'B'
         else 'C'
    end as abc
  from shops
),
shops_abc_extended as (
  select shop, shopsales,
    abc || ' (' ||
      rank() over (partition by abc order by shopsales desc) || '/' ||
      count(*) over (partition by abc) || ') (' ||
      rank() over (order by shopsales desc) || '/' ||
      count(*) over () || ')'
      as abc_extended
  from shops_abc
  order by shopsales desc
)
select *
from shops_abc
-- from shops_abc_extended


-- A1 (1/100) (2/200)




-- Window functions: Die besten drei Tage jedes Monats
-- CTE mit Window Function und Selektion
with sales_per_day as (
   select datum, extract(month from datum) as month, sum(sales) as sales
   from advsql_sales
   where extract(year from datum) = 2018
   group by datum
),
monthrank as (
	select month, datum, sales,
		RANK() over (partition by month order by sales desc) as monthrank
	from sales_per_day
)
select month, datum, sales, monthrank
from monthrank
where monthrank <= 3
order by month, monthrank






-- Window functions: gleitender Mittelwert
with sales_per_day as (
   select datum, extract(month from datum) as month, sum(sales) as sales
   from advsql_sales
   where datum between '2018-03-15' and '2018-04-15'
   group by datum
)
select datum, sales,
	round(avg(sales) OVER (ORDER BY datum ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)) AS avg7,
	round(avg(sales) OVER (ORDER BY datum ROWS BETWEEN 7 PRECEDING AND 6 FOLLOWING)) AS avg14
from sales_per_day
order by datum



-- Andere Festlegung von Window Functions
with sales_per_day as (
   select datum, extract(month from datum) as month, sum(sales) as sales
   from advsql_sales
   where datum between '2018-03-15' and '2018-04-15'
   group by datum
)
select datum, sales,
	round(avg(sales) OVER _7_days) AS avg7,
	round(avg(sales) OVER _14_days) AS avg14,
	count(sales) OVER _14_days as count14
from sales_per_day
window _7_days as (order by datum rows between 3 preceding and 3 following),
      _14_days as (order by datum rows between 7 preceding and 6 following)
order by datum







-- Window functions: tägliche Änderung
with sales_per_day as (
   select datum, extract(month from datum) as month, sum(sales) as sales
   from advsql_sales
   where datum between '2018-03-15' and '2018-04-15'
   group by datum
)
select datum, sales,
	sales - lag(sales, 1) over (order by datum) as vs_yesterday,
	sales - lead(sales, 1) over (order by datum) as vs_tomorrow
from sales_per_day
order by datum

-- <---- Folien








-- Beispieltabelle mit XML-Daten
SELECT * 
FROM xmltest;


-- XML-Funktionen
SELECT beschreibung, 
    daten is document as is_document, 
    xmlexists('//vehicles/vehicle' passing by ref daten) as xml_exists, 
    xml_is_well_formed(daten::text) as xml_well_formed, 
    daten 
FROM xmltest;


-- XPath-Abfrage
SELECT beschreibung, 
    unnest(xpath('//vehicles/vehicle/@id', daten)) as id,
    unnest(xpath('//vehicles/vehicle/@license_plate', daten)) as license_plate 
FROM xmltest

-- XPath-Filter
SELECT beschreibung, id, model_name
FROM ( 
    SELECT beschreibung, 
        unnest(xpath('//vehicles/vehicle/@id', daten)) as id,
        unnest(xpath('//vehicles/vehicle/model/@model_name', daten)) as model_name FROM xmltest 
) vehicles125ccm 
WHERE model_name::text like '%125%ccm';






-- JSON-Beispieltabelle
select * 
FROM opendata;


-- JSON-Funktionen
SELECT bezeichnung, jsonb_pretty(daten) 
FROM opendata;

SELECT bezeichnung, 
    jsonb_object_keys(daten), 
    jsonb_each(daten) 
FROM opendata;


-- Zugriff auf JSON-Elemente
SELECT bezeichnung, 
    jsonb_array_elements(daten->'features')
FROM opendata 
WHERE bezeichnung like 'Universität%';

SELECT bezeichnung, 
    jsonb_array_elements(daten->'features')->'geometry'->'coordinates' as latlong 
FROM opendata 
WHERE bezeichnung like 'Universität%';


-- Filter auf Basis von Elementen
with features as ( 
    SELECT bezeichnung, jsonb_array_elements(daten->'features') as features 
    FROM opendata 
    WHERE bezeichnung like 'Universität%'
) 
SELECT features->'properties'->>'NAME' as name, 
       features->'properties'->>'BEZEICHNUNG' as bezeichnung 
FROM features 
WHERE features->'properties'->>'NAME' like 'Universit%' ;