Cross-tab Report

Another case with aggregate function. A sales table is defined and contains raw records of sales activity as follows:

CREATE TABLE sales
(
  id serial NOT NULL,
  item text NOT NULL,
  sales_unit text NOT NULL,
  amount numeric NOT NULL,
  CONSTRAINT sales_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

id item    sales_unit amount
1  ITEM_1  UNIT_1     1000.5
2  ITEM_1  UNIT_2     1100
3  ITEM_1  UNIT_3     1150.25
4  ITEM_1  UNIT_1     100.75
5  ITEM_2  UNIT_2     275
6  ITEM_2  UNIT_3     750.5
7  ITEM_2  UNIT_2     300.5

Total sales amount for each sales unit for each item will be presented in cross-tab format:

item    UNIT_1   UNIT_2   UNIT_3
ITEM_1  1101.25  1100     1150.25
ITEM_2  0        575.5    750.5

To create aggregate function, let us prepare the State Transition Function:

CREATE OR REPLACE FUNCTION sales_amount(
  amounts numeric[],
  unit text,
  amount numeric,
  units text,
  unitcount integer)
RETURNS numeric[] AS
$BODY$
DECLARE
  out_amounts numeric[];
  unit_pattern text;
  sub_unit text[];
  idx integer;
BEGIN
  out_amounts:=amounts;
  ——–units in format: [unit_name]:[array_index],[unit_name]:[array_index],…
  unit_pattern:= unit || ‘:[0-9]{1,}’;
  sub_unit:=regexp_matches(units,unit_pattern);
  IF sub_unit IS NULL THEN
    RETURN out_amounts;
  END IF;
  ——–get array_index for dept
  idx:=split_part(array_to_string(sub_unit,”),’:’,2)::integer;
  IF array_upper(out_amounts,1) IS NULL THEN
  ——–initiate amount array with 0
    out_amounts:=string_to_array(
    ‘0’ || repeat( ‘,0’, unitcount-1 ) ,
    ‘,’ )::numeric[];
  END IF;
  out_amounts[idx]:=out_amounts[idx]+amount;
  RETURN out_amounts;
END;$BODY$
LANGUAGE ‘plpgsql’ IMMUTABLE STRICT;

Here is the aggregate function:

CREATE AGGREGATE sales_agg(text, numeric, text, integer) (
  SFUNC=sales_amount,
  STYPE=numeric[],
  INITCOND='{}’
);

Now, let us create user defined function:

CREATE OR REPLACE FUNCTION create_sales_report(
  refcursor, –cursor name
  units text[] –array of your expected units to appear on corss-tab report
)
RETURNS void AS
$BODY$
DECLARE
  unit_count integer;
  units_series text;
  i integer;
  sql text;
BEGIN
  unit_count:=array_upper(units,1);
  ——–convert units into [dept_name]:[array_index],[dept_name]:[array_index],…
  ——–to avoid repetitive loops inside aggregate function
  units_series:=”;
  FOR i in 1..unit_count LOOP
    IF i>1 THEN
      units_series:= units_series || ‘,’;
    END IF;
  units_series:= units_series || units[i] || ‘:’ || i;
  END LOOP;
  ——–compose sql statement
  sql:=    ‘SELECT t.item’;
  i:=1;
  WHILE units[i] IS NOT NULL LOOP
    sql:= sql || ‘,t.amounts[‘ || i || ‘] AS ‘ || quote_ident(units[i]);
    i:=i+1;
  END LOOP;
  sql:=sql || ‘ FROM (SELECT item,’
    || ‘sales_agg(sales_unit,amount,’
    || quote_literal(units_series) || ‘,’
    || unit_count || ‘) AS amounts ‘
  || ‘FROM sales GROUP BY item’
  || ‘) t ORDER BY t.item’;
  ——–open cursor
  OPEN $1 FOR EXECUTE sql;
RETURN;
END;$BODY$
LANGUAGE ‘plpgsql’ IMMUTABLE STRICT;

Following is how the function is called in psql console:

citra=# begin;
BEGIN
citra=# select create_sales_report(‘report’,'{UNIT_1,UNIT_2,UNIT_3}’);
create_sales_report
———————

(1 row)

citra=# FETCH ALL IN report;
item  | UNIT_1  | UNIT_2 | UNIT_3
——–+———+——–+———
ITEM_1 | 1101.25 |   1100 | 1150.25
ITEM_2 |       0 |  575.5 |   750.5
(2 rows)

citra=# COMMIT;

The query can be expanded to have more sales units:

#select create_sales_report(‘report’,'{UNIT_1,UNIT_2,UNIT_3,…,…}’);

Or, to display all available sales units in the sales table, just use array_agg (see my previous post):

#select create_sales_report(‘report’,
  (SELECT array_agg(t.sales_unit) FROM (SELECT DISTINCT ON (sales_unit) sales_unit FROM sales) t)
);
Advertisements
14 comments
  1. Heri Samuel said:

    Thanks,
    It’s really use full for my development case,

    Kawan SAM
    =============
    Batam Indonesia

  2. Athailah said:

    Wah….., this is “ilmu silat tingkat tinggi” I have to learn more and more….

  3. I love your site!

    _____________________
    Experiencing a slow PC recently? Fix it now!

  4. Not that I’m totally impressed, but this is more than I expected for when I found a link on Digg telling that the info here is awesome. Thanks.

  5. postinganya keren, bisa saya taruh ga artikel ini di website saya? nanti saya link balik sumbernya

  6. HeryHadi said:

    Dear pak Yadi, saya pernah membuat perintah CURSOR dan FETCH di SqlServer2000, kira-2 seperti ini querynya :

    —————————-Awal
    CREATE PROCEDURE ADEgetNoRak

    As

    Declare @Plu nvarchar(14)

    Declare @NoRak nvarchar(10)

    Declare @NoRak2 nvarchar(10)

    declare @Quantity nvarchar(10)

    Declare TrCursor Cursor For

    Select Plu,No_Rak,quantity From TmpRak Order By Plu,No_Rak

    Open TrCursor

    While (0=0)
    Begin

    Fetch Next From TrCursor Into @Plu,@NoRak,@Quantity

    If @@fetch_status0 Break

    Set @NoRak2=(Select No_Rak From TmpPluRak Where Plu=@Plu)

    If ltrim(rtrim(@NoRak2))=”

    Update TmpPluRak Set [No_Rak]=ltrim(rtrim(@NoRak))+’=’+@Quantity Where Plu=@Plu

    Else

    Update TmpPluRak Set [No_Rak]=ltrim(rtrim(No_Rak))+’,’+@NoRak+’=’+@quantity Where Plu=@Plu

    End
    —————————-Akhir

    tetapi saya mengalami kesulitan untuk mentransfer query ini kedalam query di postgres 8.4, sebelumnya saya ucapkan banyak terimakasih…

  7. HeryHadi, you can try following code (please fix any incorrect parts since I don’t have idea on your table structure):
    ———————————————–
    CREATE FUNCTION ADEgetNoRak RETURNS void AS $BODY$
    DECLARE
    _plu text;
    _norak text;
    _qty integer;
    BEGIN

    FOR _plu, _norak, _qty IN
    SELECT “Plu”,”No_Rak”,quantity FROM “TmpRak” ORDER BY “Plu”, “No_Rak”
    LOOP
    IF NOT EXISTS(SELECT 1 FROM TmpPluRak
    WHERE “Plu”=_plu AND LENGTH(TRIM(“No_RAK”))=0
    ) THEN
    UPDATE “TmpPluRak” SET “No_Rak”=TRIM(_norak) || ‘=’ || _qty::text
    WHERE “Plu”=_plu;
    ELSE
    UPDATE “TmplPluRak” SET “No_Rak”=TRIM(_norak), … (sorry, have no idea on your table structure)
    END IF;
    END LOOP;

    END; $BODY$ LANGUAGE plpgsql VOLATILE;

  8. HeryDai said:

    Terimakasih atas respon nya pak, sangat bermanfaat untuk saya…..

    sepertinya next time saya akan banyak bertanya nih sama bapak… :p

  9. A fantastic web page , it has long sought a paper com this

  10. Woah! I’m really loving the template/theme of this blog. It’s simple, yet effective. A lot of times it’s challenging to get that “perfect balance” between user friendliness and visual appeal. I must say you have done a amazing job with this. Additionally, the blog loads very fast for me on Opera. Excellent Blog!

  11. jotiramuc said:

    Good evening.
    They will both make you tired. Take them when all your stuff for the day is done. Or take them all day if you have nothing to do.!buy cheap!!!!
    Bye..
    ____________________________
    buy now

  12. For most recent information you have to pay a quick visit web
    and on world-wide-web I found this web page as a finest web
    page for latest updates.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: