В FB3 появились статистические функции
VAR_POP, VAR_SAMP, STDDEV_POP, STDDEV_SAMP, CORR, COVAR_POP, COVAR_SAMP
Описание в doc/README.statistical_functions.txt
и функции вычисления линейной регресии
REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT,
REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY
описаны в doc/README.regr_functions.txt
кстати то что IBE не знает их не так уж и безобидно. Попытался скомпилировать вот такую процедуру
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
CREATE OR ALTER PROCEDURE SP_INCORRECT_MEASURE (
CODE_DEPARTURE INTEGER)
RETURNS (
DEPARTURENAME VARCHAR(100),
BREEDNAME VARCHAR(30),
CODE_HORSE INTEGER,
GPB VARCHAR(15),
NAME VARCHAR(50),
BRAND VARCHAR(15),
BIRTHYEAR SMALLINT,
AGE NUMERIC(15,1),
MEASURENAME CHAR(14),
AVALUE NUMERIC(15,3),
AVG_AVALUE NUMERIC(15,3),
DIFF DOUBLE PRECISION,
SIGMA DOUBLE PRECISION)
AS
BEGIN
FOR
WITH T
AS (SELECT
M.CODE_HORSE AS CODE_HORSE,
'Высота в холке' AS MEASURENAME,
IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE) AS AGE,
M.HEIGHT_HORSE AS AVALUE,
AVG(M.HEIGHT_HORSE) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED) AS AVG_AVALUE,
(STDDEV_SAMP(M.HEIGHT_HORSE) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED) / AVG(M.HEIGHT_HORSE) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED)) AS SIGMA
FROM
MEASURE M
JOIN HORSE ON HORSE.CODE_HORSE = M.CODE_HORSE
WHERE HORSE.CODE_BREED + 0 > 0
AND M.HEIGHT_HORSE IS NOT NULL
UNION ALL
SELECT
M.CODE_HORSE AS CODE_HORSE,
'Косая длина' AS MEASURENAME,
IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE) AS AGE,
M.LENGTH_HORSE AS AVALUE,
AVG(M.LENGTH_HORSE) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED) AS AVG_AVALUE,
(STDDEV_SAMP(M.LENGTH_HORSE) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED) / AVG(M.LENGTH_HORSE) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED)) AS SIGMA
FROM
MEASURE M
JOIN HORSE ON HORSE.CODE_HORSE = M.CODE_HORSE
WHERE HORSE.CODE_BREED + 0 > 0
AND M.LENGTH_HORSE IS NOT NULL
UNION ALL
SELECT
M.CODE_HORSE AS CODE_HORSE,
'Обхват груди' AS MEASURENAME,
IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE) AS AGE,
M.CHESTAROUND AS AVALUE,
AVG(M.CHESTAROUND) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED) AS AVG_AVALUE,
(STDDEV_SAMP(M.CHESTAROUND) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED) / AVG(M.CHESTAROUND) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED)) AS SIGMA
FROM
MEASURE M
JOIN HORSE ON HORSE.CODE_HORSE = M.CODE_HORSE
WHERE HORSE.CODE_BREED + 0 > 0
AND M.CHESTAROUND IS NOT NULL
UNION ALL
SELECT
M.CODE_HORSE AS CODE_HORSE,
'Обхват пястья' AS MEASURENAME,
IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE) AS AGE,
M.WRISTAROUND AS AVALUE,
AVG(M.WRISTAROUND) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED) AS AVG_AVALUE,
(STDDEV_SAMP(M.WRISTAROUND) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED) / AVG(M.WRISTAROUND) OVER(PARTITION BY IIF(M.AGE > 100, M.AGE - EXTRACT(YEAR FROM HORSE.BIRTHDAY), M.AGE), HORSE.CODE_BREED)) AS SIGMA
FROM
MEASURE M
JOIN HORSE ON HORSE.CODE_HORSE = M.CODE_HORSE
WHERE HORSE.CODE_BREED + 0 > 0
AND M.WRISTAROUND IS NOT NULL)
SELECT
DEPARTURE.NAME AS DEPARTURENAME,
BREED.NAME AS BREEDNAME,
HORSE.CODE_HORSE AS CODE_HORSE,
COALESCE(HORSE.GPB_NEWNUMBER, HORSE.GPB_NUMBER) AS GPB,
HORSE.NAME AS NAME,
HORSE.BRAND AS BRAND,
EXTRACT(YEAR FROM HORSE.BIRTHDAY) AS BIRTHYEAR,
T.AGE AS AGE,
T.MEASURENAME AS MEASURENAME,
T.AVALUE AS AVALUE,
T.AVG_AVALUE AS AVG_AVALUE,
ABS(T.AVALUE - T.AVG_AVALUE) / T.AVG_AVALUE * 100 AS DIFF,
T.SIGMA * 100 AS SIGMA
FROM
T
JOIN HORSE ON T.CODE_HORSE = HORSE.CODE_HORSE
JOIN BREED ON BREED.CODE_BREED = HORSE.CODE_BREED
JOIN DEPARTURE ON DEPARTURE.CODE_DEPARTURE = HORSE.CODE_DEPARTURE
WHERE NOT(T.AVALUE BETWEEN T.AVG_AVALUE * (1 - 3 * T.SIGMA) AND T.AVG_AVALUE * (1 + 3 * T.SIGMA))
AND HORSE.CODE_DEPARTURE = :CODE_DEPARTURE
ORDER BY DEPARTURE.NAME, BREED.NAME, T.AGE, T.MEASURENAME
INTO :DEPARTURENAME,
:BREEDNAME,
:CODE_HORSE,
:GPB,
:NAME,
:BRAND,
:BIRTHYEAR,
:AGE,
:MEASURENAME,
:AVALUE,
:AVG_AVALUE,
:DIFF,
:SIGMA
DO
BEGIN
SUSPEND;
END
END
ругается
IBEFunction 'STDEV_SAMP' is not a window function, OVER clause cannot be used
естественно в ISQL всё пучком.
P.S. маленькая хотелка нельзя ли сделать возможность копировать текст ошибки из редактора процедур, то что там в нижнем гриде появляется? А то набирать текст в ручную лень |
