All Forums Analytics
fox 2 posts Joined 07/06
20 Jul 2006
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

fox 2 posts Joined 07/06
20 Jul 2006

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 themselvesFor 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) )

DiEgoR 33 posts Joined 08/06
12 Aug 2006

if you want to have your information verbatim, then how about using something like SELECT city, state,AVG(population),STDDEV_SAMP (population)from tablenamegroup by 1,2

input output putput

dae 13 posts Joined 07/12
12 Jan 2015

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,
 
 

dnoeth 4628 posts Joined 11/04
12 Jan 2015

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.

Dieter

dae 13 posts Joined 07/12
13 Jan 2015

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 !
 
 

dnoeth 4628 posts Joined 11/04
13 Jan 2015

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.

Dieter

dae 13 posts Joined 07/12
13 Jan 2015

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 !

dnoeth 4628 posts Joined 11/04
13 Jan 2015

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 :-)

Dieter

You must sign in to leave a comment.