Moshe Katz's questions

我有一张events这样的桌子：

create table events
(
    correlation_id char(26) not null,
    user_id        bigint,
    task_id        bigint not null,
    location_id    bigint,
    type           bigint not null,
    created_at     timestamp(6) with time zone not null,
    constraint events_correlation_id_created_at_user_id_unique
        unique (correlation_id, created_at, user_id)
);

CREATE TABLE

该表包含正在执行的任务的记录，如下所示：

insert into events (correlation_id, user_id, task_id, location_id, type, created_at)
values  ('01CN4HP4AN0000000000000001', 4, 58, 30, 0, '2018-08-17 18:17:15.348629+00'),
        ('01CN4HP4AN0000000000000001', 4, 58, 30, 1, '2018-08-17 18:17:22.852299+00'),
        ('01CN4HP4AN0000000000000001', 4, 58, 30, 99, '2018-08-17 18:17:25.535593+00'),
        ('01CN4J9SZ80000000000000003', 4, 97, 30, 0, '2018-08-17 18:28:00.104093+00'),
        ('01CN4J9SZ80000000000000003', 4, 97, 30, 98, '2018-08-17 18:28:49.04584+00'),
        ('01CN4J9SZ80000000000000003', 4, 97, 30, 99, '2018-08-17 18:29:09.01684+00'),
        ('01CN4JC1430000000000000004', 4, 99, 30, 0, '2018-08-17 18:29:12.963264+00'),
        ('01CN4JC1430000000000000004', 4, 99, 30, 3, '2018-08-17 18:29:47.83452+00'),
        ('01CN4JC1430000000000000004', 4, 99, 30, 98, '2018-08-17 18:31:01.86342+00'),
        ('01CN4JC1430000000000000004', 4, 99, 30, 99, '2018-08-17 18:32:09.272632+00'),
        ('01CN4KJCDY0000000000000005', 139, 97, 30, 0, '2018-08-17 18:50:09.725668+00'),
        ('01CN4KJCDY0000000000000005', 139, 97, 30, 3, '2018-08-17 18:50:11.842+00'),
        ('01CN4KJCDY0000000000000005', 139, 97, 30, 99, '2018-08-17 18:51:42.240895+00'),
        ('01CNC4G1Y40000000000000008', 139, 99, 30, 0, '2018-08-20 17:00:40.26043+00'),
        ('01CNC4G1Y40000000000000008', 139, 99, 30, 99, '2018-08-20 17:00:47.583501+00');

INSERT 0 15

该列的某些值type表示紧急情况。我可以使用这个简单的查询来计算每种类型的紧急情况在执行过程中发生了多少任务：

SELECT COUNT(DISTINCT correlation_id), type
FROM events
WHERE type IN (3, 5, 97, 98)
GROUP BY type;

SELECT 2

我还可以使用这个简单的查询来计算在执行过程中有多少任务发生了任何类型的紧急情况：

SELECT COUNT(DISTINCT correlation_id)
FROM events
WHERE type IN (3, 5, 97, 98);

SELECT 1

问题是：有没有办法将这两个查询结合起来，以便我可以在单个查询中获得总数和按类型细分？

我不能只将count列相加，因为一个correlation_id值可以有多个与之关联的紧急类型。在上面的示例中，有三个correlation_id紧急情况的总值，但添加该count列将达到四个。

小提琴

db<>fiddle用于下面的所有数据和查询

我有一个events具有以下结构的表：

create table events (
    correlation_id char(26) not null,
    user_id        bigint,
    task_id        bigint not null,
    location_id    bigint,
    type           bigint not null,
    created_at     timestamp(6) with time zone not null,
    constraint events_correlation_id_created_at_user_id_unique
        unique (correlation_id, created_at, user_id)
);

此表包含正在执行的任务的记录，如下所示：

带有 的行type = 0表示任务的开始，带有 的行type = 99表示任务的结束。（其他值表示与此问题无关的其他内容，但为了完整起见，此处包含两个示例行。）

每个task_id对应于tasks表中的一行。任务表中唯一与该问题相关的其他字段称为inprogress_status，它可以是1or 2，分别表示Opening task和Closing task。

我最初被要求提供一个查询，该查询将返回按开始日期和位置排序的任务列表，其中一行包含每个任务的开始 ( type = 0) 和结束 ( type = 99)。

这是我曾经这样做的查询：

SELECT e.created_at::DATE, e.location_id, e.task_id
     , CASE t.inprogress_status WHEN 2 THEN 'CLOSE' WHEN 1 THEN 'OPEN' END AS task_type
     , e.correlation_id
     , json_object_agg(e.type, json_build_object('timestamp', e.created_at, 'user_id', e.user_id)) AS events
FROM events e
JOIN tasks t on e.task_id = t.id
WHERE e.type IN (0, 99)
AND t.inprogress_status IN (1, 2)
group by created_at::DATE, location_id, task_id, correlation_id, inprogress_status
ORDER BY 1, 2, 3;

这是使用上面显示的数据进行查询的结果：

在上面的例子中，task_id 58and 99haveinprogress_status = 1和task_id 97has inprogress_status = 2。

现在我被要求修改返回的数据结构，以便它也可以聚合inprogress_status，并将行作为 OPEN+CLOSE 事件对返回。

为了弄清楚如何构建它，我首先尝试获取这种格式（我真正想要的最终格式如下）：

这是我写的第一个查询，试图使这项工作：

WITH grouped_events AS (
    SELECT e.created_at::DATE AS created_date,
        location_id,
        task_id,
        CASE t.inprogress_status WHEN 2 THEN 'CLOSE' WHEN 1 THEN 'OPEN' END AS task_type,
        jsonb_build_object('id', e.correlation_id) ||
                jsonb_object_agg(type, jsonb_build_object('timestamp', e.created_at, 'user_id', user_id)) AS events
    FROM events e
    JOIN tasks t on e.task_id = t.id
    WHERE type IN (0, 99)
    AND inprogress_status IN (1, 2)
    GROUP BY e.created_at::DATE, location_id, task_id, correlation_id, t.inprogress_status
)
SELECT created_date, location_id, json_object_agg(task_type, events)
FROM grouped_events
GROUP BY 1, 2
ORDER BY 1, 2

问题是这会产生无效的 JSON。具有多个相同的键：

{
    "OPEN": {
        "0": { "user_id": 4, "timestamp": "2018-08-17T18:29:12.963264+00:00" },
        "99": { "user_id": 4, "timestamp": "2018-08-17T18:32:09.272632+00:00" },
        "id": "01CN4JC1430000000000000004"
    },
    "OPEN": {
        "0": { "user_id": 4, "timestamp": "2018-08-17T18:17:15.348629+00:00" },
        "99": { "user_id": 4, "timestamp": "2018-08-17T18:17:25.535593+00:00" },
        "id": "01CN4HP4AN0000000000000001"
    },
    // ... etc.
}

我发现这个查询以上面显示的格式返回数据：

WITH grouped_events1 AS (
    SELECT e.created_at::DATE AS created_date,
        location_id,
        task_id,
        CASE t.inprogress_status WHEN 2 THEN 'CLOSE' WHEN 1 THEN 'OPEN' END AS task_type,
        jsonb_build_object('id', e.correlation_id) ||
                jsonb_object_agg(type, jsonb_build_object('timestamp', e.created_at, 'user_id', user_id)) AS events
    FROM events e
    JOIN tasks t on e.task_id = t.id
    WHERE type IN (0, 99)
    AND inprogress_status IN (1, 2)
    GROUP BY e.created_at::DATE, location_id, task_id, correlation_id, t.inprogress_status
), grouped_events2 AS (
    SELECT created_date, location_id, task_type, json_agg(events) AS events
    FROM grouped_events1
    GROUP BY 1, 2, 3
)
SELECT created_date, location_id, json_object_agg(task_type, events)
FROM grouped_events2
GROUP BY 1, 2
ORDER BY 1, 2

但是，我实际需要的格式应该只是将单个 OPEN 与单个 CLOSE 配对，如下所示（每个 OPEN 和紧随其后的 CLOSE）：

现在我想弄清楚我是否走错了方向，因为我看不出如何从我所拥有的东西中得到我的最终格式。

我接近这个错误吗？我怎样才能得到我正在寻找的结果？