Skomplikowane zapytanie SQL: agregacja dla każdego dnia i miesiąca i grupowanie

0

Witam,
Stworzyłem dwie tabele do przechowywania danych pomiarowych. W tabeli pomiarowej oznaczam, punkt w którym zbieram pomiary.

Co jest ważne:

  1. W ciągu jednego dnia może zdarzyć się wiele 'danych pomiaru' do 'jednego pomiaru'.
  2. Chcę uzyskać dla całego miesiąca mniej więcej takie dane wyjściowe:
    nazwa pomiaru | średnia wartość na dzień 1 | ... | średnia wartość na ostatni dzień|
    Czyli dla każdego dnia muszę przeprowadzić agregacje.

Pytanie: czy jest sens myśleć o rozwiązaniu w postaci PL/pgSQL (czy jest to możliwe). Czy też raczej powinienem nastawiać się na napisanie procedury składowanej.

CREATE TABLE pomiar (
nazwa VARCHAR(10) NOT NULL,
CONSTRAINT nazwa PRIMARY KEY (nazwa)
);

CREATE TABLE dane_pomiaru (
data TIMESTAMP NOT NULL,
nazwa VARCHAR(10) NOT NULL,
wartosc NUMERIC NOT NULL,
CONSTRAINT dane_pomiaru_pk PRIMARY KEY (data, nazwa)
);

ALTER TABLE dane_pomiaru ADD CONSTRAINT pomiar_dane_pomiaru_fk
FOREIGN KEY (nazwa)
REFERENCES pomiar (nazwa)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

Pozdrawiam,

0

Źle napisałem swoje pytanie. Chciałem napisać:
"Czy jest sens myśleć o rozwiązaniu w postaci zwykłego SQL (czy jest to możliwe). Czy też raczej powinienem nastawiać się na napisanie procedury składowanej (PL/pgSQL)."

0

musisz sklecić zapytanie, które da Ci w wyniku kolumny nazwa pomiaru, data, średnia wartość i zastosować to http://www.postgresql.org/docs/9.1/static/tablefunc.html

0

Witam, dziękuje za wskazówkę, ale jeszcze nie zrozumiałem tego co napisałeś.
Za to stworzyłem takiego potworka (który w zasadzie powinien działać, tylko w pętli w języku wyższego rzędu powinienem generować natywne zaptanie SQL za pomocą pętli FOR i StringBuildera):

SELECT dp.nazwa, COALESCE(AVG(dp.wartosc), 0::DECIMAL) AS day1,
-- day 2
	(SELECT COALESCE(AVG(dpp.wartosc), 0::DECIMAL) AS day2
	FROM dane_pomiaru dpp
	WHERE
		dpp.data BETWEEN '2015-02-02 00:00' AND '2015-02-02 23:59'
	) AS day2,
-- day 3
	(SELECT COALESCE(AVG(dpp.wartosc), 0::DECIMAL) AS day3
	FROM dane_pomiaru dpp
	WHERE
		dpp.data BETWEEN '2015-02-03 00:00' AND '2015-02-03 23:59'
	) AS day3,
-- day 4
	(SELECT COALESCE(AVG(dpp.wartosc), 0::DECIMAL) AS day4
	FROM dane_pomiaru dpp
	WHERE
		dpp.data BETWEEN '2015-02-04 00:00' AND '2015-02-04 23:59'
	) AS day4
FROM dane_pomiaru dp
WHERE
	dp.data BETWEEN '2015-02-01 00:00' AND '2015-02-01 23:59'
GROUP BY
	dp.nazwa

Byłbym niezmiernie wdzięczny za info jak przerobić go na estetyczny (i pewnie wydajeniejszy( pivot. Jeżeli na świeżo uda mi się to zrobić to również podeślę.

0

lekko zmodyfikowany kod ze strony http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/

-- PL/pgSQL code to create pivot tables with automatic column names
-- Eric Minikel, CureFFI.org - 2013-03-19

-- prerequisite: install the tablefunc module
create extension tablefunc;

-- tablename: name of source table you want to pivot
-- rowc: the name of the column in source table you want to be the rows
-- colc: the name of the column in source table you want to be the columns
-- cellc: an aggregate expression determining how the cell values will be created
-- celldatatype: desired data type for the cells
-- wherec warunek dla wybrania tylko zakresu danych
create or replace function pivotcode (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype VARCHAR, wherec varchar) returns varchar language plpgsql as $$
declare
    dynsql1 varchar;
    dynsql2 varchar;
    columnlist varchar;
begin
    -- 1. retrieve list of column names.
    dynsql1 = 'select string_agg(distinct ''_''||'||colc||'||'' '||celldatatype||''','','' where ' || wherec || ' order by ''_''||'||colc||'||'' '||celldatatype||''') from '||tablename||';';
    execute dynsql1 into columnlist;
    -- 2. set up the crosstab query
    dynsql2 = 'select * from crosstab (
 ''select '||rowc||','||colc||','||cellc||' from '||tablename||' where ' || wherec || ' group by 1,2 order by 1,2'',
 ''select distinct '||colc||' from '||tablename||'  where ' || wherec || 'order by 1''
 )
 as newtable (
 '||rowc||' varchar,'||columnlist||'
 );';
    return dynsql2;
end
$$

i użycie dla Twojego przypadku

select pivotcode('dane_pomiaru','nazwa','data::date data','AVG(wartosc)','numeric', '1=1');

Zamiast '1-1' możesz wstawić dowolny warunek, np. data between data1 and data2. Jednak jakiś warunek MUSI być bo skrypt nie jest zabezpieczony dla braku warunku

0

Dzięki za podpowiedź, ale to nie działa, a ja jeszcze nie widzę błędu.

Doinstalowałem postgresql-contrib (9.3). Zainstalowałem skrypt i rozszerzenia.

Mam taki błąd:

ERROR: syntax error at or near "date"
LINIA 1: select string_agg(distinct ''||data::date date||' numeric',...
^
ZAPYTANIE: select string_agg(distinct '
'||data::date date||' numeric',',' where 1 = 1 order by '_'||data::date date||' numeric') from dane_pomiaru;
KONTEKST: PL/pgSQL function pivotcode(character varying,character varying,character varying,character varying,character varying,character varying) line 9 at EXECUTE statement

     * Błąd **********

ERROR: syntax error at or near "date"
Stan SQL: 42601
Kontekst: PL/pgSQL function pivotcode(character varying,character varying,character varying,character varying,character varying,character varying) line 9 at EXECUTE statement

Nie widzę tego błędu składniowego.

0

Wydaje mi się, że brak tam przecinka. Ale najlepiej, jakbyś wypluł sobie zawartość dynsql1 za pomocą NOTICE ...

Ja sobie kiedyś napisałem takową funkcję (bo nie mogłem tablefunc zainstalować). Wprawdzie działa trochę inaczej (zwraca wygenerowanego SQL, bo tak potrzebowałem - jako źródło do komponentu Delphiowego)

CREATE OR REPLACE FUNCTION sql_crosstab(CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING)
  RETURNS CHARACTER VARYING AS
$BODY$
-- $1 - sqlStm
-- $2 - column field
-- $3 - row field
-- $4 - aggregate function (value)
DECLARE
    t VARCHAR; -- text
    r VARCHAR; -- result
BEGIN
    t:='SELECT array_to_string(array_agg(DISTINCT ''' || SUBSTRING($4 FOR POSITION('(' IN $4)-1) || '(Case When ' || $2 || '=''' || ''''' || ' || $2 || ' || ''''' || '''' || ' Then ' || SUBSTRING($4 FROM POSITION('(' IN $4)+1 FOR POSITION(')' IN $4)-POSITION('(' IN $4)-1) || ' End)' || ' "'' || ' || $2 || ' || ''" ' || '''), '','' )  FROM (' || $1 || ') x';
    EXECUTE t INTO r ;
    t:='SELECT ' || $3 || ', ' || r || ' FROM (' || $1 ||') x GROUP BY ' || $3 || ';';
    RETURN t AS "sql_text";
END 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1

Przykład wywołania dla twojej tabeli jest taki:
SELECT * FROM sql_crosstab('tabela', 'typ', 'data', 'Avg(wartosc)')

Jak widzisz w 1 parametrze podajemy z czego ma być pobierane wyniki, 2 to jakie pole z tego selecta będzie w kolumnach (tu będzie 'TYP'), 3 parametr to po jakim polu odbędzie się grupowanie - tu data, a czwarty parametr, to co się będzie odkładać w wynikach.

0

poprawione / działające

create table pivottable (sql text);

create or replace function pivotcode (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar, wherec varchar) returns void language plpgsql as $$
declare
  dynsql1 varchar;
  dynsql2 text;
  columnlist varchar;
  where_c varchar;
begin
  if (wherec = '') or (wherec is null) then
    where_c = '';
  else
    where_c = ' where ' || wherec;
  end if; 

  dynsql1 = 'select string_agg(distinct ''_''||'||colc||'||'' '||celldatatype||''','','' order by ''_''||'||colc||'||'' '||celldatatype||''') from '||tablename|| where_c ||';';
  execute dynsql1 into columnlist;
  dynsql2 = 'select * from crosstab (
    ''select '||replace(rowc, '''', '''''')||','||replace(colc, '''', '''''')||','||replace(cellc, '''', '''''')||' from '||tablename|| replace(where_c, '''', '''''') || ' group by 1,2 order by 1,2'',
    ''select distinct '||replace(colc, '''', '''''')||' from '||tablename|| replace(where_c, '''', '''''') || ' order by 1''
    ) 
    as newtable (
    '||rowc||' varchar,'||columnlist||'
    );';

  truncate table pivottable;
  insert into pivottable values (dynsql2);
end
$$

wywołanie (z wyliczeniem średniej dla dni), ostatni parametr to warunek dla zawężenia danych (może być pusty lub null)

SELECT * from pivotcode('dane_pomiaru','nazwa','to_char(data, ''yyyymmdd'')','AVG(wartosc)','numeric','');

następnie trzeba z tabeli pivottable (jest tam jeden rekord) skopiować zawartość - to będzie gotowy SQL, który pokaże Ci Twój wynik

tutaj przykładowy wygenerowany kod SQL dla zapytania

SELECT * from pivotcode('dane_pomiaru','nazwa','to_char(data, ''yyyymm'')','AVG(wartosc)','numeric','');
select * from crosstab (
    'select nazwa,to_char(data, ''yyyymm''),AVG(wartosc) from dane_pomiaru group by 1,2 order by 1,2',
    'select distinct to_char(data, ''yyyymm'') from dane_pomiaru order by 1'
    ) 
    as newtable (
    nazwa varchar,_201401 numeric,_201402 numeric,_201403 numeric,_201404 numeric,_201405 numeric,_201406 numeric,_201407 numeric,_201408 numeric,_201409 numeric,_201410 numeric,_201411 numeric,_201412 numeric,_201501 numeric,_201502 numeric,_201503 numeric,_201504 numeric,_201505 numeric,_201506 numeric,_201507 numeric,_201508 numeric,_201509 numeric,_201510 numeric,_201511 numeric,_201512 numeric,_201601 numeric,_201602 numeric
    );

i efekt uruchomienia zapytania
user image

0

Bardzo dziękuję: działa.

0

A czy można to zapytanie wrzucone do tabeli pivottable Od razu w funkcji wywołać ?
Automatycznie otrzymał bym wynik w postaci zapytania bez kopiowania selecta z tabeli pivottable

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