"某表存储多个账号在多个日期发生的事件。 Row Account Number Date 1 1001 2011-01-10 2 1001 2011-02-01 3 1001 2011-02-2 .."

jinxing
乾学院 53 号会员
1 回帖 • 69 浏览 • 1 周前

@从 SQL 到 SPL：计算组内符合条件的一对最小值

应用计算

某表存储多个账号在多个日期发生的事件。

Row	Account Number	Date
1	1001	2011-01-10
2	1001	2011-02-01
3	1001	2011-02-20
4	1001	2011-02-22
5	2001	2011-04-11
6	2001	2012-01-01
7	2001	2012-01-30
8	2001	2012-02-09

现在要找出每个账号下符合条件的一对事件，分别是：日期最早的事件 a、距 a 事件 30 天以上的事件中日期最早的事件 b。

Row	Account Number	Date
1	1001	2011-01-10
3	1001	2011-02-20
5	2001	2011-04-11
6	2001	2012-01-01

可以按日期排序并按账号分组，取每组第一条 a，再筛选出距 a 事件 30 天以上的所有记录，也取第一条 b。SQL 使用窗口函数取出 a 并不太难，但无法保持子集继续筛选 b。这就要转换思路，取到 a 后，再用原表和 a 做 join 计算出所有距 a 事件 30 天以上的事件，再用与求 a 类似的办法取得 b，最后将 a union b，排序后可获得结果， SQL 会涉及多个 CTE 子句，很繁琐。这里有两次取组内第一条，而 SQL 没有天然序号，要额外制造序号再取，也很麻烦。

SPL 分组后可以保持分组子集，就可以用前面的思路实现了。SPL 天然支持序号，可以方便地取第 1 条、组内第 1 条、筛选结果的第 1 条。

	A
1	=dbConn.query("select Row, ’Account Number’, Date from tb order by Date")
2	=A1.group(#2)
3	=A2.conj(~1 \| ~.select@1((#3 - A2.~1.#3)>30))

A1：从数据库加载数据，按日期排序。

A2：用 group 函数按账号分组，#2 表示第 2 个字段。

A3：取每组第 1 条，再筛选出距第 1 条 30 天以上的记录，也取第 1 条；合并 2 条记录；最后合并各组的计算结果。A2.~1 表示组内第 1 条记录，作用范围无误解时可以简写做 ~1。select 函数用于筛选，@1 表示取第 1 条筛选结果。

问题来源：https://stackoverflow.com/questions/78306966/select-rows-based-on-desired-date-difference-from-first-row-by-group-sql