Oracle 分组设置无效的分组依据

我有下面的测试用例（见下面） ，它工作的很好。我希望所有的信息都显示为原样。问题是，我只想按access_date、雇员ID分组。当我删除第一个分组集时，我得到错误“无效分组依据”。有什么办法可以绕过这个问题吗？

ALTER SESSION SET 
 NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/
CREATE OR REPLACE FUNCTION generate_dates_pipelined(
  p_from IN DATE,
  p_to   IN DATE
)
RETURN nt_date  PIPELINED   DETERMINISTIC
IS
  v_start DATE := TRUNC(LEAST(p_from, p_to));
  v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
 LOOP
    PIPE ROW (v_start);
    EXIT WHEN v_start >= v_end;
    v_start := v_start + INTERVAL '1' DAY;
  END LOOP;
  RETURN;
END generate_dates_pipelined;
/

create table employees(
  employee_id NUMBER(6), 
  first_name VARCHAR2(20),
  last_name VARCHAR2(20),
  card_num VARCHAR2(10),
  work_days VARCHAR2(7)
);

ALTER TABLE employees
ADD (
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
      );

INSERT INTO employees (
  EMPLOYEE_ID,
  first_name, 
  last_name,
  card_num,
  work_days
)
WITH names AS ( 
  SELECT 1, 'Jane',     'Doe',      'F123456', 'NYYYYYN'   FROM dual UNION ALL 
  SELECT 2, 'Madison', 'Smith', 'R33432','NYYYYYN' FROM dual UNION ALL 
  SELECT 3, 'Justin',   'Case',     'C765341','NYYYYYN' FROM dual UNION ALL 
  SELECT 4, 'Mike',     'Jones',      'D564311','NYYYYYN' FROM dual  )
SELECT * FROM names;  

CREATE TABLE locations AS
   SELECT level AS location_id,
          'Door ' || level AS location_name,
          CASE round(dbms_random.value(1,3)) 
              WHEN 1 THEN 'T' 
              WHEN 2 THEN 'T' 
              WHEN 3 THEN 'T' 
          END AS location_type
  FROM   dual
  CONNECT BY level <= 3;


ALTER TABLE locations 
   ADD ( CONSTRAINT locations_pk
   PRIMARY KEY (location_id));

create table access_history(     
   seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   employee_id NUMBER(6), 
   card_num varchar2(10),
   location_id number(4),
   access_date date,
   processed NUMBER(1) default 0
);


create or replace procedure create_access_history(p_start_date date, p_end_date date)
IS
BEGIN
  INSERT into  access_history
     (
      employee_id,
      card_num,
       location_id, 
       access_date
     )
  WITH  cntr  AS
    (
  SELECT LEVEL - 1 AS n
  FROM     dual
  CONNECT BY LEVEL <= 15 -- Max number of rows per employee per date
  )
    , got_location_num  AS
  (
  SELECT  location_id
  ,    ROW_NUMBER () OVER (ORDER BY location_id) AS location_num
  ,    COUNT (*)   OVER ()            AS max_location_num
  FROM     locations
  )
  ,    employee_days  AS
  (
  SELECT     e.employee_id, e.card_num
  ,      d.column_value         AS access_date
  ,          dbms_random.value (0, 15) AS rn    -- 0 to max number of rows per employee per date
  FROM     employees e
  CROSS JOIN TABLE (generate_dates_pipelined (p_start_date, p_end_date)) d
  )
   ,        employee_n_days  AS
  (
  SELECT     ed.employee_id, ed.card_num, ed.access_date
  ,     dbms_random.value (0, 1) AS lrn
  FROM     employee_days        ed
  JOIN    cntr            c  ON  c.n <= ed.rn
  )
    SELECT    n.employee_id,       n.card_num, l.location_id,  n.access_date + 
    NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND') 
    FROM     employee_n_days  n
    JOIN     got_location_num  l  ON l.location_num = CEIL (n.lrn * l.max_location_num); 
END;
/

EXEC  create_access_history (SYSDATE,  SYSDATE+3);

select TRUNC(a. access_date) access_date,
       e.employee_id, 
       e.first_name,
       e.last_name,
       e.card_num,
       l.location_id,
       l.location_name,
       count(*) cnt         
FROM  employees    e
JOIN  access_history a ON a.employee_id = e.employee_id
JOIN  locations     l ON l.location_id = a.location_id
GROUP BY  GROUPING SETS (
  (TRUNC(a.access_date),
     e.employee_id, 
     e.first_name,
     e.last_name,
     e.card_num,
     l.location_id,
   l.location_name),
  (TRUNC(a.access_date), e.employee_id),       
  (trunc(a.access_date)),
  ()
)
ORDER BY trunc(a. access_date),e.employee_id,l.location_id;
复制

你是说你做这个的时候会得到ORA-979 ?

select TRUNC(a. access_date) access_date,
       e.employee_id, 
       e.first_name,
       e.last_name,
       e.card_num,
       l.location_id,
       l.location_name,
       count(*) cnt         
FROM  employees    e
JOIN  access_history a ON a.employee_id = e.employee_id
JOIN  locations     l ON l.location_id = a.location_id
GROUP BY  GROUPING SETS (
  (TRUNC(a.access_date), e.employee_id),       
  (trunc(a.access_date)),
  ()
)
ORDER BY trunc(a. access_date),e.employee_id,l.location_id;

ORA-00979: not a GROUP BY expression
复制

如果是这样，问题是选择列表（和排序依据）中的所有非聚合列也必须在您的分组依据中。

因此，您需要从选择和排序依据中删除这些列，或者将它们包括在您的组中，例如：

select TRUNC(a. access_date) access_date,
       e.employee_id, 
       count(*) cnt         
FROM  employees    e
JOIN  access_history a ON a.employee_id = e.employee_id
JOIN  locations     l ON l.location_id = a.location_id
GROUP BY  GROUPING SETS (
  (TRUNC(a.access_date), e.employee_id),       
  (trunc(a.access_date)),
  ()
)
ORDER BY trunc(a. access_date),e.employee_id;

ACCESS_DATE             EMPLOYEE_ID    CNT   
08032021 00:00:00                 1      4 
08032021 00:00:00                 2      6 
08032021 00:00:00                 3      8 
08032021 00:00:00                 4      9 
08032021 00:00:00                 27 
08042021 00:00:00                 1      5 
08042021 00:00:00                 2      9 
...
复制

如果要在输出中包括这些列，但“不关心”它们有哪些值，则可以从21c中使用any_value函数：

select TRUNC(a. access_date) access_date,
       e.employee_id,
       any_value ( e.first_name ),
       any_value ( e.last_name ),
       any_value ( e.card_num ),
       any_value ( l.location_id ),
       any_value ( l.location_name ),
       count(*) cnt         
FROM  employees    e
JOIN  access_history a ON a.employee_id = e.employee_id
JOIN  locations     l ON l.location_id = a.location_id
GROUP BY  GROUPING SETS (
  (TRUNC(a.access_date), e.employee_id),       
  (trunc(a.access_date)),
  ()
)
ORDER BY trunc(a. access_date),e.employee_id;

ACCESS_DATE         EMPLOYEE_ID ANY_VALUE(E.FIRST_NAME)   ANY_VALUE(E.LAST_NAME)   ANY_VALUE(E.CARD_NUM)   ANY_VALUE(L.LOCATION_ID) ANY_VALUE(L.LOCATION_NAME)  CNT   
08032021 00:00:00             1 Jane                      Doe                      F123456                                        1 Door 1                        3 
08032021 00:00:00             2 Madison                   Smith                    R33432                                         1 Door 1                        4 
08032021 00:00:00             3 Justin                    Case                     C765341                                        1 Door 1                        5 
08032021 00:00:00             4 Mike                      Jones                    D564311                                        1 Door 1                       11 
08032021 00:00:00         Madison                   Smith                    R33432                                         1 Door 1                       23 
08042021 00:00:00             1 Jane                      Doe                      F123456                                        1 Door 1                        9 
08042021 00:00:00             2 Madison                   Smith                    R33432                                         1 Door 1                        7 
...
复制