Count Date Ranges per Year — From SQL to SPL #23

Problem description & analysis: The x field of the database table example is ID, and the ts field is the time interval. Task: Now we need to count which years are included in the time interval of each ID, and how many days are included in each year. Code comparisons: 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) Common databases do not have data types related to time intervals, making it difficult to break down data. PostgreSQL has tsrange and daterange types, making the code relatively easy to write. However, it also requires recursive subqueries to generate date sequences, which have complex structures and are not easy to understand. SPL: SPL can directly generate date sequences

Apr 27, 2025 - 03:36

Count Date Ranges per Year — From SQL to SPL #23

Problem description & analysis:

The x field of the database table example is ID, and the ts field is the time interval.

Task: Now we need to count which years are included in the time interval of each ID, and how many days are included in each year.

Code comparisons:

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)

Common databases do not have data types related to time intervals, making it difficult to break down data. PostgreSQL has tsrange and daterange types, making the code relatively easy to write. However, it also requires recursive subqueries to generate date sequences, which have complex structures and are not easy to understand.

SPL: SPL can directly generate date sequences