从 SQL 到 SPL：count date ranges per year
数据库表 example 的 x 字段是 id，ts 字段是时间区间。
x |
ts |
1 |
["2024-12-27 00:00:00","2025-02-01 00:00:00"] |
2 |
["2025-05-01 00:00:00","2025-05-05 00:00:00"] |
3 |
["2025-05-08 00:00:00","2025-05-20 00:00:00"] |
现在要统计出每个 id 的时间区间里含有哪些年份，每年包含多少天。
x |
extract |
count |
1 |
2024 |
5 |
1 |
2025 |
32 |
2 |
2025 |
5 |
3 |
2025 |
13 |
SQL：
WITH RECURSIVE days as (
SELECT x, LOWER(ts) as t FROM example
UNION ALL
SELECT x, t+'1 day' FROM days
where t < (SELECT UPPER(ts) FROM example where x=days.x)
)
SELECT x, extract(year from t), count(*)
FROM days
GROUP BY x,extract(year from t)
ORDER BY x,extract(year from t)
一般数据库没有时间区间相关的数据类型，拆解数据会很麻烦，postgresql 有 tsrange、daterange 类型，代码相对好写，但也要用递归子查询造出日期序列，结构复杂不易理解。SPL 可以直接生成日期序列：https://try.esproc.com/splx?3uS
A |
|
1 |
$select * from example.txt |
2 |
=A1.news(periods(date(ts(1)),date(ts(2))); x,~:t) |
3 |
=A2.groups(x, year(t):extract; count(1):count) |
A1：加载数据。[…] 会被解析为序列。
A2：用每条记录的 ts 字段生成日期序列，再将序列的成员展开，与本条记录的 x 字段组成新二维表。函数 periods 根据起止日期生成序列，(1) 表示序列的第 1 个成员。
