透過時間間隔從大數據量中篩選資料

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，即隱藏當前列，如下圖所示：

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 為取餘函式，透過對時間差取餘，實現按一定時間間隔抽取資料的功能。