乾学院 53 号会员
1 回帖  •  111 浏览  • 
     ...

从 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 个成员。

问题来源：https://stackoverflow.com/questions/78171203/is-it-possible-with-sql-to-count-all-dateranges-per-year