Digamos que tengo una tabla de orders con las siguientes filas:
ID Cost Date (timestamp) 1 100 2020-06-30 21:18:53.328386+00 2 45 2020-06-30 11:18:53.328386+00 3 200 2020-05-29 21:32:56.620174+00 4 20 2020-06-28 21:32:56.620174+00Y necesito una consulta que devuelva exactamente esto:
Month Year Costs 5 2020 {"1": 0, "2": 0, ..., "29": 200, "30": 200, "31": 200} 6 2020 {"1": 0, "2": 0, ..., "28": 20, "29": 20, "30": 165} Tenga en cuenta que la columna Costs tiene que ser un json con la clave siendo el día del mes y el valor siendo la suma acumulada de todos los días anteriores de ese mes. Sé que esta probablemente no sea una tarea que Postgres debería estar haciendo, pero tengo curiosidad por ver cuál es la solución (incluso si no es la más eficiente en entornos de producción)
Puede usar dos niveles de agregación y json_object_agg() :
select date_month, date_year, json_object_agg(date_day, cnt) costs from ( select extract(month from date) date_month, extract(year from date) date_year, extract(day from date) date_day, sum(sum(cost)) over( partition by extract(month from date), extract(year from date) order by extract(day from date) ) cnt from mytable group by 1, 2, 3 ) t group by date_month, date_year