在SQL中将单行拆分为多行 [英] split single row into multiple rows in SQL

问题描述

在我的表格中，有两个字段start和stop分别存储开始时间和停止时间。

In my table there are two fields start and stop which stores the start time and stop time respectively.

例如开始时间= 2014-01-01 23:43:00和停止= 2014-01-03 03:33:00。

for example the Start time = 2014-01-01 23:43:00 and stop = 2014-01-03 03:33:00. This timestamp needs to brokendown to.

1=> 2014-01-01 23:43:00 - 2014-01-02 00:00:00, as date 2014-01-01
2=> 2014-01-02 00:00:01 - 2014-01-03 00:00:00, as date 2014-01-02 
3=> 2014-01-03 00:00:01 - 2014-01-03 03:33:00, as date 2014-01-03 

作为三个不同的行。

这里的问题是停止和开始时间的不同，例如1天到10天。

Here the problem is the difference in stop and start time varies say 1 day to 10 days.

到更复杂的是，在上面的示例中，我根据日期划分了时间段，这需要根据时间进行划分，即。例如说在时间02:30:00分裂，那么吐痰应该如下所示。

To make it more complicate, in the above example i split the period on basis of date, this i need to split on basis of time ie. say split at time 02:30:00, so the spiting should e as follows.

1=> 2014-01-01 23:43:00 - 2014-01-02 02:30:00, as date 2014-01-01 
2=> 2014-01-02 02:30:01 - 2014-01-03 02:30:00, as date 2014-01-02 
3=> 2014-01-03 02:30:01 - 2014-01-03 02:30:00, as date 2014-01-03 
4=> 2014-01-03 02:30:01 - 2014-01-03 03:33:00, as date 2014-01-04 

完成上述拆分后，我需要计算按日期分组的行。

Once the above split has done, i need to count the rows grouped by date.

我正在使用 PostgreSQL 。

有人可以对此有所启发！

Can anyone throw some light on this !!

谢谢！

推荐答案

我认为您的按时间分割期望的输出示例是错误的，应该以
为准

I think your "split by time" desired output sample is wrong and should in instead be this

1=> 2014-01-01 23:43:00 - 2014-01-02 02:30:00, as date 2014-01-01 
2=> 2014-01-02 02:30:01 - 2014-01-03 02:30:00, as date 2014-01-02 
3=> 2014-01-03 02:30:01 - 2014-01-03 03:33:00, as date 2014-01-03 

如果是这种情况，那就这样做

If that is the case then this do it

select day, count(*)
from (
    select generate_series(
        (start_time - interval '2 hours 30 minutes')::date,
        stop_time,
        interval '1 day'
    )::date as day
    from t
) s
group by day
order by day

这篇关于在SQL中将单行拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！