Агрегирование значений в тип таблицы в запросе GROUP BY

Предположим, у вас есть таблица (в Oracle):

CREATE TABLE CUSTOMER
(
  customer_id NUMBER,
  gender      CHAR(1)
);

Предположим, у вас есть тип таблицы:

CREATE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;

Можно ли написать GROUP BY запрос так, чтобы для каждой группы поля первичного ключа этой группы хранились в NUMBER_TABLE_TYPE? Например:

DECLARE
  CURSOR gender_cursor IS
    SELECT
      gender,
      /* The CUSTOMER_IDS column will be of type NUMBER_TABLE_TYPE: */
      SOME_MAGICAL_AGGREGATE_FUNCTION(customer_id) AS customer_ids
    FROM
      CUSTOMER
    GROUP BY
      gender;

  customer_ids NUMBER_TABLE_TYPE;
BEGIN
  FOR gender IN gender_cursor LOOP
    customer_ids := gender.customer_ids;
    FOR i IN customer_ids.FIRST .. customer_ids.LAST LOOP
      dbms_output.put_line(customer_ids(i));
    END LOOP;
  END LOOP;
END;

Я знаю, что для этого нужно использовать два курсора (один для гендерной группы, а другой - для запросов клиентов каждой группы). Мне просто любопытно посмотреть, можно ли это сделать. :)


types oracle group-by aggregate-functions plsql
person Adam Paynter    schedule 29.07.2010    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Два способа добиться этого. Во-первых, с помощью аналитики, чтобы получить количество всех записей для этого пола в каждой строке.

DECLARE
  CURSOR gender_cursor IS
    SELECT gender, customer_id, count(*) over (partition by gender) cnt_in_gender,
    FROM   CUSTOMER
    ORDER BY gender;
  v_prev_gender varchar2(1) := '?';
BEGIN
  FOR gender IN gender_cursor LOOP
      IF gender.gender != v_prev gender then
        dbms_output.put_line('You will now see '||gender.cnt_in_gender);
        v_prev_gender := gender.gender);
      END IF;
      dbms_output.put_line(gender.customer_ids);
  END LOOP;
END;

Во-вторых и ближе к вашему запросу

DECLARE
  CURSOR gender_cursor IS
    SELECT
      gender,
      /* The CUSTOMER_IDS column will be of type NUMBER_TABLE_TYPE: */
      CAST(COLLECT(customer_id) AS NUMBER_TABLE_TYPE) AS customer_ids
    FROM
      CUSTOMER
    GROUP BY
      gender;

  customer_ids NUMBER_TABLE_TYPE;
BEGIN
  FOR gender IN gender_cursor LOOP
    customer_ids := gender.customer_ids;
    FOR i IN customer_ids.FIRST .. customer_ids.LAST LOOP
      dbms_output.put_line(customer_ids(i));
    END LOOP;
  END LOOP;
END;
person Gary Myers    schedule 30.07.2010
comment
Ух ты! Это второе решение такое чистое и элегантное! Большое спасибо! - person Adam Paynter; 30.07.2010