按客戶匯總支出，其中帳戶可以重新分配給不同的客戶

我想將獨特的客戶 ID 與他們的支出結合起來。

問題： account_no 可以在不同的時間點分配給不同的客戶（A、B、C 等）（假設員工 A 離開組織並且他的帳戶分配給 B）。

**例如，從下表中：**如果一個新客戶，比如 B，在 2015 年 3 月 1 日被分配了一個帳號 123（之前分配給 A），那麼 2015 年 3 月 1 日之後的支出必須屬於 B 匯款，直到他的賬戶 123 被分配給下一個顧客。

**要加入的表：**表 1 和表 2

**期望結果：**表 3

表一：不同客戶在不同日期的賬戶分配

Customer_ID   Account_no     Date 
A              123           1Feb2015   
B              123           1Mar2015
A              124           5Mar2015
C              123           1May2015
B              124           20May2015

表 2：每日級別支出資訊

Account_no      Spend        Date
123             100         2Feb2015
123             200         2Mar2015
124             200         7Mar2015
123             200         17May2015
124             150         25May2015

期望的輸出：

Customer_ID     Spend
A               100+200=300
B               150+200=350
C               200

帳號可以分配兩次、三次或更多給不同的客戶，以防被多個客戶關閉。

如何明智地匯總消費客戶 ID？

您需要一種像@TypoCubeᵀᴹ的方法這樣的最佳匹配連接，但是根據實際數據（即每個 Account_no 的行數），這可能會在聚合之前產生巨大的中間結果（並且需要對 進行唯一性約束）。table2(Account_no, Date)

可以通過不同的方法使用UNION ALLand來實現相同的邏輯邏輯LAST_VALUE：

SELECT Customer_ID, Sum(spend)
FROM 
(
  SELECT
     -- assign the "previous" Customer_ID to the current row
     Last_Value(Customer_ID IGNORE NULLS) 
         -- within a date first rows from table1, followed by table2
     Over (ORDER BY Date, x) AS Customer_ID,
     Spend
  FROM
   (
     SELECT 
        1 AS x, -- flag indicating source table
        Customer_ID,
        Account_no,
        dt,
        -- need to cast to datatype of table2.Spend
        Cast(NULL AS ...) AS Spend
     FROM cust
     UNION ALL
     SELECT
        2 AS x,
        -- need to cast to datatype of table1.Customer_ID 
        Cast(NULL AS ...), 
        Account_no,
        dt,
        spend
     FROM spend
   ) AS dt
  QUALIFY -- only rows from table2
          x = 2
      AND -- only if table1 and table2 Account_no match
          Last_Value(CASE WHEN x = 1 THEN Account_no END IGNORE NULLS) 
          Over (ORDER BY dt, Spend) = Account_no

) AS dt
GROUP BY Customer_ID
;

在解釋了賬戶被更改/轉移給不同的客戶後，如Customers_Accounts.date列所示，因此該表儲存了 anaccount_no與 a 關聯的開始日期customer_id，我們可以嘗試編寫查詢。

假設：

• 第一個表被呼叫Customers_Accounts，第二個被呼叫Daily_Spend。
• 邏輯或帳戶轉換如下所示：

該行B 123 1Mar2015表示帳戶 123 在 2015 年 3 月 15 日重新分配給客戶 B，因此它不再與以前的客戶 (A) 相關，並一直分配給 B，直到重新分配給另一個客戶客戶（2015 年 5 月 1 日至 C）。

• 有一個UNIQUE限制Customers_Accounts (account_no, date)（否則帳戶從客戶到另一個客戶的轉換將是模棱兩可的）。
• 有一個UNIQUE約束Daily_Spend (account_no, date)。這不是完全需要的。如果對 有不同的唯一約束Daily_Spend，則應在PARTITION BY子句中使用它。

詢問：

WITH ct AS
 ( SELECT 
       ca.customer_id,
       ds.spend,
       ROW_NUMBER() OVER 
           (PARTITION BY ds.account_no, ds.date     -- the PK of Daily_Spend
            ORDER BY ca.date DESC) 
           AS rn
   FROM
           Customers_Accounts AS ca
       JOIN 
           Daily_Spend AS ds
       ON 
           ca.account_no = ds.account_no
       AND ca.date <= ds.date
 )
SELECT
   customer_id,
   SUM(spend) AS spend
FROM
   ct
WHERE
   rn = 1
GROUP BY
   customer_id ;

在SQL Fiddle中測試（在 Postgres 中，因為沒有可用的 Teradata）。

引用自：https://dba.stackexchange.com/questions/159145