如何定义SQL双日期变量，未手动输入时默认取最近24小时数据？

针对BI工具（如Metabase、Looker）的模板变量方案 #

你当前SQL使用的{{start_date}}/{{end_date}}属于BI工具的模板变量语法，直接在工具里给变量设置默认值即可实现未指定时自动查询最近24小时：

• 给start_date设置默认值：根据工具适配的数据库语法，比如CURRENT_TIMESTAMP - INTERVAL '24 HOUR'（PostgreSQL）或DATE_SUB(NOW(), INTERVAL 24 HOUR)（MySQL）
• 给end_date设置默认值：CURRENT_TIMESTAMP（或NOW()）

修正并优化后的SQL：

with all_data as (
  select  
    agent_id,
    date(created_at) as order_date,
    count(delivered) as number_of_orders 
  from orders_order
  where created_at between {{start_date}} and {{end_date}}
  group by order_date, agent_id
  order by order_date
)
select 
  ag.first_name, 
  ag.last_name, 
  ad.* 
from all_data as ad 
left join agents_agent as ag on ad.agent_id = ag.id

优化点：

• 移除了原SQL中select语句里多余的逗号
• 给date(created_at)添加别名order_date，避免group by时与数据库关键字冲突
• 直接用created_at而非date(created_at)做范围过滤，避免索引失效，提升查询性能

纯SQL环境下的实现方案 #

如果无需BI工具，直接在数据库中执行，可使用COALESCE（或IFNULL）给变量赋予默认值：

PostgreSQL示例 #

with all_data as (
  select  
    agent_id,
    date(created_at) as order_date,
    count(delivered) as number_of_orders 
  from orders_order
  where created_at between 
    COALESCE($1, CURRENT_TIMESTAMP - INTERVAL '24 HOUR') 
    and COALESCE($2, CURRENT_TIMESTAMP)
  group by order_date, agent_id
  order by order_date
)
select 
  ag.first_name, 
  ag.last_name, 
  ad.* 
from all_data as ad 
left join agents_agent as ag on ad.agent_id = ag.id

（注：$1/$2是数据库参数占位符，替换为你实际使用的变量语法）

MySQL示例 #

with all_data as (
  select  
    agent_id,
    date(created_at) as order_date,
    count(delivered) as number_of_orders 
  from orders_order
  where created_at between 
    IFNULL(@start_date, DATE_SUB(NOW(), INTERVAL 24 HOUR)) 
    and IFNULL(@end_date, NOW())
  group by order_date, agent_id
  order by order_date
)
select 
  ag.first_name, 
  ag.last_name, 
  ad.* 
from all_data as ad 
left join agents_agent as ag on ad.agent_id = ag.id

核心注意事项 #

• 不要用date(created_at)做过滤条件：这会导致created_at字段的索引无法被使用，大幅降低查询效率
• 始终给聚合字段/表达式取别名：避免语法冲突，提升SQL可读性

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