我使用哪个数据库
我使用的是PostgreSQL 9.5。
我需要什么
这是我的data_store表的一部分：
id | starttime
-----+----------------------------
185 | 2011-09-12 15:24:03.248+02
189 | 2011-09-12 15:24:03.256+02
312 | 2011-09-12 15:24:06.112+02
313 | 2011-09-12 15:24:06.119+02
450 | 2011-09-12 15:24:09.196+02
451 | 2011-09-12 15:24:09.203+02
452 | 2011-09-12 15:24:09.21+02
... | ...
我想创建一个查询，该查询将按特定的时间间隔对记录进行计数。例如，对于4秒的时间间隔，查询应该返回给我这样的信息：
starttime-from | starttime-to | count
---------------------+---------------------+---------
2011-09-12 15:24:03 | 2011-09-12 15:24:07 | 4
2011-09-12 15:24:07 | 2011-09-12 15:24:11 | 3
2011-09-12 15:24:11 | 2011-09-12 15:24:15 | 0
... | ... | ...
最重要的事情：
- 时间间隔取决于用户的选择。它可以是
1 second、37 seconds、50 minutes或某种混合物：2 month and 30 mintues。时间间隔的可用单位：millisecond、second、minute、hour、day、month、year。如您所见，我需要一些通用/通用查询，但我也可以为每个单元创建几个查询，这不是问题 - 查询应该是高效的，因为我在一个大型数据库中工作(2000万行甚至更多，但在查询中我只使用这个数据库的一部分，例如：100万(
问题是：查询应该如何实现
我试图转换我在以下线程中找到的解决方案，但没有成功：
- PostgreSQL：按分钟运行查询的行数
- 按数据间隔分组
- 在Rails+Postgres中以任意时间间隔计算记录的最佳方式
我有什么
为了提高帖子的透明度，我删除了帖子的这一部分。这一部分不必回答我的问题。如果你想看看这里是什么，看看帖子的历史。
您的查询似乎很复杂。您只需要生成时间序列，然后使用left join将它们组合在一起。和骨料：
select g.ts, g.ts + interval '4 second', count(ds.id)
from (select generate_series(min(starttime), max(strttime), interval '4 second') as ts
from data_store
) g left join
data_store ds
on ds.starttime >= g.ts and ds.starttime < g.ts + interval '4 second'
group by g.ts
order by g.ts;
注意：如果您希望间隔从精确的一秒开始(并且1000次中没有999次的奇怪毫秒数(，则使用date_trunc()。
编辑：
相关子查询是否更快可能值得一看：
select gs.ts,
(select count(*)
from data_store ds
where ds.starttime >= g.ts and ds.starttime < g.ts + interval '4 second'
) as cnt
from (select generate_series(min(starttime), max(strttime), interval '4 second') as ts
from data_store
) g;
如果有帮助，我可以使用UDF创建动态日期/时间范围。
在SomeDate>=DateR1和SomeDate的Join中使用结果
Range、DatePart和Increment是参数
Declare @Date1 DateTime = '2011-09-12 15:24:03 '
Declare @Date2 DateTime = '2011-09-12 15:30:00 '
Declare @DatePart varchar(25)='SS'
Declare @Incr int=3
Select DateR1 = RetVal
,DateR2 = LEAD(RetVal,1,@Date2) OVER (ORDER BY RetVal)
From (Select * from [dbo].[udf-Create-Range-Date](@Date1,@Date2,@DatePart,@Incr) ) A
Where RetVal<@Date2
返回
DateR1 DateR2
2011-09-12 15:24:03.000 2011-09-12 15:24:06.000
2011-09-12 15:24:06.000 2011-09-12 15:24:09.000
2011-09-12 15:24:09.000 2011-09-12 15:24:12.000
2011-09-12 15:24:12.000 2011-09-12 15:24:15.000
2011-09-12 15:24:15.000 2011-09-12 15:24:18.000
2011-09-12 15:24:18.000 2011-09-12 15:24:21.000
...
2011-09-12 15:29:48.000 2011-09-12 15:29:51.000
2011-09-12 15:29:51.000 2011-09-12 15:29:54.000
2011-09-12 15:29:54.000 2011-09-12 15:29:57.000
2011-09-12 15:29:57.000 2011-09-12 15:30:00.000
UDF
CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)
Returns
@ReturnVal Table (RetVal datetime)
As
Begin
With DateTable As (
Select DateFrom = @DateFrom
Union All
Select Case @DatePart
When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
End
From DateTable DF
Where DF.DateFrom < @DateTo
)
Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)
Return
End
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1)
所选答案中查询的改进
我刚刚改进了您可以在所选答案中找到的查询。
最终查询如下：
SELECT gp.tp AS starttime_from, gp.tp + interval '4 second' AS starttime_to, count(ds.id)
FROM (SELECT generate_series(min(starttime),max(starttime), interval '4 second') as tp
FROM data_store
WHERE id_user_table=1 and sip='147.32.84.138'
ORDER BY 1
) gp
LEFT JOIN data_store ds
ON ds.id_user_table=1 and ds.sip='147.32.84.138'
and ds.starttime >= gp.tp and ds.starttime < gp.tp + interval '4 second'
GROUP BY starttime_from
我已将ORDER BY移到子查询中。现在速度快了一点。我还在WHERE子句中添加了所需列。最后，我在查询中一直使用的列上创建了多列索引：
CREATE INDEX my_index ON data_store (id_user_table, sip, starttime);
目前查询速度非常快请注意：对于非常小的时间间隔，查询结果包括许多零计数行。这些行占用了空间。在这种情况下，查询应该包括HAVING count(ds.id) > 0限制，但您必须在客户端处理这些0。
另一个解决方案
这个解决方案没有上一个那么快，但下面的查询没有使用多列索引，而且仍然很快。
在查询中有两件重要的事情，你可以在这个答案的末尾找到：
'second'是截断输入值的精度。您还可以选择其他精度，如：millisecond、minute、day等。'4 second'是时间间隔。时间间隔可以具有其他单位，如millisecond、minute、day等。
在这里你可以找到查询的解释：
generate_period查询生成从指定日期时间开始到特定日期时间的间隔。您可以手动或通过表中的列来指示这个特定的日期时间(就像我的情况一样(。对于4秒的间隔时间间隔，查询返回：tp --------------------- 2011-09-12 15:24:03 2011-09-12 15:24:07 2011-09-12 15:24:11 ...data_series查询统计日期时间特定精度的记录：for 1 second time interval、for 1 day time interval等。在我的情况下，特定精度是'second'，所以是for 1 second time interval，但选择操作的结果不包括没有出现的日期时间的0值。在我的情况下，data_series查询返回：starttime | ct ---------------------+----------- 2011-09-12 15:24:03 | 2 2011-09-12 15:24:06 | 2 2011-09-12 15:24:09 | 3 ... | ...最后，查询的最后部分总结了特定时间段的
ct列。查询返回以下内容：starttime-from | starttime-to | ct ---------------------+---------------------+--------- 2011-09-12 15:24:03 | 2011-09-12 15:24:07 | 4 2011-09-12 15:24:07 | 2011-09-12 15:24:11 | 3 2011-09-12 15:24:11 | 2011-09-12 15:24:15 | 0 ... | ... | ...
以下是查询：
WITH generate_period AS(
SELECT generate_series(date_trunc('second',min(starttime)),
date_trunc('second',max(starttime)),
interval '4 second') as tp
FROM data_store
WHERE id_user_table=1 --other restrictions
), data_series AS(
SELECT date_trunc('second', starttime) AS starttime, count(*) AS ct
FROM data_store
WHERE id_user_table=1 --other restrictions
GROUP BY 1
)
SELECT gp.tp AS starttime-from,
gp.tp + interval '4 second' AS starttime-to,
COALESCE(sum(ds.ct),0) AS ct
FROM generate_period gp
LEFT JOIN data_series ds ON date_trunc('second',ds.starttime) >= gp.tp
and date_trunc('second',ds.starttime) < gp.tp + interval '4 second'
GROUP BY 1
ORDER BY 1;