doris中,获取某天的上周一到上周日的数据。如:今天是2023-12-07,我想获取2023-11-27到2023-12-02的数据
WHERE
case
when DAYOFWEEK('${statdate}') = 1 then statdate BETWEEN DATE_FORMAT(
DATE_SUB(
DATE_FORMAT(
DATE_SUB(
'${statdate}',
INTERVAL 1 DAY
),
'%Y-%m-%d'
),
INTERVAL DAYOFWEEK(
DATE_FORMAT(
DATE_SUB(
'${statdate}',
INTERVAL 1 DAY
),
'%Y-%m-%d'
)
) + 5 DAY
),
'%Y-%m-%d'
)
AND DATE_FORMAT(
DATE_SUB(
DATE_FORMAT(
DATE_SUB(
'${statdate}',
INTERVAL 1 DAY
),
'%Y-%m-%d'
),
INTERVAL DAYOFWEEK(
DATE_FORMAT(
DATE_SUB(
'${statdate}',
INTERVAL 1 DAY
),
'%Y-%m-%d'
)
) - 1 DAY
),
'%Y-%m-%d'
)
else statdate BETWEEN DATE_FORMAT(
DATE_SUB(
'${statdate}',
INTERVAL DAYOFWEEK('${statdate}') + 5 DAY
),
'%Y-%m-%d'
)
AND DATE_FORMAT(
DATE_SUB(
'${statdate}',
INTERVAL DAYOFWEEK('${statdate}') - 1 DAY
),
'%Y-%m-%d'
)
end
其中:'${statdate}'是传参变量,如:‘2023-12-07’。