通过时间间隔从大数据量中筛选数据

1.1 预期效果

当数据库中某张表有时间字段，且几乎每分每秒都会有一条数据生成，为减少报表显示数据量，我们可以通过时间间隔来筛选数据，仅显示部分数据。

例如：在 starttime 和 endtime 里面输入时间查询范围，在 number 里面输入间隔数 3，按 3 秒时间间隔筛选数据，效果如下图所示：

1.2 实现思路

1）在 SQL 查询中添加数据集参数，通过参数面板中控件给数参数赋值，SQL 根据传递的参数查出符合条件的数据。

2）给单元格添加条件属性，通过隐藏单元格的方式显示需要显示的数据。

不同的数据库，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.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 为取余函数，通过对时间差取余，实现按一定时间间隔抽取数据的功能。