You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by Selvam Raman <se...@gmail.com> on 2016/10/17 21:00:55 UTC

PostgresSql queries vs spark sql

Hi,

Please share me some idea if you work on this earlier.
How can i develop postgres CROSSTAB function in spark.

Postgres Example

Example 1:

SELECT mthreport.*
	FROM
	*crosstab*('SELECT i.item_name::text As row_name,
to_char(if.action_date, ''mon'')::text As bucket,
		SUM(if.num_used)::integer As bucketvalue
	FROM inventory As i INNER JOIN inventory_flow As if
		ON i.item_id = if.item_id
	  AND action_date BETWEEN date ''2007-01-01'' and date ''2007-12-31 23:59''
	GROUP BY i.item_name, to_char(if.action_date, ''mon''),
date_part(''month'', if.action_date)
	ORDER BY i.item_name',
	'SELECT to_char(date ''2007-01-01'' + (n || '' month'')::interval,
''mon'') As short_mname
		FROM generate_series(0,11) n')
		As mthreport(item_name text, jan integer, feb integer, mar integer,
			apr integer, may integer, jun integer, jul integer,
			aug integer, sep integer, oct integer, nov integer,
			dec integer)

The output of the above crosstab looks as follows:
[image: crosstab source_sql cat_sql example]

Example 2:

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

 row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
 test1    | val2       | val3       |
 test2    | val6       | val7       |


-- 
Selvam Raman
"லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"

Re: PostgresSql queries vs spark sql

Posted by Selvam Raman <se...@gmail.com>.
I found it. We can use pivot which is similar to cross tab
In postgres.

Thank you.
On Oct 17, 2016 10:00 PM, "Selvam Raman" <se...@gmail.com> wrote:

> Hi,
>
> Please share me some idea if you work on this earlier.
> How can i develop postgres CROSSTAB function in spark.
>
> Postgres Example
>
> Example 1:
>
> SELECT mthreport.*
> 	FROM
> 	*crosstab*('SELECT i.item_name::text As row_name, to_char(if.action_date, ''mon'')::text As bucket,
> 		SUM(if.num_used)::integer As bucketvalue
> 	FROM inventory As i INNER JOIN inventory_flow As if
> 		ON i.item_id = if.item_id
> 	  AND action_date BETWEEN date ''2007-01-01'' and date ''2007-12-31 23:59''
> 	GROUP BY i.item_name, to_char(if.action_date, ''mon''), date_part(''month'', if.action_date)
> 	ORDER BY i.item_name',
> 	'SELECT to_char(date ''2007-01-01'' + (n || '' month'')::interval, ''mon'') As short_mname
> 		FROM generate_series(0,11) n')
> 		As mthreport(item_name text, jan integer, feb integer, mar integer,
> 			apr integer, may integer, jun integer, jul integer,
> 			aug integer, sep integer, oct integer, nov integer,
> 			dec integer)
>
> The output of the above crosstab looks as follows:
> [image: crosstab source_sql cat_sql example]
>
> Example 2:
>
> CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
> INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
> INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
> INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
> INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
> INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
> INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
> INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
> INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
>
> SELECT *
> FROM crosstab(
>   'select rowid, attribute, value
>    from ct
>    where attribute = ''att2'' or attribute = ''att3''
>    order by 1,2')
> AS ct(row_name text, category_1 text, category_2 text, category_3 text);
>
>  row_name | category_1 | category_2 | category_3
> ----------+------------+------------+------------
>  test1    | val2       | val3       |
>  test2    | val6       | val7       |
>
>
> --
> Selvam Raman
> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>