sql - 按天分组聚合

Question

我在 Postgres 数据库中有一个简单的表 log，看起来像这样(为示例进行了简化):

time::timestamptz
action::varchar

time                          | action
2017-10-16 17:14:49.473903-08 | 'some action'
2017-10-16 17:14:49.473903-08 | 'some other action'
2017-10-17 17:14:49.473903-08 | 'some action'
//etc.

有多种操作。我想查询此表以每天获取一行，其中一列包含每个操作的计数数组。这是我想要的结果:

day            actions
'2017-10-08' | [{"action":"some action", "count":10}, 
             |  {"action":"some other action", "count":20}}]
'2017-10-09' | [{"action":"some action", "count":15}, 
             |  {"action":"some other action", "count":18}}]

我几乎可以做到这一点:

SELECT day, json_agg(act) as actions
FROM (
    SELECT action, time::date as day,  COUNT(*)
    FROM log_hits
    GROUP BY (action, day)
) act
GROUP BY day

当然，这会导致日期出现在 Action 数组中的每个对象中......

day           actions
'2017-10-08' | [{"action":"some action", day:"2017-10-08", "count":10}, 
             |  {"action":"some other action",  day:"2017-10-08", "count":20}}]

…这是多余的(并且可能效率低下)。获得按天分组的结果的正确方法是什么，该天仅出现在其自己的列中并且操作仅针对该天进行聚合？

Answer 1

使用jsonb_build_object():

WITH log_hits (time, action) AS (
VALUES
    ('2017-10-16 17:14:49.473903-08'::timestamptz, 'some action'),
    ('2017-10-16 17:14:49.473903-08', 'some other action'),
    ('2017-10-17 17:14:49.473903-08', 'some action')
)

SELECT 
    day, 
    json_agg(jsonb_build_object('action', action, 'count', count)) as actions
FROM (
    SELECT action, time::date as day, COUNT(*)
    FROM log_hits
    GROUP BY (action, day)
) act
GROUP BY day;

    day     |                                       actions                                        
------------+--------------------------------------------------------------------------------------
 2017-10-17 | [{"count": 1, "action": "some action"}, {"count": 1, "action": "some other action"}]
 2017-10-18 | [{"count": 1, "action": "some action"}]
(2 rows)