我有两个蜂巢表,客户和事务。
customer table
---------------------------------
customer_id | account_threshold
---------------------------------
101 | 200
102 | 500
transaction table
-------------------------------------------
transaction_date | customer_id | amount
-------------------------------------------
07/01/2018 101 250
07/01/2018 102 450
07/02/2018 101 500
07/03/2018 102 100
07/04/2018 102 50
Result:
------------------------------
customer_id | breach_count
------------------------------
101 2
102 1
我必须计算交易表中金额总和超过客户表中account_threshold的实例数。 当检测到违规行为时,我将计数器重置为 0。
对于客户 101,第一个事务高于阈值,因此,违规计数为 1。然后,在第 3 笔交易中再次出现 101 的违规行为。因此,101 的总违规计数为 2。 对于客户 102,第一个事务 (450) 低于阈值。102 的下一笔交易是 100 美元,它突破了 500 的阈值,因此breach_count将是 1。
我已经尝试过窗口化,但我无法获得任何线索,如何通过连接两个表进行。
您可以尝试编写一个子查询来按amount
按customer_id
获得累加amount
顺序,然后基于customer
然后Outer JOIN
Count
SELECT t.customer_id, COUNT(t.totle) breach_count
FROM customer c
LEFT JOIN
(
select t1.*,SUM(t1.amount) OVER(PARTITION BY t1.customer_id order by t1.amount) as totle
from transaction1 t1
) t on c.customer_id = t.customer_id
WHERE c.account_threshold < t.totle
GROUP BY t.customer_id
这里是来自 Sqlserver 的一个 sqlfildde,虽然 DBMS 不同,但 windows 函数语法是一样的
[结果]:
| customer_id | breach_count |
|-------------|--------------|
| 101 | 2 |
| 102 | 1 |
在值更改时重置计数/排名/总和
输入表 :- 时间 |价值 12 |一个 13 |一个 14 |C 15 |C 16 |B 17 |B 18 |一个
您只需要延迟即可了解以前的值 步骤1.选择*,滞后(状态)作为滞后
现在将滞后值与实际值进行比较,如果它不同,则取 1 否则 0(将此列作为标志) 第 2 步。选择*,当滞后时!= 状态然后 1 否则 0
现在做总和标志将其作为运行总和 - 你会得到每个组不同的总和值,组意味着当值发生变化时它是一个新组 第 3 步。选择 *,总和(标志)超过(按时间排序)flag_sum
现在只需每组的行号
步骤 4.选择 Rownumber() 上(按时间flag_sum顺序划分)
最终结果
时间 | 值 | 滞后 | 标志 | flag_sum | 行号 12 |一 |空 |1 |1 |1 13 |一 |A |0 |1 |2 14 |C |A |1 |2 |1 15 |C |C |0 |2 |2 16 |乙 |C |1 |3 |1 17 |乙 |B |0 |3 |2 18 |一 |B |1 |4 |1
您可以使用总和/计数代替行号,只要值更改,您就可以重置任何内容。