counting records by time interval

Asked

Viewed 45 times

0

I have 3 tables:
aircraft(id_aeronvave, registration); occurrence(id_occurrence, data_utc); aeronave_occurrence(id_aeronave_occurrence, id_aircraft, id_occurrence)

I have this list of aircraft by groups of dates:

SELECT
  o.data_utc,
  listagg(a.matricula, ', ') within group (order by a.matricula)
FROM
  aeronave a
  JOIN aeronave_ocorrencia ao ON a.id_aeronave = ao.id_aeronave
  JOIN ocorrencia o ON ao.id_ocorrencia = o.id_ocorrencia
GROUP BY o.data_utc

How do you find out how many aircraft with equal license plates are within 2 months? Calculating this for every date I have in my database.

Example:

data_utc = 11/08/2020 | matricules = PPTTN, PATAR | qtd_repeated aeronaves_1 = 1
data_utc = 20/07/2020 | matricules = PPTTN, PTABC | qtd_repeated aeronaves_1 = 1

sql oracle

  • I don’t know if I understood it correctly but it wouldn’t be the COUNT aggregation function associated with the GROUP BY enrollment clause and the range in the WHERE clause?

  • You speak aircraft aggregation count in range ?

  • Exactly. But this calculation is based on the data_utc records that you have in my database.

  • Would it be a recurrent or possible consultation ? Recurring I would make an aggregate table loaded via job , eventual a virtual table generated by your select , in both cases a Join with itself testing the rule of months.

  • Possibly even consultation.

1 answer

1

Try, maybe slow down

SELECT T1.DATA_UTC , T1.MATS , COUNT(*) QTD
FROM
(SELECT o.data_utc, listagg(a.matricula, ', ') within group (order by a.matricula) mats
 FROM aeronave a 
  JOIN aeronave_ocorrencia ao ON a.id_aeronave = ao.id_aeronave 
  JOIN ocorrencia o ON ao.id_ocorrencia = o.id_ocorrencia GROUP BY o.data_utc) T1,
(SELECT o.data_utc, listagg(a.matricula, ', ') within group (order by a.matricula) mats
 FROM aeronave a 
  JOIN aeronave_ocorrencia ao ON a.id_aeronave = ao.id_aeronave 
  JOIN ocorrencia o ON ao.id_ocorrencia = o.id_ocorrencia GROUP BY o.data_utc) T2
Where t1.mats = t2.mats
and months_between(t1.data_utc,t2.data_utc) between 0 and 2
GROUP BY T1.DATA_UTC , T1.MATS

  • But how do I return to Qtd of times that enrollment repeats in that 2 month interval for each data_utc?

  • sql is a basis to extract the merge of dates , I edited the same

  • Thank you very much!

  • See if it works, was no test

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.