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