Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: basic question about analytic functions
Well, I may ask you where you heard that from. Analytical functions
provide "windowing" capability that is either difficult or impossible
to achieve with regular SQL. I would suggest that you read up on the
ORACLE DOC and see what it is all about. With analytical functions you
can calculate aggregates, such as SUM, in many different ways. You can
even massage it so that it gives you the same output as your regular
aggregate SQLs. But it really serves no purpose.
udo456_at_yahoo.com (Udo Diekmann) wrote in message news:<5757f268.0302191451.3e77c9a4_at_posting.google.com>...
> I have heard that analytic functions can give a performance boost and want
> to actually time a standard query versus an analytic query which returns
> the exact same data. Thus the need for the same query as an analytic
> function query. So all I want to do is to translate the query using
> analytic functions and see if it's faster.
>
> JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0302181557.126a1de6_at_posting.google.com>...
> > Why converting? Is you query not giving you the results you want? If
> > it is, there is no reason to convert. If it is not, then explain what
> > you need and people will be able to help you. Analytical functions
> > give you particular fomat of output which may or may not be what you
> > are after. It is neat but not a magic bullet.
> >
> >
> > - Jusung Yang
> >
> >
> > udo456_at_yahoo.com (Udo Diekmann) wrote in message news:<5757f268.0302181059.7b98c9d9_at_posting.google.com>...
> > > I got a basic query that I need to turn into a analytic function query.
> > >
> > > Can someone help me translate it? I tried several ideas (using over() etc.
> > > but I'm no guru at this and have failed). I'm sure it's simple.
> > >
> > > Here is a normal query that I need to translate as an analytic function query:
> > >
> > > select dev1.sales_div_no, dev1.sales_div_name,
> > > sum(fact.t_total_sales_units) "T UNITS",
> > > sum(fact.l_total_sales_units) "L UNITS"
> > > from dim_dev1_plan dev1, fact_weekly_plan fact
> > > where dev1.dev1_id=fact.dev1_id
> > > and fact.per_id between 440 and 540
> > > and dev1.sales_div_no between 300 and 500
> > > group by dev1.sales_div_no, dev1.sales_div_name
> > > order by dev1.sales_div_no, dev1.sales_div_name;
> > >
> > > Thanks in advance for any assistance.
> > >
> > > Udo.
Received on Thu Feb 20 2003 - 00:05:53 CST