引言：一段SQL代码，避免不了对时间进行限制，最简单的方式就是赋值，但是这恰恰最笨的方法，因为赋值是一次性的，每次都需要手动修改需要赋值的时间，执行起来很不灵活，同时因为没有SQL代码中没有参数，也不能配置数据看板进行线上化，其中时间统计维度有周，月，季，年，如此多时间维度的代码实现，我们就可以让SQL直接帮我们输出想要的维度下的汇总数据，以此提升工作效率，下面我们一起学习一下时间筛选器的制作。
第1步：需要一张时间维表date，表结构如下
第2步：根据date维表输出一张临时表end_dt，实现代码如下
with end_dt as (
select `date` as stat_date,
case when substr(week_begin_date,1,4) = substr(week_end_date,1,4) then concat(year,'-',lpad(weekofyear(cast(date as date)),2,'0'))
when substr(week_begin_date,1,4) = substr(date,1,4) and datediff(year_end_date,week_begin_date)>=3 then concat(year,'-',lpad(weekofyear(cast(date as date)),2,'0'))
when substr(week_end_date,1,4) = substr(date,1,4) and datediff(week_end_date,year_begin_date)>=3 then concat(year,'-',lpad(weekofyear(cast(date as date)),2,'0'))
when substr(week_begin_date,1,4) = substr(date,1,4) then concat(substr(week_end_date,1,4),'-',lpad(weekofyear(cast(date as date)),2,'0'))
when substr(week_end_date,1,4) = substr(date,1,4) then concat(substr(week_begin_date,1,4),'-',lpad(weekofyear(cast(date as date)),2,'0'))
else 'error'
end as stat_week，
year as stat_year,
year_quarter_name as stat_quarter,
substr(date,1,7) as stat_month
from dw.dim_date
where date <= '$yesterday'
and month_begin_date>='2019-01-01'
)第3步：调用临时表end_dt作为主表，逻辑如下
from(
select *,
case when '$datetype'='1' then stat_week
when '$datetype'='2' then stat_month
when '$datetype'='3' then stat_quarter
when '$datetype'='4' then stat_year
else null
end as stat_period
from end_dt
where stat_date between '$begindate' and '$enddate'
) dim_date第4步：SQL执行，调出窗口，给各变量赋值
- 当datetype=1时，统计时间按周统计，
- 当datetype=2时，统计时间按月统计，
- 当datetype=3时，统计时间按季统计，
- 当datetype=4时，统计时间按年统计。