[PostgreSQL] Perspektywa

0

Witam,
mam do zrobienia perspektywę jak na obrazku poniżej
screenshot-20170711225304.png

Na tę chwilę mam coś takiego

create view v_druzyny as 
select  d_nazwa, 
	count(zawodnicy) as liczba_pilkarzy, 
	round(avg(z_rok_urodzenia), 2) as sredni_rok_urodzenia
from druzyny natural join zawodnicy
group by d_nazwa;

Problem leży w tym, że polecenie nie wyświetla mi rekordu dla Borusii.
screenshot-20170711230154.png

Polecenia tworzące bazę:

CREATE TABLE kraje (
  k_symbol     varchar(2) PRIMARY KEY,
  k_nazwa      varchar(50));

CREATE TABLE druzyny (
  d_id         decimal(3) PRIMARY KEY,
  k_symbol     varchar(2) 
    REFERENCES kraje(k_symbol),
  d_nazwa      varchar(100),
  d_miasto     varchar(100),
  d_stadion    varchar(100));

CREATE TABLE zawodnicy (
  z_id         decimal(3),
  z_nazwisko   varchar(50),
  z_pozycja    varchar(50),
  z_rok_urodzenia decimal(4),
  d_id         decimal(3) 
    REFERENCES druzyny(d_id)
  );
INSERT INTO kraje (k_symbol,k_nazwa) VALUES ('p1','Niemcy'); 
INSERT INTO kraje (k_symbol,k_nazwa) VALUES ('p2','Anglia'); 
INSERT INTO kraje (k_symbol,k_nazwa) VALUES ('p3','Hiszpania'); 
INSERT INTO kraje (k_symbol,k_nazwa) VALUES ('p4','Włochy'); 
INSERT INTO kraje (k_symbol,k_nazwa) VALUES ('p5','Polska');

INSERT INTO druzyny (d_id,k_symbol,d_nazwa,d_miasto,d_stadion) VALUES (1,'p1','Bayern Monachium','Monachium','Alianz Arena'); 
INSERT INTO druzyny (d_id,k_symbol,d_nazwa,d_miasto,d_stadion) VALUES (2,'p1','Borussia Dortmund','Dortmund','Signal Iduna Park'); 
INSERT INTO druzyny (d_id,k_symbol,d_nazwa,d_miasto,d_stadion) VALUES (3,'p1','Schalke 04','Gelsenkirchen','Veltins Arena'); 
INSERT INTO druzyny (d_id,k_symbol,d_nazwa,d_miasto,d_stadion) VALUES (4,'p2','Chelsea Londyn','Londyn','Stamford Bridge'); 
INSERT INTO druzyny (d_id,k_symbol,d_nazwa,d_miasto,d_stadion) VALUES (5,'p2','Manchester United','Manchester','Old Trafford'); 
INSERT INTO druzyny (d_id,k_symbol,d_nazwa,d_miasto,d_stadion) VALUES (6,'p3','Real Madryt','Madryt','Santiago Bernabeu'); 
INSERT INTO druzyny (d_id,k_symbol,d_nazwa,d_miasto,d_stadion) VALUES (7,'p3','Athletico Madryt','Madryt','Estadio V. Calderon');

INSERT INTO zawodnicy (z_id,z_nazwisko,z_pozycja,z_rok_urodzenia,d_id) VALUES (1,'Ribery','pomocnik',1983, 1); 
INSERT INTO zawodnicy (z_id,z_nazwisko,z_pozycja,z_rok_urodzenia,d_id) VALUES (2,'Lewandowski','napastnik',1988, 1); 
INSERT INTO zawodnicy (z_id,z_nazwisko,z_pozycja,z_rok_urodzenia,d_id) VALUES (3,'Huntelar','napastnik',1983, 3); 
INSERT INTO zawodnicy (z_id,z_nazwisko,z_pozycja,z_rok_urodzenia,d_id) VALUES (4,'Draxler','pomocnik',1993, 3); 
INSERT INTO zawodnicy (z_id,z_nazwisko,z_pozycja,z_rok_urodzenia,d_id) VALUES (5,'Eto','napastnik',1981, 4); 
INSERT INTO zawodnicy (z_id,z_nazwisko,z_pozycja,z_rok_urodzenia,d_id) VALUES (6,'Rooney','napastnik',1985, 5); 
INSERT INTO zawodnicy (z_id,z_nazwisko,z_pozycja,z_rok_urodzenia,d_id) VALUES (7,'Ronaldo','napastnik',1985, 6); 
INSERT INTO zawodnicy (z_id,z_nazwisko,z_pozycja,z_rok_urodzenia,d_id) VALUES (8,'Diego','napastnik',1980, 7); 
INSERT INTO zawodnicy (z_id,z_nazwisko,z_pozycja,z_rok_urodzenia,d_id) VALUES (9,'Filipe','obronca',1985, 7);
1

W borussi nie ma zawodników. Dlatego w wynikach nie jest wyświetlona. Spróbuj left join.

0

Bangla;) Na takiej rzeczy się złapać...
screenshot-20170712202704.png

0

Żeby nie tworzyć nowego wątku.
Kolejny problem z funkcją.
screenshot-20170713202228.png
screenshot-20170713203813.png

Dwa pomysły na rozwiązanie tego zadania:

  1. a) Tworzę funkcję, która dla danego symbolu kraju(np p1) zwraca mi sredni rok urodzenia(decimal)
    b) Deklaruję zmienną(decimal), do której zapiszę wynik
    c) Wyliczam średni rok ur. za pomocą funkcji avg() i zaokrąglam round()
    d) Funkcja zwraca mi wynik sredni_rok_urodzenia
  2. a) Tworzę funkcję, która dla danego symbolu kraju(np p1) zwraca mi sredni rok urodzenia(decimal)
    b) Deklaruję zmienne(decimal), do których zapisuję:
    - zsumowane roki urodzenia,
    - ilosc zawodnikow grających w kubach z danego państwa,
    - wynik(średni rok ur.) z dzielenia dwóch powyższych
    c) Średni rok ur. zaokrąglam round()
    d) Funkcja zwraca mi wynik sredni_rok_urodzenia

Niestety nie wiem jak to ugryźć od strony kodu. Moje wypociny:
1.

create function sredni_rok_urodzenia(P_K_SYMBOL varchar)
returns decimal as $$
declare
	sredni_rok_ur decimal;
begin
	sredni_rok_ur := round(avg(z_rok_urodzenia), 2);
	return sredni_rok_ur;
end;
$$ language plpgsql;
create function sredni_rok_urodzenia(P_K_SYMBOL varchar)
returns decimal as $$
declare
	sredni_rok_ur decimal;
	suma decimal;
	ilosc_zawodnikow decimal;
begin
	suma := sum(z_rok_urodzenia);
	ilosc_zawodnikow := count(d_id);
	sredni_rok_ur := round((suma/ilosc_zawodnikow), 2);
	return sredni_rok_ur;
end;
$$ language plpgsql;
0

Ok, oblicza mi średnią wieku ale dla zawodników ze wszystkich klubów i przypisuje każdemu z krajów. Jak teraz ustawić odpowiedni filtr(warunek?), aby liczył dla wszystkich zawodników, ALE dla zespołów z danego kraju(parametru funkcji tj. k_symbol, p1, p2, itp.)

screenshot-20170713235832.png

create function sredni_rok_urodzenia(P_K_SYMBOL varchar)
returns decimal as $$
declare
	sredni_rok_ur decimal;
begin
	sredni_rok_ur := avg(z_rok_urodzenia) from zawodnicy;
	return round(sredni_rok_ur, 2);
end;
$$ language plpgsql;
create function sredni_rok_urodzenia(P_K_SYMBOL varchar)
returns decimal as $$
declare
	sredni_rok_ur decimal;
	suma decimal;
	ilosc_zawodnikow decimal;
begin
	suma := sum(z_rok_urodzenia) from zawodnicy;
	ilosc_zawodnikow := count(d_id) from zawodnicy;
	sredni_rok_ur := suma/ilosc_zawodnikow;
	return round(sredni_rok_ur, 2);
end;
$$ language plpgsql;
1
CREATE FUNCTION sredni_rok_urodzenia(P_K_SYMBOL VARCHAR)
returns DECIMAL AS $$
DECLARE
    sredni_rok_ur DECIMAL;
BEGIN
    SELECT AVG(z_rok_urodzenia) 
    INTO sredni_rok_ur 
    FROM zawodnicy z INNER JOIN druzyny  d ON z.d_id = d.d_id
    WHERE k_symbol = P_K_SYMBOL;

    RETURN ROUND(sredni_rok_ur, 2);
END;
$$ language plpgsql;
1
CREATE FUNCTION sredni_rok_urodzenia(IN  P_K_SYMBOL VARCHAR)
RETURNS DECIMAL AS $$
    SELECT (AVG(z_rok_urodzenia))::decimal(10,2) 
    FROM zawodnicy z INNER JOIN druzyny  d ON z.d_id = d.d_id
    WHERE k_symbol = $1;
 $$ LANGUAGE sql COST 1;
0

Co nie co pomieszałem i też działa:)

create function sredni_rok_urodzenia(P_K_SYMBOL varchar)
returns decimal as $$
declare
	sredni_rok_ur decimal;
begin
	SELECT AVG(z_rok_urodzenia)
	INTO sredni_rok_ur
	FROM zawodnicy LEFT JOIN druzyny USING (d_id)
	WHERE k_symbol = P_K_SYMBOL;
	
	RETURN ROUND(sredni_rok_ur, 2);
end;
$$ language plpgsql;

1 użytkowników online, w tym zalogowanych: 0, gości: 1