J'ai une table avec l'historique d'alerte, contenant une date de début, end_date et raison de l'alerte.
Je souhaite pour chaque date des 30 derniers jours pour calculer les alertes totales de ce jour-là, cela signifie si une alerte Début du jour 1 et est toujours en cours (la date de fin est NULL), elle comptera pour tous les jours du jour 1 pour durer. P>
Cette question que j'ai proposée avec p> vue Le problème est que je reçois des duplicats, des triplicates, etc. p> Voici le résultat: p> v_app_calendar code> contient des dates et v_active_assets code> contient le group_id code> que je veux vérifier. P> TRUNC_DATE GROUP_ID REASON_ID ASSET_ID GEOFENCE_ID START_DATE_DEVICE END_DATE_DEVICE TOTAL_ASSETS
--------- -------- --------- -------- ----------- ------------------------------- ------------------------------- ------------
03-FEB-19 1462 1 1704 134 03-FEB-19 11.50.09.385000000 AM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 11.55.09.475000000 AM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 12.00.10.073000000 PM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 12.05.11.126000000 PM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 12.10.12.668000000 PM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 12.15.12.858000000 PM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 11.45.09.283000000 AM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 11.20.03.587000000 AM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 11.25.05.434000000 AM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 11.30.07.294000000 AM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 11.35.09.141000000 AM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 11.40.09.251000000 AM 13
03-FEB-19 1462 1 1704 134 03-FEB-19 12.20.14.178000000 PM 13
05-FEB-19 1462 1 1663 134 05-FEB-19 02.33.02.475000000 PM 14
09-FEB-19 1462 1 1663 134 09-FEB-19 09.33.02.475000000 PM 09-FEB-19 11.33.22.475000000 PM 16
09-FEB-19 1462 1 1782 149 09-FEB-19 02.33.02.475000000 PM 09-FEB-19 02.36.02.475000000 PM 16
11-FEB-19 1462 1 2647 134 11-FEB-19 09.56.08.325000000 AM 140
11-FEB-19 1462 1 2647 164 11-FEB-19 09.56.08.325000000 AM 140
11-FEB-19 1462 1 2646 164 11-FEB-19 10.03.31.611000000 AM 140
11-FEB-19 1462 1 2646 134 11-FEB-19 10.03.31.611000000 AM 140
11-FEB-19 1462 1 1781 164 11-FEB-19 10.14.09.612000000 AM 140
11-FEB-19 1462 1 2647 134 11-FEB-19 11.55.20.281000000 AM 140
11-FEB-19 1462 1 1781 134 11-FEB-19 10.14.09.612000000 AM 140
11-FEB-19 1462 1 2647 164 11-FEB-19 10.55.32.300000000 AM 140
11-FEB-19 1462 1 1781 134 11-FEB-19 02.52.45.104000000 PM 140
11-FEB-19 1462 1 1781 164 11-FEB-19 03.20.40.461000000 PM 140
11-FEB-19 1462 1 1781 134 11-FEB-19 03.20.40.461000000 PM 140
11-FEB-19 1462 1 1781 164 11-FEB-19 08.28.13.331000000 PM 140
11-FEB-19 1462 1 1781 134 11-FEB-19 08.28.13.331000000 PM 140
11-FEB-19 1462 1 1781 134 11-FEB-19 03.20.42.461000000 PM 140
11-FEB-19 1462 1 1781 134 11-FEB-19 08.28.25.939000000 PM 140
11-FEB-19 1462 1 1781 164 11-FEB-19 08.28.25.939000000 PM 140
3 Réponses :
Si vous avez besoin de données au niveau de la journée, vous devez appliquer une clause distincte après avoir converti des colonnes horodatées à la date.
une chose comme ci-dessous - P>
select cal.trunc_date,assets.group_id,
alert.req_col,
cast(alert.start_date_device as date),
cast(alert.end_date_device as date)
count( alert.asset_id)
over (PARTITION BY alert.REASON_ID ORDER BY
cal.trunc_date) TOTAL_ASSETS
from g_alert_history alert,
v_app_calendar cal,V_ACTIVE_ASSETS assets
where REASON_ID in (1,2)
and assets.asset_id=alert.asset_id
and assets.group_id=1462
and cal.trunc_date >= trunc(systimestamp - 30)
and alert.START_DATE_DEVICE >= trunc(systimestamp - 30)
and alert.START_DATE_DEVICE >= cal.trunc_date
and alert.START_DATE_DEVICE <= cal.trunc_date +1
and nvl (alert.END_DATE_DEVICE, systimestamp)
>=cal.trunc_date;
Essayez le code suivant.
La table des dates contient toutes les dates des 30 derniers jours, y compris aujourd'hui. p>
J'ai également modifié votre code> Syntaxe sur un nouveau formulaire. P>
with dates as (
select trunc(sysdate) - (level - 1) trunc_date from dual connect by level<=30
)
select dates.trunc_date
, count(alert.asset_id)
from g_alert_history alert
join v_app_calendar cal
on (alert.START_DATE_DEVICE between cal.trunc_date and (cal.trunc_date +1)
and nvl (alert.END_DATE_DEVICE, systimestamp) >= cal.trunc_date )
join V_ACTIVE_ASSETS assets
on (assets.asset_id=alert.asset_id)
where REASON_ID in (1,2)
and dates.trunc_date between trunc(alert.START_DATE_DEVICE) and nvl(alert.END_DATE_DEVICE, trunc(sysdate))
and cal.trunc_date >= trunc(systimestamp - 30)
and assets.group_id=1462
group by dates.trunc_date
Puisque vous voulez un nombre quotidien de toutes les alarmes survenant ce jour-là (l'alarme aurait pu commencer le jour précédent et terminé un jour de futur), vous souhaitez utiliser un nombre agrégé groupé par jour (et éventuellement d'autres Critères) et non un compte analytique Comme vous l'avez montré dans votre requête. Pour effectuer l'agrégat sans excès de doublons pendant un jour donné, vous devez éliminer les colonnes fournissant des valeurs non distinctes. Pour la plupart des dates de début et de fin de votre alerte, et du La requête ci-dessous vous donnera le nombre d'altérations survenues pour le Asset_id CODE> et GEOFENCE_ID CODE>. requis group_id code> et raisonnaire code> S survenu pour chacun des 30 derniers jours. P> select cal.trunc_date
, assets.group_id
, alert.reason_id
, count( alert.asset_id) TOTAL_ASSETS
from g_alert_history alert
join V_ACTIVE_ASSETS assets
on assets.asset_id=alert.asset_id
join v_app_calendar cal
on alert.START_DATE_DEVICE < cal.trunc_date + 1
and (alert.END_DATE_DEVICE is null or cal.trunc_date <= alert.END_DATE_DEVICE)
where alert.REASON_ID in (1,2)
and assets.group_id=1462
and cal.trunc_date between trunc(sysdate - 30) and sysdate
group by cal.trunc_date
, assets.group_id
, alert.reason_id
order by cal.trunc_date
, assets.group_id
, alert.reason_id;