1. 首页
  2. 问题
  3. oracle为彼此之间在一分钟之内出售的商品选择日期
小编典典

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

sql

Oracle 12c数据库。

我有汽车销售表：

CREATE TABLE CAR_SALES 
   (    NUM_CARS NUMBER(10,0), 
    EQUIPMENT_TYPE VARCHAR2(100), 
    LOCATION VARCHAR2(500), 
    SOLD_DATE DATE
   ) ;

--Insert sample data

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('8','Rovers','coventry','07-SEP-19 10:00:12');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('1','Rovers','coventry','07-SEP-19 10:00:45');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('9','Jaguars','coventry','07-SEP-19 06:00:00');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('7','Rovers','leamington','30-AUG-19 13:10:13');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('10','Trans Am','leamington','30-AUG-19 09:00:00');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('2','Trans Am','leamington','30-AUG-19 13:10:48');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('8','Rovers','coventry','06-SEP-19 18:00:00');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('4','Rovers','leamington','06-SEP-19 09:00:00');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('100','Trans Am','leamington','06-SEP-19 08:59:45');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('1','corvette','leamington','06-SEP-19 09:00:10');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('2','Toyota','coventry','06-SEP-19 10:00:00');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('15','Rovers','coventry','07-SEP-19 11:05:00');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('2','Jaguars','coventry','07-SEP-19 17:02:07');

insert into car_sales (num_cars,equipment_type,location,sold_date) values ('3','Trans Am','leamington','30-AUG-19 13:10:25');

commit;

我只需要选择一个位置在1分钟内发生的销售（销售日期）。

我创建了以下sql示例，但它不只显示在1分钟内某个位置共享销售日期的记录，而是显示该位置的所有记录。另外，是否可以根据位置类型创建一个结果集的列表列表，以匹配1分钟内的日期？我不知道如何得到结果，然后将结果显示为：

对于1分钟内的记录：

coventry  07-SEP-19 10:00:45 Rovers
coventry  07-SEP-19 10:00:12 Rovers

Listagg将是：

LOCATION listagg(EQUIPMENT_TYPE)

coventry Rovers,Rovers

-在此示例中，equipment_type恰好是流动站，流动站，即匹配1分钟销售量的任何equipment_type。

SQL>
select location,sold_date,equipment_type,num_cars
from car_sales c
where exists( select 'X' 
                from car_sales x
                  where c.location=x.location
                  and c.equipment_type=x.equipment_type
                  and c.sold_date between x.sold_date - interval '1' MINUTE
                  and x.sold_date + interval '1' MINUTE
                  )
                  group by location,sold_date,equipment_type,num_cars
                  order by sold_date desc;

我如何才能创建正确的结果，并按位置列出在60秒内有销售的equipment_types结果列表。

先感谢您。吉莉


阅读 126

收藏
2021-05-16

共1个答案

小编典典

您可以使用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;

输出：

``` LOCATION EQUIPMENT_TYPES SOLD_DATES
coventry Rovers,Rovers 10:00:12,10:00:45
leamington Rovers,Trans Am,Trans Am 13:10:13,13:10:25,13:10:48
leamington Trans Am,Rovers,corvette 08:59:45,09:00:00,09:00:10

db
<>在这里拨弄

2021-05-16