主页 / dba / 问题 / 321270
Accepted
Moshe Katz
Asked: 2022-12-22 08:58:50 +0800 CST

同时聚合计数和全计数

  • 772

我有一张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;
数数 类型
2个 3个
2个 98 
SELECT 2

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

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

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

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

小提琴

postgresql

2 个回答

  1. Best Answer

    你想要的是ROLLUP：

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

  2. 我会为所有类型做一些固定值的简单联合

    SELECT type, COUNT(DISTINCT correlation_id)
FROM events
WHERE type IN (3, 5, 97, 98)
GROUP BY type
UNION
SELECT 0, COUNT(DISTINCT correlation_id)
FROM events
WHERE type IN (3, 5, 97, 98)
order by 1
    • 0

相关问题