sql - 基于日期运行计数器

问题描述

我有一张带有警报历史记录的表格，其中包含警报的开始日期、结束日期和原因。

我想为过去 30 天的每个日期计算当天发生的警报总数，这意味着如果警报从第 1 天开始并且仍在进行中（结束日期为空），那么它将计算从第 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

标签： sqloracle

如果您需要日级别的数据，则必须在将时间戳列转换为日期后应用 distinct 子句。

像下面这样的东西 -

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;

您提供的数据不重复，因为它包含每条记录的唯一时间戳。

希望这可以帮助

sql - 基于日期运行计数器

问题描述

解决方案

推荐阅读