1. 概述
1.1 预期效果
当数据库中某张表有时间字段,且几乎每分每秒都会有一条数据生成,为减少报表显示数据量,我们可以通过时间间隔来筛选数据,仅显示部分数据。
例如:在 starttime 和 endtime 里面输入时间查询范围,在 number 里面输入间隔数 3,按 3 秒时间间隔筛选数据,效果如下图所示:
1.2 实现思路
1)在 SQL 查询中添加数据集参数,通过参数面板中控件给数参数赋值,SQL 根据传递的参数查出符合条件的数据。
2)给单元格添加条件属性,通过隐藏单元格的方式显示需要显示的数据。
2. MySQL 数据库示例
不同的数据库,SQL 语句会有所不同,下面以 MySQL 数据库为例进行讲解。假设已经存在数据库表 datetest,其中每隔 1 秒就会有一笔数据,如下图所示:
2.1 新建数据集
新建模板,新建数据集 ds1,如下图所示:
SQL 语句为:
SELECT *,timestampdiff(SECOND,date_format(datets, '%Y-%m-%d'),date_format(datets, '%Y-%m-%d %H:%i:%s')) % ${number} as jg
FROM
datetest
where
1=1
${if(len(starttime)==0,"","and date_format(datets,'%Y-%m-%d %H:%i:%s') >='"+ starttime +"'")}
${if(len(endtime)==0,"","and date_format(datets,'%Y-%m-%d %H:%i:%s') <='"+ endtime +"'")}
注:数据库中日期存储为日期格式或字符串格式,SQL 语句的写法会有不同,可根据实际场景调整。
SQL 语句说明:
1)SQL 语句中有 3 个数据集参数,分别是 number,starttime,endtime。其中 number 用来传递筛选间隔数,默认值设置为 1,starttime 用来传递开始时间,endtime 用来传递结束时间,将会对应参数面板上的文本控件框和时间筛选框。
2)MySQL 中,timestampdiff 函数用于计算两个时间的指定单位的时间差,参数可以是年、季度、月、日、时、分、秒等。date_format 函数用于以不同的格式显示日期/时间数据。
3)% 为取余函数,通过对时间差取余的方式,通过对时间差取余,实现按一定时间间隔抽取数据的功能。
2.2 设计模板样式
1)将数 ds1 据集字段依次拖入单元格 A1、B1、C1单元格中,并设置单元格样式,如下图所示:
2)点击 C2 单元格,为其添加条件属性,内容为:当 C2 值不为 0 时,设改该行的行高为 0,即隐藏当前行,如下图所示:
3)隐藏 C1 单元格。
2.3 设置参数控件
点击「参数面板」,添加参数控件后设置控件属性,其中 starttime,endtime 为日期控件,number为文本控件。如下图所示:
2.4 预览效果
保存模板后,点击「分页预览」,输入开始时间、结束时间、间隔数,查询后结果如 1.1 节预期效果所示。
App 及 HTML5 效果如下图所示:
3. 其他数据库 SQL 示例
3.1 SQL Server 数据库
SELECT *,datediff(s,convert(varchar(100),datets,111),convert(varchar(100),datets,120)) % ${number} AS jg
FROM
dbo.datetest
WHERE 1 = 1
${IF(len(starttime) == 0,"","and CONVERT(varchar(100),datets,120)>='" + starttime + "'")}
${IF(len(endtime) == 0,"","and CONVERT(varchar(100),datets,120)<='" + endtime + "'") }
注:数据库中日期存储为日期格式或字符串格式,SQL 语句的写法会有不同,可根据实际场景调整。
SQL 语句说明:
1)SQL 语句中有 3 个数据集参数,分别是 number,starttime,endtime。其中 number 用来传递筛选间隔数,默认值设置为 1,starttime 用来传递开始时间,endtime 用来传递结束时间。
2)SQL Server 中,datediff 函数返回两个日期之间的时间,参数可以是年、季度、月、日、时、分、秒等。CONVERT() 函数是把日期转换为新数据类型的通用函数,也可以显示不同的格式的日期。
3)%为取余函数,通过对时间差取余,实现按一定时间间隔抽取数据的功能。
3.2 Oracle 数据库
SELECT *,to_char(datets,'yyyy-mm-dd hh24:mi:ss'),mod((datets - to_date(to_char(datets, 'yyyy-mm-dd'),'yyyy-mm-dd'))
* 24 * 60 * 60,${number}) AS jg
from dbo.datetest
where 1=1
${IF(len(starttime) == 0,"","and to_char(datets,'yyyy-mm-dd')>='" + starttime + "'")}
${IF(len(starttime) == 0,"","and to_char(datets,'yyyy-mm-dd')<='" + endtime + "'") }
注:数据库中日期存储为日期格式或字符串格式,SQL 语句的写法会有不同,可根据实际场景调整。
SQL 语句说明:
1)SQL 语句中有 3 个数据集参数,分别是 number,starttime,endtime。其中 number 用来传递筛选间隔数,默认值设置为 1,starttime 用来传递开始时间,endtime 用来传递结束时间。
2)SQL Server 中,to_date 函数将字符类型按一定格式转换为日期类型,to_char 函数将日期类型按一定格式转换为字符类型。上述SQL 中datets-to_date(to_char(datets, 'yyyy-mm-dd'),'yyyy-mm-dd') 返回天数,所以通过 * 24 * 60 * 60 的方式得到秒数。
3)mod 为取余函数,通过对时间差取余,实现按一定时间间隔抽取数据的功能。