spark-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Selvam Raman <sel...@gmail.com>
Subject Re: PostgresSql queries vs spark sql
Date Sun, 23 Oct 2016 21:22:17 GMT
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" <selmna@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
> "லஞ்சம் தவிர்த்து நெஞ்சம் நிமிர்த்து"
>

Mime
View raw message