SQL / Postgres日期时间除法/归一化 [英] SQL/Postgres datetime division / normalizing

问题描述

我有这个活动表

  + -------------- +  - ---------------- + 
 |字段|类型| 
 + -------------- + ------------------ + 
 | id | int（11）unsigned | 
 | start_date | timestamp | 
 | end_date | timestamp | 
 | ... | | 
 + -------------- + ------------------ + 
  
 
 
 
我需要一个视图，它通过start_date在DAY分组这些活动，但是如果end_date与start_date不在同一天，该视图再次包含条目，但start_date设置为第二天的00:00 ..（依此类推，重复所需次数，直到start_date与end_date在同一天）
 
 
 
例如：
 
 
 
如果活动表包含：
  + -------------- + -------------------------- -  + ---------------------------- + 
 | id | start_date | end_date | 
 + -------------- + ---------------------------- +  - --------------------------- + 
 | 1 | 2014-12-02 14：12：00 + 00 | 2014-12-03 06：45：00 + 00 | 
 | 2 | 2014-12-05 15：25：00 + 00 | 2014-12-05 07：29：00 + 00 | 
 + -------------- + ---------------------------- +  - --------------------------- + 
  
视图应包含：
  + ------------- -  + ---------------------------- + ------------------- --------- + 
 | activity_id | start_date | end_date | 
 + -------------- + ---------------------------- +  - --------------------------- + 
 | 1 | 2014-12-02 14：12：00 + 00 | 2014-12-02 23：59：59 + 00 | 
 | 1 | 2014-12-03 00：00：00 + 00 | 2014-12-03 06：45：00 + 00 | 
 | 2 | 2014-12-05 15：25：00 + 00 | 2014-12-05 07：29：00 + 00 | 
 + -------------- + ---------------------------- +  - --------------------------- + 
  
任何帮助将非常感谢！
 
 
 
 PS：我使用postgresql 
解决方案
要获取所需的行，请先使用设置退回功能以及横向加入。从那里，使用 CASE 语句和 
 
 
下面是一个让你开始的例子：
 ，数据为（
 select id，start_date，end_date 
 from（values 
（1，'2014-12-02 14:12 ：00 + 00':: timestamptz，'2014-12-03 06：45：00 + 00':: timestamptz），
（2，'2014-12-05 15：25：00 + 00'： ：timestamptz，'2014-12-05 07：29：00 + 00':: timestamptz）
）as rows（id，start_date，end_date）
）
 select data.id，
 case days.d = date_trunc（'day'，data.start_date）
当为true时then data.start_date 
 else days.d 
结束为start_date，
 case天.d = date_trunc（'day'，data.end_date）
当为true时，data.end_date 
 else days.d + interval'1 day' -  interval'1 sec'
 end as end_date 
 from data 
 join generate_series（
 date_trunc（'day'，data.start_date），
 date_trunc（'day'，data.end_date），
' '
）as days（d）
 on days.d> = date_trunc（'day'，data.start_date）
 and days.d< = date_trunc .end_date）
 
 id | start_date | end_date 
 ---- + ------------------------ + --------------- --------- 
 1 | 2014-12-02 15：12：00 + 01 | 2014-12-02 23：59：59 + 01 
 1 | 2014-12-03 00：00：00 + 01 | 2014-12-03 07：45：00 + 01 
 2 | 2014-12-05 16：25：00 + 01 | 2014-12-05 08：29：00 + 01 
（3排）
  
根据你在做什么，你可能会更有意义的使用日期范围：
 ，数据为（
 select id，start_date，end_date 
 from（values 
（1，'2014-12-02 14：12：00 + 00':: timestamptz，'2014-12-03 06：45：00 + 00':: timestamptz），
（2，'2014-12-05 07：25：00 + 00':: timestamptz，'2014-12-05 15：29：00 + 00':: timestamptz）
）as rows ，start_date，end_date）
）
 select data.id，
 tstzrange（data.start_date，data.end_date）
 from data; 
 
 id | tstzrange 
 ---- + ---------------------------------------- ------------- 
 1 | [2014-12-02 15：12：00 + 01，2014-12-03 07：45：00 + 01）
 2 | [2014-12-05 08：25：00 + 01，2014-12-05 16：29：00 + 01）
（2排）
  
 
I have this activity table
+--------------+------------------+
| Field        | Type             |
+--------------+------------------+
| id           | int(11) unsigned |
| start_date   | timestamp        |
| end_date     | timestamp        |
| ...          |                  |
+--------------+------------------+
I need a view which groups these activities by start_date by DAY, but in such a way that, if the end_date is not in the same day as start_date, the view contain the entry again but with the start_date set to 00:00 of the next day.. (and so on, repeated as many times as needed until the start_date is in the same day as the end_date)

As an example:

if the activity table contains:
+--------------+----------------------------+----------------------------+
| id           | start_date                 | end_date                   |
+--------------+----------------------------+----------------------------+
|  1           | 2014-12-02 14:12:00+00     | 2014-12-03 06:45:00+00     |   
|  2           | 2014-12-05 15:25:00+00     | 2014-12-05 07:29:00+00     |                                
+--------------+----------------------------+----------------------------+
The view should contain:
+--------------+----------------------------+----------------------------+
| activity_id  | start_date                 | end_date                   |
+--------------+----------------------------+----------------------------+
|  1           | 2014-12-02 14:12:00+00     | 2014-12-02 23:59:59+00     |   
|  1           | 2014-12-03 00:00:00+00     | 2014-12-03 06:45:00+00     |
|  2           | 2014-12-05 15:25:00+00     | 2014-12-05 07:29:00+00     |                                  
+--------------+----------------------------+----------------------------+
Any help would be greatly appreciated!

PS: I'm using postgresql
解决方案
To get the needed rows, start by using a set returning function along with a lateral join. From there, use CASE statements and date arithmetics to pull out the relevant values.

Here's an example to get you started:
with data as (
  select id, start_date, end_date
  from (values
    (1, '2014-12-02 14:12:00+00'::timestamptz, '2014-12-03 06:45:00+00'::timestamptz),
    (2, '2014-12-05 15:25:00+00'::timestamptz, '2014-12-05 07:29:00+00'::timestamptz)
  ) as rows (id, start_date, end_date)
)
select data.id,
      case days.d = date_trunc('day', data.start_date)
        when true then data.start_date
        else days.d
      end as start_date,
      case days.d = date_trunc('day', data.end_date)
        when true then data.end_date
        else days.d + interval '1 day' - interval '1 sec'
      end as end_date
from data
join generate_series(
      date_trunc('day', data.start_date),
      date_trunc('day', data.end_date),
      '1 day'
      ) as days (d)
      on days.d >= date_trunc('day', data.start_date)
      and days.d <= date_trunc('day', data.end_date)

 id |       start_date       |        end_date        
----+------------------------+------------------------
  1 | 2014-12-02 15:12:00+01 | 2014-12-02 23:59:59+01
  1 | 2014-12-03 00:00:00+01 | 2014-12-03 07:45:00+01
  2 | 2014-12-05 16:25:00+01 | 2014-12-05 08:29:00+01
(3 rows)
As an aside, depending on what you're doing, it might make more sense for you to use a date range:
with data as (
  select id, start_date, end_date
  from (values
    (1, '2014-12-02 14:12:00+00'::timestamptz, '2014-12-03 06:45:00+00'::timestamptz),
    (2, '2014-12-05 07:25:00+00'::timestamptz, '2014-12-05 15:29:00+00'::timestamptz)
  ) as rows (id, start_date, end_date)
)
select data.id,
      tstzrange(data.start_date, data.end_date)
from data;

 id |                      tstzrange                      
----+-----------------------------------------------------
  1 | ["2014-12-02 15:12:00+01","2014-12-03 07:45:00+01")
  2 | ["2014-12-05 08:25:00+01","2014-12-05 16:29:00+01")
(2 rows)


                        
这篇关于SQL / Postgres日期时间除法/归一化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！