Как получить максимальную дату в LISTAGG Oracle 12c

Установка Oracle 12c. У меня возникли проблемы с организацией Oracle listagg() для отображения столбца количества автомобилей в моем запросе для отображения результатов только данных max (inventory_date) для каждого местоположения.

Я думал создать представление для агрегирования результатов:

create or replace view car_inventory_vw as 
select max(a.inventory_date) as max_date,
a.location,LISTAGG(a.COUNT_CARS||' '||a.EQUIPMENT_TYPE,',')
within GROUP (ORDER BY a.equipment_type) as equipment_list 
from car_equipment a 
group by a.location;

Создайте таблицу и вставьте тестовые строки, установленные на Oracle 12c в Windows

CREATE TABLE "CAR_EQUIPMENT" 
   (    "COUNT_CARS" NUMBER(10,0), 
"EQUIPMENT_TYPE" VARCHAR2(100 BYTE), 
"LOCATION" VARCHAR2(500 BYTE), 
"INVENTORY_DATE" DATE) 
SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "USERS" ;

INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION,     INVENTORY_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2019-09-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('9', 'Jaguars', 'coventry', TO_DATE('2019-09-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('7', 'Rovers', 'leamington', TO_DATE('2019-08-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('10','Trans Am', 'leamington', TO_DATE('2019-08-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2019-01-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('4', 'Rovers', 'leamington', TO_DATE('2019-01-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

Результатом должен быть инвентарный список макс (inventory_date) по местоположению:

MAX(INVENTORY_DATE) LOCATION    INVENTORY_LIST
-------------------- ----------- -------------------------
2019-08-30          leamington  10 Trans AM, 7 Rovers
2019-09-07          coventry    8 Rovers, 9 Jaguars

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

Заранее спасибо, Джилли


sql date oracle max listagg
person Gilly    schedule 09.09.2019    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете выполнить два шага:

  • сначала используйте RANK() в подзапросе, чтобы определить записи, которые соответствуют последней дате инвентаризации в каждом месте.

  • затем используйте агрегацию во внешнем запросе и сгенерируйте инвентарный список каждого местоположения, используя LISTAGG()

Запрос:

SELECT 
    inventory_date max_inventory_date,
    location,
    LISTAGG(count_cars || ' ' || equipment_type, ', ') 
        WITHIN GROUP (ORDER BY equipment_type) inventory_list
FROM (
    SELECT c.*, RANK() OVER(PARTITION BY location ORDER BY inventory_date DESC) rn
    FROM car_equipment c
) x 
WHERE rn = 1
GROUP BY inventory_date, location;

Демонстрация DB Fiddle

Данные:

COUNT_CARS | EQUIPMENT_TYPE | LOCATION   | INVENTORY_DATE
---------: | :------------- | :--------- | :-------------
         8 | Rovers         | coventry   | 07-SEP-19     
         9 | Jaguars        | coventry   | 07-SEP-19     
         7 | Rovers         | leamington | 30-AUG-19     
        10 | Trans Am       | leamington | 30-AUG-19     
         8 | Rovers         | coventry   | 07-JAN-19     
         4 | Rovers         | leamington | 08-JAN-19

Результаты:

MAX_INVENTORY_DATE | LOCATION   | INVENTORY_LIST       
:----------------- | :--------- | :--------------------
30-AUG-19          | leamington | 7 Rovers, 10 Trans Am
07-SEP-19          | coventry   | 9 Jaguars, 8 Rovers
person GMB    schedule 09.09.2019
comment
Как бы вы показали объединенные данные с даты вместо максимальной даты? - person Gilly; 11.09.2019