QuickBI的参数SQL应该怎样写

产品名称

QuickBI

产品模块

数据集

概述

本文通过介绍参数SQL的写法,在仪表板制作时,生成取数SQL的逻辑等方面,介绍参数SQL应该如何写,以便在应用过程可以更灵活,更高效。关于参数SQL的创建入口,以及查询条件与参数字段关联方法,可以参考产品文档:https://help.aliyun.com/document_detail/261180.html?spm=a2c4g.11186623.6.635.5fe978caZChS2h

应用场景

如果某些要展示的数据无法从已有的数据表里直接取出,需要先对表内的数据做一次聚合、去重或者需要先聚合后再将多个表关联到一起等复杂加工,然后再应用处理后的结果取数,那么此时就需要通过SQL创建数据集。

参数SQL创建数据集就是在SQL里添加参数,在应用该数据集时,就可以通过查询条件给该参数传递值,即可以提高SQL查询的效率,也可以让取数方式更灵活。

使用方法

目前参数SQL有两种写法,分别是参数占位符的写法

参数的写法

参数的写法是固定的,而且只能放在SQL的where后面(也可以放到having后面,与where后面同理),标准写法是:${字段名:参数名}

例如,目前需求是:

  • 可以任意输入日期区间,查询该区间内的订单数据
  • 可以任意输入订单等级,查询不同等级的订单数据
  • 可以任意输入数字,查询不同金额区间的订单数据
  • 查询某些姓氏的客户的订单数据(该需求仅是举例,无实际意义)

SQL如下所示

SELECT order_id,province,order_amt
from company_sales_record
where ${report_date :report_date}
and ${order_level :order_level}
and ${order_amt :order_amt}
and ${substring(customer_name,1,1):customer_name}

从该例子中可以看到,

  • 参数SQL的语句中并没有直接把“>”“<”“=”等写在SQL里
  • 如果某个字段需要截取、转换字段类型等加工操作,可以直接在该字段上做加工

因为比较符等都会在图表查询数据时,根据仪表板的查询条件的配置,以及参数的字段类型,自动生成相应的SQL语句。配置参数字段类型如下:

仪表板使用该数据集制作图表时,查询条件筛选内容,及生成的SQL如下:

从上面自动生成的SQL来看

  • report_date字段(日期类型),自动以区间的形式查询,如果查询条件里输入的是单日的日期,SQL会自动查询该日数据。
  • order_level字段(文本类型),根据输入的值,自动将值放到了“in”里面查询。
  • order_amt字段(数值类型),根据输入的数值以及符号(本例中是“>”),以比较符的形式查询。
  • customer字段(文本类型,加了字符串函数),直接查询该字段截取后的值。

占位符的写法

占位符的写法是固定的,只能放到where后面(也可以放到having后面,与where后面同理),标准写法是:${占位符名}。根据上述例子,通过占位符重新写参数SQL

SELECT order_id,province,order_amt
from company_sales_record
where report_date>'${report_date.get(0)}'-- 日期类型 获取日期范围控件起始日期
and report_date<'${report_date.get(1)}'-- 日期类型 获取日期范围控件结束日期
and order_level in ('${order_level}') -- 文本类型多选
and order_amt >${order_amt}  --数值类型大于
and substring(customer_name,1,${number}) in ('${customer_name}') --文本类型多选

该写法可以理解为,把占位符当做一个值,查询条件将该值传到占位符的位置,所以需要在SQL里把字段名,比较符都写上才行。占位符字段类型设置如下:

仪表板使用该数据集制作图表时,查询条件筛选内容,及生成的SQL如下:

从上面自动生成的SQL来看,查询条件直接将值传到了占位符的位置,没有增加任何其他信息。

占位符与参数的区别

参数写法相比,占位符写法更灵活,比如上面占位符的写法举例中,“number”这个值,就无法通过参数写法将值传入这个位置;

参数写法相比,占位符需要写更多的代码,比如“>”,“<”,日期的起止值,都要手动写出来;

应用参数写法的数据集,查询条件可以同时关联其他数据集的图表,应用占位符写法的数据集,查询条件只能关联占位符字段,无法关联其他数据集的字段,同时关联不同数据集会出现如下异常信息

 

相关文档

自定义SQL:https://help.aliyun.com/document_detail/261180.html