oracle为彼此之间在一分钟之内出售的商品选择日期

oracle为彼此之间在一分钟之内出售的商品选择日期

您可以使用

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_datesFROM   (  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.*,CASEWHEN ( sold_date       - LAG( sold_date ) OVER ( PARTITION BY location ORDER BY sold_date )     ) DAY TO SECOND     <= INTERVAL '1' MINUTETHEN 1ELSE 0END AS within_minute_of_prev,CASEWHEN ( LEAD( sold_date ) OVER ( PARTITION BY location ORDER BY sold_date )       - sold_date     ) DAY TO SECOND     <= INTERVAL '1' MINUTETHEN 1ELSE 0END AS within_minute_of_next    FROM   car_sales c  ))WHERe grp IS NOT NULLGROUP 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_timesFROM   car_salesMATCH_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, mnoORDER 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;

输出：

``` LOCATIONEQUIPMENT_TYPESSOLD_DATEScoventryRovers,Rovers10:00:12,10:00:45leamingtonRovers,Trans Am,Trans Am13:10:13,13:10:25,13:10:48leamingtonTrans Am,Rovers,corvette08:59:45,09:00:00,09:00:10

db
<>在这里拨弄