Ruby ActiveRecord group and average "syntax error at or near "AS""

Question

params = {device_id: "u100", device_point: "1", point_no: "1", max_date: "2016-12-01 12:00", min_date: "2016-12-01 11:40"}

I am working in a ruby/rails environment with a PG database.

I am trying to add to my device controller to use ActiveRecord to get 10 minute averages from a some dummy sensor data.
I can group by minute fine with;

.group("date_trunc('minute', device_time)").average('point_data_val')

e.g

device_data = DeviceDatum.select('device_time, point_data_val').filter(params.slice(:device_id, :device_point, :point_no, :iot_version, :iot_time, :device_time, :point_data_type, :point_data_val, :point_bat_val,:min_val, :max_val, :min_date, :max_date)).limit(1008).group("date_trunc('minute', device_time)").average('point_data_val')
   (240.3ms)  SELECT  AVG("device_data"."point_data_val") AS average_point_data_val, date_trunc('minute', device_time) AS date_trunc_minute_device_time FROM "device_data" WHERE "device_data"."device_id" = $1 AND "device_data"."device_point" = $2 AND "device_data"."point_no" = $3 AND (device_time >= '2016-12-01 11:40') AND (device_time <= '2016-12-01 12:00') GROUP BY date_trunc('minute', device_time) LIMIT 1008  [["device_id", "u100"], ["device_point", 1], ["point_no", 1]]
=> {2016-12-01 11:41:00 +0900=>#<BigDecimal:7f91599cfbc0,'-0.4816E1',18(27)>, 2016-12-01 11:51:00 +0900=>#<BigDecimal:7f91599cf8f0,'-0.4868E1',18(27)>}

I found this method to do it But when I try and use AS in the group method

DeviceDatum.select('device_time, point_data_val')
.filter(params.slice(:device_id, :device_point, :point_no, :iot_version, :iot_time, :device_time, 
:point_data_type, :point_data_val, :point_bat_val,:min_val, :max_val, :min_date, 
:max_date)).limit(1008)
.group("date_trunc('hour', device_time) AS hour_stump,
(extract(minute FROM device_time)::int / 10) 
AS min10_slot,count(*)").average('point_data_val')

I am getting this error.

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "AS"
LINE 1: ... 12:00') GROUP BY date_trunc('hour', device_time) AS hour_st... : SELECT  AVG("device_data"."point_data_val") AS average_point_data_val, date_trunc('hour', device_time) AS hour_stump,(extract(minute FROM device_time)::int / 10) AS min10_slot,count(*) AS date_trunc_hour_device_time_as_hour_stump_extract_minute_from_d FROM "device_data" WHERE "device_data"."device_id" = $1 AND "device_data"."device_point" = $2 AND "device_data"."point_no" = $3 AND (device_time >= '2016-12-01 11:40') AND (device_time <= '2016-12-01 12:00') GROUP BY date_trunc('hour', device_time) AS hour_stump,(extract(minute FROM device_time)::int / 10) AS min10_slot,count(*) LIMIT 1008
from ...../bundle/ruby/2.1.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/postgresql_adapter.rb:637:in `prepare'

ActiveRecord seems to be using the options from the group method in sql it generates for average as well.

 SELECT
   AVG("device_data"."point_data_val") AS average_point_data_val,
   date_trunc('hour', device_time) AS hour_stump,
   (
      extract(minute 
   FROM
      device_time)::int / 10
   )
   AS min10_slot,
   count(*) AS date_trunc_hour_device_time_as_hour_stump_extract_minute_from_d 
FROM
   "device_data" 
WHERE
   "device_data"."device_id" = $1 
   AND "device_data"."device_point" = $2 
   AND "device_data"."point_no" = $3 
   AND 
   (
      device_time >= '2016-12-01 11:40'
   )
   AND 
   (
      device_time <= '2016-12-01 12:00'
   )
GROUP BY
   date_trunc('hour', device_time) AS hour_stump,
   (
      extract(minute 
   FROM
      device_time)::int / 10
   )
   AS min10_slot,
   count(*) LIMIT 1008

What am I missing here?

Is what you're trying to do even possible in the `group by` clause? The question you're referring to does this trick in the select clause. — Eyeslandic, Mar 16 '17 at 10:48

score 0 · Accepted Answer · edited Mar 16 '17 at 13:30

0

I think that your group clause should look like this:

group("hour_stump, min10_slot,
       date_trunc_hour_device_time_as_hour_stump_extract_minute_from_d")

if you use aliases in select clause. As @Fallenhero and @Iceman said, aliases in group by are not allowed.

If aliases are substituted in select automatically, I think the only way is to use functions as is

group("date_trunc('hour', device_time),
(extract(minute FROM device_time)::int / 10), count(*)")

.

edited Mar 16 '17 at 13:30

Ajay Barot

1,681
1
21
37

answered Mar 16 '17 at 13:11

Maxim Khan-Magomedov

1,326
12
15

Thanks I went with: `group("to_char(date_trunc('hour', device_time), 'YYYY-MM-DD HH:') || to_char((extract(minutes FROM device_time)::int / 10*10), 'fm00')")` Which gives: `[["2016-12-01 11:00", #], ["2016-12-01 11:10", #], ["2016-12-01 11:20", #], ["2016-12-01 11:30", #], ["2016-12-01 11:40", #], ["2016-12-01 11:50", #]]` – Ivan Mar 17 '17 at 07:41
updated to `group("to_timestamp(to_char(date_trunc('hour', device_time), 'YYYY-MM-DD HH:') || (to_char((extract(minutes FROM device_time)::int / 10*10), 'fm00')), 'YYYY-MM-DD HH:MI')::timestamp without time zone").calculate(params[:func], 'point_data_val')` This format the output in a nice timestamp, and the '::time stamp without time zone' fixes the time being off by nine hours – Ivan Mar 24 '17 at 04:36

Ruby ActiveRecord group and average "syntax error at or near "AS""

1 Answers1