时间戳

首页 > 解决方案 > 在 ORACLE 中查找帐户的活动天数

sql - 在 ORACLE 中查找帐户的活动天数

问题描述

我正在尝试查找关闭特定帐户的天数。

我有如下表：

OPER_DAY    |   CODE_FILIAL  |  SUM_IN         |      SALDO_OUT     |   ACC   
-------------------------------------------------------------------------------
2020-11-02  |   00690        |   0             |   1578509367.58    | 001
2020-11-03  |   00690        |   1578509367.58 |   9116497.5        | 001
2020-11-04  |   00690        |   9116497.5     |   0                | 001
2020-11-02  |   00690        |   0             |   157430882.96     | 101
2020-11-03  |   00690        |   157430882.96  |   0                | 101
2020-11-09  |   00690        |   0             |   500000           | 101
2020-11-19  |   00690        |   500000        |   0                | 101

对于特定的ACC，一天以 0 sum 开始，以 0 结束。我需要找出孝顺结账的天数。例如，对于ACC 001 ，从 2020 年 11 月2 日到 2020 年 11 月 4 日需要 2 天。对于 101 ACC，它需要 11 天。因为从 2020-11-02 - 2020-11-03 -> 1 天，从 2020-11-09 - 2020-11-19 -> 10 天 总共13 天。

我想要的结果：

----------------------------
CODE_FILIAL   | NUM_OF_DAYS
---------------------------
  00690       |  13

标签： sqloracledatetimegaps-and-islandsdate-arithmetic

解决方案

这听起来像是一个差距和孤岛问题。一个岛以 in 的值开始，以in0的sum_in值结束。0saldo_out

假设每次开始最多有一个结束，您可以使用窗口函数和聚合，如下所示：

select code_filial, sum(end_dt - start_dt) as num_of_days
from (
    select code_filial, acc, grp
        min(oper_day) as start_dt,
        max(case when saldo_out = 0 then oper_day end) as end_dt
    from (
        select t.*,
            sum(case when sum_in = 0 then 1 else 0 end) over(partition by code_filial, acc order by oper_day) as grp
        from mytable t
    ) t
    group by code_filial, acc, grp
) group by code_filial

这通过构建具有窗口总和的记录组来工作，该窗口总和每次在给定元组的列0中遇到一个值时都会增加。然后我们可以使用聚合来计算相应的结束日期。最后一步是聚合。sum_in(code_filial, acc)code_filial

推荐阅读