时间戳

首页 > 解决方案 > 按日期和类别将数据放入聚合存储桶

sql - 按日期和类别将数据放入聚合存储桶

问题描述

我正在尝试按日期和类别显示打开和关闭的 IT 票证的总数。

父表由以下列组成： 

Alert_ID     Alert_Open_Date     Alert_Closed_Date

我希望我的最终结果类似于以下，我有

A. 任何指定日期范围内的日期，

B. 截至该日期仍显示为打开的警报总数 (Outstanding_Alerts)，

C. 在该日期打开的警报总数 (New_Alerts)，

D. 在该日期关闭的新警报总数 (Closed_New_Alerts)，以及

E. 在该日期关闭的新旧警报的总数量（Closed_Total）：

  Date     Outstanding_Alerts     New_Alerts     Closed_New_Alerts     Closed_Total
6/1/2018       20                      10             5                     7
6/2/2018       23                      20             8                     10
6/3/2018       33                      13             10                    15
  etc.         #                       #              #                     #

我正在考虑类似以下概念查询来完成此操作，但我在逻辑上遇到了我想要的结果。无论措辞如何，我似乎都无法正确使用水桶。例如，某些列在应该填充时保持空白。任何帮助表示赞赏。 

SELECT DISTINCT
    alert_date
  , SUM(OOA)                AS Outstanding_Open_Alerts
  , SUM(NOA)                AS New_Open_Alerts
  , SUM(CNA*NOA)            AS Closed_New_Alerts
  , SUM(CT)                 AS Total_Closed_Alerts
  , SUM(CNA+NOA-CT)         AS Remaining_Alerts  --optional column

 FROM

 (SELECT
      TRUNC(open_date) AS Alert_Date
    , CASE WHEN alert_date < trunc(SYSDATE)-1 AND closed_date IS NULL THEN 1 ELSE 0
            END AS      OOA     --old open alerts
    , CASE WHEN alert_date > trunc(SYSDATE)-1 THEN 1 ELSE 0
            END AS      NOA     --new open alerts
    , CASE WHEN closed_date >= trunc(SYSDATE)-1 THEN 1 ELSE 0
            END AS      CNA     --closed new alerts
    , CASE WHEN closed_date < trunc(SYSDATE)-1 THEN 1 ELSE 0
            END AS      CT      --closed total
    FROM sys_alerts)

GROUP BY alert_date;

标签： sqloracle

解决方案

如果我按照您的描述，获取some_date您想要的任意日期的数字，例如：

SELECT
    some_date AS Alert_Date
  , COUNT(CASE WHEN open_date < some_date
      AND (closed_date IS NULL OR closed_date > some_date
      THEN alert_id END) AS Current_Open_Alerts
  , COUNT(CASE WHEN open_date >= some_date
      AND open_date < some_date + 1
      THEN alert_id END AS New_Alerts
  , COUNT(CASE WHEN open_date >= some_date
      AND open_date < some_date + 1
      AND closed_date < some_date + 1
      THEN alert_id END AS Closed_New_Alerts
  , COUNT(CASE WHEN closed_date >= some_date
      AND closed_date < some_date + 1
      THEN alert_id END AS Closed_Total
  , COUNT(CASE WHEN open_date < some_date + 1
      AND (closed_date IS NULL OR closed_date >= some_date + 1
      THEN alert_id END) AS Alerts_Remaining
FROM sys_alerts
GROUP BY some_date

我使用了计数而不是总和，这依赖于计数忽略空值这一事实 - 并将案例表达式中的默认值保留为空值。它真的不需要 sunquery，所以我已经删除了它。

似乎您想要某个日期范围内的所有数据，因此您可以在内联视图或 CTE 中生成这些数据，然后加入您的真实表，这样some_date就可以从该生成的范围中查看单个日期。例如，要获得最近 30 天的信息，例如：

FROM (
    SELECT sysdate - level AS some_date
    FROM dual
    CONNECT BY level <= 30
LEFT JOIN sys_alerts
ON closed_date IS NULL OR closed_date >= some_date
GROUP BY some_date
ORDER BY some_date

或者你可以做一些事情cross apply或分区外连接。希望这能给你一个起点。

推荐阅读