问题：
oracle为在1分钟内售出的商品选择日期
林泰平
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分钟内共享销售日期的记录，而是显示一个位置的所有记录。另外，是否可以通过locationequipment_type在1分钟内为匹配日期创建结果集的listagg？我不知道如何得到结果，然后让这些结果显示如下：
对于1分钟内的记录：
coventry 07-SEP-19 10:00:45 Rovers
coventry 07-SEP-19 10:00:12 Rovers
LOCATION listagg(EQUIPMENT_TYPE)
coventry Rovers,Rovers
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;
共有1个答案
郭俊人
我还没有一个答案，应该如何聚合一个由几行组成的链，其中每一行比上一行早不到一分钟，例如
SELECT 1, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '10:00:12' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, '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
因为您已经有了一个解决方案，它将所有这些值聚合到一个组中（即10:01:30-10:00:12>1分钟，但它们仍然在同一个组中），所以我将展示如何获得第一次和最后一次销售之间的最大差异<=1分钟的组。
在这种情况下，最好使用范围在当前行和之后的间隔'1'分钟之间的分析函数。例如，对于每一个销售，我们可以很容易地得到下一分钟在同一地点有多少销售：
with CAR_SALES ( NUM_CARS, EQUIPMENT_TYPE, LOCATION, SOLD_DATE ) AS (
SELECT 1, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '10:00:12' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, '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
)
SELECT
location,
num_cars,
equipment_type,
sold_date,
count(*)over(partition by LOCATION order by SOLD_DATE range between current row and interval'1' minute following) cnt
from car_sales
order by location,sold_date;
LOCATION NUM_CARS EQUIPMEN SOLD_DATE CNT
---------- ---------- -------- ------------------- ----------
coventry 2 Toyota 2019-09-06 10:00:00 1
coventry 8 Rovers 2019-09-06 18:00:00 1
coventry 9 Jaguars 2019-09-07 06:00:00 1
coventry 1 Rovers 2019-09-07 10:00:12 2
coventry 2 Rovers 2019-09-07 10:00:45 3
coventry 3 Rovers 2019-09-07 10:01:15 2
coventry 3 Rovers 2019-09-07 10:01:30 1
coventry 15 Rovers 2019-09-07 11:05:00 1
coventry 2 Jaguars 2019-09-07 17:02:07 1
leamington 10 Trans Am 2019-08-30 09:00:00 1
leamington 7 Rovers 2019-08-30 13:10:13 3
leamington 3 Trans Am 2019-08-30 13:10:25 2
leamington 2 Trans Am 2019-08-30 13:10:48 1
leamington 100 Trans Am 2019-09-06 08:59:45 3
leamington 4 Rovers 2019-09-06 09:00:00 2
leamington 1 corvette 2019-09-06 09:00:10 1
16 rows selected.
select *
from (
SELECT
location,
num_cars,
equipment_type,
sold_date,
count(*)over(partition by LOCATION order by SOLD_DATE range between interval'1' minute preceding and current row) cnt_preceding,
count(*)over(partition by LOCATION order by SOLD_DATE range between current row and interval'1' minute following) cnt_following
from car_sales
)
where
cnt_preceding > 1
or cnt_following > 1
order by location, sold_date;
结果:https://dbfiddle.uk/？rdbms=oracle_18&fiddle=000865dd639ab8d6d6e9fbf64100fcf0
LOCATION NUM_CARS EQUIPMEN SOLD_DATE CNT_PRECEDING CNT_FOLLOWING
---------- ---------- -------- ------------------- ------------- -------------
coventry 1 Rovers 2019-09-07 10:00:12 1 2
coventry 2 Rovers 2019-09-07 10:00:45 2 3
coventry 3 Rovers 2019-09-07 10:01:15 2 2
coventry 3 Rovers 2019-09-07 10:01:30 3 1
leamington 7 Rovers 2019-08-30 13:10:13 1 3
leamington 3 Trans Am 2019-08-30 13:10:25 2 2
leamington 2 Trans Am 2019-08-30 13:10:48 3 1
leamington 100 Trans Am 2019-09-06 08:59:45 1 3
leamington 4 Rovers 2019-09-06 09:00:00 2 2
leamington 1 corvette 2019-09-06 09:00:10 3 1
所以我们现在唯一需要做的就是以不重叠的间隔<=1分钟来聚合它们。我将使用另一种方法--match_recognite子句来显示它：
with CAR_SALES ( NUM_CARS, EQUIPMENT_TYPE, LOCATION, SOLD_DATE ) AS (
SELECT 1, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '10:00:12' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, '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
)
select *
from car_sales
match_recognize (
partition by location
order by sold_date
MEASURES
FIRST(A.SOLD_DATE) dt_strt,
LAST(SOLD_DATE) dt_end,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
PATTERN (A B+)
DEFINE
B AS B.sold_date < first(A.sold_date) + interval '1' minute
)
order by location, sold_date
;
结果：
LOCATION SOLD_DATE DT_STRT DT_END MNO CLS NUM_CARS EQUIPMEN
---------- ------------------- ------------------- ------------------- ---------- ----- ---------- --------
coventry 2019-09-07 10:00:12 2019-09-07 10:00:12 2019-09-07 10:00:12 1 A 1 Rovers
coventry 2019-09-07 10:00:45 2019-09-07 10:00:12 2019-09-07 10:00:45 1 B 2 Rovers
coventry 2019-09-07 10:01:15 2019-09-07 10:01:15 2019-09-07 10:01:15 2 A 3 Rovers
coventry 2019-09-07 10:01:30 2019-09-07 10:01:15 2019-09-07 10:01:30 2 B 3 Rovers
leamington 2019-08-30 13:10:13 2019-08-30 13:10:13 2019-08-30 13:10:13 1 A 7 Rovers
leamington 2019-08-30 13:10:25 2019-08-30 13:10:13 2019-08-30 13:10:25 1 B 3 Trans Am
leamington 2019-08-30 13:10:48 2019-08-30 13:10:13 2019-08-30 13:10:48 1 B 2 Trans Am
leamington 2019-09-06 08:59:45 2019-09-06 08:59:45 2019-09-06 08:59:45 2 A 100 Trans Am
leamington 2019-09-06 09:00:00 2019-09-06 08:59:45 2019-09-06 09:00:00 2 B 4 Rovers
leamington 2019-09-06 09:00:10 2019-09-06 08:59:45 2019-09-06 09:00:10 2 B 1 corvette
10 rows selected.
with CAR_SALES ( NUM_CARS, EQUIPMENT_TYPE, LOCATION, SOLD_DATE ) AS (
SELECT 1, 'Rovers', 'coventry', DATE '2019-09-07' + INTERVAL '10:00:12' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, '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
)
,matches as (
select *
from car_sales
match_recognize (
partition by location
order by sold_date
MEASURES
FIRST(A.SOLD_DATE) dt_strt,
LAST(SOLD_DATE) dt_end,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
PATTERN (A B+)
DEFINE
B AS B.sold_date < first(A.sold_date) + interval '1' minute
)
)
select
location,
mno,
dt_strt,
listagg(EQUIPMENT_TYPE,',')
within group(order by sold_date) EQUIPMENT_TYPEs,
listagg(to_char(sold_date,'hh24:mi:ss'),',')
within group(order by sold_date) sold_dates
from matches
group by
location,
mno,
dt_strt
order by 1,2
;
LOCATION MNO DT_STRT EQUIPMENT_TYPES SOLD_DATES
---------- ---------- ------------------- ---------------------------------------- --------------------------------------------------
coventry 1 2019-09-07 10:00:12 Rovers,Rovers 10:00:12,10:00:45
coventry 2 2019-09-07 10:01:15 Rovers,Rovers 10:01:15,10:01:30
leamington 1 2019-08-30 13:10:13 Rovers,Trans Am,Trans Am 13:10:13,13:10:25,13:10:48
leamington 2 2019-09-06 08:59:45 Trans Am,Rovers,corvette 08:59:45,09:00:00,09:00:10
类似资料：
-
我试图选择头衔、姓氏、出生日期和头衔为“销售代表”并且他们出生在1950年之前或之后的国家。 当a把日期放在代码中时，它会给我一个错误。 我认为这是正确的： 它给ORA-01843的错误：不是有效的月份 如果你能帮助我，谢谢你。 以下是表格的示例数据：
-
问题内容： 我在一个字段中有一个表格（其中包括日期）。 我需要获取所有列表的列表，这些列表比最早的日期更早，比最新的日期更早并且在表格中完全丢失。 因此，如果表包含： 我想要一个返回的查询： 问题答案： 这样的事情（假设您的表被命名，而date列被命名）： 编辑： 该子句称为“公用表表达式”，等效于派生表（“内联视图”）。 类似于 第二个CTE使用Oracle实现中未记录的功能简单地“即时”创建日
-
问题内容： 我正在研究文件加载器程序。 该程序的目的是获取一个输入文件，对其数据进行一些转换，然后将数据上传到Oracle数据库中。 我面临的问题是我需要优化在Oracle上非常大的输入数据的插入。 我正在将数据上传到表格中，可以说是ABC。 我正在C ++程序中使用Oracle提供的OCI库。具体来说，我使用OCI连接池进行多线程并加载到ORACLE中。（http://docs.oracle.c
-
问题内容： 我有一个像这样的Oracle SELECT查询： 我的意思是，我想选择字段“ date_enter”今天所在的所有位置。我已经尝试过类似的操作，但是没有用。 忠告： 我不能使用TO_CHAR，因为它变得太慢。 问题答案： 假设是一个字段： 默认情况下，该功能会删除时间部分，因此您可以在今天早晨午夜12点前使用该功能。 如果您特别想坚持使用，而不是，则可以执行以下操作： 是包容性的，因此
-
预售 设置预售相关活动 【应用场景】： 1、农产品未上市，比如非本季节食品，提前销售。 2、生鲜食品、蔬菜等为了避免损耗，提前销售，根据销量进行采购进货。 【功能描述】： 1、商家添加预售商品，设置商品的预售类型-定金预售、全款预售。 2、消费者根据预售商品类型，购买商品，定金预售模式商品，消费者下单时仅支付定金，等到预售活动结束后，可进行支付尾款。 3、全款模式预售，消费者下单时，需支付整笔订单
-
问题内容： 我有以下数据： 我正在尝试编写一个查询，该查询选择与某个日期匹配的所有记录，但是我正在为该字段使用时间戳记，无论我如何尝试，我的查询都不会产生任何结果。 SQL ：我尝试了以下查询，但没有任何结果 1。 2。 3。 仅当我将整个日期值设置为时，它才起作用； 问题答案： 尼古拉斯·克拉斯诺夫（Nicholas Krasnov）提供的答案