oracle为彼此之间在一分钟之内出售的商品选择日期 [英] oracle Select dates for items sold within 1 minute of each other
本文介绍了oracle为彼此之间在一分钟之内出售的商品选择日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
Oracle 12c数据库.
Oracle 12c database.
我有汽车销售表:
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分钟内发生的销售(销售日期).
I need to select only the sales (dates of sales) that have occurred within 1 minute by a location.
我创建了以下sql示例，但它不只显示在1分钟内某个位置共享销售日期的记录，而是显示该位置的所有记录.另外，是否可以根据位置类型创建一个结果集的列表列表，以匹配1分钟内的日期?我不知道如何得到结果，然后将结果显示为:
I have created the following sql example, but it is not displaying only the records that share a sales date within 1 minute for a location, it is showing all the records for a location. Also, is it possible to create a listagg of the result set by location|equipment_type for matching dates within 1 minute? I don't know how I would get the results then have those results display like:
对于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.
-- the equipment_type in this example just happens to be rover,rover, it would be whatever equipment_type is joined by the matching 1 minute sales.
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的结果进行列表汇总.
How could I create the correct results and make a listagg of the results of equipment_types by location that have sales within 60 seconds.
先谢谢您.吉莉
推荐答案
您可以使用 LAG / LEAD 分析函数比较上一行和下一行以确定它们是否在当前行的一分钟之内:
You can use LAG/LEAD analytic functions to compare the previous and next rows to determine if they are within a minute of the current row:
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;
其中，为您的示例数据:
Which, for your sample data:
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;
输出:
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<>小提琴此处
一个简短得多的Oracle 12c查询使用
A much shorter Oracle 12c query uses 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_TIMES
:--------- | :-------------------------- | :----------------------------------
coventry | Rovers,Rovers,Rovers,Rovers | 10:00:12,10:00:45,10:01:15,10:01:30
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<>小提琴此处
这篇关于oracle为彼此之间在一分钟之内出售的商品选择日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
