|
|
Archives of the TeradataForum
Message Posted: Thu, 19 Feb 2009 @ 21:44:25 GMT
Subj: | | Re: Too much time spent on aggregate function |
|
From: | | Dieter Noeth |
Simard Rudel wrote:
| I try to have more performance with this kind of SQL model. We spend too much time to get the 'last group of service', the 'last state' and
the 'first service' information. | |
If this i done to return additional columns for the 'last' row, then RANK/ROW_NUMBER is probably the fastest version, e.g.:
SELECT * FROM lineitem
WHERE (l_partkey, l_shipdate, l_quantity) IN
(SELECT l_partkey, l_shipdate, MAX(l_quantity)
FROM lineitem l1
GROUP BY 1,2);
is rewritten as:
SELECT * FROM lineitem
QUALIFY
RANK() OVER
(PARTITION BY l_partkey, l_shipdate
ORDER BY l_quantity DESC) = 1;
> > ---------- Last group of service ----------
> >
> > LEFT JOIN ((SELECT A.NO,A.NO_ACTIV,A.NO_ASK,
> > MAX(R.NO_REGR_MES) NO_LAST_GROUP_SERV
> > <========== aggregate operation
> > FROM ........
> > GROUP BY 1,2,3) AS TNO_REGR
> > <========== derive table
> >
> > INNER JOIN DB_NAME.TREGR_MES ON
> > .........
> > AND TNO_REGR.NO_LAST_GROUP_SERV =
> > TREGR_MES.NO_REGR_MES)
> >
> > ON .....
qualify
rank() over
(partition by A.NO,A.NO_ACTIV,A.NO_ASK
order by R.NO_REGR_MES desc) = 1
If NO_REGR_MES is not unique you might have to use ROW_NUMBER, but RANK might have better performance.
Dieter
| |