折叠具有连续值的结果集行

Question

我有一个查询，它生成一个人在其时间表上注册的假期。 考勤表中的每一天都是单独的记录，但如果您休假一周（2011 年 12 月 26 日至 2011 年 12 月 30 日），则必须通过查询将其表示在一个块（1 行）中。

这是我对问题进行了一些简化的查询：

SELECT -1 ord_li_pers_plan_id
       ,ts.person_id person_id
       ,-1 order_line_id
       ,ts.timesheet_dt start_dt
       ,ts.timesheet_dt end_dt
       ,'Vacation' project
       ,null color
       ,1013011 planning_type
FROM timesheets ts
  JOIN persons pers ON ts.person_id = pers.person_id
  JOIN person_holidays per_hol ON 
    ts.person_holiday_id = per_hol.person_holiday_id
  JOIN v_holiday_types hoty_peho ON 
    per_hol.holiday_type_id = hoty_peho.holiday_type_id
WHERE ts.person_holiday_id IS NOT NULL
AND per_hol.holiday_type_id IN (SELECT holiday_type_id FROM v_holiday_types)
AND ts.person_id = :p_person_id

这是我现在得到的输出：

-1  11182   -1  30-DEC-11   30-DEC-11   Vacation    1013011
-1  11182   -1  29-DEC-11   29-DEC-11   Vacation    1013011
-1  11182   -1  28-DEC-11   28-DEC-11   Vacation    1013011
-1  11182   -1  27-DEC-11   27-DEC-11   Vacation    1013011
-1  11182   -1  26-DEC-11   26-DEC-11   Vacation    1013011
-1  11182   -1  31-OCT-11   31-OCT-11   Vacation    1013011
-1  11182   -1  02-SEP-11   02-SEP-11   Vacation    1013011
-1  11182   -1  29-JUL-11   29-JUL-11   Vacation    1013011
-1  11182   -1  22-JUL-11   22-JUL-11   Vacation    1013011
-1  11182   -1  25-APR-11   25-APR-11   Vacation    1013011

更新：

相反，查询应输出以下内容：

-1  11182   -1  26-DEC-11   30-DEC-11   Vacation    1013011
-1  11182   -1  31-OCT-11   31-OCT-11   Vacation    1013011
-1  11182   -1  02-SEP-11   02-SEP-11   Vacation    1013011
-1  11182   -1  29-JUL-11   29-JUL-11   Vacation    1013011
-1  11182   -1  22-JUL-11   22-JUL-11   Vacation    1013011
-1  11182   -1  25-APR-11   25-APR-11   Vacation    1013011

请注意第一行。现在它代表整个假期。

更新2

我的客户提出了另一个想法。此查询链接到时间表，其中包含分配给某人的所有项目。项目日期最初存储为一个块（每个日期范围 1 条记录，例如 01/01/2012 - 10/01/2012 => 1 条记录）。现在，客户希望将所有日期存储为单独的记录。这并不难做到，而且有效。

我现在面临的问题是 group by 不再起作用，因为 ord_li_pers-plan_id 是 PK。

附件是查询。这是一个相当大的。

    SELECT person_id ,
    order_line_id ,
    MIN(start_dt) start_dt ,
    MAX(end_dt) end_dt ,
    project ,
    color ,
    planning_type,
    ord_li_pers_plan_id
  FROM
    (SELECT ord_li_pers_plan_id ,
      person_id ,
      order_line_id ,
      start_dt ,
      end_dt ,
      project ,
      color ,
      planning_type ,
      SUM(gap) OVER (PARTITION BY person_id ORDER BY start_dt) contiguous_grp
    FROM
      (SELECT ord_li_pers_plan_id ,
        person_id ,
        order_line_id ,
        start_dt ,
        end_dt ,
        project ,
        color ,
        planning_type ,
        CASE
          WHEN lag(end_dt) over(PARTITION BY person_id ORDER BY start_dt) + 1 >= start_dt
          THEN 0
          ELSE 1
        END gap
      FROM
        (SELECT ord_li_pers_plan_id ,
          person_id ,
          order_line_id ,
          start_dt ,
          end_dt ,
          project ,
          color ,
          planning_type
        FROM
          (SELECT op.ord_li_pers_plan_id ord_li_pers_plan_id ,
            p.person_id person_id ,
            p.order_line_id order_line_id ,
            op.start_dt start_dt ,
            op.end_dt end_dt ,
            pl$planning.prep_tooltip(NVL2(ord.end_customer_id, end_cus.name, cus.name)
            || ' - '
            || NVL2(ord_li.project_cd,ord_li.project_cd,ord.project_cd)
            || ' - '
            || func_tp.name
            || ' - '
            || ROUND((con_tp.contract_tp / 5), 2)
            ||' - '
            || bl$gen_codes.Name_by_Code_Id (op.planning_type)) project ,
            olc.color color ,
            op.planning_type planning_type
          FROM order_line_person_planning op
          JOIN order_line_persons p
          ON p.ORDER_LINE_PERSON_ID = op.ORDER_LINE_PERSON_ID
          JOIN order_lines ord_li
          ON ord_li.order_line_id = p.order_line_id
          JOIN orders ord
          ON ord.order_id = ord_li.order_id
          LEFT JOIN order_line_colors olc
          ON olc.order_line_id = ord_li.order_line_id
          JOIN customers cus
          ON ord.customer_id=cus.customer_id
          LEFT JOIN customers end_cus
          ON ord.end_customer_id=end_cus.customer_id
          LEFT JOIN v_contract_types con_tp
          ON ord.contract_type_id=con_tp.contract_type_id
          JOIN v_function_types func_tp
          ON ord_li.function_id=func_tp.function_id
          UNION
          SELECT -1 ord_li_pers_plan_id ,
            ts.person_id person_id ,
            -1 order_line_id ,
            ts.timesheet_dt start_dt ,
            ts.timesheet_dt end_dt ,
            'Vacation' project ,
            '#99FF33' color ,
            -1 planning_type
          FROM hrm_iadvise.timesheets ts
          JOIN hrm_iadvise.persons pers
          ON ts.person_id = pers.person_id
          JOIN hrm_iadvise.person_holidays per_hol
          ON ts.person_holiday_id = per_hol.person_holiday_id
          JOIN hrm_iadvise.v_holiday_types hoty_peho
          ON per_hol.holiday_type_id  = hoty_peho.holiday_type_id
          WHERE ts.person_holiday_id IS NOT NULL
          UNION
          SELECT -1 ord_li_pers_plan_id ,
            per_hol.person_id person_id ,
            -1 order_line_id ,
            hol.dt start_dt ,
            hol.dt end_dt ,
            'Vacation' project ,
            '#99FF33' color ,
            -1 planning_type
          FROM holidays hol
          JOIN person_holidays per_hol
          ON per_hol.holiday_type_id = hol.holiday_type_id
          JOIN countries coty
          ON coty.country_id    = hol.country_id
          WHERE coty.country_cd = 150
          )
        )
      )
    )
  GROUP BY person_id ,
    order_line_id ,
    project ,
    color ,
    planning_type ,
    contiguous_grp,
    ord_li_pers_plan_id;

第一个查询获取分配给某人的所有项目 第二个查询获取一个人已经注册的所有假期 第三个查询获取所有国定假日

提前致谢

Answer 1

您可以使用分析。数据只读取一次：

SQL> WITH your_query AS (
  2     SELECT 11182 person_id, to_date('30-DEC-11') start_dt,
  3            to_date('30-DEC-11') end_dt FROM dual
  4  UNION ALL SELECT 11182,to_date('29-DEC-11'),to_date('29-DEC-11') FROM DUAL
  5  UNION ALL SELECT 11182,to_date('28-DEC-11'),to_date('28-DEC-11') FROM DUAL
  6  UNION ALL SELECT 11182,to_date('27-DEC-11'),to_date('27-DEC-11') FROM DUAL
  7  UNION ALL SELECT 11182,to_date('26-DEC-11'),to_date('26-DEC-11') FROM DUAL
  8  UNION ALL SELECT 11182,to_date('31-OCT-11'),to_date('31-OCT-11') FROM DUAL
  9  UNION ALL SELECT 11182,to_date('02-SEP-11'),to_date('02-SEP-11') FROM DUAL
 10  UNION ALL SELECT 11182,to_date('29-JUL-11'),to_date('29-JUL-11') FROM DUAL
 11  UNION ALL SELECT 11182,to_date('22-JUL-11'),to_date('22-JUL-11') FROM DUAL
 12  UNION ALL SELECT 11182,to_date('25-APR-11'),to_date('25-APR-11') FROM DUAL
 13  )
 14  SELECT person_id, MIN(start_dt) start_dt, MAX(end_dt) end_dt
 15    FROM (SELECT person_id, start_dt, end_dt,
 16                 SUM(gap) over(PARTITION BY person_id
 17                               ORDER BY start_dt) contiguous_grp
 18             FROM (SELECT person_id, start_dt, end_dt,
 19                           CASE WHEN lag(end_dt) over(PARTITION BY person_id
 20                                     ORDER BY start_dt) + 1 >= start_dt
 21                              THEN 0 ELSE 1
 22                           END gap
 23                      FROM (SELECT * FROM your_query)))
 24   GROUP BY person_id, contiguous_grp
 25   ORDER BY 1, 2 DESC;
 PERSON_ID START_DT  END_DT
---------- --------- ---------
     11182 26-DEC-11 30-DEC-11
     11182 31-OCT-11 31-OCT-11
     11182 02-SEP-11 02-SEP-11
     11182 29-JUL-11 29-JUL-11
     11182 22-JUL-11 22-JUL-11
     11182 25-APR-11 25-APR-11

Answer 2

您需要使用分层查询来实现此目的。使用类似这样的内容：

SELECT
    person_id,
    person_holiday_id,
    MIN(BEGIN),
    END
FROM
(
    SELECT
        person_id,
        person_holiday_id,
        connect_by_root(timesheet_dt) AS BEGIN,
        timesheet_dt AS END,
        connect_by_isleaf AS is_leaf
    FROM
        timesheets
        CONNECT BY
            PRIOR TRUNC(timesheet_dt) + 1 = TRUNC(timesheet_dt)
            AND PRIOR person_id = person_id
)
WHERE
    is_leaf = 1
GROUP BY
    person_id,
    person_holiday_id,
    END
;

每个用户每个连续休假期返回一行。将其放入视图中，并使用它代替您在问题中发布的查询中的 timesheets 表。

请注意：

1. 此查询不会具有最高性能，因为timesheets中的每一行都用作分层查询的根行，并且可能用作叶行和中间行。当您比较运行子查询并将其返回的行数与完整查询返回的行数进行比较时，您可以看到这一点。
2. 如果您在 timesheet_dt 上放置索引并确保它从不包含时间部分，则可能会提高性能 - 然后您可以摆脱 TRUNC 函数。
3. 如果 timesheets 表中未输入周六和周日，则此语句将返回的最长休假期为周一到周五的一周。如果这是一个问题，则需要调整 CONNECT BY 子句。但是，这并不能解决公共假期未输入 timesheets 表的问题...