Re: Grouping Sets

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(dot)stehule(at)gmail(dot)com]
Sent: Monday, September 19, 2011 10:45 PM
To: edwbroker(at)gmail(dot)com
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Grouping Sets

Hello

2011/9/20 David Rinaldi <edwbroker(at)gmail(dot)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.', 10000, 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.      10000   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   10000   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.      10000   8000    0.8     80      80
> bmw     germany 18000   15000   0.8333  83.3333 83
>
>
>
> Thanks
>
>
>
>
> --
> Regards
>
> David
>
>
> -----Original Message-----
> From: Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)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 <edwbroker(at)gmail(dot)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
>>
>
>