standard deviation function with parition by

Analytics
fox
Fan

standard deviation function with parition by

I want to use the STDDEV_SAMP() with the OVER (Partition by) clause.

Teradata's standard deviation functions do not allow this. I want to calculate the standard deviation for each subset of data.

Any suggestions or workarounds would be greatly appreciated.

Thanks.

Kind of what I am looking to do:

SELECT city, state
,AVG(population) OVER (PARTITION BY city,state)
,STDDEV_SAMP (population) OVER (PARTITION BY city,state)
from tablename

8 REPLIES
fox
Fan

Re: standard deviation function with parition by

I found this post --

Although you can't use STDDEV as a window function, the calculations
for each of these involve functions that can be done with window
aggregates. So, another alternative can be to do the calculations behind the
STDDEV functions instead of the functions themselves

For STDDEV_SAMP:

sqrt ((
(count(x) over (partition by y,z)
* sum(x**2) over (partition by y,z))
- (sum(x) over (partition by y,z))**2)
/ (count(x) over (partition by y,z)
* count(x) over (partition by y,z) - 1)
)
Enthusiast

Re: standard deviation function with parition by

if you want to have your information verbatim, then how about using something like

SELECT city, state
,AVG(population)
,STDDEV_SAMP (population)
from tablename
group by 1,2
dae
Enthusiast

Re: standard deviation function with parition by

Hello,

do you want me to confirm that the function STDDEV_SAMP is running in combination with OLAP syntax ? I use TD V14.10 and I get an error code 3704 when I try to use it the way below:

                 , STDDEV_SAMP (CONSO_JOUR) OVER(

                                                  PARTITION BY  POM

                                                  ORDER BY      DATE_MSR

                                                  ROWS BETWEEN  28 PRECEDING AND CURRENT ROW

                                                )                                               AS STD_CONSO

Thanks a lot,

Senior Apprentice

Re: standard deviation function with parition by

Of course it's working, what's your exact error message?

"is not a valid Teradata SQL token" indicates some strange character in your source code.

dae
Enthusiast

Re: standard deviation function with parition by

Dieter,

thanks for your email.

The error seems specific to ODBC - treatment executed via SQLA - and not found using BTEQ for example.

If I consider the treatment, the function STDDEV_SAMP is applied over a derived table "T" (cf. below):

SELECT             POM

                 , PRM

                 , PS

                 , DATE_MSR

                 , TARIF

                 , LIB_TARIF

                 , CODE_INSEE

                 , NOM_VOIE

                 , PROPART

                 , DPT

                 , CONSO_JOUR                                                                   AS CONSO_JOUR_TOT

                 , CONSO_SEM

                 , CONSO_2SEM                                                                   AS CONSO_QUATORZ

                   -- CONSO ARRONDIE À 100 PRÈS, UTILE POUR FAIRE DES REGROUPEMENTS PAR CONSO PRÉCÉDENTES

                 , ROUND( (CONSO_SEM_PREC+50)/100)*100                                          AS CONSO_ARRONDIE_SEM_PREC

                 , ROUND( (CONSO_2SEM_PREC+50)/100)*100                                         AS CONSO_ARRONDIE_QUATORZ_PREC

                   -- STATS CONSO CLIENT

                 , PERCENT_RANK () OVER(

                                         PARTITION BY POM

                                         ORDER BY     CONSO_JOUR

                                       )                                                        AS PERCENT_CONSO

                 , STDDEV_SAMP (CONSO_JOUR) OVER(

                                                  PARTITION BY  POM

                                                  ORDER BY      DATE_MSR

                                                  ROWS BETWEEN  28 PRECEDING AND CURRENT ROW

                                                )                                               AS STD_CONSO

FROM              (

                    SELECT             POM

                                   ...

                  ) T

If I create a working physical table and feed it with the SQL used in the derived table, the treatment is OK !

Senior Apprentice

Re: standard deviation function with parition by

What's the exact error message?

Can you extract the actual SQL submitted from DBQL, maybe it was modified by the ODBC driver.

Try if checking "Disable Parsing" in the ODBC datasource options helps.

dae
Enthusiast

Re: standard deviation function with parition by

Dieter, you're right => disabling the parsing allowed to execute the treatment.

I understand the parsing of the STDDEV_SAMP by the ODBC driver is not correct, right ?

Thanks again for your help !

Senior Apprentice

Re: standard deviation function with parition by

I don't think that the ODBC driver changed the STDDEV_SAMP, but maybe a calculation of a column used within, that's why I was asking for the exact error message and checking DBQL :-)