Question: I
need assistance understanding the SQL
regr_slope built-in function. Can you give an example
of regr_slope?Answer:
(by Steve Callan)
The general equation of a line is
y = mx + b. In English, that means "Y" is equal to the slope ("m")
times "X" plus a constant "b." That constant is actually called
the y-intercept (if the input of x = 0, then the output of y = b,
or that is where the line crosses the Y axis). The y-intercept may
or may not have any real meaning. The slope is important because
it shows the rate of change between X and Y, and the sign of the
slope reflects the direction of the relationship (a positive slope
shows that as X increases, so does Y; a negative slope shows that
as X increases, Y decreases).
The example shown in the SQL
Reference guide has five channel_id's, but we will concentrate on
the first one (identified as "C"). Here is the select statement
and its output as shown in a SQL*Plus session as the user named SH:
SQL> SELECT
2 s.channel_id,
3
REGR_SLOPE(s.quantity_sold, p.prod_list_price) SLOPE ,
4 REGR_INTERCEPT(s.quantity_sold, p.prod_list_price) INTCPT ,
5 REGR_R2(s.quantity_sold, p.prod_list_price) RSQR ,
6 REGR_COUNT(s.quantity_sold, p.prod_list_price) COUNT ,
7 REGR_AVGX(s.quantity_sold, p.prod_list_price) AVGLISTP ,
8 REGR_AVGY(s.quantity_sold, p.prod_list_price) AVGQSOLD
9 FROM sales s, products p
10 WHERE s.prod_id=p.prod_id AND
11 p.prod_category='Men' AND
12 s.time_id=to_DATE('10-OCT-2000')
13 GROUP BY s.channel_id;
C SLOPE INTCPT RSQR COUNT AVGLISTP AVGQSOLD
- ---------- ---------- ---------- ------ ---------- ----------
C -.0683687 16.627808 .051342581 20 65.495 12.15
I .019710295 14.8113924 .001631488 46 51.4804348 15.826087
P -.01247359 12.854546 .017039788 30 81.87 11.8333333
S .006155886 13.9919243 .000898438 83 69.813253 14.4216867
T -.00411314 5.22717214 .008132242 27 82.2444444 4.88888889
Let's add another AND to the WHERE
clause to get just the first row:
SQL> SELECT
2 s.channel_id,
3
REGR_SLOPE(s.quantity_sold, p.prod_list_price) SLOPE ,
4 REGR_INTERCEPT(s.quantity_sold, p.prod_list_price) INTCPT ,
5 REGR_R2(s.quantity_sold, p.prod_list_price) RSQR ,
6 REGR_COUNT(s.quantity_sold, p.prod_list_price) COUNT ,
7 REGR_AVGX(s.quantity_sold, p.prod_list_price) AVGLISTP ,
8 REGR_AVGY(s.quantity_sold, p.prod_list_price) AVGQSOLD
9 FROM sales s, products p
10 WHERE s.prod_id=p.prod_id AND
11 p.prod_category='Men' AND
12 s.time_id=to_DATE('10-OCT-2000')
13 AND s.channel_id = 'C'
14 group by s.channel_id;
C SLOPE INTCPT RSQR COUNT AVGLISTP AVGQSOLD
- ---------- ---------- ---------- ------ ---------- ----------
C -.0683687 16.627808 .051342581 20 65.495 12.15
.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
