R语言中SQLDF执行日期关联查询返回空结果的问题求助

数据集 #

现有R数据集df定义如下：

df <- structure(list(student = c(1L, 1L, 1L, 1L, 2L, 2L, 2L),
                       var1 = c("a", "b", "b", "a", "c", "a", "b"),
                       start = structure(c(14610, 14610, 15869, 17439, 14610, 16436, 17897), class = "Date"),
                       end = structure(c(15706, 15706, 16679, 17723, 16071, 17492, 18791), class = "Date")),
                  row.names = c(NA, -7L), class = "data.frame")

数据集展示：

student var1      start        end
1       1    a 2010-01-01 2013-01-01
2       1    b 2010-01-01 2013-01-01
3       1    b 2013-06-13 2015-09-01
4       1    a 2017-09-30 2018-07-11
5       2    c 2010-01-01 2014-01-01
6       2    a 2015-01-01 2017-11-22
7       2    b 2019-01-01 2021-06-13

问题描述 #

需求是判断2010年3月1日至2020年3月1日期间，每个学生在每年区间内是否至少有一次var1=a的记录。使用sqldf执行查询后，joined_data返回空结果：

library(sqldf)

sqldf("WITH date_ranges AS (
  SELECT '2010-03-01' AS start_date, '2011-03-01' AS end_date UNION ALL
  SELECT '2011-03-01', '2012-03-01' UNION ALL
  SELECT '2012-03-01', '2013-03-01' UNION ALL
  SELECT '2013-03-01', '2014-03-01' UNION ALL
  SELECT '2014-03-01', '2015-03-01' UNION ALL
  SELECT '2015-03-01', '2016-03-01' UNION ALL
  SELECT '2016-03-01', '2017-03-01' UNION ALL
  SELECT '2017-03-01', '2018-03-01' UNION ALL
  SELECT '2018-03-01', '2019-03-01' UNION ALL
  SELECT '2019-03-01', '2020-03-01'
), 
joined_data AS (
  SELECT 
    t.student,
    d.start_date,
    d.end_date,
    t.var1
  FROM 
   df t
  JOIN 
    date_ranges d
  ON 
    t.start <= d.end_date AND t.end >= d.start_date
)

select * from joined_data;")

返回结果：

[1] student    start_date end_date   var1      
<0 rows> (or 0-length row.names)

原因分析 #

sqldf默认使用SQLite引擎，而SQLite中字符串类型的日期无法和R的Date类型直接进行正确的比较。在查询的CTEdate_ranges中，start_date和end_date是字符串格式，而df中的start和end是R的Date类型，两者类型不匹配导致区间重叠的判断逻辑失效，最终返回空结果。

解决办法 #

将date_ranges中的字符串日期转换为SQLite的日期类型，使用SQLite的date()函数即可实现类型统一，确保日期比较逻辑正确。

修改后的测试查询（验证joined_data） #

library(sqldf)

sqldf("WITH date_ranges AS (
  SELECT date('2010-03-01') AS start_date, date('2011-03-01') AS end_date UNION ALL
  SELECT date('2011-03-01'), date('2012-03-01') UNION ALL
  SELECT date('2012-03-01'), date('2013-03-01') UNION ALL
  SELECT date('2013-03-01'), date('2014-03-01') UNION ALL
  SELECT date('2014-03-01'), date('2015-03-01') UNION ALL
  SELECT date('2015-03-01'), date('2016-03-01') UNION ALL
  SELECT date('2016-03-01'), date('2017-03-01') UNION ALL
  SELECT date('2017-03-01'), date('2018-03-01') UNION ALL
  SELECT date('2018-03-01'), date('2019-03-01') UNION ALL
  SELECT date('2019-03-01'), date('2020-03-01')
), 
joined_data AS (
  SELECT 
    t.student,
    d.start_date,
    d.end_date,
    t.var1
  FROM 
   df t
  JOIN 
    date_ranges d
  ON 
    t.start <= d.end_date AND t.end >= d.start_date
)

select * from joined_data;")

执行后将返回正确的关联结果。

完整需求实现代码 #

library(sqldf)

sqldf("WITH date_ranges AS (
  SELECT date('2010-03-01') AS start_date, date('2011-03-01') AS end_date UNION ALL
  SELECT date('2011-03-01'), date('2012-03-01') UNION ALL
  SELECT date('2012-03-01'), date('2013-03-01') UNION ALL
  SELECT date('2013-03-01'), date('2014-03-01') UNION ALL
  SELECT date('2014-03-01'), date('2015-03-01') UNION ALL
  SELECT date('2015-03-01'), date('2016-03-01') UNION ALL
  SELECT date('2016-03-01'), date('2017-03-01') UNION ALL
  SELECT date('2017-03-01'), date('2018-03-01') UNION ALL
  SELECT date('2018-03-01'), date('2019-03-01') UNION ALL
  SELECT date('2019-03-01'), date('2020-03-01')
), 
joined_data AS (
  SELECT 
    t.student,
    d.start_date,
    d.end_date,
    t.var1
  FROM 
   df t
  JOIN 
    date_ranges d
  ON 
    t.start <= d.end_date AND t.end >= d.start_date
), var1_counts AS (
  SELECT 
    student,
    start_date,
    end_date,
    COUNT(CASE WHEN var1 = 'a' THEN 1 END) AS var1_count
  FROM 
    joined_data
  GROUP BY 
    student, start_date, end_date
)
SELECT 
  student,
  start_date,
  end_date,
  CASE WHEN var1_count > 0 THEN 'Yes' ELSE 'No' END AS at_least_one_var1_a
FROM 
  var1_counts;")

该代码将输出每个学生在指定年度区间内是否存在var1=a的记录。

内容的提问来源于stack exchange，提问作者stats_noob