您可以使用LAG/LEAD解析函数比较上一行和下一行，以确定它们是否在当前行的一分钟之内：
SELECT location,
LISTAGG( equipment_type, ',' )
WITHIN GROUP ( ORDER BY sold_date )
AS equipment_types,
LISTAGG( TO_CHAR( sold_date, 'HH24:MI:SS' ), ',' )
WITHIN GROUP ( ORDER BY sold_date )
AS sold_dates
FROM (
SELECT num_cars,
equipment_type,
location,
sold_date,
CASE
WHEN within_minute_of_prev = 1 OR within_minute_of_next = 1
THEN SUM(
CASE
WHEN within_minute_of_prev = 0 AND within_minute_of_next = 1
THEN 1
ELSE 0
END
) OVER ( PARTITION BY location ORDER BY sold_date )
END AS grp
FROM (
SELECT c.*,
CASE
WHEN ( sold_date
- LAG( sold_date ) OVER ( PARTITION BY location ORDER BY sold_date )
) DAY TO SECOND
<= INTERVAL '1' MINUTE
THEN 1
ELSE 0
END AS within_minute_of_prev,
CASE
WHEN ( LEAD( sold_date ) OVER ( PARTITION BY location ORDER BY sold_date )
- sold_date
) DAY TO SECOND
<= INTERVAL '1' MINUTE
THEN 1
ELSE 0
END AS within_minute_of_next
FROM car_sales c
)
)
WHERE grp IS NOT NULL
GROUP BY location, grp;
其中，对于您的示例数据：
CREATE TABLE CAR_SALES ( NUM_CARS, EQUIPMENT_TYPE, LOCATION, SOLD_DATE ) AS
SELECT 8, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '10:00:12' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '10:00:45' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 9, 'Jaguars', 'coventry', DATE '2019-09-07' + INTERVAL '06:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 7, 'Rovers', 'leamington', DATE '2019-08-30' + INTERVAL '13:10:13' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 10, 'Trans Am', 'leamington', DATE '2019-08-30' + INTERVAL '09:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'Trans Am', 'leamington', DATE '2019-08-30' + INTERVAL '13:10:48' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 8, 'Rovers', 'coventry', DATE '2019-09-06' + INTERVAL '18:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 4, 'Rovers', 'leamington', DATE '2019-09-06' + INTERVAL '09:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 100, 'Trans Am', 'leamington', DATE '2019-09-06' + INTERVAL '08:59:45' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'corvette', 'leamington', DATE '2019-09-06' + INTERVAL '09:00:10' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'Toyota', 'coventry', DATE '2019-09-06' + INTERVAL '10:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 15, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '11:05:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'Jaguars', 'coventry', DATE '2019-09-07' + INTERVAL '17:02:07' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, 'Trans Am', 'leamington', DATE '2019-08-30' + INTERVAL '13:10:25' HOUR TO SECOND FROM DUAL;
输出：
位置| EQUIPMENT_TYPES | SOLD_DATES
：--------- | ：----------------------- | ：-------------------------
考文垂| 流浪者，流浪者| 10：00：12,10：00：45
利明顿| 流浪者，Trans Am，Trans Am | 13：10：13,13：10：25,13：10：48
利明顿| Trans Am，流浪者，护卫舰| 08：59：45,09：00：00,09：00：10
db <>在这里拨弄
一个简短得多的Oracle 12c查询使用MATCH_RECOGNIZE：
SELECT location,
LISTAGG( equipment_type, ',' )
WITHIN GROUP ( ORDER BY sold_date )
AS equipment_types,
LISTAGG( TO_CHAR( sold_date, 'HH24:MI:SS' ), ',' )
WITHIN GROUP ( ORDER BY sold_date )
AS sold_times
FROM car_sales
MATCH_RECOGNIZE (
PARTITION BY location
ORDER BY sold_date
MEASURES
MATCH_NUMBER() AS mno
ALL ROWS PER MATCH
PATTERN (A B+)
DEFINE
B AS B.sold_date <= PREV(B.sold_date) + interval '1' minute
)
GROUP BY location, mno
ORDER BY location, mno;
其中，对于测试数据：
CREATE TABLE CAR_SALES ( NUM_CARS, EQUIPMENT_TYPE, LOCATION, SOLD_DATE ) AS
SELECT 8, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '10:00:12' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '10:00:45' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '10:01:15' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '10:01:30' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 9, 'Jaguars', 'coventry', DATE '2019-09-07' + INTERVAL '06:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 7, 'Rovers', 'leamington', DATE '2019-08-30' + INTERVAL '13:10:13' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 10, 'Trans Am', 'leamington', DATE '2019-08-30' + INTERVAL '09:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'Trans Am', 'leamington', DATE '2019-08-30' + INTERVAL '13:10:48' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 8, 'Rovers', 'coventry', DATE '2019-09-06' + INTERVAL '18:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 4, 'Rovers', 'leamington', DATE '2019-09-06' + INTERVAL '09:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 100, 'Trans Am', 'leamington', DATE '2019-09-06' + INTERVAL '08:59:45' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'corvette', 'leamington', DATE '2019-09-06' + INTERVAL '09:00:10' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'Toyota', 'coventry', DATE '2019-09-06' + INTERVAL '10:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 15, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '11:05:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, 'Jaguars', 'coventry', DATE '2019-09-07' + INTERVAL '17:02:07' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, 'Trans Am', 'leamington', DATE '2019-08-30' + INTERVAL '13:10:25' HOUR TO SECOND FROM DUAL;
输出：
db <>在这里拨弄
602392714
清零编程群