Module: ActiveMedian

Defined in:
lib/active_median.rb,
lib/active_median/version.rb

Constant Summary collapse

VERSION = 
"0.1.1"

Class Method Summary collapse

Class Method Details

.create_function ⇒ Object 



5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/active_median.rb', line 5

def self.create_function
  # create median method
  # http://wiki.postgresql.org/wiki/Aggregate_Median
  ActiveRecord::Base.connection.execute <<-SQL
    CREATE OR REPLACE FUNCTION median(anyarray)
       RETURNS float8 AS
    $$
      WITH q AS
      (
         SELECT val
         FROM unnest($1) val
         WHERE VAL IS NOT NULL
         ORDER BY 1
      ),
      cnt AS
      (
        SELECT COUNT(*) AS c FROM q
      )
      SELECT AVG(val)::float8
      FROM
      (
        SELECT val FROM q
        LIMIT  2 - MOD((SELECT c FROM cnt), 2)
        OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - 1,0)
      ) q2;
    $$
    LANGUAGE sql IMMUTABLE;

    DROP AGGREGATE IF EXISTS median(numeric);
    DROP AGGREGATE IF EXISTS median(double precision);
    DROP AGGREGATE IF EXISTS median(anyelement);
    CREATE AGGREGATE median(anyelement) (
      SFUNC=array_append,
      STYPE=anyarray,
      FINALFUNC=median,
      INITCOND='{}'
    );
  SQL
  true
end