在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

實(shí)戰(zhàn) SQL：銀行等金融機(jī)構(gòu)可疑支付交易的監(jiān)測(cè)

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

大家好，我是只談技術(shù)、不聊人生的 Tony 老師，在實(shí)戰(zhàn) SQL 系列文章的上一篇中我們介紹了如何實(shí)現(xiàn)微信、微博等社交網(wǎng)絡(luò)中的友好、粉絲關(guān)系分析。

今天，我們來(lái)談?wù)劻硪粋€(gè)話題，如何利用 SQL 窗口函數(shù)發(fā)現(xiàn)可疑的銀行卡支付交易。2002 年，中國(guó)人民銀行為了加強(qiáng)對(duì)人民幣支付交易的監(jiān)督管理，規(guī)范人民幣支付交易報(bào)告行為，防范利用銀行支付結(jié)算進(jìn)行洗錢等違法犯罪活動(dòng)，制定了《人民幣大額和可疑支付交易報(bào)告管理辦法》。

該辦法定義了大額支付交易和可疑交易支付的各種場(chǎng)景和定義。其中大額交易判斷比較簡(jiǎn)單，主要是通過(guò)單筆交易額進(jìn)行監(jiān)測(cè)；可疑交易的情況比較復(fù)雜，其中有一些是基于短期交易頻率、相同收付款人和交易額度等數(shù)據(jù)進(jìn)行監(jiān)測(cè)。針對(duì)這種類型的可疑交易，利用 SQL 窗口函數(shù)可以非常方便地進(jìn)行分析。

本文示例經(jīng)過(guò)驗(yàn)證的數(shù)據(jù)庫(kù)包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite，首先給出結(jié)論：

上面這些函數(shù)包含了OVER子句，都屬于窗口函數(shù)而不是聚合函數(shù)。
窗口函數(shù)簡(jiǎn)介

窗口函數(shù)（Window Function）是專門用于數(shù)據(jù)分析的函數(shù)，它們針對(duì)查詢中的每一行數(shù)據(jù)，基于和當(dāng)前行相關(guān)的一組數(shù)據(jù)計(jì)算出一個(gè)結(jié)果。我們可以通過(guò)與聚合函數(shù)比較來(lái)了解窗口函數(shù)的作用：

上圖中的 COUNT、SUM 以及 AVG 既可以用做聚合函數(shù)，也可以用作窗口函數(shù)；聚合函數(shù)針對(duì)所有的數(shù)據(jù)只返回一條結(jié)果，窗口函數(shù)為每行數(shù)據(jù)都返回一個(gè)結(jié)果。

從定義上來(lái)講，窗口函數(shù)包含了一個(gè)OVER子句，用于指定數(shù)據(jù)分析的窗口：

window_function ( expression, … ) OVER (
PARTITION BY …
ORDER BY …
frame_clause
)

其中，window_function 是窗口函數(shù)的名稱；expression 是參數(shù)，有些函數(shù)不需要參數(shù)；OVER子句包含三個(gè)選項(xiàng)：分區(qū)（PARTITION BY）、排序（ORDER BY）以及窗口大小（frame_clause）。

PARTITION BY選項(xiàng)用于定義分區(qū)，作用類似于 GROUP BY 的分組。如果指定了分區(qū)選項(xiàng)，窗口函數(shù)將會(huì)分別針對(duì)每個(gè)分區(qū)單獨(dú)進(jìn)行分析；如果省略分區(qū)選項(xiàng)，所有的數(shù)據(jù)作為一個(gè)整體進(jìn)行分析。

ORDER BY選項(xiàng)用于指定分區(qū)內(nèi)的排序方式，通常用于數(shù)據(jù)的排名分析。

窗口選項(xiàng) frame_clause 用于在當(dāng)前分區(qū)內(nèi)指定一個(gè)可移動(dòng)的計(jì)算窗口；指定了窗口之后，分析函數(shù)不再基于分區(qū)進(jìn)行計(jì)算，而是基于窗口內(nèi)的數(shù)據(jù)進(jìn)行計(jì)算。具體來(lái)說(shuō)，窗口大小的常用選項(xiàng)如下：

{ ROWS | RANGE } frame_start
{ ROWS | RANGE } BETWEEN frame_start AND frame_end

其中，ROWS表示以行為單位計(jì)算窗口的偏移量，RANGE表示以數(shù)值（例如 30 分鐘）為單位計(jì)算窗口的偏移量，參考下圖：

CURRENT ROW表示當(dāng)前正在處理的行；其他的行可以使用相對(duì)當(dāng)前行的位置表示；窗口的大小不會(huì)超出當(dāng)前分區(qū)的范圍。

frame_start 用于定義窗口的起始位置，可以指定以下內(nèi)容之一：

UNBOUNDED PRECEDING，窗口從分區(qū)的第一行開始，默認(rèn)值；
N PRECEDING，窗口從當(dāng)前行之前的第 N 行或者數(shù)值開始；
CURRENT ROW，窗口從當(dāng)前行開始。
1
2
3

frame_end 用于定義窗口的結(jié)束位置，可以指定以下內(nèi)容之一：

CURRENT ROW，窗口到當(dāng)前行結(jié)束，默認(rèn)值；
N FOLLOWING，窗口到當(dāng)前行之后的第 N 行或者數(shù)值結(jié)束；
UNBOUNDED FOLLOWING，窗口到分區(qū)的最后一行結(jié)束。
1
2
3

常見的窗口函數(shù)可以分為以下幾類：聚合窗口函數(shù)、排名窗口函數(shù)（實(shí)現(xiàn)產(chǎn)品的分類排名）以及取值窗口函數(shù)（實(shí)現(xiàn)銷量的同比/環(huán)比分析）。本文只涉及聚合窗口函數(shù)，其他函數(shù)下回分解。

接下來(lái)我們介紹兩個(gè)具體的案例，創(chuàng)建一個(gè)記錄銀行卡交易流水的表 transfer_log：

CREATE TABLE transfer_log
( log_id INTEGER NOT NULL PRIMARY KEY,
log_ts TIMESTAMP NOT NULL,
from_user VARCHAR(50) NOT NULL,
to_user VARCHAR(50),
type VARCHAR(10) NOT NULL,
amount NUMERIC(10) NOT NULL
);

INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (1,‘2019-01-02 10:31:40’,‘62221234567890’,NULL,‘存款’,50000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (2,‘2019-01-02 10:32:15’,‘62221234567890’,NULL,‘存款’,100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (3,‘2019-01-03 08:14:29’,‘62221234567890’,‘62226666666666’,‘轉(zhuǎn)賬’,200000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (4,‘2019-01-05 13:55:38’,‘62221234567890’,‘62226666666666’,‘轉(zhuǎn)賬’,150000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (5,‘2019-01-07 20:00:31’,‘62221234567890’,‘62227777777777’,‘轉(zhuǎn)賬’,300000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (6,‘2019-01-09 17:28:07’,‘62221234567890’,‘62227777777777’,‘轉(zhuǎn)賬’,500000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (7,‘2019-01-10 07:46:02’,‘62221234567890’,‘62227777777777’,‘轉(zhuǎn)賬’,100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (8,‘2019-01-11 09:36:53’,‘62221234567890’,NULL,‘存款’,40000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (9,‘2019-01-12 07:10:01’,‘62221234567890’,‘62228888888881’,‘轉(zhuǎn)賬’,10000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (10,‘2019-01-12 07:11:12’,‘62221234567890’,‘62228888888882’,‘轉(zhuǎn)賬’,8000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (11,‘2019-01-12 07:12:36’,‘62221234567890’,‘62228888888883’,‘轉(zhuǎn)賬’,5000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (12,‘2019-01-12 07:13:55’,‘62221234567890’,‘62228888888884’,‘轉(zhuǎn)賬’,6000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (13,‘2019-01-12 07:14:24’,‘62221234567890’,‘62228888888885’,‘轉(zhuǎn)賬’,7000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (14,‘2019-01-21 12:11:16’,‘62221234567890’,‘62228888888885’,‘轉(zhuǎn)賬’,70000);

還是需要說(shuō)明一下：可疑支付交易并不一定就是有問(wèn)題的交易；本文只是采用了一個(gè)簡(jiǎn)化的計(jì)算模式作為演示，主要目的是為了說(shuō)明窗口函數(shù)的作用。
短期累計(jì)轉(zhuǎn)賬超過(guò)一百萬(wàn)元

當(dāng)個(gè)人賬戶在短期（通常是 10 個(gè)營(yíng)業(yè)日）內(nèi)出現(xiàn)累計(jì) 100 萬(wàn)元以上轉(zhuǎn)賬操作，我們認(rèn)為這是一個(gè)可疑的行為，需要記錄并進(jìn)一步進(jìn)行分析。以下語(yǔ)句用于查詢 5 天之內(nèi)累積轉(zhuǎn)賬超過(guò) 100 萬(wàn)的賬號(hào)：

select *
from (
select *,
sum(amount) over (partition by from_user order by log_ts range interval ‘5’ day preceding) total_amount
from transfer_log
where type = ‘轉(zhuǎn)賬’
) t
where total_amount > 1000000;

 7|2019-01-10 07:46:02|62221234567890|62227777777777|轉(zhuǎn)賬  |100000|     1050000|
1

該查詢主要使用了窗口函數(shù) sum，partition by 用于按照用戶進(jìn)行分析，而不是將所有用戶交易混合在一起；order by 按照交易時(shí)間進(jìn)行排序；range 將數(shù)據(jù)分析的窗口定義為 5 天之內(nèi)的交易流水。

查詢結(jié)果顯示賬號(hào) 62221234567890 在 5 天之內(nèi)累計(jì)轉(zhuǎn)賬 105 萬(wàn)。
相同收付款人短期頻繁轉(zhuǎn)賬

利用 COUNT 窗口函數(shù)，可以分析相同收付款人短期內(nèi)的轉(zhuǎn)賬頻率，例如：

select *
from (
select *,
count(1) over (partition by from_user,to_user order by log_ts range interval ‘5’ day preceding) times
from transfer_log
where type = ‘轉(zhuǎn)賬’
) t
where times >= 3;

 7|2019-01-10 07:46:02|62221234567890|62227777777777|轉(zhuǎn)賬  |100000|    3|
1

其中，count 函數(shù)用于統(tǒng)計(jì)次數(shù)；partition by 按照不同的發(fā)起方和接收方進(jìn)行分組；其他參數(shù)和上一個(gè)示例相同。查詢表明賬號(hào) 62221234567890 在 5 天之內(nèi)給賬號(hào) 62227777777777 轉(zhuǎn)賬了 3 次以上。

下面我們?cè)賮?lái)介紹一個(gè) AVG 窗口函數(shù)的使用案例。
移動(dòng)平均法預(yù)測(cè)產(chǎn)品的銷量

移動(dòng)平均法是用一組最近的實(shí)際數(shù)據(jù)值來(lái)預(yù)測(cè)未來(lái)一期或幾期內(nèi)公司產(chǎn)品的需求量、公司產(chǎn)能等的一種常用方法。移動(dòng)平均法適用于近期預(yù)測(cè)，分為簡(jiǎn)單移動(dòng)平均法、加權(quán)移動(dòng)平均法、趨勢(shì)移動(dòng)平均法等。

我們以簡(jiǎn)單移動(dòng)平均法為例，也就是說(shuō)未來(lái)一期的銷量等于前 N 期銷量的算術(shù)平均值?；谠撲N售數(shù)據(jù)，我們預(yù)測(cè)一下未來(lái)的產(chǎn)品銷量：

select *, avg(amount) over (partition by product order by ym rows 4 preceding) next_amount
from sales_monthly
order by product,ym desc;

…

avg 函數(shù)用于計(jì)算平均值；partition by 按照不同產(chǎn)品進(jìn)行分析；order by 按照月份進(jìn)行排序；rows 指定分析窗口為前 4 個(gè)月和當(dāng)前月（共 5 期數(shù)據(jù)進(jìn)行平均）。

查詢結(jié)果顯示“桔子”最新一期（201907）的預(yù)期銷量為 11351.4；利用已有的銷量數(shù)據(jù)和基于歷史的預(yù)測(cè)值，可以計(jì)算出預(yù)測(cè)的標(biāo)準(zhǔn)誤差（需要用到取值窗口函數(shù) LAG），從而可以嘗試不同的 N 值并找出更誤差最小的值。
總結(jié)

SQL 窗口函數(shù)提供了強(qiáng)大的數(shù)據(jù)分析功能，我們介紹了一些聚合窗口函數(shù)的使用。SUM 函數(shù)常常用于計(jì)算歷史累計(jì)值，COUNT 函數(shù)可以用于計(jì)算數(shù)據(jù)累計(jì)出現(xiàn)的次數(shù)，AVG 函數(shù)可以用于計(jì)算移動(dòng)平均值。