基于对象-事件模式的数据计算，是一种常见的数据分析任务。这里说的对象可以是手机银行用户，一个唯一的ID，对象涉及的事件都记录在这个ID下，例如手机银行用户的行为记录。

有了事件数据后，我们就可以进行各种各样的统计。一个比较常见的的任务就是统计指定时间段内、涉及事件满足某种条件的 ID 的数量，更一般的说法是计算每个 ID（在指定时间段内）的涉及事件的某些聚合值，然后再基于这些聚合值做 ID 的整体统计。

本文使用SPL，基于这样的数据结构，模拟数据，提供了常用的计算例程（按区域分组统计对象数量、查询对象、按对象维度统计等）。

1 表结构

1.1 用户行为记录表（event）

字段名	字段类型	含义及示例
oid	String/Long	必填，对象id
etime	Date/DateTime/Long	必填，事件时间，最高精度毫秒。示例：2021-06-01 23:59:20.912/1622563160912
etype	String	必填，事件类型。示例：deposit、withdrawal、loan、repayment等
…	…	非必填，其他字段

数据示例(对象、时间有序)：

oid	etime	etype	areano	areacode	…
100000000000000	2024-01-01	hc	ineypagsie	1000	…
100000000000000	2024-01-02	ih	ixahfazscx	1000	…
100000000000000	2024-01-03	gy	puuvbizfla	1000	…
100000000000000	2024-01-05	vw	ygpedbuihz	1000	…
100000000000000	2024-01-07	gy	uwplxammgc	1000	…
100000000000000	2024-01-10	vw	rcunsgtqsy	1000	…
100000000000001	2024-01-07	xz	hezsdalvql	1000	…
100000000000001	2024-01-10	eb	nkxwdypvzq	1000	…
100000000000001	2024-01-10	ls	puuvbizfla	1000	…
…	…	…	…	…	…

1.2 特殊对象表（event_obj）

字段名	字段类型	含义及示例
oid	String/Long	必填，对象id
…	…	非必填，其他字段

数据示例(对象、时间有序)：

oid	gender	age
100000000000084	M	36
100000000000103	F	47
100000000000167	F	45
100000000000196	M	25
100000000000231	F	34
100000000000272	F	35
100000000000545	M	21
100000000000556	M	45
100000000001107	M	38
…	…	…

2 安装并配置SPL

2.1 下载企业版

https://www.raqsoft.com.cn/download/download-jsq

2.2 编辑配置文件

…/esProc/config/raqsoftConfig.xml

内容示例：

<?xml version="1.0" encoding="UTF-8"?><Config Version="3">
    <Runtime>
        <DBList encryptLevel="0">
        </DBList>
        <Esproc>
            <charSet>ISO-8859-1</charSet>
            <splPathList>
                <splPath>/home/raqsoft/Desktop/share/SPL</splPath>
            </splPathList>
            <dateFormat>yyyy-MM-dd</dateFormat>
            <timeFormat>HH🇲🇲ss</timeFormat>
            <dateTimeFormat>yyyy-MM-dd HH🇲🇲ss.SSS</dateTimeFormat>
            <mainPath>/home/raqsoft/Desktop/share/SPL</mainPath>
            <tempPath>/home/raqsoft/Desktop/share/SPL</tempPath>
            <bufSize>65536</bufSize>
            <parallelNum>8</parallelNum>
            <cursorParallelNum>8</cursorParallelNum>
            <blockSize>1048576</blockSize>
            <nullStrings>nan,null,n/a</nullStrings>
            <fetchCount>9999</fetchCount>
            <extLibsPath>/home/raqsoft/Desktop/share/SPL</extLibsPath>
            <importLibs>
                <lib>MathCli</lib>
            </importLibs>
            <customFunctionFile>customFunctions.properties</customFunctionFile>
            <serialNo>????</serialNo>
        </Esproc>
        <Logger>
            <Level>INFO</Level>
            <Type>default</Type>
        </Logger>
    </Runtime>
    <JDBC>
    </JDBC>
</Config>

说明：

a) splPath（脚本路径）、mainPath（主路径）、tempPath（临时文件路径）设为同一路径；

b) parallelNum（最大并行数）设为不大于CPU核数的整数；

c) serialNo（系列号）设为从下载页面上得到的试用序列号；

d) 更多详细配置可参考

https://d.raqsoft.com.cn:6443/esproc/tutorial/pzraqsoftconfig.html

2.3 修改jvm内存

…/esProc/bin/config.txt

内容示例：

esproc_port=41733;init=com.scudata.ide.spl.EsprocsEE.loadQFunctions;jvm_args=-Xms60g -Xmx60g

说明：

若可供SPL使用内存有256GB，将其中-Xms60g -Xmx60g改为-Xms200g -Xmx200g

3 生成数据

3.1 生成模拟数据

data_gen.splx、event.json、event_obj.json，将这三个文件放到raqsoftConfig.xml中配置的主路径下。

在设计器中打开data_gen.splx并执行，这时需要输入三个参数：

序号	参数名	默认值	备注
1	days	15	共几天的数据
2	n	1000000	每天记录数
3	custnum	30000000	对象总个数

执行后得到《用户行为记录表》和《特殊对象表》的组表文件：event.ctx和event_obj.ctx

3.2 使用自己的数据

data_trans.splx、event.json、event_obj.json，将这三个文件放到raqsoftConfig.xml中配置的主路径下。

序号	参数名	默认值	备注
1	event_filename	event.txt	相对主路径的数据来源文件，可绝对路径
2	obj_filename	event_obj.txt	相对主路径的数据来源文件，可绝对路径
3	spt		数据分隔符，默认空，代表用制表符。也可自定义分隔符，例如填: ","，"|"等（ 填写时不带"）
4	event_fields	custfield,pt_dt,…	数据字段名，若空代表数据文件包含表头
5	obj_fields	custfield,gender,age	数据字段名，若空代表数据文件包含表头

参照表结构描述，填写event.json、event_obj.json

示例：

无表头时，填写对应列号
event.json
[
  {
    "fieldname": "oid",
    "ur_fieldname": 1
  },
  {
    "fieldname": "etime",
    "ur_fieldname": 2
  },
  {
    "fieldname": "etype",
    "ur_fieldname": 10
  }
]

event_obj.json
[
  {
    "fieldname": "oid",
    "ur_fieldname": 1
  }
]

有表头时，填写对应列名
event.json
[
  {
    "fieldname": "oid",
    "ur_fieldname": "custfield"
  },
  {
    "fieldname": "etime",
    "ur_fieldname": "pt_dt"
  },
  {
    "fieldname": "etype",
    "ur_fieldname": "event_type"
  }
]

event_obj.json
[
  {
    "fieldname": "oid",
    "ur_fieldname": "custfield"
  }
]

执行后得到《用户行为记录表》和《特殊对象表》的组表文件：event.ctx和event_obj.ctx

4 统计功能

4.1 按区域分组统计对象数量

参数

参数名	默认值	含义及示例
g1		分组字段。默认为空，代表不分组。 填写分组字段名，则按该字段分组，如按区域编号（areano）分组。
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && pos@h(wordfield,"ELE")。
p	8	并行数

SQL示例

--按区域统计访问客户数
SELECT areano, count(DISTINCT oid) AS cnt
FROM event
WHERE etime BETWEEN '2024-01-01' AND '2024-01-02'
GROUP BY areano;

--按控件统计客户数
SELECT buttonno,COUNT(DISTINCT oid) AS cnt
FROM event
WHERE instr(wordfield, 'ELE200000067202404300918021320') > 0
    AND etime BETWEEN '2024-01-01' AND '2024-01-02'
GROUP BY buttonno;

4.2 查询对象

参数

参数名	默认值	含义及示例
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && pos@h(wordfield,"ELE")。
p	8	并行数

SQL示例

--查询登陆客户
SELECT oid
FROM event
WHERE etime BETWEEN '2024-01-01' AND '2024-01-02'
GROUP BY oid;

4.3 按对象维度统计

参数

参数名	默认值	含义及示例
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && etype=="ad" && ["adaysbnijk","aholzkrtci","alcnfnodwg","augdftjsqi"].pos(areano)。
grpfld	oid	分组字段，如：oid, transdatefield
agg	count(~):cnt	聚合表达式。有默认值，表示按对象统计总事件数。 填写条件表达式后，数据按该表达式聚合统计，如：count(if(buttonno=="lbcct",1,null)):num1,count(if(buttonno=="fewxc",1,null)):num2,count(if(areano=="hqetlaswqd" && etype=="dq",1,null)):numa1。
p	8	并行数

SQL示例

--按指定地区客户统计每个客户的登录次数
SELECT oid, transdatefield, count(oid) AS cnt
FROM event
WHERE etime BETWEEN '2024-01-01' AND '2024-01-02'
    AND areacode IN (1601, 1602, 1603)
GROUP BY oid, transdatefield;

--按客户维度统计不同运营位的曝光点击次数
SELECT oid
    , count(if(buttonno = 'logined-adv', 1, NULL)) AS num1
    , count(if(buttonno = 'logined-adv-clickadverpage', 1, NULL)) AS num2
    , count(if(buttonno = 'messageRead', 1, NULL)) AS num5
    , count(if(buttonno = 'clickpicMsgCard', 1, NULL)) AS num6
    , count(if(areano = 'ShouYeScrollADs'
        AND etype = 'bg', 1, NULL)) AS numa1
    , count(if(areano = 'ShouYeScrollADs'
        AND etype = 'dj', 1, NULL)) AS numa2
FROM event
WHERE etime >= '2024-01-01'
GROUP BY oid;

4.4 按某字段分组统计特殊对象数量

参数

参数名	默认值	含义及示例
so	event_obj	特殊对象组表名（不含后缀），如：event_obj
g1		分组字段。默认为空，代表不分组。 填写分组字段名，则按该字段分组，如按区域编号（areano）分组。
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && pos@h(wordfield,"ELE")。
p	8	并行数

SQL示例

--按区域统计对应客群中客户的登录次数
SELECT areano, count(t1.oid) AS cnt
FROM event t1
    JOIN event_obj t2 ON t1.oid = t2.oid
WHERE buttonno = 'logined-adv'
    AND instr(wordfield, '${ELE000000067202404300918021199') > 0
    AND etime BETWEEN '2024-01-02' AND '2024-01-02'
GROUP BY areano;

--统计对应客群中客户的登录次数
SELECT count(t1.oid) AS cnt
FROM event t1
    JOIN event_obj t2 ON t1.oid = t2.oid
WHERE buttonno = 'logined-adv'
    AND instr(wordfield, '${ELE000000067202404300918021199') > 0
    AND etime BETWEEN '2024-01-02' AND '2024-01-02';

4.5 某段时间内的任意步漏斗n日转化结果统计

参数

参数名	默认值	含义及示例
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && pos@h(wordfield,"ELE")。
days	7	计算n日转化率，例如7，代表计算7日转化率
etypes	["co","ta","la"]	漏斗事件序列，例如co代表浏览页面，ta代表加入购入车，la代表完成付款
p	8	并行数

SQL示例

WITH e1 AS (
    SELECT oid,1 AS step1, MIN(etime) AS t1
    FROM event
    WHERE etime>=end_date-14 AND etime<end_date AND etype='co'
    GROUP BY oid),
e2 AS (
    SELECT oid,1 AS step2, MIN(e1.t1) as t1, MIN(e2.etime) AS t2
    FROM event AS e2 JOIN e1 ON e2.oid = e1.oid
    WHERE e2.etime>=end_date-14 AND e2.etime<end_date AND e2.etime>t1 AND e2.etime<t1+7 AND etype='ta'
    GROUP BY oid),
e3 as (
    SELECT oid,1 AS step3, MIN(e2.t1) as t1, MIN(e3.etime) AS t3
    FROM event AS e3 JOIN e2 ON e3.oid = e2.oid
    WHERE e3.etime>=end_date-14 AND e3.etime<end_date AND e3.etime>t2 AND e3.etime<t1+7 AND etype='la'
    GROUP BY oid)
SELECT SUM(step1) AS step1, SUM(step2) AS step2, SUM(step3) AS step3
FROM e1 LEFT JOIN e2 ON e1.oid = e2.oid LEFT JOIN e3 ON e2.oid = e3.oid;

4.6 统计对象第二天的留存率

参数

参数名	默认值	含义及示例
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && pos@h(wordfield,"ELE")。
p	8	并行数

SQL示例

with t1 as (
    select oid, date(etime) edate from event group by oid,date(etime)),
t2 as (
    select oid, edate, row_number() over(partition by oid order by edate) rn 
    from t1
),
firstday as (
    select oid, min(edate) frst from t2 group by oid),
retention as (
    select fd.oid, frst, t.edate nxt
    from firstday fd left join t2 t  
    on fd.oid=t.oid and date_add(fd.frst, interval 1 day)=t.edate
    group by fd.oid, frst, nxt
)
select frst edate, count(nxt)/count(frst) rate
from retention
group by edate
order by edate;

4.7 连续 n 天活跃度排名前 k 的对象

参数

参数名	默认值	含义及示例
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && pos@h(wordfield,"ELE")。
n	4	连续n天
k	500	排名前k
p	8	并行数

SQL示例

with t1 as (
select date(edate) edate, oid, count(*) cnt
    from event
    group by date(edate), oid),
t2 as (
    select edate, oid, rank() over(partition by edate order by cnt desc) rn from t1),
t3 as (
    select * from t2 where rn<=500),
t4 as (
    select a2.edate,a2.oid from t3 a1 
    join t3 a2 on a1.oid=a2.oid and a1.edate=a2.edate-interval 1 day 
    join t3 a3 on a1.oid=a3.oid and a1.edate=a3.edate-interval 2 day
    join t3 a4 on a1.oid=a4.oid and a1.edate=a4.edate-interval 3 day
)
select distinct oid from t4 order by oid;

4.8 每天统计最近 n 天 (含当天) 中连续活跃 k天及以上对象个数

参数

参数名	默认值	含义及示例
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && pos@h(wordfield,"ELE")。
st	2024-01-01	每天统计的起始日期
et	2024-01-10	每天统计的截止日期
n	7	连续n天
k	3	活跃k天及以上
p	8	并行数

SQL示例

with recursive edates as (  
    select min(date(etime)) edate from event
    union all
    select date_add(edate, interval 1 day) from edates
        where edate<(select max(date(etime)) from event)
),
users as (
    select distinct oid from event
),
crox as (
    select u.oid, d.edate, t.edate rdate
    from edates d cross join users u
    left join (select distinct oid, date(etime) edate from event) t
        on d.edate=t.edate and u.oid=t.oid
),
crox1 as (
    select oid,edate, rdate, row_number() over(partition by oid order by edate) rn,
    case when rdate is null or 
        (lag(rdate) over(partition by oid order by edate) is null and rdate is not null)
    then 1 else 0 end f
    from crox
),
crox2 as (
    select oid, edate, rdate, 
    cast(rn as decimal) rn, sum(f) over(partition by oid order by edate) g
    from crox1
),
crox3 as (
    select t1.oid, t1.edate, t2.g,  case when count(*)>=3 then 1 else 0 end active3
    from crox2 t1 join crox2 t2
        on t1.oid=t2.oid and t2.rn between t1.rn-6 and t1.rn
    group by t1.oid,t1.edate,t2.g
),
crox4 as (
    select oid, edate, max(active3) active3
    from crox3
    group by oid,edate
)
select edate, sum(active3) active3
from crox4
group by edate;

4.9 查找按顺序触发过系列事件的对象

参数

参数名	默认值	含义及示例
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && pos@h(wordfield,"ELE")。
etypes	["co","ta","la"]	系列事件
p	8	并行数

SQL示例

select distinct t1.oid 
from event t1 join event t2 join event t3 
    on t1.oid=t2.oid and t2.oid=t3.oid 
        and t1.etime<t2.etime and t2.etime<t3.etime
where t1.event='e2' and t2.event='e3' and t3.event='e7';

4.10 查找连续触发过系列事件的对象

参数

参数名	默认值	含义及示例
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && pos@h(wordfield,"ELE")。
etypes	["co","ta","la"]	系列事件
p	8	并行数

SQL示例

with t as (
    select oid,etime,event,
        row_number() over(partition by oid order by etime) rn 
    from event)

select distinct t1.oid
from t t1 join t t2 join t t3 
    on t1.oid=t2.oid and t2.oid=t3.oid and t1.rn+1=t2.rn and t2.rn+1=t3.rn
where t1.event='e2' and t2.event='e3' and t3.event='e7';

4.11 给定的多个事件，查找按次序发生过其中任意n个事件的对象及其事件列表

参数

参数名	默认值	含义及示例
filter		数据过滤条件。默认为空，代表不过滤数据。 填写条件表达式后，数据按该表达式过滤，如：etime>=date("2024-01-01") && etime<date("2024-01-02") && pos@h(wordfield,"ELE")。
etypes	["co","ta","la"]	系列事件
n	2	任意n个事件
p	8	并行数

SQL示例

--单句 SQL 做不了过程化的计算，难以算出这些连续事件，只能认为事先知道这些事件序列，用4.10中的方法算出触发每个事件序列的用户，然后再求并集

上述 11 个例子分别对应附件中 uba1.splx 至 uba11.splx
SPL_demo.zip