问题计算SQL中的并发事件数

我有一个可以拨打电话的表，其中包含以下字段：

ID
开始时间
时间结束
状态
CALL_FROM
拨电至

在本地PostgreSQL数据库中加载了290万条记录。我在ID（唯一索引），starttime和endtime上添加了索引。

在stackoverflow上搜索，我找到了一些有用的SQL并将其修改为我认为逻辑应该工作的内容。问题是查询运行了很多个小时，从不返回：

SELECT T1.sid, count(*) as CountSimultaneous
FROM calls_nov T1, calls_nov T2
WHERE
     T1.StartTime between T2.StartTime and T2.EndTime
     and T1.StartTime between '2011-11-02' and '2011-11-03'
GROUP BY
     T1.sid
ORDER BY CountSimultaneous DESC;

有人可以建议一种方法来修复查询/索引，以便它实际工作或建议另一种方式来计算并发调用？

编辑：

解释计划：

Sort  (cost=11796758237.81..11796758679.47 rows=176663 width=35)
  Sort Key: (count(*))
  ->  GroupAggregate  (cost=0.00..11796738007.56 rows=176663 width=35)
        ->  Nested Loop  (cost=0.00..11511290152.45 rows=57089217697 width=35)

表创建脚本：

CREATE TABLE calls_nov (
  sid varchar,
  starttime timestamp, 
  endtime timestamp, 
  call_to varchar, 
  call_from varchar, 
  status varchar);

索引创建：

CREATE UNIQUE INDEX sid_unique_index on calls_nov (sid);

CREATE INDEX starttime_index on calls_nov (starttime);

CREATE INDEX endtime_index on calls_nov (endtime);

5085

2018-01-04 20:43

起源

T1和T2是一样的？ - fge

你能提供解释计划吗？ postgresql.org/docs/8.1/static/sql-explain.html 另外，假设“sid”是ID，包括它在选择和分组中没有意义 - “计数”总是1。 - Neville Kuyt

@fge - 当然他们是......这是一个电话记录。他想知道在每次通话期间还发生了多少同时通话。 - Eric

什么是 t1.sid？ - Ben

添加了创建表和索引脚本。谢谢！ - Sologoub

答案:

1.）您的查询没有捕获所有重叠 - 这已由其他答案修复。

2.）列的数据类型 starttime 和 endtime 是 timestamp。所以你的 WHERE 条款也有点错误：

BETWEEN '2011-11-02' AND '2011-11-03'

这将包括'2011-11-03 00:00'。上边界必须是排除。

3.）删除了不带双引号的混合大小写语法。不带引号的标识符会自动转换为小写。简单来说：最好不要在PostgreSQL中使用混合大小写标识符。

4.）转换查询以使用显式JOIN，这总是更可取的。实际上，我把它设为LEFT [OUTER] JOIN，因为我想计算与其他呼叫重叠的呼叫。

5.）简化语法以获得此基本查询：

SELECT t1.sid, count(*) AS ct
FROM   calls_nov t1
LEFT   JOIN calls_nov t2 ON t1.starttime <= t2.endtime
                        AND t1.endtime >= t2.starttime
WHERE  t1.starttime >= '2011-11-02 0:0'::timestamp
AND    t1.starttime <  '2011-11-03 0:0'::timestamp
GROUP  BY 1
ORDER  BY 2 DESC;

这个查询是 非常慢 对于一个大表，因为必须将从'2011-11-02'开始的每一行与整个表中的每一行进行比较，这导致（几乎）O（n²）成本。

更快

我们可以大幅降低成本 预选可能的候选人。只选择您需要的列和行。我用两个CTE做这个。

从当天开始选择通话。 - > CTE x
计算这些呼叫的最新结束。（CTE中的子查询 y）
仅选择与CTE总范围重叠的呼叫 x。 - > CTE y
最后的查询是 快多了 而不是查询庞大的基础表。

2018-01-06 01:33

谢谢。解释计划仍然没有启发：ort（成本= 4785158982.43..4785158982.93行= 200宽度= 32）然而，它似乎比以前的好10倍。现在运行查询，希望它会返回。 - Sologoub

@Sologoub：我在答案中添加了更多内容。 - Erwin Brandstetter

好的......只是哇！谢谢你的所有信息。努力吸收知识:) - Sologoub

好了，得到一个错误：错误：查询结构与函数结果类型不匹配SQL状态：42804详细信息：返回类型字符变化与第1列中的预期类型整数不匹配。上下文：PL / pgSQL函数“f_call_overlaps”第23行在RETURN QUERY - Sologoub

修改为这似乎允许函数运行：RETURNS TABLE（sid varchar，ct bigint） - Sologoub

这是可能的重叠的样子，其中'A'是“参考”区间。请注意，下面的查询（远远低于）并未给出与已发布的任何答案相同的结果。

-- A            |------|
-- B |-|
-- C        |---|
-- D          |---|
-- E             |---|
-- F               |---|
-- G                 |---|
-- H                   |---|
-- I                       |---|

“B”根本不与“A”重叠。 “C”紧靠它。 {“D”，“E”，“F”，“G”}重叠。 “H”紧靠它。 “我”根本不重叠。

create table calls_nov (
  sid varchar(5) primary key,
  starttime timestamp not null,
  endtime timestamp not null
);  

insert into calls_nov values
('A', '2012-01-04 08:00:00', '2012-01-04 08:00:10'),
('B', '2012-01-04 07:50:00', '2012-01-04 07:50:03'),
('C', '2012-01-04 07:59:57', '2012-01-04 08:00:00'),
('D', '2012-01-04 07:59:57', '2012-01-04 08:00:03'),
('E', '2012-01-04 08:00:01', '2012-01-04 08:00:04'),
('F', '2012-01-04 08:00:07', '2012-01-04 08:00:10'),
('G', '2012-01-04 08:00:07', '2012-01-04 08:00:13'),
('H', '2012-01-04 08:00:10', '2012-01-04 08:00:13'),
('I', '2012-01-04 08:00:15', '2012-01-04 08:00:18');

你可以看到这样的所有重叠间隔。（我只是使用to_char（）来查看所有数据。您可以在生产中省略它。）

select t1.sid, to_char(t1.starttime, 'HH12:MI:SS'), 
               to_char(t1.endtime,   'HH12:MI:SS'), 
       t2.sid, to_char(t2.starttime, 'HH12:MI:SS'), 
               to_char(t2.endtime,   'HH12:MI:SS')
from calls_nov t1
inner join calls_nov t2 on (t2.starttime, t2.endtime) 
                  overlaps (t1.starttime, t1.endtime) 
order by t1.sid, t2.sid;

A   08:00:00   08:00:10   A   08:00:00   08:00:10
A   08:00:00   08:00:10   D   07:59:57   08:00:03
A   08:00:00   08:00:10   E   08:00:01   08:00:04
A   08:00:00   08:00:10   F   08:00:07   08:00:10
A   08:00:00   08:00:10   G   08:00:07   08:00:13
B   07:50:00   07:50:03   B   07:50:00   07:50:03
C   07:59:57   08:00:00   C   07:59:57   08:00:00
C   07:59:57   08:00:00   D   07:59:57   08:00:03
D   07:59:57   08:00:03   A   08:00:00   08:00:10
D   07:59:57   08:00:03   C   07:59:57   08:00:00
D   07:59:57   08:00:03   D   07:59:57   08:00:03
D   07:59:57   08:00:03   E   08:00:01   08:00:04
E   08:00:01   08:00:04   A   08:00:00   08:00:10
E   08:00:01   08:00:04   D   07:59:57   08:00:03
E   08:00:01   08:00:04   E   08:00:01   08:00:04
F   08:00:07   08:00:10   A   08:00:00   08:00:10
F   08:00:07   08:00:10   F   08:00:07   08:00:10
F   08:00:07   08:00:10   G   08:00:07   08:00:13
G   08:00:07   08:00:13   A   08:00:00   08:00:10
G   08:00:07   08:00:13   F   08:00:07   08:00:10
G   08:00:07   08:00:13   G   08:00:07   08:00:13
G   08:00:07   08:00:13   H   08:00:10   08:00:13
H   08:00:10   08:00:13   G   08:00:07   08:00:13
H   08:00:10   08:00:13   H   08:00:10   08:00:13
I   08:00:15   08:00:18   I   08:00:15   08:00:18

你可以从这张表中看到“A”应该算5，包括它自己。 “B”应该算1;它重叠，但没有其他间隔重叠。这似乎是正确的做法。

计数很简单，但就像破裂的乌龟一样。那是因为评估重叠需要做很多工作。

select t1.sid, count(t2.sid) as num_concurrent
from calls_nov t1
inner join calls_nov t2 on (t2.starttime, t2.endtime) 
                  overlaps (t1.starttime, t1.endtime) 
group by t1.sid
order by num_concurrent desc;

A   5
D   4
G   4
E   3
F   3
H   2
C   2
I   1
B   1

为了获得更好的性能，您可以在公用表表达式中使用上面的“表”，并根据进行计数那。

with interval_table as (
select t1.sid as sid_1, t1.starttime, t1.endtime,
       t2.sid as sid_2, t2.starttime, t2.endtime
from calls_nov t1
inner join calls_nov t2 on (t2.starttime, t2.endtime) 
                  overlaps (t1.starttime, t1.endtime) 
order by t1.sid, t2.sid
) 
select sid_1, count(sid_2) as num_concurrent
from interval_table
group by sid_1
order by num_concurrent desc;

2018-01-04 22:15

OVERLAPS为+1 - pilcrow

谢谢你提供非常丰富的答案！但是，当我运行解释计划时，带有表表达式的查询的数量级会更糟：“排序（成本= 2566228269298.11..2566228269298.61行= 200宽度= 64）”vs“排序（成本= 11294858654.81..11294859096.47行= 176663宽度= 35）“为@ Eric的回答。这可能是缺失索引的情况吗？ - Sologoub

我有开始时间和结束时间的索引。这里的CTE要快得多，但我没有290万行。 - Mike Sherrill 'Cat Recall'

是的，我也已将其编入索引，但也许只是我本地的盒子不够坚固。 - Sologoub

试试这个代替你的中间和交叉连接：

select
    t1.sid,
    count(1) as CountSimultaneous
from
   calls_nov t1
   inner join nov t2 on
       t1.starttime <= t2.endtime
       and t1.endtime >= t2.starttime
where
    t1.starttime between '2011-11-02' and '2011-11-03'
group by
    t1.sid
order by CountSimultaneous desc

2018-01-04 20:48

这很接近，但需要 and t1.sid != t2.sid 确保同一行不会加入自身 - rejj

@reff - 我想到了这一点，但我没有把它放进去。实际上，你可以把这个条件放进去，让它成为一个 left join，和 count(t2.sid)，每个号码只会少给你1个。或者你可以做到 count(1)-1。无论哪种方式都可以洗。 - Eric

没有它应该没问题，因为我正在寻找并发呼叫的数量。数自我是好的。 - Sologoub

查询成本稍好一些，但总体而言仍然是数十亿... 1行结果集的成本是91k。不确定这里发生了什么。 - Sologoub

@Eric - 您的连接条件错误，您重复相同的条件 - Lamak

我假设您想知道任何给定时间的活动呼叫数量。其他答案可以显示当前呼叫处于活动状态时有多少其他呼叫处于活动状态。对于很长时间的通话，这可以给你很高的数字。有人告诉我，从你的一个评论到其他答案，你想要的活动电话数量（此外，我还在电信公司工作）。不幸的是，我没有足够的声誉来评论这个答案，因为我创建了自己的帐户来回答这个问题。要获得活动呼叫的数量，您可以使用一个变量，该变量在呼叫开始时增加1，在结束时减少1。我已经在拥有5000多万次调用的MySQL数据库上对此进行了测试。很抱歉MySQL和pgsql之间存在任何语法差异。

我为速度添加了临时表，但只有2m行和索引，可能不需要它们。 MySQL不能两次引用同一个临时表，所以我不得不创建两个。

CREATE TEMPORARY TABLE a
SELECT sid, StartTime, EndTime 
FROM calls_nov
WHERE StartTime between '2011-11-02' and '2011-11-03';

CREATE TEMPORARY TABLE b
SELECT *
FROM a;

SET @i := 0;

SELECT *, @i := @i + c.delta AS concurrent
FROM (
  SELECT StartTime AS time, 1 AS delta
  FROM a
  UNION ALL
  SELECT EndTime AS time, -1 AS delta
  FROM b
  ORDER BY time
) AS c
ORDER BY concurrent DESC
;

内部SELECT返回两列。时间列包括原始表中的每个StartTime和每个EndTime（行数的两倍），delta列为+1或-1，具体取决于在“time”中放入的列。该集按时间排序，然后我们可以在外部SELECT中迭代。

不像你在查询中那样“ORDER BY concurrent DESC”，我会使用一个额外的外部SELECT，我可以获得MAX，MIN等值，我也可以GROUP BY日期，小时等。这部分查询（ORDER）通过并发DESC），我实际上没有测试。我使用自己的建议和一个额外的外部查询，因为当在同一个SELECT中设置的变量进行排序时，ORDER BY在MySQL中没有按预期执行。它通过变量的先前值来命令。如果你绝对需要通过并发调用来订购（并且pgsql有相同的问题），我相信你可以通过再次使用额外的外部SELECT并在那里订购来解决这个问题。

我跑的查询非常快！它扫描每个临时表一次，然后一次扫描两次（每行数据较少），对于我自己的版本，通过一个额外的外部查询再次扫描组合，然后对其进行分组。 每张桌子只扫描一次！这一切都将在RAM中完成 如果您的配置和硬件允许它。如果没有，其他答案（或问题）将对您有所帮助。

2017-09-23 16:25

问题 计算SQL中的并发事件数

答案:

更快

热门问题

问题计算SQL中的并发事件数