SQLite使用日期条件进行统计查询返回空值问题求助

核心问题及修复方案 #

问题根因 #

1. SQLite无原生日期类型，日期比较本质是字符串逐位比对：你存储的playdate字段格式为DD/MM/YYYY，和查询时用的YYYY-MM-DD格式完全不匹配，比如字符串'17/10/2017'首字符是1，远小于'2017-10-17'的首字符2，日期条件会直接过滤掉所有数据。
2. 条件位置错误：HAVING是对聚合后的分组加过滤条件，你直接写ast >= xxx属于对原始行的过滤，应该放在WHERE子句中，放在HAVING中要么语法报错要么匹配不到符合要求的分组。

具体修复步骤 #

方案1（最推荐，长期无坑）：统一日期存储格式

把现有playdate字段的存储格式改成SQLite默认支持的YYYY-MM-DD，后续插入数据也用该格式，不需要改字段类型，SQLite文本类型即可支持正确的日期比较。

方案2（不改现有数据，临时适配）：查询时转换日期格式

用SQLite内置的strftime函数把存储的DD/MM/YYYY格式转成可比较的标准格式即可。

修正后可运行代码示例 #

-- 先计算目标时间段的平均ast值
WITH period_avg_ast AS (
    SELECT avg(ast) AS avg_val
    FROM BStats
    WHERE GS = 1
    -- 适配DD/MM/YYYY存储格式的日期转换
    AND strftime('%Y-%m-%d', playdate, '%d/%m/%Y') >= '2017-10-17'
    AND strftime('%Y-%m-%d', playdate, '%d/%m/%Y') < '2018-04-18'
)
SELECT 
    COUNT (*) AS "Number of Games",
    CASE
        WHEN ftp > .968077144 THEN "Signifigantly Above League Average"
        WHEN ftp > .884497663 THEN "Firmly Above League Average"
        WHEN ftp > .800918182 THEN "Slighty Above League Average"
        WHEN ftp > .717338701 THEN "Slightly Below League Average"
        WHEN ftp > .63375922 THEN "Firmly Below League Average"
        WHEN ftp > .565 THEN "Signifigantly Below League Average"
        ELSE "Hack A Simmons"
    END AS "Free Throw Percentage"
FROM BStats, period_avg_ast
WHERE 
    GS = 1
    AND strftime('%Y-%m-%d', playdate, '%d/%m/%Y') >= '2017-10-17'
    AND strftime('%Y-%m-%d', playdate, '%d/%m/%Y') < '2018-04-18'
    -- 原始行过滤条件放在WHERE中
    AND ast >= period_avg_ast.avg_val
GROUP BY "Free Throw Percentage";

如果已经把playdate存储格式改成YYYY-MM-DD，直接删掉strftime转换逻辑，用你原来的playdate >= 'xxx'写法即可。

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