Pass Extra Parameter to Postgresql Aggregate Final Function

Custom Aggregate in postgres using multiple columns

The function _gaussian_density() should depend on the value calculated in the previous step. If in your case this is the first argument weight then the initial condition should not be 0, as all next calculations will give zero as a result. I assume that the initial value of weight is 1.0:

DROP AGGREGATE weighted_density(DOUBLE PRECISION, DOUBLE PRECISION);
CREATE AGGREGATE weighted_density(DOUBLE PRECISION, DOUBLE PRECISION)
(
sfunc = _gaussian_density,
stype = DOUBLE PRECISION,
initcond = 1.0 -- !!
);

Note that the aggregate does not use the column weight of the table as it is internal-state value, for which only the initial condition should be declared and which is returned as a final result.

SELECT
idx, time, category,
weighted_density(distance, 10000) AS wd -- !!
FROM my_table
GROUP BY idx, time, category
ORDER BY idx, time, category;

idx | time | category | wd
-----+---------------------+----------+---------------------
1 | 2017-01-01 00:00:00 | class_a | 0.476331421206002
1 | 2017-01-01 00:00:00 | class_b | 0.968750868953701
1 | 2017-01-01 00:13:00 | class_a | 0.69665860026144
1 | 2017-01-01 00:13:00 | class_b | 0.952383202706387
2 | 2017-01-01 00:00:00 | class_a | 0.00519142111518706
2 | 2017-01-27 21:07:00 | class_b | 0.893107967346503
(6 rows)

I am not sure I have correctly read your intentions, however my remarks should put you on the right path.

Rails Postgres Error GROUP BY clause or be used in an aggregate function

I guest you want to show general number of distinct Missions related with Commits, anyway it won't be number on page.

Try this:

@commits = @user.commits.order("updated_at DESC").page(params[:page]).per(25)
@missions_commits = @user.commits.distinct.count(:mission_id)

However if you want to get the number of distinct Missions on page I suppose it should be:

@missions_commits = @commits.collect(&:mission_id).uniq.count

Update

In Rails 3, distinct did not exist, but pure SQL counting should be used this way:

@missions_commits = @user.commits.count(:mission_id, distinct: true)


Related Topics



Leave a reply



Submit