Re: query optimization question - Mailing list pgsql-sql

That looks really promising as a possibility, however I think you intended
to add a group by clause.


Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com



> -----Original Message-----
> From: Masaru Sugawara [mailto:rk73@sea.plala.or.jp]
> Sent: Wednesday, November 06, 2002 11:44 AM
> To: terry@ashtonwoodshomes.com
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] query optimization question
>
>
> On Wed, 6 Nov 2002 09:01:49 -0500
> <terry@ashtonwoodshomes.com> wrote:
>
> > If anyone can see a way to do a group by to do this, then I
> will be happy to
> > hear about it, because currently the resultset has to do a separate
> > (sequential or index) scan of the deficiencies table.  The
> only way I can
> > see to do a group by would be to break out the aging categories into
> > separate queries, but that wins me nothing because each
> query then does its
> > own scan...
> >
> > The expected simplified output of this query looks like this:
> > Project    <30     30-60     >=60    lot total    <30
> 30-60    >=60    def total
> > X    1    2    1    4    5    10    5
> 20    (if X had 4 lots, each of 5 deficiencies)
> > Y    1    1    0    2    3    3    0
> 6    (each has eg 3 deficiencies in project Y)
> >
>
>
> The following query may be one of the ways, but I cannot
> confirm whether
> it goes well or not.
>
>
> SELECT
>     project_id,
>     marketing_name,
>     COUNT(lots.lot_id) AS def_count,
>     COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
>                           THEN lots.lot_id ELSE NULL END) AS
> def_count_less_30,
>     COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
>                             AND dt.days_old_start_date < {d
> '2002-09-07'}
>                           THEN lots.lot_id ELSE NULL END) AS
> def_count_30_60,
>     COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-09-07'}
>                             AND dt.days_old_start_date < {d
> '2002-08-08'}
>                           THEN lots.lot_id ELSE NULL END) AS
> def_count_60_90,
>     COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-08-08'}
>                           THEN lots.lot_id ELSE NULL END) AS
> def_count_greater_90,
>     COUNT(DISTINCT(CASE WHEN
>                                    dt.days_old_start_date <
> {d '2002-10-07'}
>                           THEN lots.lot_id ELSE NULL END ))
> AS lot_count_less_30,
>    COUNT(DISTINCT(CASE WHEN
>                                    dt.days_old_start_date >=
> {d '2002-10-07'}
>                            AND dt.days_old_start_date < {d
> '2002-09-07'}
>                           THEN lots.lot_id ELSE NULL END ))
> AS lot_count_30_60,
>    COUNT(DISTINCT(CASE WHEN
>                                    dt.days_old_start_date >=
> {d '2002-09-07'}
>                            AND dt.days_old_start_date < {d
> '2002-08-08'}
>                           THEN lots.lot_id ELSE NULL END ))
> AS lot_count_60_90,
>    COUNT(DISTINCT(CASE WHEN
>                                   dt.days_old_start_date >=
> {d '2002-08-08'}
>                           THEN lots.lot_id ELSE NULL END ))
> AS lot_count_greater_90,
>    COUNT(DISTINCT lots.lot_id) AS lot_count
> FROM
>    (SELECT * FROM deficiency_table
>                WHERE assigned_supplier_id = '101690') AS dt,
>    (SELECT * FROM deficiency_status,
>                WHERE ds.is_outstanding) AS ds,
>    (SELECT * FROM projects
>                WHERE proj.division_id = 'GGH') AS proj
>    lots
> WHERE
>    dt.lot_id = lots.lot_id
>    AND lots.division_id = proj.division_id
>    AND lots.project_id = proj.project_id
>    AND dt.deficiency_status_id = ds.deficiency_status_id
>    AND NOT EXISTS
>           (SELECT 1 FROM menu_group_projects
>              WHERE menu_code = 'WA'
>                  AND division_id = proj.division_id
>                  AND project_id = proj.project_id
>                  AND status = 'I')
> ORDER BY proj.project_id
>
>
>
>
> Regards,
> Masaru Sugawara
>