SQL：使用年月日分区查询某个时间段的数据

我有一个由镶木地板文件组成的AWS S3数据湖， struct 如下:

s3://bucket/device/table_x/year=2000/month=01/day=02/xyz.parquet

我的目标是使用AWS Athena来查询数据，以便在Grafana仪表板上显示.我面临的挑战是，为了在利用分区的同时创建任意时间段的动态面板，我需要找到一种方法，将数据限制在WHERE分区中的相关时间段，但这样做的方式可以跨年、跨月、跨天工作，而不必根据查询构造SQL语句.

我现在最好的建议是在Query下面，这应该是可行的--但它很复杂.对于这样的声明，有没有推荐的最佳实践？

SELECT
    Count(a1) as AVG_a1                 
FROM
    tbl_11111111_a
WHERE
    (
        -- Same year, same month
        (year = 'START_YEAR' AND month = 'START_MONTH' AND day BETWEEN 'START_DAY' AND 'END_DAY')
        OR
        -- Same year, different months
        (year = 'START_YEAR' AND month = 'START_MONTH' AND day >= 'START_DAY')
        OR
        (year = 'START_YEAR' AND month > 'START_MONTH' AND month < 'END_MONTH' AND day BETWEEN '01' AND '31')
        OR
        (year = 'START_YEAR' AND month = 'END_MONTH' AND day <= 'END_DAY')
        OR
        -- Different years
        (year > 'START_YEAR' AND year < 'END_YEAR')
        OR
        (year = 'END_YEAR' AND month < 'END_MONTH' AND day BETWEEN '01' AND '31')
        OR
        (year = 'END_YEAR' AND month = 'END_MONTH' AND day <= 'END_DAY')
    )
    AND
    t BETWEEN TIMESTAMP 'START_YEAR-START_MONTH-START_DAY 00:00:00' AND TIMESTAMP 'END_YEAR-END_MONTH-END_DAY 00:00:00'