时间戳

首页 > 解决方案 > 比较可用性和预订活动以计算一天中可用的分钟数

sql - 比较可用性和预订活动以计算一天中可用的分钟数

问题描述

我想写一个查询，告诉我哪几天有分钟可以预约。我有两张桌子，availabilities和events。

availabilitiesand表events都存储：开始时间start_time、结束时间end_time和用户 ID user_id。

我试图加入events表格availabilities，然后找到事件时间和可用总分钟数的可用性之间的差异。如果没有与可用性重叠的事件，则availability.start_time进行availability.end_time比较。

这仅在只有一个事件与可用性重叠时才有效。如果多个事件与可用性重叠，则在查找差异时如果不考虑每个事件。

WITH merged_availabilities AS (
  SELECT
    availabilities.id,
    COALESCE(
      (SELECT TRUE WHERE (events.id IS NULL) AND EXTRACT(EPOCH FROM availabilities.end_time - availabilities.start_time)::int / 60 >= 45),
      (SELECT TRUE WHERE (NOT events.id IS NULL)AND EXTRACT(EPOCH FROM availabilities.end_time - events.end_time)::int / 60 >= 45),
      (SELECT TRUE WHERE (NOT events.id IS NULL) AND EXTRACT(EPOCH FROM events.start_time - availabilities.start_time)::int / 60 >= 45)
    ) as "available"
  FROM
    availabilities
  LEFT JOIN
    events
    ON
      tsrange(availabilities.start_time, availabilities.end_time) && tsrange(events.start_time, events.end_time)
)

SELECT
  merged_availabilities.id,
  merged_availabilities.available
FROM
  merged_availabilities

单元格示例

考虑上图，其中每个单元格代表 15 分钟的时间（从左到右移动）。上排显示 4 个预订的活动，下排显示 2 个可用时段（第一个为 45 分钟，第二个为 30 分钟，中间有 15 分钟的休息时间）。

我的 pgSQL 会说两个可用性块仍有 15 分钟可用，即使第二个已满，因为在查找差异时未考虑过度事件。

只有第一个可用性块可用。

编辑：

表结构

availabilities

id int,
start_time timestamp,
end_time timestamp

events

id int,
start_time timestamp,
end_time timestamp

样本数据

availabilities

1, 2019-07-10 09:00:00, 2019-07-10 12:00:00
2, 2019-07-10 13:00:00, 2019-07-10 17:30:00
3, 2019-07-11 09:00:00, 2019-07-10 12:00:00
4, 2019-07-11 13:00:00, 2019-07-10 17:30:00
5, 2019-07-13 09:00:00, 2019-07-10 12:00:00

events

1, 2019-07-10 09:00:00, 2019-07-10 09:45:00
2, 2019-07-10 09:45:00, 2019-07-10 10:30:00
3, 2019-07-10 10:30:00, 2019-07-10 11:30:00

标签： sqlpostgresql

解决方案

一种方法：

SUBQUERY、DATE_PART 函数、SUM 和 CASE STATEMENT 的组合，用于标记剩余 >=45 分钟的 ID。

该解决方案假定您的事件表中的 ID 是 FOREIGN KEYS 并且可以出现多次，并且您的可用性表中的 ID 是主键。

使用此 ID，可以计算会话中的总分钟数，并将计算的总事件分钟数与每个事件时间相抵消。

SELECT
   av.id,
   av.start_time,
   av.end_time,
   DATE_PART('hour', start_time - end_time) * 60 +
   DATE_PART('minute',  start_time - end_time) AS session_mins_available,
   COALESCE(TOTAL_MIN_USED,0) AS TOTAL_MIN_USED,
   DATE_PART('hour', start_time - end_time) * 60 +
   DATE_PART('minute',  start_time - end_time) - COALESCE(TOTAL_MIN_USED,0) AS MINUTES_AVAILABLE,
   CASE WHEN DATE_PART('hour', start_time - end_time) * 60 +
             DATE_PART('minute',  start_time - end_time) - COALESCE(TOTAL_MIN_USED,0)
   <=-45 THEN 'TRUE' ELSE 'FALSE' END AS FLAG
FROM availabilities av
LEFT JOIN
   (SELECT 
       e.id,
       SUM(MINUSED) AS TOTAL_MIN_USED
    FROM
       (SELECT
       E.id,
       E.start_time,
       E.next_start_time,
       E.end_time,
       DATE_PART('hour', start_time - COALESCE(next_start_time, end_time)) * 60 +
       DATE_PART('minute',  start_time - COALESCE(next_start_time, end_time)) AS MINUSED
       FROM
       (SELECT e.start_time, 
           lead(e.start_time) over (PARTITION BY e.ID ORDER BY e.start_time) as next_start_time, 
           e.end_time,
           e.id
        FROM events e
       )E
      )e
   GROUP BY e.id
 )EVENTTIMES
 ON av.id = EVENTTIMES.id

由于事件之间的时间被忽略为潜在可用性，因此需要从前一个事件开始时间到下一个事件开始时间计算会话中使用的总分钟数。

   DATE_PART('hour', start_time - COALESCE(next_start_time, end_time)) * 60 +
   DATE_PART('minute',  start_time - COALESCE(next_start_time, end_time)) AS MINUSED

LEAD 函数已用于将相对于每个 ID 的下一个事件开始时间带到与前一个事件开始时间相同的行上。

当没有下一个事件开始时间时，事件时间计算到事件结束时间。

 COALESCE(next_start_time, end_time)

最后添加一个 case 语句将标记剩余总分钟数 >=45 的可用性 ID

推荐阅读