SQL 数据集参数写法示例

过滤组件绑定参数功能优化

1）参数显示优化：

• 只显示主题内数据源包含的参数，不再显示与主题无关的参数；

• 参数显示参数来源路径和参数名，方便定位参数来源；

• 参数展示使用双层目录树，展示效果更清晰；

2）参数绑定优化

• 支持一个过滤控件绑定多个SQL参数，绑定多个参数后，所有绑定参数都受过滤控件值的控制

• 同一个仪表板的过滤组件设置绑定SQL参数时，不能选择在该仪表板中已经被绑定过的参数

SELECT * FROM 互联网访问数据 WHERE YEAR(销售日期)='${年份}' and month(销售日期)='${月份}'

SELECT * FROM 互联网访问数据 WHERE format(统计日期,'yyyyMM') >=concat('${开始年份}',case when '${开始月份}'>9 then '${开始月份}'else concat(0,'${开始月份}') end ) and format(统计日期,'yyyyMM') <=concat('${截至年份}',case when '${截至月份}'>9 then '${截至月份}'else concat(0,'${截至月份}') end )

参数只能取一个值

select *  from new_dian  where  1=1 ${if(len(所属大区2) == 0,"","and  所属大区 in ('" + 所属大区2 + "')")}

或者：select * from new_dian where 1=1 <parameter> and 所属大区 in ('${所属大区}')</parameter>

SELECT * FROM 互联网访问数据 WHERE TO_CHAR("销售日期,"'YYYY')='${年份A}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("销售日期",'YYYY')='${年份}' and EXTRACT(MONTH FROM "销售日期")='${月份}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("销售日期",'YYYY-MM-DD') in ('${日期参数}')

SELECT * FROM new_salesdetail WHERE TO_CHAR("销售日期",'YYYY-MM-DD') between '${起始日期}' and '${结束日期}'

SELECT * FROM new_salesdetail  WHERE TO_CHAR("销售日期",'YYYYMM') >=concat('${开始年份}',case when '${开始月份}'>9 then '${开始月份}'else concat(0,'${开始月份}') end ) and TO_CHAR("销售日期",'YYYYMM') <=concat('${截止年份}',case when '${截止月份}'>9 then '${截止月份}' else concat(0,'${截止月份}') end )

SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("注册时间",'YYYY')='${年份}' and FLOOR((EXTRACT(MONTH FROM "注册时间")+2)/3)='${季度}'

文本

参数只能取一个值

SELECT * FROM demo_contract WHERE "合同类型"='${文本参数}'

SELECT * FROM demo_contract WHERE "合同类型" in ('${文本参数}')

SELECT * FROM demo_contract WHERE 1=1 <parameter> and "合同类型" in ('${文本参数}') </parameter>

SELECT * FROM new_salesdetail WHERE cast(year(销售日期) as varchar)='${年份}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("销售日期",'yyyy')='${年份}' and cast(EXTRACT(MONTH FROM "销售日期") as varchar)='${月份}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("销售日期",'yyyy-mm-dd') in ('${日期参数}')

SELECT * FROM new_salesdetail WHERE TO_CHAR("销售日期",'yyyy-mm-dd') between '${起始日期}' and '${结束日期}'

SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("注册时间",'yyyymm') >=concat('${开始年份}',case when cast('${开始月份}' as tinyint)>9 then '${开始月份}' else concat('0','${开始月份}') end ) and TO_CHAR("注册时间",'yyyymm') <=concat('${截至年份}',case when cast('${截至月份}' as tinyint)>9 then '${截至月份}' else concat('0','${截至月份}') end )

SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("注册时间",'yyyy')='${年份}' and cast(FLOOR((EXTRACT(MONTH FROM "注册时间")+2)/3) as varchar)='${季度}'

参数只能取一个值

SELECT * FROM demo_contract WHERE "合同类型"='${文本参数}'

SELECT * FROM demo_contract WHERE "合同类型" in ('${文本参数}')

SELECT * FROM demo_contract WHERE 1=1 <parameter> and "合同类型" in ('${文本参数}') </parameter> 用于

SELECT * FROM new_salesdetail WHERE TO_CHAR("销售日期",'YYYY')='${年份A}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("销售日期",'YYYY')='${年份}' and EXTRACT(MONTH FROM "销售日期")='${月份}'

SELECT * FROM new_salesdetail WHERE TO_CHAR("销售日期",'YYYY-MM-DD') in ('${日期参数}')

SELECT * FROM new_salesdetail WHERE TO_CHAR("销售日期",'YYYY-MM-DD') between '${起始日期}' and '${结束日期}'

SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("注册时间",'yyyymm') >=concat('${开始年份}',case when cast('${开始月份}' as tinyint)>9 then '${开始月份}' else concat('0','${开始月份}') end ) and TO_CHAR("注册时间",'yyyymm') <=concat('${截至年份}',case when cast('${截至月份}' as tinyint)>9 then '${截至月份}' else concat('0','${截至月份}') end )

SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("注册时间",'YYYY')='${年份}' and FLOOR((EXTRACT(MONTH FROM "注册时间")+2)/3) ='${季度}'

参数只能取一个值

SELECT * FROM demo_contract WHERE "合同类型"='${文本参数}'

SELECT * FROM demo_contract WHERE "合同类型" in ('${文本参数}')

SELECT * FROM demo_contract WHERE 1=1 <parameter> and "合同类型" in ('${文本参数}') </parameter>

SELECT * FROM xxxx WHERE cast(year(销售日期) as STRING)='${年份}'

SELECT * FROM xxxx WHERE cast(year(销售日期) as STRING)='${年份}' and cast(month(销售日期) as STRING)='${月份}'

SELECT * FROM xxxx WHERE 注册时间 in ('${日期参数}')

SELECT * FROM xxxx WHERE 注册时间 between '${起始日期}' and '${结束日期}'

SELECT * FROM xxxx WHERE TO_CHAR("注册时间",'yyyymm') >=concat('${开始年份}',case when cast('${开始月份}' as tinyint)>9 then '${开始月份}' else concat('0','${开始月份}') end ) and TO_CHAR("注册时间",'yyyymm') <=concat('${截至年份}',case when cast('${截至月份}' as tinyint)>9 then '${截至月份}' else concat('0','${截至月份}') end )

SELECT * FROM xxxx WHERE cast(year(注册时间) as STRING)='${年份}' and cast(truncate((month(注册时间)+2)/3) as STRING)='${季度}'

参数只能取一个值

SELECT * FROM demo_contract WHERE 合同类型='${文本参数}'

SELECT * FROM demo_contract WHERE 合同类型 in ('${文本参数1}')

SELECT * FROM demo_contract WHERE 1=1 <parameter> and 合同类型 in ('${文本参数}') </parameter>

SELECT * FROM xxxx WHERE toString(toYear(注册时间)) ='${年份}'

SELECT * FROM xxxx WHERE toString(toYear(注册时间)) ='${年份}' and toString(toMonth(注册时间)) ='${月份}'

SELECT * FROM xxxx WHERE 注册时间 in ('${日期参数}')

SELECT * FROM xxxx WHERE 注册时间 between '${起始日期}' and '${结束日期}'

SELECT * FROM xxxx WHERE TO_CHAR("注册时间",'yyyymm') >=concat('${开始年份}',case when cast('${开始月份}' as tinyint)>9 then '${开始月份}' else concat('0','${开始月份}') end ) and TO_CHAR("注册时间",'yyyymm') <=concat('${截至年份}',case when cast('${截至月份}' as tinyint)>9 then '${截至月份}' else concat('0','${截至月份}') end )

SELECT * FROM xxxx WHERE toString(toYear(注册时间))='${年份}' and toString(floor((toMonth(注册时间)+2)/3))='${季度}'

参数只能取一个值

SELECT * FROM demo_contract WHERE "合同类型"='${文本参数}'

SELECT * FROM demo_contract WHERE "合同类型" in ('${文本参数}')

SELECT * FROM demo_contract WHERE 1=1 <parameter> and "合同类型" in ('${文本参数}') </parameter>