Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-20-2006
02:16 PM

07-20-2006
02:16 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-20-2006
04:50 PM

07-20-2006
04:50 PM

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)

)

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)

)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-12-2006
07:23 PM

08-12-2006
07:23 PM

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

SELECT city, state

,AVG(population)

,STDDEV_SAMP (population)

from tablename

group by 1,2

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-12-2015
02:54 AM

01-12-2015
02:54 AM

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,

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-12-2015
03:55 AM

01-12-2015
03:55 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-13-2015
05:19 AM

01-13-2015
05:19 AM

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 !

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-13-2015
06:27 AM

01-13-2015
06:27 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-13-2015
08:38 AM

01-13-2015
08:38 AM

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 !

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-13-2015
09:35 AM

01-13-2015
09:35 AM

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