Documentazione dell'infrastruttura Oracle Cloud

Riferimento funzioni (flusso dati)

Le funzioni riportate di seguito vengono utilizzate con gli operatori di un flusso di dati per consentire la creazione di espressioni.

Funzioni di aggregazione
Funzione descrizione; Esempio
COUNT(value[, value]*) Restituisce il numero di righe per le quali una o più espressioni fornite sono tutte non nulle. COUNT(expr1)
COUNT(*) Restituisce il numero totale di righe recuperate, incluse le righe contenenti valori nulli. COUNT(*)
MAX(value) Restituisce il valore massimo dell'argomento. MAX(expr)
MIN(value) Restituisce il valore minimo dell'argomento. MIN(expr)
SUM(numeric) Restituisce la somma calcolata in base ai valori di un gruppo. SUM(expr1)
AVG(numeric) Restituisce la media dei valori numerici in un'espressione. AVG(AGGREGATE_1.src1.attribute1)
LISTAGG(column[, delimiter]) WITHIN GROUP (order_by_clause)

Concatena i valori della colonna di input con il delimitatore specificato per ogni gruppo in base alla clausola order.

La colonna contiene i valori che si desidera concatenare insieme nel risultato.

Il delimitatore separa i valori della colonna nel risultato. Se non viene fornito un delimitatore, viene utilizzato un carattere vuoto.

order_by_clause determina l'ordine di restituzione dei valori concatenati.

Questa funzione può essere utilizzata solo come aggregatore e può essere utilizzata con il raggruppamento o senza raggruppamento. Se si utilizza senza raggruppamento, il risultato è una singola riga. Se si utilizza un raggruppamento, la funzione restituisce una riga per ogni gruppo.

Si consideri una tabella con due colonne, id, name. Nella tabella sono presenti tre righe. I valori della colonna id sono 101, 102, 102. I valori della colonna name sono A, B, C.

+-----+--------+
| id  | name  |
+-----+--------+
| 101 | A     |
+-----+--------+
| 102 | B     |
+-----+--------+
| 102 | C     |
+-----+--------+

Esempio 1: senza raggruppamento

LISTAGG(id, '-') WITHIN GROUP (ORDER BY id) restituisce la colonna name con il valore A-B-C 

+--------+
| name   |
+--------+
| A-B-C  |
+--------+

Esempio 2: Raggruppa per id

LISTAGG(id, '-') WITHIN GROUP (ORDER BY id) restituisce la colonna name con i valori A e B-C in due gruppi.

+--------+
| name   |
+--------+
| A      |
+--------+
| B-C    |
+--------+
Funzioni analitiche
Funzione descrizione; Esempio
FIRST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ] ) Restituisce il valore valutato nella riga corrispondente alla prima riga del frame della finestra. FIRST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) restituisce il primo valore di BANK_ID in una finestra in cui le righe vengono calcolate come riga corrente e 1 riga dopo tale riga, partizionata per BANK_ID e in ordine crescente per BANK_NAME.
LAG(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) Restituisce il valore valutato alla riga in corrispondenza di un determinato offset prima della riga corrente all'interno della partizione. In assenza di tale riga, viene restituito il valore predefinito. L'offset e il valore predefinito vengono valutati rispetto alla riga corrente. Se omesso, l'offset viene impostato su 1 per impostazione predefinita e su NULL. LAG(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME DESC) restituisce il valore di BANK_ID dalla seconda riga prima della riga corrente, partizionato da BANK_ID e in ordine decrescente di BANK_NAME. In assenza di tale valore, viene restituito hello.
LAST_VALUE(value) OVER ([ partition_clause ] order_by_clause [ windowFrame_clause ]) Restituisce il valore valutato nella riga che corrisponde all'ultima riga del frame della finestra. LAST_VALUE(BANK_ID) OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) restituisce l'ultimo valore di BANK_ID in una finestra in cui le righe vengono calcolate come riga corrente e 1 riga dopo tale riga, partizionata per BANK_ID e in ordine crescente per BANK_NAME.
LEAD(value[, offset[, default]]) OVER ([ partition_clause ] order_by_clause) Restituisce il valore valutato alla riga in corrispondenza di un determinato offset dopo la riga corrente all'interno della partizione. In assenza di tale riga, viene restituito il valore predefinito. L'offset e il valore predefinito vengono valutati rispetto alla riga corrente. Se omesso, l'offset viene impostato su 1 per impostazione predefinita e su NULL. LEAD(BANK_ID, 2, 'hello') OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) restituisce il valore di BANK_ID dalla seconda riga dopo la riga corrente, partizionato da BANK_ID e in ordine crescente da BANK_NAME. In assenza di tale valore, viene restituito hello.
RANK() OVER([ partition_clause ] order_by_clause) Restituisce la classificazione della riga corrente con interruzioni, contando da 1. RANK() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) restituisce la classificazione di ogni riga all'interno del gruppo di partizioni di BANK_ID, in ordine crescente di BANK_NAME.
ROW_NUMBER() OVER([ partition_clause ] order_by_clause) Restituisce il numero univoco della riga corrente all'interno della partizione, contando da 1. ROW_NUMBER() OVER (PARTITION BY BANK_ID ORDER BY BANK_NAME) restituisce il numero di riga univoco di ogni riga all'interno del gruppo di partizioni di BANK_ID, in ordine crescente di BANK_NAME.
Funzioni aritmetriche
Funzione descrizione; Esempio
ABS(numeric) Restituisce la potenza assoluta del valore numeric. ABS(-1)
CEIL(numeric) Restituisce il numero intero minimo non maggiore del valore numeric CEIL(-1,2)
FLOOR(numeric) Restituisce il numero intero più alto non maggiore del valore numeric. FLOOR(-1,2)
MOD(numeric1, numeric2) Restituisce il resto dopo che numeric1 è diviso per numeric2. MOD(8,2)
POWER(numeric1, numeric2) Alza numeric1 alla potenza di numeric2. POWER(2,3)
ROUND(numeric1, numeric2) Restituisce numeric1 arrotondato alle posizioni decimali numeric2. ROUND(2.5,0)
TRUNC(numeric1, numeric2) Restituisce numeric1 troncato alle posizioni decimali numeric2. TRUNC(2.5,0)
TO_NUMBER(expr[, format, locale]) Converte un valore expr in un numero, in base al valore format e al valore locale facoltativo fornito. La versione locale predefinita è en-US. Tag lingua supportati.

Pattern di formato supportati:

  • 0: una cifra
  • #: una cifra, zero viene visualizzata come assente
  • .: segnaposto per il separatore decimale
  • ,: segnaposto per il separatore di raggruppamento
  • E: separa mantissa ed esponente per i formati esponenziali
  • -: prefisso negativo predefinito
  • ¤: segno di valuta; sostituito dal simbolo di valuta; se raddoppiato, sostituito dal simbolo di valuta internazionale; se presente in un modello, viene utilizzato il separatore decimale monetario al posto del separatore decimale

TO_NUMBER('5467.12') returns restituisce 5467.12

TO_NUMBER('-USD45,677.7', '¤¤##,###.#', 'en-US') restituisce -45677.7

Funzioni array

Solo l'operatore Expression supporta le funzioni array.

Funzione descrizione; Esempio
ARRAY_POSITION(array(...), element) Restituisce la posizione della prima occorrenza dell'elemento specificato nell'array specificato.

La posizione non è basata su zero, ma inizia con 1.

ARRAY_POSITION(array(3, 2, 1, 4, 1), 1) restituisce 3
REVERSE(array(...)) Restituisce l'array di elementi specificato in ordine inverso. REVERSE(array(2, 1, 4, 3)) restituisce [3,4,1,2]
ELEMENT_AT(array(...), index) Restituisce l'elemento dell'array specificato nella posizione di indice specificata.

L'indice non è basato su zero, ma inizia con 1.

Se index = -1, restituisce l'ultimo elemento.

 ELEMENT_AT(array(1, 2, 3), 2) restituisce 2
Funzioni condizionali
Funzione descrizione; Esempio
COALESCE(value, value [, value]*) Restituisce il primo argomento non nullo, se esistente, altrimenti restituisce un valore nullo. COALESCE(NULL, 1, NULL) restituisce 1
NULLIF(value, value) Restituisce un valore nullo se i due valori sono uguali, altrimenti restituisce il primo valore. NULLIF('ABC','XYZ') restituisce ABC
Funzioni di data e ora
Funzione descrizione; Esempio
CURRENT_DATE Restituisce la data corrente. CURRENT_DATE restituisce la data odierna, ad esempio 2023-05-26
CURRENT_TIMESTAMP Restituisce la data e l'ora correnti per il fuso orario della sessione. CURRENT_TIMESTAMP restituisce la data odierna e l'ora corrente, ad esempio 2023-05-26 12:34:56
DATE_ADD(date, number_of_days) Restituisce la data number specificata dei giorni successivi al valore date specificato. DATE_ADD('2017-07-30', 1) restituisce 2017-07-31
DATE_FORMAT(expr, format[, locale])

Formatta un valore expr di data, in base al valore format e all'opzione locale fornita. La versione locale predefinita è en-US. Tag lingua supportati.

Pattern di formato data supportati:

  • aa: anno composto da due cifre.
  • yyyy: anno composto da quattro cifre.
  • M: mese numerico, ad esempio 1 per gennaio
  • MM: mese numerico, ad esempio 01 per gennaio
  • MMM: mese abbreviato, ad esempio Gen
  • MMMM: mese completo, ad esempio gennaio
  • d: giorno numerico del mese, ad esempio 1 per il 1° giugno
  • dd: giorno numerico del mese, ad esempio 01 per il 1° giugno
  • DDD: giorno numerico dell'anno compreso tra 001 e 366, ad esempio 002 per il 2 gennaio
  • F: giorno numerico della settimana in un mese, ad esempio 3 per il 3° lunedì di giugno.
  • EEE o E: il giorno della settimana denominato abbreviato, ad esempio Sole per domenica
  • EEEE: il giorno della settimana, ad esempio la domenica.
  • HH: formato di 24 ore da 00 a 23
  • H: formato di 24 ore da 0 a 23
  • hh: formato di 12 ore da 01 a 12
  • h: formato di 12 ore da 1 a 12
  • mm: minuti da 00 a 59
  • ss: secondi da 00 a 59
  • SSS: millisecondi da 000 a 999
  • a: AM o PM
  • z: fuso orario come PDT

DATE_FORMAT(Date '2020-10-11', 'yyyy-MM-dd') restituisce '2020-10-11'. Il primo argomento è un oggetto Date che rappresenta l'11 ottobre 2020.

DATE_FORMAT(Date '2018-junio-17', 'yyyy/MMMM/dd', 'es-ES') restituisce '2018/junio/17'

DAYOFMONTH(date) Restituisce il giorno della data del mese. DAYOFMONTH('2020-12-25') restituisce 25
DAYOFWEEK(date) Restituisce il giorno della data della settimana. DAYOFWEEK('2020-12-25') restituisce 6 per venerdì. Negli Stati Uniti, la domenica è considerata 1, il lunedì è 2 e così via.
DAYOFYEAR(date) Restituisce il giorno della data nell'anno. DAYOFYEAR('2020-12-25') restituisce 360
WEEKOFYEAR(date) Restituisce la settimana della data nell'anno.

WEEKOFYEAR('2022-07-28') restituisce 30

WEEKOFYEAR('2022-07-28 13:24:30') restituisce 30

HOUR(datetime) Restituisce il valore ora di dataora. HOUR('2020-12-25 15:10:30') restituisce 15
LAST_DAY(date) Restituisce l'ultimo giorno del mese della data. LAST_DAY('2020-12-25') restituisce 31
MINUTE(datetime) Restituisce il valore minuto della data/ora. HOUR('2020-12-25 15:10:30') restituisce 10
MONTH(date) Restituisce il valore del mese della data. MONTH('2020-06-25') restituisce 6
QUARTER(date) Restituisce il trimestre dell'anno in cui si trova la data. QUARTER('2020-12-25') restituisce 4
SECOND(datetime) Restituisce il secondo valore della data/ora. SECOND('2020-12-25 15:10:30') restituisce 30
TO_DATE(string, format_string[, localeStr]) Analizza l'espressione di stringa con l'espressione format_string in una data. Le impostazioni nazionali sono facoltative. L'impostazione predefinita è en-US. Tag lingua supportati.

Nelle espressioni della pipeline, format_string deve utilizzare i codici di formato strftime. Di seguito sono riportate le stringhe di formato che fanno distinzione tra maiuscole e minuscole supportate.

  • aa: anno composto da due cifre.
  • yyyy: anno composto da quattro cifre.
  • M: mese numerico, ad esempio 1 per gennaio
  • MM: mese numerico, ad esempio 01 per gennaio
  • MMM: mese abbreviato, ad esempio Gen
  • MMMM: mese completo, ad esempio gennaio
  • d: giorno numerico del mese, ad esempio 1 per il 1° giugno
  • dd: giorno numerico del mese, ad esempio 01 per il 1° giugno
  • DDD: giorno numerico dell'anno compreso tra 001 e 366, ad esempio 002 per il 2 gennaio
  • F: giorno numerico della settimana in un mese, ad esempio 3 per il 3° lunedì di giugno.
  • EEE o E: il giorno della settimana denominato abbreviato, ad esempio Sole per domenica
  • EEEE: il giorno della settimana, ad esempio la domenica.
  • HH: formato di 24 ore da 00 a 23
  • H: formato di 24 ore da 0 a 23
  • hh: formato di 12 ore da 01 a 12
  • h: formato di 12 ore da 1 a 12
  • mm: minuti da 00 a 59
  • ss: secondi da 00 a 59
  • SSS: millisecondi da 000 a 999
  • a: AM o PM
  • z: fuso orario come PDT

TO_DATE('31 December 2016', 'dd MMMM yyyy') restituisce un valore di data pari a 2016-12-31

TO_DATE('2018/junio/17', 'yyyy/MMMM/dd', 'es-ES') restituisce un valore di data pari a 2018-06-17

TO_TIMESTAMP(expr, format_string[, localeStr]) Converte un valore expr di VARCHAR in un valore di TIMESTAMP, in base al valore format_string e al valore localeStr facoltativo fornito.

Nelle espressioni della pipeline, format_string deve utilizzare i codici di formato strftime. Altrimenti, i pattern di formato supportati sono:

  • aa: anno composto da due cifre.
  • yyyy: anno composto da quattro cifre.
  • M: mese numerico, ad esempio 1 per gennaio
  • MM: mese numerico, ad esempio 01 per gennaio
  • MMM: mese abbreviato, ad esempio Gen
  • MMMM: mese completo, ad esempio gennaio
  • d: giorno numerico del mese, ad esempio 1 per il 1° giugno
  • dd: giorno numerico del mese, ad esempio 01 per il 1° giugno
  • DDD: giorno numerico dell'anno compreso tra 001 e 366, ad esempio 002 per il 2 gennaio
  • F: giorno numerico della settimana in un mese, ad esempio 3 per il 3° lunedì di giugno.
  • EEE o E: il giorno della settimana denominato abbreviato, ad esempio Sole per domenica
  • EEEE: il giorno della settimana, ad esempio la domenica.
  • HH: formato di 24 ore da 00 a 23
  • H: formato di 24 ore da 0 a 23
  • hh: formato di 12 ore da 01 a 12
  • h: formato di 12 ore da 1 a 12
  • mm: minuti da 00 a 59
  • ss: secondi da 00 a 59
  • SSS: millisecondi da 000 a 999
  • a: AM o PM
  • z: fuso orario come PDT
 TO_TIMESTAMP('2020-10-11 11:10:10', 'yyyy-MM-dd HH:mm:ss') restituisce un oggetto TIMESTAMP che rappresenta 11am 10:10 Oct 11th, 2020
WEEK(date)

Restituisce il valore della settimana della data.

 WEEK('2020-06-25') restituisce 4
YEAR(date) Restituisce il valore dell'anno della data. YEAR('2020-06-25') restituisce 2020
ADD_MONTHS(date_expr, number_months) Restituisce la data successiva all'aggiunta del numero di mesi specificato alla data, all'indicatore orario o alla stringa specificata con un formato quale yyyy-MM-dd o yyyy-MM-dd HH:mm:ss.SSS.

ADD_MONTHS('2017-07-30', 1) restituisce 2017-08-30

ADD_MONTHS('2017-07-30 09:07:21', 1) restituisce 2017-08-30

MONTHS_BETWEEN(start_date_expr, end_date_expr)

Restituisce il numero di mesi compresi tra start_date_expr e end_date_expr. start_date_expr e end_date_expr possono essere una data, un indicatore orario o una stringa con un formato quale yyyy-MM-dd o yyyy-MM-dd HH:mm:ss.SSS

Viene restituito un numero intero se entrambe le date sono lo stesso giorno del mese o se entrambe sono l'ultimo giorno dei rispettivi mesi. In caso contrario, la differenza viene calcolata in base a 31 giorni al mese.

MONTHS_BETWEEN('2022-01-01', '2022-01-31') restituisce 1

MONTHS_BETWEEN('2022-07-28', '2020-07-25') restituisce 24

MONTHS_BETWEEN('2022-07-28 13:24:30', '2020-07-25 13:24:30') restituisce 24
FROM_UTC_TIMESTAMP(time_stamp, time_zone)

Interpreta una data, un indicatore orario o una stringa come ora UTC e la converte in un indicatore orario nel fuso orario specificato.

Per la stringa, utilizzare un formato quale: yyyy-MM-dd o yyyy-MM-dd HH:mm:ss.SSS

Il formato del fuso orario è un ID di zona basato sull'area (ad esempio, 'area/città' come 'Asia/Seoul' o un offset del fuso orario (ad esempio, UTC+02).

 FROM_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') restituisce 2017-07-14 03:40:00.0
TO_UTC_TIMESTAMP(time_stamp, time_zone)

Converte una data, un indicatore orario o una stringa nel fuso orario specificato in un indicatore orario UTC.

Per la stringa, utilizzare un formato quale: yyyy-MM-dd o yyyy-MM-dd HH:mm:ss.SSS

Il formato del fuso orario è un ID di zona basato sull'area (ad esempio, 'area/città' come 'Asia/Seoul') o un offset del fuso orario (ad esempio, UTC+02).

 TO_UTC_TIMESTAMP('2017-07-14 02:40:00.0', 'GMT+1') restituisce 2017-07-14 01:40:00.0
FROM_UNIXTIME(unix_time[, fmt])

Converte l'ora o l'epoca Unix specificata in una stringa che rappresenta l'indicatore orario di quel momento nel fuso orario di sistema corrente e nel formato specificato.

Nota: l'ora Unix è il numero di secondi trascorsi dal 1° gennaio 1970 alle 00:00:00 UTC.

Se fmt viene omesso, il formato predefinito è yyyy-MM-dd HH:mm:ss

FROM_UNIXTIME(1255033470) restituisce '2009-10-08 13:24:30'

FROM_UNIXTIME(1637258854) restituisce '2021-11-18 10:07:34'

Il fuso orario predefinito è PST negli esempi
UNIX_TIMESTAMP([time_expr[, fmt]])

Converte l'ora corrente o specificata in un indicatore orario Unix in secondi.

time_expr è una data, un indicatore orario o una stringa con un formato quale yyyy-MM-dd o yyyy-MM-dd HH:mm:ss.SSS

Se time_expr non viene fornito, l'ora corrente viene convertita.

Se time_expr è una stringa e fmt viene omesso, l'impostazione predefinita è yyyy-MM-dd HH:mm:ss

UNIX_TIMESTAMP('1970-01-01 00:00:00', 'yyyy-MM-dd HH:mm:ss') restituisce 28800

Il fuso orario predefinito è PST in questo esempio
INTERVAL 'year' YEAR[(year_precision)]

Restituisce un periodo di tempo in anni.

year_precision è il numero di cifre nel campo anno. È compreso tra 0 e 9. Se year_precision viene omesso, l'impostazione predefinita è 2 (deve essere inferiore a 100 anni).

INTERVAL '1' YEAR restituisce un intervallo di 1 anno

INTERVAL '200' YEAR(3) restituisce un intervallo di 200 anni
INTERVAL 'year month' YEAR[(year_precision)] TO MONTH

Restituisce un periodo di tempo in anni e mesi. Consente di memorizzare un periodo di tempo utilizzando i campi anno e mese.

year_precision è il numero di cifre nel campo anno. È compreso tra 0 e 9. Se year_precision viene omesso, l'impostazione predefinita è 2 (deve essere inferiore a 100 anni).

 INTERVAL '100-5' YEAR(3) TO MONTH restituisce un intervallo di 100 anni, 5 mesi. È necessario specificare la precisione dell'anno iniziale pari a 3.
INTERVAL 'month' MONTH[(month_precision)]

Restituisce un periodo di tempo in mesi.

month_precision è il numero di cifre nel campo mese. È compreso tra 0 e 9. Se month_precision viene omesso, l'impostazione predefinita è 2 (deve essere inferiore a 100 anni).

 INTERVAL '200' MONTH(3) restituisce un intervallo di 200 mesi. È necessario specificare la precisione del mese pari a 3.
INTERVAL 'day time' DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]

Restituisce un periodo di tempo in termini di giorni, ore, minuti e secondi.

day_precision è il numero di cifre nel campo giorno. È compreso tra 0 e 9. Il valore predefinito è 2.

fractional_seconds_precision è il numero di cifre nella parte frazionaria del secondo valore nel campo time; è compreso tra 0 e 9.

 INTERVAL '11 10:09:08.555' DAY TO SECOND(3) restituisce un intervallo di 11 giorni, 10 ore, 09 minuti, 08 secondi e 555 millesimi di secondo
INTERVAL 'day time' DAY[(day_precision)] TO MINUTE[(minute_precision)]

Restituisce un periodo di tempo in termini di giorni, ore e minuti.

day_precision è il numero di cifre nel campo giorno. È compreso tra 0 e 9. Il valore predefinito è 2.

minute_precision è il numero di cifre nel campo minuto, con un intervallo compreso tra 0 e 2. Il valore predefinito è 2.

 INTERVAL '11 10:09' DAY TO MINUTE restituisce un intervallo di 11 giorni, 10 ore e 09 minuti
INTERVAL 'day time' DAY[(day_precision)] TO HOUR[(hour_precision)]

Restituisce un periodo di tempo in termini di giorni e ore.

day_precision è il numero di cifre nel campo giorno. È compreso tra 0 e 9. Il valore predefinito è 2.

hour_precision è il numero di cifre nel campo ora; è compreso tra 0 e 2. Il valore predefinito è 2.

 INTERVAL '100 10' DAY(3) TO HOUR restituisce un intervallo di 100 giorni e 10 ore
INTERVAL 'day' DAY[(day_precision)]

Restituisce un periodo di tempo in termini di giorni.

day_precision è il numero di cifre nel campo giorno. È compreso tra 0 e 9. Il valore predefinito è 2.

 INTERVAL '999' DAY(3) restituisce un intervallo di 999 giorni
INTERVAL 'time' HOUR[(hour_precision)] TO SECOND[(fractional_seconds_precision)]

Restituisce un periodo di tempo in termini di ore, minuti e secondi.

hour_precision è il numero di cifre nel campo ora; è compreso tra 0 e 2. Il valore predefinito è 2.

fractional_seconds_precision è il numero di cifre nella parte frazionaria del secondo valore nel campo time; è compreso tra 0 e 9.

 INTERVAL '09:08:07.6666666' HOUR TO SECOND(7) restituisce un intervallo di 9 ore, 08 minuti e 7,66666666 secondi
INTERVAL 'time' HOUR[(hour_precision)] TO MINUTE[(minute_precision)]

Restituisce un periodo di tempo in termini di ore e minuti.

hour_precision è il numero di cifre nel campo ora; è compreso tra 0 e 2. Il valore predefinito è 2.

minute_precision è il numero di cifre nel campo minuto, con un intervallo compreso tra 0 e 2. Il valore predefinito è 2.

 INTERVAL '09:30' HOUR TO MINUTE restituisce un intervallo di 9 ore e 30 minuti
INTERVAL 'hour' HOUR[(hour_precision)]

Restituisce un periodo di tempo in termini di ore.

hour_precision è il numero di cifre nel campo ora; è compreso tra 0 e 2. Il valore predefinito è 2.

 INTERVAL '40' HOUR restituisce un intervallo di 40 ore
INTERVAL 'minute' MINUTE[(minute_precision)]

Restituisce un periodo di tempo in termini di minuti.

minute_precision è il numero di cifre nel campo minuto, con un intervallo compreso tra 0 e 2. Il valore predefinito è 2.

 INTERVAL '15' MINUTE restituisce un intervallo di 15 minuti
INTERVAL 'time' MINUTE[(minute_precision)] TO SECOND[(fractional_seconds_precision)]

Restituisce un periodo di tempo in termini di minuti e secondi.

minute_precision è il numero di cifre nel campo minuto, con un intervallo compreso tra 0 e 2. Il valore predefinito è 2.

fractional_seconds_precision è il numero di cifre nella parte frazionaria del secondo valore nel campo time; è compreso tra 0 e 9.

 INTERVAL '15:30' MINUTE TO SECOND restituisce un intervallo di 15 minuti e 30 secondi
INTERVAL 'second' SECOND[(fractional_seconds_precision)]

Restituisce un periodo di tempo in termini di secondi.

fractional_seconds_precision è il numero di cifre nella parte frazionaria del campo secondo; è compreso tra 0 e 9. Il valore predefinito è 3.

 INTERVAL '15.678' SECOND restituisce un intervallo di 15,678 secondi
Funzioni hash
Funzione descrizione; Esempio
MD5(all data types) Calcola un checksum MD5 del tipo di dati e restituisce un valore stringa. MD5(column_name)
SHA1(all data types) Calcola un valore hash SHA-1 del tipo di dati e restituisce un valore stringa. SHA1(column_name)
SHA2(all data types, bitLength) Calcola un valore hash SHA-2 del tipo di dati e restituisce un valore stringa. bitLength è un numero intero. SHA2 (column_name, bitLength can be set to 0 (equivalent to 256), 256, 384, or 512).
ORA_HASH(expr, [max_bucket], [seed_value])

Calcola un valore hash per expr e restituisce un valore NUMBER.

expr può essere un'espressione, una colonna, un valore.

max_bucket è il valore massimo del bucket restituito, compreso tra 0 e 4294967295 (impostazione predefinita).

seed_value è un valore compreso tra 0 (predefinito) e 4294967295.

Oracle applica la funzione hash alla combinazione di expr e seed_value per produrre molti risultati diversi per lo stesso set di dati.

ORA_HASH('1')

ORA_HASH('b', 2)

ORA_HASH(100, 10, 10)

ORA_HASH(EXPRESSION_1.CUSTOMERS.SSN, 2)

Funzioni gerarchiche
Funzione descrizione; Esempio
SCHEMA_OF_JSON(string) Analizza una stringa JSON e inserisce lo schema in formato DDL.

SCHEMA_OF_JSON('[{\"Zipcode\":704,\"ZipCodeType\":\"STANDARD\",\"City\":\"ORACLECITY\",\"State\":\"OC\"}]') restituisce 'ARRAY<STRUCT<City:string,State:string,ZipCodeType:string,Zipcode:bigint>>'

SCHEMA_OF_JSON('[{\"col\":0}]') restituisce 'ARRAY<STRUCT<col: BIGINT>>'

FROM_JSON(column, string)

Analizza una colonna contenente una stringa JSON in uno dei tipi seguenti, con lo schema specificato.

  • Mappa, con String come tipo di chiave
  • Struttura
  • Array

FROM_JSON('{\"Zipcode\":704,\"City\":\"ORACLE CITY\"}', 'STRUCT<Zipcode: BIGINT, City: STRING>') restituisce una colonna di tipo Struttura con lo schema specificato: {704, ORACLE CITY}

FROM_JSON('{\"a\":1, \"b\":0.8}', 'STRUCT<a: BIGINT, b: DOUBLE>') restituisce una colonna di tipo Struttura con lo schema specificato: {1, 0.8}

TO_JSON(column) Converte una colonna contenente un tipo Struct o Array of Structs o Map o Array of Map in una stringa JSON. TO_JSON(TO_STRUCT('s1', TO_ARRAY(1,2,3), 's2', TO_MAP('key', 'value'))) restituisce una stringa JSON {"s1":[1,2,3],"s2":{"key":"value"}}
TO_MAP(string,column[,string,column]*) Crea una nuova colonna di tipo Mappa. Le colonne di input devono essere raggruppate come coppie chiave-valore. Le colonne chiave di input non possono essere nulle e devono avere tutti lo stesso tipo di dati. Le colonne dei valori di input devono avere tutti lo stesso tipo di dati.

TO_MAP('Ename',Expression_1.attribute1) restituisce una colonna di tipo Mappa: {"ENAME" -> 100}

TO_MAP('block', EXPRESSION_1.MYSOURCE.address.block, 'unit', EXPRESSION_1.MYSOURCE.address.unit) restituisce una colonna di tipo Mappa: {"block" -> 1,"unit" -> 1}

TO_STRUCT(string,column[,string,column]*) Crea una nuova colonna di tipo Struttura. Le colonne di input devono essere raggruppate come coppie chiave-valore.

TO_STRUCT('Ename',Expression_1.attribute1) restituisce {100}

TO_STRUCT('Id',Expression_1.attribute1, 'Name', Expression_1.attribute2) restituisce {100, "John"}

TO_ARRAY(column[,column]*) Crea una nuova colonna come tipo di array. Le colonne di input devono avere tutte lo stesso tipo di dati.

TO_Array(Expression_1.attribute1) restituisce [100]

TO_ARRAY(EXPRESSION_1.attribute2,EXPRESSION_1.attribute3) restituisce ["John","Friend"]

Funzioni ordine superiore

Gli operatori di flusso dati che supportano la creazione di espressioni e tipi di dati gerarchici possono utilizzare funzioni di ordine superiore.

Gli operatori supportati sono:

  • aggregato

  • Espressione

  • Filtro

  • Join

  • Cerca

  • Dividi

  • Pivot

Funzione descrizione; Esempio
TRANSFORM(column, lambda_function) Prende un array e una funzione anonima e imposta un nuovo array applicando la funzione a ciascun elemento e assegnando il risultato all'array di output. Per un array di input di numeri interi [1, 2, 3], TRANSFORM(array, x -> x + 1) restituisce un nuovo array di valori [2, 3, 4].
TRANSFORM_KEYS(column, lambda_function) Prende una mappa e una funzione con 2 argomenti (chiave e valore) e restituisce una mappa in cui le chiavi hanno il tipo del risultato della funzione lambda e i valori hanno il tipo dei valori della mappa delle colonne. Per una mappa di input con chiavi interi e valori stringa di {1 -> 'value1', 2 -> 'value2', 3 -> 'value3'}, TRANSFORM_KEYS(map, (k, v) -> k * 2 + 1) restituisce una nuova mappa di {3 -> 'value1', 5 -> 'value2', 7 -> 'value3'}.
TRANSFORM_VALUES(column, lambda_function) Prende una mappa e una funzione con 2 argomenti (chiave e valore) e restituisce una mappa in cui i valori hanno il tipo del risultato delle funzioni lambda e i tasti hanno il tipo delle chiavi della mappa delle colonne. Per una mappa di input con chiavi stringa e valori stringa {'a' -> 'value1', 'b' -> 'value2', 'c' -> 'value3'}, TRANSFORM_VALUES(map, (k, v) -> k || __ '' || v) restituisce una nuova mappa di {'a' -> 'a_value1', 'b' -> 'b_value2', 'c' -> 'c_value3'}.
ARRAY_SORT(array(...), lambda_function)

Solo l'operatore Espressione supporta ARRAY_SORT.

Prende un array e ordina in base alla funzione specificata che accetta 2 argomenti.

La funzione deve restituire -1, 0 o 1 a seconda che il primo elemento sia minore, uguale o maggiore del secondo elemento.

Se la funzione viene omessa, l'array viene ordinato in ordine crescente.

 
array_sort(to_array(5, 6, 1),
                   (left, right) -> CASE WHEN left < right THEN -1
                                         WHEN left > right THEN 1 ELSE 0 END)

L'array restituito è:

[1,5,6]
Funzioni operatore (confronto)
Funzione descrizione; Esempio
CASE WHEN condition1 THEN result1 ELSE result2 END Restituisce il valore per il quale viene soddisfatta una condizione. CASE WHEN 1 > 0 THEN 'ABC' ELSE 'XYZ' END restituisce ABC se 1> 0, altrimenti restituisce XYZ
AND L'operatore AND logico. Restituisce true se entrambi gli operandi sono true, altrimenti restituisce false. (x = 10 AND y = 20) restituisce "true" se x è uguale a 10 e y è uguale a 20. Se uno dei due non è vero, restituisce "falso"
OR Operatore OR logico. Restituisce true se uno degli operandi è true o entrambi sono true, altrimenti restituisce false. (x = 10 OR y = 20) restituisce "false" se x non è uguale a 10 e anche y non è uguale a 20. Se uno dei due è vero, allora restituisce "vero"
NOT L'operatore NOT logico.
LIKE Esegue la corrispondenza dei pattern di stringa, indipendentemente dal fatto che string1 corrisponda al pattern in string2.
= Test per l'uguaglianza. Restituisce true se expr1 è uguale a expr2, altrimenti restituisce false. x = 10 restituisce "true" quando il valore di x è 10, altrimenti restituisce "false"
!= Test per la disuguaglianza. Restituisce true se expr1 non è uguale a expr2, altrimenti restituisce false. x != 10 restituisce "false" se il valore di x è 10, altrimenti restituisce "true"
> Test per un'espressione maggiore di. Restituisce true se expr1 è maggiore di expr2. x > 10 restituisce "true" se il valore di x è maggiore di 10, altrimenti restituisce "false"
>= Test per un'espressione maggiore o uguale a. Restituisce true se expr1 è maggiore o uguale a expr2. x > =10 restituisce "true" se il valore di x è maggiore o uguale a 10, altrimenti restituisce "false"
< Test per un'espressione minore di. Restituisce true se expr1 è minore di expr2. x < 10 restituisce "true" se il valore di x è minore di 10, altrimenti restituisce "false"
<= Test per un'espressione minore o uguale a. Restituisce true se expr1 è minore o uguale a expr2. x <= 10 restituisce "true" se il valore di x è minore di 10, altrimenti restituisce "false"
|| Concatena due stringhe. 'XYZ' || 'hello' restituisce 'XYZhello'
BETWEEN Valuta un intervallo. FILTER_1.BANK.BANK_ID BETWEEN 1003 AND 1007
IN Verifica se un'espressione corrisponde a una lista di valori. FILTER_2.ORDERS.ORDER_ID IN (1003, 1007)
Funzioni di stringa
Funzioni descrizione; Esempio
CAST(value AS type) Restituisce il valore specificato nel tipo specificato. CAST("10" AS INT) restituisce 10
CONCAT(string, string) Restituisce i valori combinati di stringhe o colonne. CONCAT('Oracle','SQL') restituisce OracleSQL
CONCAT_WS(separator, expression1, expression2, expression3,...) Restituisce i valori combinati di stringhe o colonne utilizzando il separatore specificato tra le stringhe o le colonne.

È necessario un separatore e deve essere una stringa.

È necessario fornire almeno un'espressione dopo il separatore. Ad esempio: CONCAT_WS(',' col1)

CONCAT_WS('-', 'Hello', 'Oracle') restituisce Hello-Oracle

CONCAT_WS(' ', address, city, postal_code) restituisce 123 MyCity 987654

Se un elemento figlio della funzione è un array, l'array viene appiattito:

CONCAT_WS(',', 1,2,3, to_array(4,5,6), to_array(7,8), 9) restituisce 1,2,3,4,5,6,7,8,9

INITCAP(string) Restituisce la stringa con la prima lettera in ogni parola maiuscola, mentre tutte le altre lettere sono minuscole e ogni parola è delimitata da uno spazio vuoto. INITCAP('oRACLE sql') restituisce Oracle Sql
INSTR(string, substring[start_position]) Restituisce l'indice (basato su 1) della prima occorrenza di substring in string. INSTR('OracleSQL', 'SQL') restituisce 7
LOWER(string) Restituisce la stringa con tutte le lettere modificate in minuscolo. LOWER('ORACLE') restituisce oracle
LENGTH(string) Restituisce la lunghezza del carattere della stringa o il numero di byte di dati binari. La lunghezza della stringa include gli spazi finali. LENGTH('Oracle') restituisce 6
LTRIM(string) Restituisce la stringa con gli spazi iniziali rimossi da sinistra. LTRIM(' Oracle')
NVL(expr1, epxr2) Restituisce l'argomento non nullo. NVL(EXPRESSION_3.CUSTOMERS_JSON.CONTINENT_ID, ROWID())
REGEXP_SUBSTR(string, regexp[, RegexGroupIdx]) Cerca ed estrae la stringa che corrisponde a un pattern di espressione regolare dalla stringa di input. Se viene fornito l'indice di gruppo di acquisizione facoltativo, la funzione estrae il gruppo specifico.

REGEXP_SUBSTR('https://www.oracle.com/products', 'https://([[:alnum:]]+\.?){3,4}/?') restituisce https://www.oracle.com

REGEXP_SUBSTR('22;33;44', '([0-9.]*);([0-9.]*);([0-9.]*)', 1) restituisce 22
REPLACE(string, search, replacement) Sostituisce tutte le occorrenze di search con replacement.

Se search non viene trovato nella stringa, la stringa viene restituita invariata.

Se replacement non è specificato o è una stringa vuota, nulla sostituisce search che viene rimosso da string.

 REPLACE('ABCabc', 'abc', 'DEF') restituisce ABCDEF
RTRIM(string) Restituisce la stringa con gli spazi iniziali rimossi da destra. RTRIM('Oracle ')
SUBSTRING(string, position[, substring_length]) Restituisce la sottostringa che inizia in posizione. SUBSTRING('Oracle SQL' FROM 2 FOR 3) restituisce rac
Per i numeri, TO_CHAR(expr) e per le date TO_CHAR(expr, format[, locale]) Converte numeri e date in stringhe. Per i numeri, non è necessario alcun formato. Per le date, utilizzare lo stesso formato di DATE_FORMAT descritto in Funzioni data e ora. La versione locale predefinita è en-US. Vedere le tag delle lingue supportate.

Nelle espressioni della pipeline, format_string deve utilizzare i codici di formato strftime. In caso contrario, i pattern di formato data supportati sono i seguenti:

  • aa: anno composto da due cifre.
  • yyyy: anno composto da quattro cifre.
  • M: mese numerico, ad esempio 1 per gennaio
  • MM: mese numerico, ad esempio 01 per gennaio
  • MMM: mese abbreviato, ad esempio Gen
  • MMMM: mese completo, ad esempio gennaio
  • d: giorno numerico del mese, ad esempio 1 per il 1° giugno
  • dd: giorno numerico del mese, ad esempio 01 per il 1° giugno
  • DDD: giorno numerico dell'anno compreso tra 001 e 366, ad esempio 002 per il 2 gennaio
  • F: giorno numerico della settimana in un mese, ad esempio 3 per il 3° lunedì di giugno.
  • EEE o E: il giorno della settimana denominato abbreviato, ad esempio Sole per domenica
  • EEEE: il giorno della settimana, ad esempio la domenica.
  • HH: formato di 24 ore da 00 a 23
  • H: formato di 24 ore da 0 a 23
  • hh: formato di 12 ore da 01 a 12
  • h: formato di 12 ore da 1 a 12
  • mm: minuti da 00 a 59
  • ss: secondi da 00 a 59
  • SSS: millisecondi da 000 a 999
  • a: AM o PM
  • z: fuso orario come PDT

Esempio numerico: TO_CHAR(123) restituisce 123

Esempio di data: TO_CHAR(Date '2020-10-30', 'yyyy.MM.dd', 'en-US') restituisce la stringa 2020.10.30. Il primo argomento è un oggetto Date che rappresenta il 30 ottobre 2020.
UPPER(string) Restituisce una stringa con tutte le lettere modificate in maiuscolo. UPPER('oracle') restituisce ORACLE
LPAD(str, len[, pad]) Restituisce una stringa riempita a sinistra con caratteri specificati fino a una certa lunghezza. Se il carattere pad viene omesso, il valore predefinito è uno spazio. LPAD('ABC', 5, '*') restituisce '**ABC'
RPAD(str, len[, pad]) Restituisce una stringa riempita a destra con i caratteri specificati fino a una certa lunghezza. Se il carattere pad viene omesso, il valore predefinito è uno spazio. RPAD('XYZ', 6, '+' ) returns 'XYZ+++'
Funzioni ID univoco
Funzione descrizione; Esempio
NUMERIC_ID() Genera un identificativo univoco universale che è un numero a 64 bit per ogni riga. NUMERIC_ID() restituisce, ad esempio, 3458761969522180096 e 3458762008176885761
ROWID() Genera numeri a 64 bit in aumento monotono. ROWID() restituisce, ad esempio, 0, 1, 2 e così via
UUID() Genera un identificativo univoco universale che è una stringa a 128 bit per ogni riga. UUID() restituisce, ad esempio, 20d45c2f-0d56-4356-8910-162f4f40fb6d
MONOTONICALLY_INCREASING_ID() Genera numeri interi a 64 bit univoci e monotoni che non sono numeri consecutivi. MONOTONICALLY_INCREASING_ID() restituisce, ad esempio, 8589934592 e 25769803776