Re: Grouping Sets - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Grouping Sets
Date
Msg-id CAFj8pRCcHyF10EXk7eR-GrbMN1sPuDGa4wkBMahk8Jcn0zEoOA@mail.gmail.com
Whole thread Raw
In response to Re: Grouping Sets  ("David Rinaldi" <edwbroker@gmail.com>)
List pgsql-hackers
Hello

2011/9/20 David Rinaldi <edwbroker@gmail.com>:
> 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?
>

it depends on your knowledge of pg internals and your motivation :). I
thing so it can be less than 40 hours for elimination of these issues
and next 40 hours for some finalisation.

If I remember well, I had a prototype after one week of hacking, and I
am not a strong programmer.

Regards

Pavel Stehule

> Thanks.
>
> --
> Regards
>
> David
>
> -----Original Message-----
> From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
> Sent: Monday, September 19, 2011 10:45 PM
> To: edwbroker@gmail.com
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Grouping Sets
>
> Hello
>
> 2011/9/20 David Rinaldi <edwbroker@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.', 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.stehule@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 <edwbroker@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
>>>
>>
>>
>
>


pgsql-hackers by date:

Previous
From: "David Rinaldi"
Date:
Subject: Re: Grouping Sets
Next
From: Andrew Dunstan
Date:
Subject: Re: Back-branch releases upcoming this week