Как написать сложный запрос Postgresql с date_trunc, sum, group by и generate_series?

Question

[cinic]

Имеется таблица table1 со следующей схемой:

create_table "table1", force: true do |t|
    t.datetime "time"
    t.string   "duration"
    t.string   "type"
    t.integer  "device_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "table1", ["device_id"], name: "index_table1_on_device_id", using: :btree
  add_index "table1", ["time"], name: "index_table1_on_time", using: :btree

в неё пишется время срабатывания датчика, тип срабатывания и продолжительность между текущим и предыдущим срабатыванием.

Необходимо получить сгенерированную по минутам серию, даже если значения time за определённое отсутствуют. Плюс группировать значения type и duration, чтобы на выходе получить коллекцию:

{"minute"=>"2015-01-16 17:16:00", "duration_norm"=>182.0, "duration_idle"=>80.0, "norm"=>6, "idle"=>2},
{"minute"=>"2015-01-16 17:17:00", "duration_norm"=>282.0, "duration_idle"=>180.0, "norm"=>10, "idle"=>4},
...
{"minute"=>"2015-01-16 17:18:00", "duration_norm"=>102.0, "duration_idle"=>40.0, "norm"=>4, "idle"=>1}

Всё что смог из себя выжать это:

SELECT date_trunc('minute', clamps.time) AS minute, (case when type = 'norm' then duration end) as type_norm, (case when type = 'idle' then duration end) as type_idle FROM clamps GROUP BY minute,type,duration ORDER BY minute ASC

Answer 1

[Amver]

Не совсем понял почему "duration" типа "string", но если там числовые значения, то полагаю нужно нечто подобное должно дать желаемый результат:

SELECT tm "minute",
	sum(CASE WHEN type = 'norm' then duration::real ELSE 0 END) "duration_norm",
	sum(CASE WHEN type = 'idle' then duration::real ELSE 0 END) "duration_idle",
	sum(CASE WHEN type = 'norm' then 1 ELSE 0 END) "norm",
	sum(CASE WHEN type = 'idle' then 1 ELSE 0 END) "idle"
	
FROM generate_series('2015-01-16 17:16:00'::timestamp, '2015-01-16 18:18:00', '1 minute') tm
left join table1 t1 ON date_trunc('minute', t1.time) = tm
group by tm
ORDER BY tm