Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 28 Nov 2002 @ 09:41:01 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: MEDIAN - using SQL
 
From:   Dieter N�th

  This is for financial median, for statistical median use only one of the where-conditions:  


  lower value: row_num = (row_count + 1) / 2
higher value: row_num = (row_count / 2) + 1
 



Of course for statistical median you don't need AVG and GROUP BY:

select
  dt1.med_group
  ,med_value
from
  (select
     med_group
     ,med_value
     ,sum(1) over (partition by med_group
                   order by med_value
                   rows unbounded preceding) as row_num
     ,count(*) over (partition by med_group
                     rows between unbounded preceding
                          and unbounded following) as row_count
   from test) as dt1
where
  row_num = (row_count + 1) / 2 --left (lesser) value
/*** r    ow_num = (row_count / 2) + 1 --right (greater) value ***/
order by med_group
;

Dieter



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023