计算空闲/可用天数Oracle SQL

j5fpnvbx 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(107)

我有一个像酒店系统一样工作的表，包含入住和退房日期。我想在这里做的是，我想看看一个月有多少天是空闲的。比如说;
| 名称|入住登记|退房|
| --|--|--|
| 乔伊·佐洛蒙|2019 - 05 - 21 00：00：00| 2019 - 08 - 15|
| 亨特·佐勒蒙|2019 - 08 - 15| 2019 - 08 - 26|
| 巴里艾伦|2019 - 02 - 21 10：00：00| 2019 - 05 - 21 10：00：00|
我需要输出像这样;
| 月|免费_天数|
| --|--|
| 2023年08月08日| 8 |
| 2023年9月| 26 |
提前感谢大家！！！

oracle

来源：https://stackoverflow.com/questions/77132906/calculate-free-available-days-oracle-sql

2条答案

按热度按时间

js5cn81o1#

这里有一个选择;读取代码中的注解：

SQL> with
  2  test (name, check_in, check_out) as
  3    -- this is your sample table
  4    (select 'Joey Zolomon'  , date '2023-08-05', date '2023-08-15'   from dual union all
  5     select 'Hunter Zolomon', date '2023-08-15', date '2023-08-26'   from dual union all
  6     select 'Barry Allen'   , date '2023-09-02', date '2023-09-05'   from dual
  7    ),
  8  checked as
  9    -- non-available dates
 10    (select check_in + column_value - 1 datum
 11     from test cross join table(cast(multiset(select level from dual
 12                                              connect by level <= check_out - check_in + 1
 13                                             ) as sys.odcinumberlist))
 14    ),
 15  calendar as
 16    -- calendar for the whole year
 17    (select trunc(sysdate, 'yyyy') + level - 1 datum
 18     from dual
 19     connect by level <= add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy')
 20    ),
 21  available as
 22    -- available dates are calculated as [all days in a year] MINUS [all non-available dates]
 23    (select k.datum from calendar k
 24     minus
 25     select c.datum from checked c
 26    )
 27  -- finally, aggregate number of free (available) dates per every month
 28  select to_char(a.datum, 'mm') month,
 29    count(*) free_days
 30  from available a
 31  group by to_char(a.datum, 'mm')
 32  order by to_char(a.datum, 'mm');

测试结果：

MONTH  FREE_DAYS
----- ----------
01            31
02            28
03            31
04            30
05            31
06            30
07            31
08             9   --> you said it should be 8; nope. August has 31 days
09            26
10            31
11            30
12            31

12 rows selected.

SQL>

赞(0）回复(0）举报 2023-10-16

r7xajy2e2#

您不需要在一个月的每一天都生成;相反，您可以将已有的行拆分为单独的月份，然后使用UNPIVOT并使用分析函数来查找人员进入和离开的累积计数，并使用该计数来生成空闲天数（这将需要您生成的行明显少于每月或每年的天数，并且没有连接）。

WITH split_months (check_in, check_out, max_check_out) AS (
  SELECT check_in,
         LEAST(check_out + 1, ADD_MONTHS(TRUNC(check_in, 'MM'), 1)),
         check_out + 1
  FROM   table_name
UNION ALL
  SELECT check_out,
         LEAST(max_check_out, ADD_MONTHS(check_out, 1)),
         max_check_out
  FROM   split_months
  WHERE  check_out < max_check_out
),
presence (prev_dt, dt, next_dt, change, num_present) AS (
  SELECT LAG(dt) OVER (
           PARTITION BY TRUNC(dt, 'MM')
           ORDER BY dt, change DESC, ROWNUM
         ),
         dt,
         LEAD(dt) OVER (
           PARTITION BY TRUNC(dt, 'MM')
           ORDER BY dt, change DESC, ROWNUM
         ),
         change,
         SUM(change) OVER (
           ORDER BY dt, change DESC, ROWNUM
         )
  FROM   split_months
  UNPIVOT ( dt FOR change IN ( Check_In AS 1, Check_Out AS -1 ) )
)
SELECT TRUNC(dt, 'MM') AS month,
       SUM(
         CASE
         WHEN num_present = 0
         THEN COALESCE(next_dt, ADD_MONTHS(TRUNC(dt, 'MM'), 1)) - dt
         ELSE dt - TRUNC(dt, 'MM')
         END
       ) AS num_free_days
FROM   presence
WHERE  num_present = 0
OR     ( prev_dt IS NULL AND num_present - change = 0 )
GROUP BY TRUNC(dt, 'MM')

其中，对于样本数据：

CREATE TABLE table_name (Name, Check_In, Check_Out) AS
SELECT 'Joey Zolomon',   DATE '2023-08-05', DATE '2023-08-15' FROM DUAL UNION ALL
SELECT 'Hunter Zolomon', DATE '2023-08-15', DATE '2023-08-26' FROM DUAL UNION ALL
SELECT 'Barry Allen',    DATE '2023-09-02', DATE '2023-09-05' FROM DUAL;

输出：
| 月|NUM_FREE_DAYS|
| --|--|
| 2019 -08-01 00：00：00| 9 |
| 2023-09-01 00：00：00| 26 |
如果您想查看每个月的空闲天数范围，那么查询的最后一部分可以调整为：

WITH split_months (check_in, check_out, max_check_out) AS (
  SELECT check_in,
         LEAST(check_out + 1, ADD_MONTHS(TRUNC(check_in, 'MM'), 1)),
         check_out + 1
  FROM   table_name
UNION ALL
  SELECT check_out,
         LEAST(max_check_out, ADD_MONTHS(check_out, 1)),
         max_check_out
  FROM   split_months
  WHERE  check_out < max_check_out
),
presence (prev_dt, dt, next_dt, change, num_present) AS (
  SELECT LAG(dt) OVER (
           PARTITION BY TRUNC(dt, 'MM')
           ORDER BY dt, change DESC, ROWNUM
         ),
         dt,
         LEAD(dt) OVER (
           PARTITION BY TRUNC(dt, 'MM')
           ORDER BY dt, change DESC, ROWNUM
         ),
         change,
         SUM(change) OVER (
           ORDER BY dt, change DESC, ROWNUM
         )
  FROM   split_months
  UNPIVOT ( dt FOR change IN ( Check_In AS 1, Check_Out AS -1 ) )
)
SELECT CASE 
       WHEN prev_dt IS NULL AND num_present - change = 0
       THEN TRUNC(dt, 'MM')
       ELSE dt
       END AS start_free,
       CASE
       WHEN prev_dt IS NULL AND num_present - change = 0
       THEN dt
       ELSE COALESCE(next_dt, ADD_MONTHS(TRUNC(dt, 'MM'), 1))
       END AS free_end,
       CASE 
       WHEN prev_dt IS NULL AND num_present - change = 0
       THEN dt - TRUNC(dt, 'MM')
       ELSE COALESCE(next_dt, ADD_MONTHS(TRUNC(dt, 'MM'), 1)) - dt
       END AS days
FROM   presence
WHERE  num_present = 0
OR     ( prev_dt IS NULL AND num_present - change = 0 )

其输出：
| 开始_免费|FREE_END|天|
| --|--|--|
| 2019 -08-01 00：00：00| 2019 -08-05 00：00：00| 4 |
| 2023-08-27 00：00：00| 2023-09-01 00：00：00| 5 |
| 2023-09-01 00：00：00| 2019 -09-02 00：00：00| 1 |
| 2023-09-06 00：00：00| 2023-10-01 00：00：00| 25 |
在Oracle 12中，您还可以使用MATCH_RECOGNIZE进行逐行模式匹配，以查找重叠（以及每个重叠之前和之后的间隙），然后可以拆分为月份并进行聚合：

WITH find_overlaps (prev_free_start, free_end, free_start, next_free_end) AS (
  SELECT LAG(check_out + 1) OVER (ORDER BY check_in),
         check_in,
         check_out + 1,
         next_check_in
  FROM   table_name
  MATCH_RECOGNIZE(
    ORDER BY check_in, check_out DESC
    MEASURES
      FIRST(check_in) AS check_in,
      MAX(check_out) AS check_out,
      NEXT(check_in) AS next_check_in
    PATTERN (overlap* non_overlap)
    DEFINE
      overlap AS MAX(check_out) >= NEXT(check_in)
  )
),
unique_free_ranges (free_start, free_end) AS (
  SELECT COALESCE(free_start, TRUNC(free_end , 'MM')),
         COALESCE(free_end, ADD_MONTHS(TRUNC(free_start, 'MM'), 1))
  FROM   find_overlaps
  UNPIVOT (
    (free_start, free_end) FOR before_after IN (
      (prev_free_start, free_end) AS 0,
      (free_start, next_free_end) AS 1
    )
  )
  WHERE before_after = 0 AND free_start IS NULL
  OR    before_after = 1
),
split_months (free_start, month_end, free_end) AS (
  SELECT free_start,
         LEAST(free_end, ADD_MONTHS(TRUNC(free_start, 'MM'), 1)),
         free_end
  FROM   unique_free_ranges
UNION ALL
  SELECT month_end,
         LEAST(free_end, ADD_MONTHS(month_end, 1)),
         free_end
  FROM   split_months
  WHERE  month_end < free_end
)
SELECT TRUNC(free_start, 'MM') AS month,
       SUM(month_end - free_start) AS days_free
FROM   split_months
GROUP BY TRUNC(free_start, 'MM');

fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

计算空闲/可用天数Oracle SQL

2条答案

相关问题

热门标签

最新问答