The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

Организация кросстабличной зависимости в PostgreSQL (pivot table) (sql postgresql select database)


<< Предыдущая ИНДЕКС Поиск в статьях src Установить закладку Перейти на закладку Следующая >>
Ключевые слова: sql, postgresql, select, database,  (найти похожие документы)
From: Christoph Haller <ch@rodos.fzk.de> Newsgroups: comp.databases.postgresql.sql Date: Fri, 15 Nov 2002 03:11:43 +0000 (UTC) Subject: Организация кросстабличной зависимости в PostgreSQL (pivot table) Два метода организации кросс-табличной связи, например, когда нужно построить запрос с выводом суммарных значений для определенных полей вида: 1 неделя | 2 неделя | 3 неделя | 4 неделя | сумма user1 user2 user3 Приводятся два метода: простой и сложный, но гибкий. Перевод на русский не привожу, так как и так все достаточно понятно, при взгляде на пример. Based on the e-mails on "Generating a cross tab (pivot table)", I can give you a PLpgSQL procedure to automatically generate a cross tab from any relation now. It's my first steps in PLpgSQL. I am pretty sure this is not the best way to implement, but I wanted to get some experience, so I did it this way. For all, who missed it last week, again the objective: There is a relation "sales", holding the sales of different products of different vendors. The task is to generate a report which shows the sales of every vendor and every product. Consider the following table populated with some data: CREATE TABLE sales ( product TEXT, vendor TEXT, sales INTEGER ); INSERT INTO sales VALUES ( 'milk' , 'mr. pink' , 12 ) ; INSERT INTO sales VALUES ( 'milk' , 'mr. brown' , 8 ) ; INSERT INTO sales VALUES ( 'honey' , 'mr. green' , 2 ) ; INSERT INTO sales VALUES ( 'milk' , 'mr. green' , 34 ) ; INSERT INTO sales VALUES ( 'butter', 'mr. pink' , 17 ) ; INSERT INTO sales VALUES ( 'butter', 'mr. brown' , 2 ) ; INSERT INTO sales VALUES ( 'honey' , 'mr. pink' , 19 ) ; The following query generates the report: CREATE VIEW sales_report AS SELECT product, SUM(CASE vendor WHEN 'mr. pink' THEN sales ELSE 0 END) AS "mr. pink ", SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr. brown", SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr. green", SUM(sales) AS "sum of sales" FROM sales GROUP BY product ; SELECT * FROM sales_report ; product | mr. pink | mr. brown | mr. green | sum of sales ---------+-----------+-----------+-----------+-------------- butter | 17 | 2 | 0 | 19 honey | 19 | 0 | 2 | 21 milk | 12 | 8 | 34 | 54 (3 rows) It's obvious this approach is most inflexible. As soon as there is a new vendor, one has to re-write the query and add SUM(CASE vendor WHEN 'mr. new' THEN ... , So what we need is a tool to automatically adapt the view to new vendors resp. new products. Here it is (choosing good mnemonics is not my favourite discipline): CREATE OR REPLACE FUNCTION create_pivot_report(TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS INTEGER AS ' DECLARE pg_views_rtype pg_views%ROWTYPE; vname_param ALIAS FOR $1; pivot_column ALIAS FOR $2; select_column ALIAS FOR $3; pivot_table ALIAS FOR $4; aggregate_func ALIAS FOR $5; aggr_column ALIAS FOR $6; pivot_record RECORD; create_view TEXT; BEGIN SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param; IF FOUND THEN EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ; END IF; create_view := ''CREATE VIEW '' || quote_ident(vname_param) || '' AS SELECT '' || quote_ident(select_column) ; FOR pivot_record IN EXECUTE ''SELECT DISTINCT CAST('' || quote_ident(pivot_column) || '' AS TEXT) AS col1 FROM '' || quote_ident(pivot_table) || '' order by '' || quote_ident(pivot_column) LOOP create_view := create_view || '','' || aggregate_func || ''(CASE '' || quote_ident(pivot_column) || '' WHEN '' || quote_literal(pivot_record.col1) || '' THEN '' || quote_ident(aggr_column) || '' ELSE 0 END) AS "'' || pivot_record.col1 || ''"'' ; END LOOP; create_view := create_view || '','' || aggregate_func || ''('' || quote_ident(aggr_column) || '') AS "'' || aggregate_func || '' of '' || aggr_column || ''" FROM '' || quote_ident(pivot_table) || '' GROUP BY '' || quote_ident(select_column); EXECUTE create_view ; RETURN 0; END; ' LANGUAGE 'plpgsql' ; -- where -- vname_param ALIAS FOR $1; -- the view's name to create -- pivot_column ALIAS FOR $2; -- the pivot column (entries to be CASEd) -- select_column ALIAS FOR $3; -- the select column (entries to be grouped) -- pivot_table ALIAS FOR $4; -- the name of the table to work on -- aggregate_func ALIAS FOR $5; -- the name of the aggregate function -- aggr_column ALIAS FOR $6; -- the aggregate column (entries to be aggregated) First try: SELECT create_pivot_report ('sales_report2','vendor','product','sales','sum','sales'); SELECT * FROM sales_report2 ; gives you 'sales_report2' as a copy of 'sales_report'. Now add another data set: INSERT INTO sales VALUES ( 'butter', 'mr. blue' , 11 ) ; Re-write the view by: SELECT create_pivot_report ('sales_report2','vendor','product','sales','sum','sales'); And here we go SELECT * FROM sales_report2 ; product | mr. blue | mr. brown | mr. green | mr. pink | sum of sales ---------+----------+-----------+-----------+----------+-------------- butter | 11 | 2 | 0 | 17 | 30 honey | 0 | 0 | 2 | 19 | 21 milk | 0 | 8 | 34 | 12 | 54 (3 rows) More examples: SELECT create_pivot_report ('sales_report3','vendor','product','sales','avg','sales'); SELECT create_pivot_report ('sales_report4','vendor','product','sales','stddev','sales'); SELECT create_pivot_report ('sales_report5','product','vendor','sales','sum','sales'); SELECT create_pivot_report ('sales_report6','product','vendor','sales','max','sales'); SELECT create_pivot_report ('sales_report7','vendor','product','sales','max','sales'); As you can see even interchanging the pivot column and the select column works. Feel free to use the code. Regards, Christoph PS I'm using PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.2 and I've noticed an unpleasant behaviour of the PLpgSQL parser. Double dash -- comments before the first statement in the statement section lead to strange parser errors. Is this intended?

<< Предыдущая ИНДЕКС Поиск в статьях src Установить закладку Перейти на закладку Следующая >>

Обсуждение [ RSS ]
  • 1, wasuaje (?), 17:48, 23/11/2007 [ответить]  
  • +/
    In its actual code returns an error in line 20
    here the code corrected a probed:
    CREATE OR REPLACE FUNCTION
    create_pivot_report(TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS INTEGER AS '
    DECLARE
    pg_views_rtype pg_views%ROWTYPE;
    vname_param    ALIAS FOR $1;
    pivot_column   ALIAS FOR $2;
    select_column  ALIAS FOR $3;
    pivot_table    ALIAS FOR $4;
    aggregate_func ALIAS FOR $5;
    aggr_column    ALIAS FOR $6;
    pivot_record   RECORD;
    create_view    TEXT;
    BEGIN

    SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
    IF FOUND THEN
      EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
    END IF;
    create_view :=
    ''CREATE VIEW '' || quote_ident(vname_param) ||
    '' AS SELECT '' || quote_ident(select_column) ;
    FOR pivot_record IN
    EXECUTE ''SELECT DISTINCT CAST(''
            || quote_ident(pivot_column)
            || '' AS TEXT) AS col1 FROM ''
            || quote_ident(pivot_table)
    || '' order by 1''
    LOOP
      create_view :=
       create_view || '','' || aggregate_func ||
       ''(CASE '' || quote_ident(pivot_column) ||
       '' WHEN '' || quote_literal(pivot_record.col1) ||
       '' THEN '' || quote_ident(aggr_column) ||
       '' ELSE 0 END) AS "'' || pivot_record.col1 || ''"'' ;
    END LOOP;
    create_view :=
    create_view || '','' || aggregate_func ||
    ''('' || quote_ident(aggr_column) || '') AS "'' || aggregate_func ||
    '' of '' || aggr_column || ''" FROM '' || quote_ident(pivot_table) ||
    '' GROUP BY '' || quote_ident(select_column);
    EXECUTE create_view ;

    RETURN 0;
    END;
    ' LANGUAGE 'plpgsql' ;

     

     Добавить комментарий
    Имя:
    E-Mail:
    Заголовок:
    Текст:




    Спонсоры:
    Слёрм
    Inferno Solutions
    Hosting by Ihor
    Хостинг:

    Закладки на сайте
    Проследить за страницей
    Created 1996-2019 by Maxim Chirkov
    Добавить, Поддержать, Вебмастеру