From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: terry@ashtonwoodshomes.com
Cc: pgsql-sql@postgresql.org
Subject: Re: query optimization question
Date: Thu, 07 Nov 2002 01:44:25 +0900
Message-ID: <20021107014333.F0D0.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <002601c2859d$0d825480$2766f30a@development.greatgulfhomes.com>
References: <002601c2859d$0d825480$2766f30a@development.greatgulfhomes.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rk73@sea.plala.or.jp, terry@ashtonwoodshomes.com
Subject: Re: query optimization question
In-Reply-To: <20021107014333.F0D0.RK73@sea.plala.or.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox