时间戳

首页 > 解决方案 > 从 Oracle SQL 中仅包含时间戳的历史数据中选择 PERIOD_BEGIN 和 PERIOD_END 日期

sql - 从 Oracle SQL 中仅包含时间戳的历史数据中选择 PERIOD_BEGIN 和 PERIOD_END 日期

问题描述

我遇到了一些问题。背景：我在一家提供财富管理服务的金融机构担任业务总监，我有责任对欧元的来往进行内部报告。由于这是用于评估经理绩效的 KPI 之一，我需要能够报告每个经理的这些数字。这一点很简单，因为每个客户都有一个经理分配给它。现在有趣的是 - 过去做出了一些有问题的 DW 设计选择，并且包含经理/客户关系的表缺少所有相关的时间信息，例如“有效期自”或“有效期至”。基本上它只存储当前状态。有时客户和投资组合会被重新分配给其他经理，这会导致所有交易在旧经理期间完成

例如，经理 Joe 在 1 月至 3 月期间管理名为 Blammo Ltd 的客户，该客户认购了 1000 万美元的资金。乔离开公司，客户被分配给经理海伦。在四月份，客户提取了 500 万。当我在 4 月底编写报告时，Joe 的 KPI 显示为 +-0，而 Helen 的 KPI 显示为 +500 万，而实际上它应该告诉 Joe 赚了 1000 万，而 Helen 输了 5。

我们确实有一个审计表，其中包含表中包含经理/客户关系的所有行，并且每行在创建时都有一个时间戳。我希望实现的是构建一个视图，使用这些时间戳来构建一个具有VALID_FROM和VALID_UNTIL日期的表，这样我就可以通过加入VALID日期之间的事务轻松地将事务分配给特定的经理。

所以基本上我所拥有的是......

  CUSTOMERID   MANAGERID   TIMESTAMP   
 ------------ ----------- ------------ 
           1   A           01-01-2018  
           1   B           28-02-2018  
           1   A           31-05-2018  
           1   C           31-08-2018

而我需要的是...

  CUSTOMERID   MANAGERID   VALID_FROM   VALID_UNTIL  
 ------------ ----------- ------------ ------------- 
           1   A           01-01-2018   28-02-2018   
           1   B           28-02-2018   31-05-2018   
           1   A           31-05-2018   31-08-2018   
           1   C           31-08-2018

我试过的是

SELECT
    CUSTOMERID,
    MANAGERID,
    MIN(TIMESTAMP) AS VALID_FROM,
    MAX(TIMESTAMP) AS VALID_UNTIL
FROM CUSMAN.CUS_MAN_AUDIT
GROUP BY
    CUSTOMERID,
    MANAGERID

这适用于从未将客户重新分配回前任经理的情况。然而，由于产假等原因，客户在经理之间来回分配，因此上述解决方案不会产生正确的结果 - 将客户 '1' 在 '30-04-2018' 进行的交易加入客户/经理关系data 将产生两个结果 - 经理 A 和 B。下面是上面的查询将产生的表。

  CUSTOMERID   MANAGERID    VALID_FROM    VALID_UNTIL  
 ------------ ----------- -------------- ------------- 
           1   A           01-01-2018     31-08-2018   
           1   B           28-02-2018     31-05-2018   
           1   C           31-08-2018

感觉有一种简单的方法可以做到这一点，但我很难过。有任何想法吗？

编辑

血腥的，我忘了提到 CUS_MAN_AUDIT 表还包含许多其他列，例如客户名称、法律形式等，现在 Caius 的答案返回如下所示的结果集（为清楚起见，包含 CUSTOMERNAME，不在实际结果集中)

+------------+-----------+------------+-------------+--------------+ | CUSTOMERID | MANAGERID | VALID_FROM | VALID_UNTIL | CUSTOMERNAME | +------------+-----------+------------+-------------+--------------+ | 1 | A | 01-01-2018 | 02-01-2018 | Blam-O Litnd | | 1 | A | 02-01-2018 | 15-01-2018 | Blamo Litd | | 1 | A | 15-01-2018 | 28-02-2018 | Blammo Ltd | +------------+-----------+------------+-------------+--------------+

虽然它应该（或者至少是我想要的）

+------------+-----------+------------+-------------+ | CUSTOMERID | MANAGERID | VALID_FROM | VALID_UNTIL | +------------+-----------+------------+-------------+ | 1 | A | 01-01-2018 | 28-02-2018 | +------------+-----------+------------+-------------+

而且我不记得我在原始帖子中是如何格式化表格的，抱歉......

标签： sqlselectoracle11g

解决方案

您可以使用窗口函数来执行此操作，该函数获取每个客户的日期的 LEAD（下一个）值，按时间戳排序

SELECT
    CUSTOMERID,
    MANAGERID,
    TIMESTAMP AS VALID_FROM,
    LEAD(TIMESTAMP) OVER(PARTITION BY CUSTOMER ORDER BY TIMESTAMP) as VALID_TIL
FROM CUSMAN.CUS_MAN_AUDIT

如果它有助于您的理解，它在功能上类似于：

SELECT
    CUSTOMERID,
    MANAGERID,
    cur.TIMESTAMP AS VALID_FROM,
    MIN(nxt.TiMESTAMP) as VALID_TIL
FROM 
  CUSMAN.CUS_MAN_AUDIT cur
  LEFT OUTER JOIN 
  CUSMAN.CUS_MAN_AUDIT nxt
  ON
    cur.CUSTOMERID = nxt.CUSTOMERID AND
    cur.TIMESTAMP < nxt.TIMESTAMP
GROUP BY
    CUSTOMERID,
    MANAGERID,
    cur.TIMESTAMP

在同一个客户上将表连接回自身，但每个 cur 记录与每个具有较晚日期 (nxt) 的记录相关联，然后获取较晚日期的 MIN..

推荐阅读