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
SQL Query - Sum(Case When X Then 1 Else 0) for Multiple Columns
Best Practices for Inserting/Updating Large Amount of Data in SQL Server 2008
Should a Composite Primary Key Be Clustered in SQL Server
Spark Dataframe Nested Case When Statement
SQL Insert into with Subquery and Value
Native Insert Query in Hibernate + Spring Data
How to Use a SQL Select Statement with Access Vba
SQL Recursion Without Recursion
Insert into a Row at Specific Position into SQL Server Table with Pk
How to Create Xml Schema from an Existing Database in SQL Server 2008
Java.Sql.Sqlexception: Ora-03115: Unsupported Network Datatype or Representation
Group by Every N Records in T-Sql
Get the Number of Affected Rows in a MySQL Update Statement
How to Determine the Primary Key for a Table in SQL Server
Complicated SQL Query--Finding Items Matching Multiple Different Foreign Keys
Splitting Variable Length Delimited String Across Multiple Rows (Sql)