http://sqle.ru/index.php?action=history&feed=atom&title=Oracle_PL%2FSQL%2FAnalytical_Functions%2FREGR_COUNT
Oracle PL/SQL/Analytical Functions/REGR COUNT - История изменений
2026-04-04T10:17:14Z
История изменений этой страницы в вики
MediaWiki 1.30.0
http://sqle.ru/index.php?title=Oracle_PL/SQL/Analytical_Functions/REGR_COUNT&diff=1422&oldid=prev
в 13:45, 26 мая 2010
2010-05-26T13:45:19Z
<p></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<tr style="vertical-align: top;" lang="ru">
<td colspan="1" style="background-color: white; color:black; text-align: center;">← Предыдущая</td>
<td colspan="1" style="background-color: white; color:black; text-align: center;">Версия 13:45, 26 мая 2010</td>
</tr><tr><td colspan="2" style="text-align: center;" lang="ru"><div class="mw-diff-empty">(нет различий)</div>
</td></tr></table>
http://sqle.ru/index.php?title=Oracle_PL/SQL/Analytical_Functions/REGR_COUNT&diff=1423&oldid=prev
Admin: 1 версия
2010-05-26T09:55:59Z
<p>1 версия</p>
<p><b>Новая страница</b></p><div>==REGR_COUNT(expr1, expr2): For the number of observations==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL> create table TestTable (<br />
2 x number primary key,<br />
3 y number<br />
4 );<br />
Table created.<br />
SQL> insert into TestTable values (1, 7 );<br />
1 row created.<br />
SQL> insert into TestTable values (2, 1 );<br />
1 row created.<br />
SQL> insert into TestTable values (3, 2 );<br />
1 row created.<br />
SQL> insert into TestTable values (4, 5 );<br />
1 row created.<br />
SQL> insert into TestTable values (5, 7 );<br />
1 row created.<br />
SQL> insert into TestTable values (6, 34 );<br />
1 row created.<br />
SQL> insert into TestTable values (7, 32 );<br />
1 row created.<br />
SQL> insert into TestTable values (8, 43 );<br />
1 row created.<br />
SQL> insert into TestTable values (9, 87 );<br />
1 row created.<br />
SQL><br />
SQL> select * from TestTable;<br />
X Y<br />
---------- ----------<br />
1 7<br />
2 1<br />
3 2<br />
4 5<br />
5 7<br />
6 34<br />
7 32<br />
8 43<br />
9 87<br />
9 rows selected.<br />
SQL><br />
SQL> -- REGR_COUNT(expr1, expr2): For the number of observations<br />
SQL><br />
SQL> --REGR_COUNT(expr1, expr2)<br />
SQL><br />
SQL> SELECT REGR_COUNT(y, x) FROM TestTable;<br />
REGR_COUNT(Y,X)<br />
---------------<br />
9<br />
SQL><br />
SQL> drop table TestTable;<br />
Table dropped.<br />
SQL><br />
SQL><br />
SQL><br />
<br />
</source><br />
<br />
<br />
<br />
<br />
==REGR_COUNT(y, x):Returns the number of non-null number pairs that are used to fit the regression line==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL> CREATE TABLE all_sales (<br />
2 year INTEGER,<br />
3 month INTEGER,<br />
4 prd_type_id INTEGER,<br />
5 emp_id INTEGER ,<br />
6 amount NUMBER(8, 2)<br />
7 );<br />
Table created.<br />
SQL><br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,1 ,1 ,21 ,16034.84);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,2 ,1 ,21 ,15644.65);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,3 ,2 ,21 ,20167.83);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,4 ,2 ,21 ,25056.45);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,5 ,2 ,21 ,NULL);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,6 ,1 ,21 ,15564.66);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,7 ,1 ,21 ,15644.65);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,8 ,1 ,21 ,16434.82);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,9 ,1 ,21 ,19654.57);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,10 ,1 ,21 ,21764.19);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,11 ,1 ,21 ,13026.73);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2006,12 ,2 ,21 ,10034.64);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,1 ,2 ,22 ,16634.84);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,1 ,2 ,21 ,26034.84);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,2 ,1 ,21 ,12644.65);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,3 ,1 ,21 ,NULL);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,4 ,1 ,21 ,25026.45);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,5 ,1 ,21 ,17212.66);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,6 ,1 ,21 ,15564.26);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,7 ,2 ,21 ,62654.82);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,8 ,2 ,21 ,26434.82);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,9 ,2 ,21 ,15644.65);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,10 ,2 ,21 ,21264.19);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,11 ,1 ,21 ,13026.73);<br />
1 row created.<br />
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)<br />
2 values(2005,12 ,1 ,21 ,10032.64);<br />
1 row created.<br />
SQL><br />
SQL> select * from all_sales;<br />
YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT<br />
---------- ---------- ----------- ---------- ----------<br />
2006 1 1 21 16034.84<br />
2006 2 1 21 15644.65<br />
2006 3 2 21 20167.83<br />
2006 4 2 21 25056.45<br />
2006 5 2 21<br />
2006 6 1 21 15564.66<br />
2006 7 1 21 15644.65<br />
2006 8 1 21 16434.82<br />
2006 9 1 21 19654.57<br />
2006 10 1 21 21764.19<br />
2006 11 1 21 13026.73<br />
2006 12 2 21 10034.64<br />
2005 1 2 22 16634.84<br />
2005 1 2 21 26034.84<br />
2005 2 1 21 12644.65<br />
2005 3 1 21<br />
2005 4 1 21 25026.45<br />
2005 5 1 21 17212.66<br />
2005 6 1 21 15564.26<br />
2005 7 2 21 62654.82<br />
2005 8 2 21 26434.82<br />
2005 9 2 21 15644.65<br />
2005 10 2 21 21264.19<br />
2005 11 1 21 13026.73<br />
2005 12 1 21 10032.64<br />
25 rows selected.<br />
SQL><br />
SQL><br />
SQL> --REGR_COUNT(y, x):Returns the number of non-null number pairs that are used to fit the regression line.<br />
SQL> -- y is interpreted by the functions as a variable that depends on x.<br />
SQL> SELECT<br />
2 prd_type_id,<br />
3 REGR_COUNT(amount, month) AS count<br />
4 FROM all_sales<br />
5 GROUP BY prd_type_id;<br />
PRD_TYPE_ID COUNT<br />
----------- ----------<br />
1 14<br />
2 9<br />
SQL><br />
SQL><br />
SQL> drop table all_sales;<br />
Table dropped.<br />
SQL><br />
SQL><br />
<br />
</source></div>
Admin