sql - 查找跨越周末的连续日期

Question

我有一份工作 list ，表明在任何特定工作上执行的工作。当白天完成工作时，只添加一个记录并包含一个 work_type。
周末不工作。工作可以在很长一段时间内完成，偶尔会有奇怪的一天，但在其生命周期的某个时刻，它应该有一段持续进行的工作。
我们的管理层希望能够在报告中突出显示任何没有发生较长时间工作的工作。
关于工作类型和团队名称还有一些其他条件，但主要的症结在于时间问题。

那么......我如何找到没有至少连续两周(10 个工作日)一致工作的工作？

在下文中，作业 164353 将不包括在内，因为它有必要的连续 10 天(忽略周末)，而作业 214325 将被标记，因为在 9 日存在中断连续天数顺序的间隙。

JOB_ID     W ACTION_DATE
---------- - -----------
164354     H 10-FEB-17
164354     H 13-FEB-17
164354     H 14-FEB-17
164354     H 15-FEB-17
164354     H 16-FEB-17
164354     H 17-FEB-17
164354     H 20-FEB-17
164354     H 21-FEB-17
164354     H 22-FEB-17
164354     H 23-FEB-17
164354     H 24-FEB-17

214325     H 01-MAR-17
214325     H 02-MAR-17
214325     H 03-MAR-17
214325     H 06-MAR-17
214325     H 07-MAR-17
214325     H 08-MAR-17
214325     H 10-MAR-17
214325     H 13-MAR-17
214325     H 14-MAR-17
214325     H 15-MAR-17

我有这个查询，我可以在其中针对每个组生成连续几天的组，但我正在努力使其适应周末。换句话说，理想情况下，下面的结果将显示连续 10 天的天数。

WITH  
  groups AS (
    SELECT
      ROW_NUMBER() OVER (ORDER BY action_date) AS rn,
      action_date -ROW_NUMBER() OVER (ORDER BY action_date) AS grp,
      action_date
    FROM test_job_list
    WHERE job_id = 164354
  )
SELECT count(*) AS num_consec_dates,
       min(action_date) AS earliest,
       max(action_date) AS latest
FROM groups
group by grp
ORDER BY num_consec_dates desc, earliest desc


NUM_CONSEC
DATES      EARLIEST  LATEST
---------- --------- ---------
         5 20-FEB-17 24-FEB-17
         5 13-FEB-17 17-FEB-17
         1 10-FEB-17 10-FEB-17

Answer 1

您可以确定它使用的是一周中的哪一天(星期一 = 0，星期日 = 6):

TRUNC( action_date ) - TRUNC( action_date, 'IW' )

并且，使用 LAG分析函数然后您可以比较前一个条目是否是前一个工作日并使用它来确定组:

Oracle 设置 :

CREATE TABLE test_job_list ( JOB_ID,     W, ACTION_DATE ) AS
SELECT 164354, 'H', DATE '2017-02-10' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-13' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-14' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-15' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-16' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-17' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-20' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-21' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-22' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-23' FROM DUAL UNION ALL
SELECT 164354, 'H', DATE '2017-02-24' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-01' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-02' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-03' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-06' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-07' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-08' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-10' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-13' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-14' FROM DUAL UNION ALL
SELECT 214325, 'H', DATE '2017-03-15' FROM DUAL;

查询 :

SELECT job_id,
       MIN( action_date ) AS start_date,
       MAX( action_date ) AS end_date,
       COUNT( 1 ) AS num_days
FROM   (
  SELECT job_id,
         action_date,
         SUM( has_changed_group ) OVER ( PARTITION BY job_id ORDER BY action_date )
           AS group_id
  FROM   (
    SELECT job_id,
           action_date,
           CASE WHEN
             LAG( action_date ) OVER ( PARTITION BY job_id ORDER BY action_date )
             = action_date - CASE TRUNC( action_date ) - TRUNC( action_date, 'IW' )
                             WHEN 0 THEN 3 ELSE 1 END
             THEN 0
             ELSE 1
           END AS has_changed_group
    FROM   test_job_list
  )
)
GROUP BY job_id, group_id
-- HAVING COUNT(1) >= 10;

输出 :

    JOB_ID START_DATE          END_DATE              NUM_DAYS
---------- ------------------- ------------------- ----------
    164354 2017-02-10 00:00:00 2017-02-24 00:00:00         11
    214325 2017-03-10 00:00:00 2017-03-15 00:00:00          4
    214325 2017-03-01 00:00:00 2017-03-08 00:00:00          6

替代 :

如果您只想要从未有过连续 10 个工作日的工作，那么您可以使用 COUNT()解析函数并指定一个 RANGE window :

SELECT job_id
FROM   (
  SELECT job_id,
         COUNT( 1 ) OVER ( PARTITION BY job_id
                           ORDER BY action_date
                           RANGE BETWEEN INTERVAL '13' DAY PRECEDING
                                 AND     INTERVAL  '0' DAY FOLLOWING )
           AS num_days
  FROM   test_job_list
)
GROUP BY job_id
HAVING MAX( num_days ) < 10;

输出 :

    JOB_ID
----------
    214325