• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 662
  • Last Modified:

DB2 function for truncated mean?

Hi,

Is there a function that will compute the truncated mean?  
http://en.wikipedia.org/wiki/Truncated_mean

Thanks.
0
pw05
Asked:
pw05
2 Solutions
 
srielauCommented:
This works on DB2 V8 for LUW.

CREATE TABLE competition(name VARCHAR(10), result INTEGER);
INSERT INTO competition VALUES
('Joe', 12),
('Joe', 10),
('Joe', 8),
('Joe', 15),
('Jill', 20),
('Jill', 17),
('Jill', 21);

SELECT name, AVG(result)
  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY result) AS rn,
                         COUNT(1) OVER(PARTITION BY name) AS cnt,
                         name, result
               FROM competition) AS X
  WHERE rn BETWEEN 2 AND cnt - 1
GROUP BY name;

NAME       2
---------- -----------
Jill                20
Joe                 11

  2 record(s) selected.

The can be eanhance to cut of more than 1 row or using %.
As it is the minimum rows required is 3.
   
           
0
 
sachinwadhwaCommented:
look at DB2 cook book at http://mysite.verizon.net/Graeme_Birchall/id1.html

see fun with sql, u will find many functions like this...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now