我有一个带有警报历史记录的表，包含开始日期，end_date和警报的原因。
我想在最后30天的每个日期计算当天发生的总警报，这意味着如果警报开始了第1天，并且仍在进行（结束日期为null），那么它将计算在一天之内的所有天数1到持续。
这是我提出的查询
select cal.trunc_date,assets.group_id,
alert.*,
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;
查看v_app_calendar包含日期，V_ACTIVE_ASSETS包含我要检查的group_id。
问题在于我获得了重复，一式三份等。
这是结果：
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
如果您需要在一天级别的数据，则必须在将时间戳列后应用不同的子句。
。像以下类似的东西 -
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;
您提供的数据并不重复，因为它包含每个记录的唯一时间戳。
希望这有帮助
尝试以下代码。
日期表包含过去30天的所有日期，包括今天。
我还将您的JOIN语法更改为较新的表格。
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
我希望我有帮助！
，因为您想每天发生所有警报（警报本来可以在前一天开始，或者在以后的一天结束），然后您想使用聚合按日分组（可能是其他条件），而不是您在查询中显示的分析数。要执行骨料，在给定的一天中具有多余的重复物，您需要消除提供非不同值的列。通常，这是您的警报开始和结束日期，以及asset_id和geofence_id。
以下查询将为您提供对过去30天中每一个发生的请求的group_id和reason_id S发生的变化计数。
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;