Tengo una consulta como la siguiente que toma min_date y max_date .
Select TO_CHAR(date_trunc('hour', table.date), 'YYYY-MM-DD HH24:00') as formatted_date, count(DISTINCT table.order_id) as unique_orders, sum(table.price) as total_price from table where table.date>=min_date and table.date<=max_date result = ActiveRecord::Base.connection.execute(query).values.to_aEsto me dará un recuento de los pedidos y el precio total por "hora". Por ejemplo, digamos que esta es mi mesa
<id: 1, order_id: 1, date: 2020-06-10 10:18:01+00, price:10> <id: 2, order_id: 1, date: 2020-06-10 10:43:19+00, price:14> <id: 3, order_id: 2, date: 2020-06-10 12:10:00+00, price:15> <id: 4, order_id: 3, date: 2020-06-10 12:20:21+00, price:10> <id: 5, order_id: 3, date: 2020-06-10 14:10:54+00, price:20> Y mi min_date = 2020-06-10 09:00:00+00 y max_date = 2020-06-10 14:00:00+00 . Este sería mi resultado:
[[2020-06-10 10:00, 1, 24], [2020-06-10 12:00, 2, 25], [2020-06-10 14:00, 1, 20]]Quiero que las horas que faltan entre min_date y max_date se incluyan así:
[[2020-06-10 09:00, 0, 0], [2020-06-10 10:00, 1, 24], [2020-06-10 11:00, 0, 0], [2020-06-10 12:00, 2, 25], [2020-06-10 13:00, 0, 0] [2020-06-10 14:00, 1, 20]] Así que también quiero todas las horas que no están presentes en la tabla pero que están incluidas en el rango de tiempo entre min_date y max_date. En este escenario, son todas las horas que quiero, pero pueden ser todas las 'semanas' o 'meses' según el argumento que pase a date_trunc .
Use generate_series() para calcular todas las fechas que desee. Luego use left join para traer los datos:
select to_char(gs.dte, 'YYYY-MM-DD HH24:00') as formatted_date, count(distinct t.order_id) as unique_orders, sum(t.price) as total_price from generate_series(min_date, max_date, interval '1 hour') gs(dte) left join table t on t.date >= gs.dte and t.date < gs.dte + interval '1 hour' and table.date<=max_date group by gs.dte order by gs.dte;