[OT] Esperti di query SQL (Oracle)

Claudio Cioletti

unread,

Oct 14, 2015, 4:54:37 AM10/14/15

to

Avendo una query che tira fuori i seguenti record:
NR - DATA INIZIO - DATA FINE
01 - 02/01/2015 10:32:52 - 13/01/2015 16:52:12
02 - 05/01/2015 07:17:50 - 08/01/2015 16:07:33
03 - 08/01/2015 10:14:56 - 08/01/2015 11:43:57
04 - 08/01/2015 11:38:01 - 08/01/2015 12:43:10
05 - 08/01/2015 12:52:39 - 08/01/2015 16:10:43
06 - 09/01/2015 09:45:45 - 12/01/2015 12:19:48
07 - 09/01/2015 12:43:01 - 09/01/2015 15:37:56
08 - 09/01/2015 12:52:16 - 14/01/2015 17:15:03
09 - 11/01/2015 20:38:36 - 14/01/2015 20:09:00
10 - 13/01/2015 13:47:38 - 15/01/2015 10:06:11
11 - 15/01/2015 15:40:35 - 21/01/2015 12:25:05
12 - 15/01/2015 18:39:30 - 15/01/2015 19:49:11
13 - 16/01/2015 13:19:54 - 16/01/2015 14:50:05
14 - 19/01/2015 12:55:17 - 19/01/2015 13:59:14
15 - 19/01/2015 13:08:49 - 19/01/2015 17:54:08
16 - 20/01/2015 12:01:18 - 20/01/2015 14:10:27
17 - 20/01/2015 12:49:04 - 20/01/2015 19:36:26
18 - 21/01/2015 10:11:23 - 21/01/2015 13:06:31
19 - 22/01/2015 09:39:45 - 04/02/2015 16:11:21
20 - 22/01/2015 11:10:07 - 24/02/2015 12:41:57

io dovrei "tenere" solo quelli che non sono compresi
nel range minimo e massimo di date e, se la data_fine
è maggiore della precedente massima data fine, dovrei
"spostare" il range.
Per farmi capire, i record da 2 a 7 li dovrei scartare
e il record 8 dovrebbe spostarmi il range da
02/01/2015 10:32:52 - 13/01/2015 16:52:12 derivato
dal record 1 al range
02/01/2015 10:32:52 - 14/01/2015 17:15:03.
Così anche i record 9 e 10 dovrebbero spostare la
data fine.
Il record 11 invece inizia un nuovo range...
Alla fine dovrei avere un query che mi da:
01 - 02/01/2015 10:32:52 - 15/01/2015 10:06:11
02 - 15/01/2015 15:40:35 - 21/01/2015 13:06:31
03 - 22/01/2015 09:39:45 - 04/02/2015 16:11:21

Claudio Cioletti

unread,

Oct 15, 2015, 6:07:11 AM10/15/15

to

Ecco lo script per fare due prove:

CREATE TABLE TEST_RANGE_DATE
(SERVICE VARCHAR2(10),
APERTURA DATE ,
CHIUSURA DATE );
--
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('03/04/2015 15:31:48','DD/MM/YYYY HH24:MI:SS'),to_date('03/04/2015 16:08:02','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('03/07/2015 13:06:14','DD/MM/YYYY HH24:MI:SS'),to_date('15/07/2015 14:07:07','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('03/07/2015 15:55:39','DD/MM/YYYY HH24:MI:SS'),to_date('14/07/2015 11:10:54','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('03/07/2015 17:32:10','DD/MM/YYYY HH24:MI:SS'),to_date('07/07/2015 18:31:14','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('04/05/2015 11:32:47','DD/MM/YYYY HH24:MI:SS'),to_date('06/05/2015 14:54:12','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('08/10/2015 15:19:31','DD/MM/YYYY HH24:MI:SS'),to_date('12/10/2015 09:16:45','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('09/07/2015 12:45:36','DD/MM/YYYY HH24:MI:SS'),to_date('10/07/2015 11:35:18','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('10/09/2015 15:02:22','DD/MM/YYYY HH24:MI:SS'),to_date('17/09/2015 16:21:13','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('12/02/2015 11:58:58','DD/MM/YYYY HH24:MI:SS'),to_date('13/02/2015 17:05:20','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('13/04/2015 13:03:47','DD/MM/YYYY HH24:MI:SS'),to_date('18/06/2015 17:02:45','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('13/08/2015 15:07:19','DD/MM/YYYY HH24:MI:SS'),to_date('13/08/2015 16:48:13','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('14/07/2015 18:45:12','DD/MM/YYYY HH24:MI:SS'),to_date('15/07/2015 17:26:36','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('14/07/2015 19:00:19','DD/MM/YYYY HH24:MI:SS'),to_date('17/07/2015 14:48:20','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('16/07/2015 11:58:23','DD/MM/YYYY HH24:MI:SS'),to_date('28/08/2015 14:04:23','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('19/01/2015 12:12:27','DD/MM/YYYY HH24:MI:SS'),to_date('05/03/2015 15:57:22','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('21/01/2015 15:43:40','DD/MM/YYYY HH24:MI:SS'),to_date('29/01/2015 13:07:35','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('24/03/2015 14:40:12','DD/MM/YYYY HH24:MI:SS'),to_date('24/03/2015 18:38:36','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('25/06/2015 11:21:57','DD/MM/YYYY HH24:MI:SS'),to_date('26/06/2015 12:29:13','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('26/06/2015 10:53:59','DD/MM/YYYY HH24:MI:SS'),to_date('26/06/2015 16:25:18','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('27/04/2015 12:46:19','DD/MM/YYYY HH24:MI:SS'),to_date('04/05/2015 13:07:15','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('28/05/2015 12:09:12','DD/MM/YYYY HH24:MI:SS'),to_date('29/05/2015 12:08:42','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_01',to_date('29/06/2015 11:47:18','DD/MM/YYYY HH24:MI:SS'),to_date('29/06/2015 16:20:18','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('10/06/2015 17:32:37','DD/MM/YYYY HH24:MI:SS'),to_date('19/06/2015 19:24:39','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('11/05/2015 09:23:21','DD/MM/YYYY HH24:MI:SS'),to_date('11/05/2015 10:49:48','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('12/10/2015 08:43:43','DD/MM/YYYY HH24:MI:SS'),to_date('12/10/2015 14:19:54','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('14/08/2015 13:31:12','DD/MM/YYYY HH24:MI:SS'),to_date('21/08/2015 11:21:35','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('15/06/2015 11:51:29','DD/MM/YYYY HH24:MI:SS'),to_date('10/07/2015 12:45:26','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('17/08/2015 14:57:16','DD/MM/YYYY HH24:MI:SS'),to_date('28/08/2015 13:13:49','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('19/08/2015 20:05:41','DD/MM/YYYY HH24:MI:SS'),to_date('24/08/2015 17:52:10','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('21/04/2015 10:06:37','DD/MM/YYYY HH24:MI:SS'),to_date('30/04/2015 10:42:48','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('21/04/2015 15:12:15','DD/MM/YYYY HH24:MI:SS'),to_date('22/04/2015 18:32:14','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('25/03/2015 09:29:18','DD/MM/YYYY HH24:MI:SS'),to_date('08/04/2015 15:14:56','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('25/03/2015 17:12:19','DD/MM/YYYY HH24:MI:SS'),to_date('07/04/2015 15:16:40','DD/MM/YYYY HH24:MI:SS'));
insert into TEST_RANGE_DATE values('SERVICE_02',to_date('26/06/2015 20:16:42','DD/MM/YYYY HH24:MI:SS'),to_date('15/07/2015 16:51:19','DD/MM/YYYY HH24:MI:SS'));

Giacomo Degli Esposti

unread,

Oct 15, 2015, 10:42:24 AM10/15/15

to

On Wednesday, October 14, 2015 at 10:54:37 AM UTC+2, Claudio Cioletti wrote:
> Avendo una query che tira fuori i seguenti record:
> NR - DATA INIZIO - DATA FINE

[...]

> 20 - 22/01/2015 11:10:07 - 24/02/2015 12:41:57
> io dovrei "tenere" solo quelli che non sono compresi
> nel range minimo e massimo di date e, se la data_fine
> è maggiore della precedente massima data fine, dovrei
> "spostare" il range.
> Per farmi capire, i record da 2 a 7 li dovrei scartare
> e il record 8 dovrebbe spostarmi il range da
> 02/01/2015 10:32:52 - 13/01/2015 16:52:12 derivato
> dal record 1 al range
> 02/01/2015 10:32:52 - 14/01/2015 17:15:03.
> Così anche i record 9 e 10 dovrebbero spostare la
> data fine.
> Il record 11 invece inizia un nuovo range...
> Alla fine dovrei avere un query che mi da:
> 01 - 02/01/2015 10:32:52 - 15/01/2015 10:06:11
> 02 - 15/01/2015 15:40:35 - 21/01/2015 13:06:31
> 03 - 22/01/2015 09:39:45 - 04/02/2015 16:11:21

Non e' tanto chiaro cosa devi ottenere... :-\
Il range minimo e massimo sono dei dati che ti arrivano
dall'esterno?
Stai cercando di ottenere una query sql o di realizzare
una stored procedure ?

ciao
Giacomo

Claudio Cioletti

unread,

Oct 20, 2015, 11:30:07 AM10/20/15

to

I dati sono su una tabella fatta come ti descrivo sotto.
La richiesta era di fare una singola query
che tirasse fuori solo i "range" di date
APERTURA - CHIUSURA non compresi in altri "range"
ordinando per SERVIZIO e APERTURA...

1) SERVICE_01 19/01/2015 12:12:27 05/03/2015 15:57:22
2) SERVICE_01 21/01/2015 15:43:40 29/01/2015 13:07:35
3) SERVICE_01 12/02/2015 11:58:58 13/02/2015 17:05:20
4) SERVICE_01 24/03/2015 14:40:12 24/03/2015 18:38:36

Per esempio i record 2 e 3 sono "compresi" nel 1
mentre il record 4 no; quindi il risultato dovrebbe
essere:
1) SERVICE_01 19/01/2015 12:12:27 05/03/2015 15:57:22
2) SERVICE_01 24/03/2015 14:40:12 24/03/2015 18:38:36

Alla fine ce l'ho fatta, ma se volete provarci voi ecco i dati:

Giacomo Degli Esposti

unread,

Oct 20, 2015, 12:13:38 PM10/20/15

to

On Tuesday, October 20, 2015 at 5:30:07 PM UTC+2, Claudio Cioletti wrote:
> I dati sono su una tabella fatta come ti descrivo sotto.
> La richiesta era di fare una singola query
> che tirasse fuori solo i "range" di date
> APERTURA - CHIUSURA non compresi in altri "range"
> ordinando per SERVIZIO e APERTURA...
>
> 1) SERVICE_01 19/01/2015 12:12:27 05/03/2015 15:57:22
> 2) SERVICE_01 21/01/2015 15:43:40 29/01/2015 13:07:35
> 3) SERVICE_01 12/02/2015 11:58:58 13/02/2015 17:05:20
> 4) SERVICE_01 24/03/2015 14:40:12 24/03/2015 18:38:36
>
> Per esempio i record 2 e 3 sono "compresi" nel 1
> mentre il record 4 no; quindi il risultato dovrebbe
> essere:
> 1) SERVICE_01 19/01/2015 12:12:27 05/03/2015 15:57:22
> 2) SERVICE_01 24/03/2015 14:40:12 24/03/2015 18:38:36

Ahhhhh, detto cosi' e' molto piu' chiaro! :)

> Alla fine ce l'ho fatta, ma se volete provarci voi ecco i dati:
>
> CREATE TABLE TEST_RANGE_DATE
> (SERVICE VARCHAR2(10),
> APERTURA DATE ,
> CHIUSURA DATE );
> --
> insert into TEST_RANGE_DATE values('SERVICE_01',to_date('03/04/2015 15:31:48','DD/MM/YYYY HH24:MI:SS'),to_date('03/04/2015 16:08:02','DD/MM/YYYY HH24:MI:SS'));

[...]

Hai usato qualcosa del genere?

select L.* from TEST_RANGE_DATE L
where not EXISTS
(select 1 from TEST_RANGE_DATE R where R.Apertura <= L.apertura
AND R.chiusura >= L.chiusura )

ciao
Giacomo

Claudio Cioletti

unread,

Oct 21, 2015, 7:24:15 AM10/21/15

to

No, come dici te non tira fuori nulla!
E' un po più complessa:
select SERVICE ,
NEXT_APER INIZIO,
nvl(LEAD(MAX_CHIU) OVER(PARTITION BY SERVICE
ORDER BY SERVICE, APERTURA),CHIUSURA) FINE
from (
select SERVICE ,
APERTURA ,
CHIUSURA ,
MIN_APER ,
nvl(MAX_CHIU,CHIUSURA) MAX_CHIU ,
CASE WHEN APERTURA > MAX_CHIU THEN APERTURA ELSE MIN_APER END NEXT_APER
from (
select SERVICE ,
APERTURA ,
CHIUSURA ,
min(APERTURA) OVER(PARTITION BY SERVICE ORDER BY SERVICE, APERTURA ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) MIN_APER,
max(CHIUSURA) OVER(PARTITION BY SERVICE ORDER BY SERVICE, APERTURA ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) MAX_CHIU
from (
select SERVICE, APERTURA, CHIUSURA
from TEST_RANGE_DATE
order by 1,2,3
)
)
)
where NEXT_APER > MIN_APER
or APERTURA = MIN_APER
order by 1,2,3;

Giacomo Degli Esposti

unread,

Oct 21, 2015, 9:01:16 AM10/21/15

to

On Wednesday, October 21, 2015 at 1:24:15 PM UTC+2, Claudio Cioletti wrote:
> Il giorno martedì 20 ottobre 2015 18:13:38 UTC+2, Giacomo Degli Esposti ha scritto:
> > select L.* from TEST_RANGE_DATE L
> > where not EXISTS
> > (select 1 from TEST_RANGE_DATE R where R.Apertura <= L.apertura
> > AND R.chiusura >= L.chiusura )
>

> No, come dici te non tira fuori nulla!

Uh, hai ragione perche' con il <= non puo' funzionare perche'
trova che ogni record su L e' maggiorato dalla copia di se stesso su R.
Se hai a disposizione un campo chiave per evitare questo caso,
puoi provare cosi':
(qui id e' il suddetto campo chiave ...)

... where not EXISTS (select 1
from TEST_RANGE_datetime R

where R.Apertura <= L.apertura
AND R.chiusura >= L.chiusura

and R.service = L.service
and L.id <> r.id) ;

> E' un po più complessa:

[...]

Ah si senza quel campo chiave ti tocca fare un bel
giro per tirar fuori i dati! :)

ciao
Giacomo

Claudio Cioletti

unread,

Oct 21, 2015, 9:42:35 AM10/21/15

to

Quasi!
Anche utilizzando la ROWID, i tuoi dati non sono corretti:

SERVICE_01 19/01/2015 12:12:27 05/03/2015 15:57:22

SERVICE_01 24/03/2015 14:40:12 24/03/2015 18:38:36

SERVICE_01 03/04/2015 15:31:48 03/04/2015 16:08:02
SERVICE_01 13/04/2015 13:03:47 18/06/2015 17:02:45
SERVICE_01 25/06/2015 11:21:57 26/06/2015 12:29:13
SERVICE_01 26/06/2015 10:53:59 26/06/2015 16:25:18
SERVICE_01 29/06/2015 11:47:18 29/06/2015 16:20:18
SERVICE_01 03/07/2015 13:06:14 15/07/2015 14:07:07
SERVICE_01 14/07/2015 18:45:12 15/07/2015 17:26:36
SERVICE_01 14/07/2015 19:00:19 17/07/2015 14:48:20
SERVICE_01 16/07/2015 11:58:23 28/08/2015 14:04:23
SERVICE_01 10/09/2015 15:02:22 17/09/2015 16:21:13
SERVICE_01 08/10/2015 15:19:31 12/10/2015 09:16:45
SERVICE_02 25/03/2015 09:29:18 08/04/2015 15:14:56
SERVICE_02 21/04/2015 10:06:37 30/04/2015 10:42:48
SERVICE_02 11/05/2015 09:23:21 11/05/2015 10:49:48
SERVICE_02 10/06/2015 17:32:37 19/06/2015 19:24:39
SERVICE_02 15/06/2015 11:51:29 10/07/2015 12:45:26
SERVICE_02 26/06/2015 20:16:42 15/07/2015 16:51:19
SERVICE_02 14/08/2015 13:31:12 21/08/2015 11:21:35
SERVICE_02 17/08/2015 14:57:16 28/08/2015 13:13:49
SERVICE_02 12/10/2015 08:43:43 12/10/2015 14:19:54

mentre i dati corretti sono:

SERVICE_01 19/01/2015 12:12:27 05/03/2015 15:57:22

SERVICE_01 24/03/2015 14:40:12 24/03/2015 18:38:36

SERVICE_01 03/04/2015 15:31:48 03/04/2015 16:08:02
SERVICE_01 13/04/2015 13:03:47 18/06/2015 17:02:45
SERVICE_01 25/06/2015 11:21:57 26/06/2015 16:25:18
SERVICE_01 29/06/2015 11:47:18 29/06/2015 16:20:18
SERVICE_01 03/07/2015 13:06:14 28/08/2015 14:04:23
SERVICE_01 10/09/2015 15:02:22 17/09/2015 16:21:13
SERVICE_01 08/10/2015 15:19:31 12/10/2015 09:16:45
SERVICE_02 25/03/2015 09:29:18 08/04/2015 15:14:56
SERVICE_02 21/04/2015 10:06:37 30/04/2015 10:42:48
SERVICE_02 11/05/2015 09:23:21 11/05/2015 10:49:48
SERVICE_02 10/06/2015 17:32:37 15/07/2015 16:51:19
SERVICE_02 14/08/2015 13:31:12 28/08/2015 13:13:49
SERVICE_02 12/10/2015 08:43:43 12/10/2015 14:19:54

Giacomo Degli Esposti

unread,

Oct 21, 2015, 9:56:33 AM10/21/15

to

On Wednesday, October 21, 2015 at 3:42:35 PM UTC+2, Claudio Cioletti wrote:
> Quasi!
> Anche utilizzando la ROWID, i tuoi dati non sono corretti:

[...]

> SERVICE_01 25/06/2015 11:21:57 26/06/2015 12:29:13
> SERVICE_01 26/06/2015 10:53:59 26/06/2015 16:25:18

[...]

> mentre i dati corretti sono:

[...]

> SERVICE_01 25/06/2015 11:21:57 26/06/2015 16:25:18

[...]

OK, adesso finalmente ho capito cosa volevi ottenere e perche'
ti esce una query cosi' complessa. In pratica da quello che vedo
hai bisogno di riunire piu' record "incatenati tra loro"
in un solo record.
Allora si, la mia query non puo' andare perche restituisce solo
record presenti nella tabella originaria.
A sto punto, se hai trovato la soluzione buon per te! :)

ciao
Giacomo

Claudio Cioletti

unread,

Oct 21, 2015, 10:48:22 AM10/21/15

to

Esatto, è difficile da spiegare anche a voce.
(anch'io ci ho messo un po per capire cosa voleva il cliente).

Ciao ciao.