子查询中进一步按周数分组

Question

我正在尝试从2列中减去值，并将它们分组为周数字。事件代码列的值为3,4。我正在尝试将事件代码4的持续时间相加，然后减去事件代码3的持续时间。这些值需要在最近12周内导出。

这是我到目前为止所拥有的。我是素材，并按周数进一步分组：

SELECT DISTINCT CUSTOMER_ID,
((SELECT SUM(DURATION_IN_SECONDS)/60 FROM TABLE1 ee WHERE ee.CUSTOMER_ID = e.CUSTOMER_ID AND EVENT_CODE IN (4))-
(SELECT SUM(DURATION_IN_SECONDS)/60 FROM TABLE1 ee WHERE ee.CUSTOMER_ID = e.CUSTOMER_ID AND EVENT_CODE IN (3))) AS UNPRODUCTIVE_MINUTES
FROM  TABLE1 e
WHERE TIMEDATE >= TO_DATE('01-OCT-19','DD-MON-YY') 
AND TIMEDATE <= TO_DATE('31-DEC-19','DD-MON-YY')
GROUP BY CUSTOMER_ID

上面的查询会产生如下结果：

CUSTOMER_ID   UNPRODUCTIVE_MINUTES
A100          1601

但我的结果必须是这样的：

CUSTOMER_ID       WEEKNUMBER  UNPRODUCTIVE_MINUTES
A100              12            171
A100              11            108
A100              10            112
A100              9             110
A100              8             98
A100              7             67
A100              6             117
A100              5             100
A100              4             111
A100              3             77
A100              2             73
A100              1             87

Answer 1

我不确定，您想如何计算周数，但我猜week number是(timedate - start timedate / 7) + 1，因此创建相应的查询。

Select customer_id,
       Sum(case when EVENT_CODE = 4 then DURATION_IN_SECONDS else (-1* DURATION_IN_SECONDS) end)/60 as dur,
       Floor(Trunc(timedate) - TO_DATE('01-OCT-19','DD-MON-YY') / 7) + 1 as weeknumber
  From table1 e
 Where TIMEDATE >= TO_DATE('01-OCT-19','DD-MON-YY') 
   AND TIMEDATE <= TO_DATE('31-DEC-19','DD-MON-YY')
   AND EVENT_CODE in (3, 4)
GROUP BY CUSTOMER_ID, floor(trunc(timedate) - TO_DATE('01-OCT-19','DD-MON-YY') / 7) + 1

在这里，我没有使用event_code 3作为event_code 3和4的DURATION_IN_SECONDS，两者都减去DURATION_IN_SECONDS for 3，最终将与单独用于event_code 4的DURATION_IN_SECONDS相同。

干杯！！