Re: [HACKERS] Grouping Sets

2011-09-20 Thread David Rinaldi
Since it seems that you have spent some considerable time investigating and
producing a working concept, what would your best guess time estimate be,
assuming the requisite skills/talent/will in (planner/executor/etc.), to
have a solid working module put together? Are we looking at something like
40 hours or more like 5000 hours, in your estimate? 

Thanks. 

--
Regards

David 

-Original Message-
From: Pavel Stehule [mailto:pavel.steh...@gmail.com] 
Sent: Monday, September 19, 2011 10:45 PM
To: edwbro...@gmail.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Grouping Sets

Hello

2011/9/20 David Rinaldi edwbro...@gmail.com:
 Paul,

 I was able to apply the patch to 9.0.4 and so far looks good.  My Oracle
 results match. Nice.

 But, when trying to calculate some percentages and control some rounding,
 the results are coming back as null for some reason.  I have tried
casting,
 to_char, etc to try to get them to show up..no love ensued. I was
wondering
 if you have any idea what could by happening. I have attached some test
 results based on the grouping sets wiki. One of the examples is just using
 group by, as a sanity check.  Any ideas or help would be much appreciated.


sorry, I have not any useful idea. This work was a concept and it is
probable, so there will be some corner issues :(.

This feature needs more love and some more significant changes in
planner and executor.

Regards

Pavel



 CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING ,
 sales real, cost real );

 insert into cars2 values('skoda', 'czech rep.', 1, 8000);
 insert into cars2 values('skoda', 'germany', 5000, 6000);
 insert into cars2 values('bmw', 'czech rep.', 6000, 4000);
 insert into cars2 values('bmw', 'germany', 18000, 15000);
 insert into cars2 values('opel', 'czech rep.', 7000, 5000);
 insert into cars2 values('opel', 'germany', 7000, 5000);

 --grouping sets test--

 select name, place,
 sum(sales) as sales,
 sum(cost) as cost,
 sum(cost) / sum(sales) as cost_sales_ratio,
 (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
 round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
 from cars2 group by rollup(name, place);


 name    place   sales   cost    cost_sales_ratio      
 cost_sales_ratio_per
 cost_sales_ratio_per_rnd
 bmw     czech rep.      6000    4000    0.6667  (null)  (null)
 skoda   germany 5000    6000    1.2     (null)  (null)
 opel    czech rep.      7000    5000    0.7143  (null)  (null)
 opel    germany 7000    5000    0.7143  (null)  (null)
 skoda   czech rep.      1   8000    0.8     (null)  (null)
 bmw     germany 18000   15000   0.8333  (null)  (null)
 bmw     (null)  24000   19000   0.7917  (null)  (null)
 skoda   (null)  15000   14000   0.9333  (null)  (null)
 opel    (null)  14000   1   0.7143  (null)  (null)
 (null)  (null)  53000   43000   0.8113  (null)  (null)

 --group by sanity test--

 select name, place,
 sum(sales) as sales,
 sum(cost) as cost,
 sum(cost) / sum(sales) as cost_sales_ratio,
 (sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
 round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
 from cars2 group by name, place;

 name    place   sales   cost    cost_sales_ratio      
 cost_sales_ratio_per
 cost_sales_ratio_per_rnd
 bmw     czech rep.      6000    4000    0.6667  66.6667 67
 skoda   germany 5000    6000    1.2     120     120
 opel    czech rep.      7000    5000    0.7143  71.4286 71
 opel    germany 7000    5000    0.7143  71.4286 71
 skoda   czech rep.      1   8000    0.8     80      80
 bmw     germany 18000   15000   0.8333  83. 83



 Thanks




 --
 Regards

 David


 -Original Message-
 From: Pavel Stehule [mailto:pavel.steh...@gmail.com]
 Sent: Sunday, September 18, 2011 2:34 PM
 To: David Rinaldi
 Subject: Re: [HACKERS] Grouping Sets

 Hello

 A last patch should be applied on 8.4 or 9.0 - should to try it. I
 worked with developer version.

 http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php

 Regards

 Pavel Stehule

 2011/9/18 David Rinaldi edwbro...@gmail.com:
 Hi,

 I tried to apply the Grouping Sets Patch to 8.4, but received several
 Hunks
 failed messages, does anyone know if the failing hunks can be applied
 manually?  Or what version they were applied to specifically?

 --
 Regards

 David





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Grouping Sets

2011-09-19 Thread David Rinaldi
Paul,

I was able to apply the patch to 9.0.4 and so far looks good.  My Oracle
results match. Nice.

But, when trying to calculate some percentages and control some rounding,
the results are coming back as null for some reason.  I have tried casting,
to_char, etc to try to get them to show up..no love ensued. I was wondering
if you have any idea what could by happening. I have attached some test
results based on the grouping sets wiki. One of the examples is just using
group by, as a sanity check.  Any ideas or help would be much appreciated.


CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING ,
sales real, cost real );

insert into cars2 values('skoda', 'czech rep.', 1, 8000);
insert into cars2 values('skoda', 'germany', 5000, 6000);
insert into cars2 values('bmw', 'czech rep.', 6000, 4000);
insert into cars2 values('bmw', 'germany', 18000, 15000);
insert into cars2 values('opel', 'czech rep.', 7000, 5000);
insert into cars2 values('opel', 'germany', 7000, 5000);

--grouping sets test--

select name, place, 
sum(sales) as sales, 
sum(cost) as cost,
sum(cost) / sum(sales) as cost_sales_ratio,
(sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
from cars2 group by rollup(name, place);


nameplace   sales   costcost_sales_ratiocost_sales_ratio_per
cost_sales_ratio_per_rnd
bmw czech rep.  600040000.6667  (null)  (null)
skoda   germany 500060001.2 (null)  (null)
opelczech rep.  700050000.7143  (null)  (null)
opelgermany 700050000.7143  (null)  (null)
skoda   czech rep.  1   80000.8 (null)  (null)
bmw germany 18000   15000   0.8333  (null)  (null)
bmw (null)  24000   19000   0.7917  (null)  (null)
skoda   (null)  15000   14000   0.9333  (null)  (null)
opel(null)  14000   1   0.7143  (null)  (null)
(null)  (null)  53000   43000   0.8113  (null)  (null)

--group by sanity test--

select name, place, 
sum(sales) as sales, 
sum(cost) as cost,
sum(cost) / sum(sales) as cost_sales_ratio,
(sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
from cars2 group by name, place;

nameplace   sales   costcost_sales_ratiocost_sales_ratio_per
cost_sales_ratio_per_rnd
bmw czech rep.  600040000.6667  66.6667 67
skoda   germany 500060001.2 120 120
opelczech rep.  700050000.7143  71.4286 71
opelgermany 700050000.7143  71.4286 71
skoda   czech rep.  1   80000.8 80  80
bmw germany 18000   15000   0.8333  83. 83



Thanks 




--
Regards

David
 

-Original Message-
From: Pavel Stehule [mailto:pavel.steh...@gmail.com] 
Sent: Sunday, September 18, 2011 2:34 PM
To: David Rinaldi
Subject: Re: [HACKERS] Grouping Sets

Hello

A last patch should be applied on 8.4 or 9.0 - should to try it. I
worked with developer version.

http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php

Regards

Pavel Stehule

2011/9/18 David Rinaldi edwbro...@gmail.com:
 Hi,

 I tried to apply the Grouping Sets Patch to 8.4, but received several
Hunks
 failed messages, does anyone know if the failing hunks can be applied
 manually?  Or what version they were applied to specifically?

 --
 Regards

 David



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Grouping Sets

2011-09-18 Thread David Rinaldi
Hi,

I tried to apply the Grouping Sets Patch to 8.4, but received several Hunks
failed messages, does anyone know if the failing hunks can be applied
manually?  Or what version they were applied to specifically?

-- 
Regards

David