在時間範圍內獲取採樣點數據的最佳方法是什麼

我正在尋找一些建議，如何設置一組查詢來檢查一段時間內採樣點的聚合值。這應該在 IQ 伺服器上執行，所以也許沒有這麼多的過程呼叫會很酷；）

我查看了視窗功能。我認為它可能僅適用於時間範圍內的聚合數據，而不適用於採樣點。所以有這種情況：

我有一個包含開始日期和結束日期的項目列表。我需要收集目前時間活動的程序總和。這不是關於系統流程，而是更多關於在給定時間有多少工匠在工作之類的東西。

想像一下這樣的表格（我對範例進行了一些修改以省略無聊的部分……所以它可能無法完美執行）

create table items (
   id int ot null default autoincrement,
   "Type" integer,
   TimeStampStart datetime null, 
   TimeStampend datetime null
)

此查詢集目前使用：

create table #Processes(
 "Type" integer,
 "timestamp" "datetime" null,
 "Sum" integer null
 )

set @date = '20120303'
while @date <= '20130505'
 begin
   insert into #Processes
     select "Type",'timestamp'=@date,'Sum'="count"()
       from "items"
       and "TimeStampStart" between "dateadd"("day",-"abs"(100),@date) and @date
       and "TimeStampStart" <= @date
       and "isnull"("TimeStampEnd",@date) >= @date
       group by "Type"
   set @date = "dateadd"("ss",3600,@date)
 end
select * from #Processes;

這可能不是最好的方法。所以我正在尋找更好的方法；）

我會進一步回答 Michael Green 並建議生成一個數字表。它也將有助於許多其他算法。另一個方便的表是日曆表，每個日期為 +/- 20 年。您也可以從此類表的 ID 列中獲取數字。

這是我提出的一個查詢。您可以輕鬆地將其包裝成 SP 到 TVF。這個對我有用。我在我的開發數據庫上進行了測試，所以必須在表名前加上 tmp。

使用 >、>=、<、<= 來計算是否在邊界上開始/結束的程序。

---- Create temp tables to test
create table tmp_numbers  (
id int null
)
insert into tmp_numbers values (0)
go
insert into tmp_numbers 
select MAX(ID)+1 from tmp_numbers 
go 1000
create clustered index Idx_ID on tmp_numbers (id)
go

create table tmp_items (
   "Type" integer,
   TimeStampStart datetime null, 
   TimeStampend datetime null
)
go

insert into tmp_items
select 1,'2012-03-03 02:13:01.000','2012-03-03 15:09:05.000'
UNION ALL
select 2,'2012-03-03 07:33:59.990','2012-03-03 14:59:10.000'
UNION ALL
select 3,'2012-03-03 22:13:01.000','2012-03-04 15:09:05.000'
UNION ALL
select 4,'2012-03-04 10:33:59.990','2012-03-04 14:59:10.000'
UNION ALL
select 5,'2012-03-04 23:20:00.000','2012-03-05 02:50:00.000'
UNION ALL
select 6,'2012-03-05 12:00:00.000','2012-03-05 23:01:00.000'

------ The query itself

declare @start datetime, @end datetime
set @start = '20120303'
set @end  = '20120305'

select 
ID,
DATEADD(hour, id, @start),
DATEADD(hour, id+1, @start),
(select COUNT (*) 
   from tmp_items 
   where TimeStampStart <= DATEADD(hour, tmp_numbers.id +1, @start) 
   and TimeStampEnd > DATEADD(hour, tmp_numbers.id, @start)

   -- To limit number of Log ("items") rows scanned:
   and TimeStampEnd >= @start

)
from tmp_numbers 
where id < DATEDIFF (hh,@start, @end)+24;

組織此查詢以克服單 CPU 限制的另一種方法

declare @start datetime, @end datetime
   set @start = '20120303'
   set @end  = '20120305'

create table #Temp (
StartTime datetime null,
EndTime datetime null,
RowNum int null)

insert into #Temp
 select 
   DATEADD(hour, id, @start),
   DATEADD(hour, id+1, @start),
 from tmp_numbers 
   where id < DATEDIFF (hh,@start, @end)+24;

update #Temp
set RowNum =  (select COUNT (*) 
       from tmp_items 
       where TimeStampStart <= #Temp.EndTime 
       and TimeStampEnd > #Temp.StartTime 

       -- To limit number of Log ("items") rows scanned:
       and TimeStampEnd >= @start  
   )

弗里安，

上週我和一位同事討論了這個算法。我還沒有機會測試它，但我們對此充滿信心。

1. 將開始時間和結束時間轉換為小時數。這可以是HOURS()函式或DATEDIFF. 基線可以是任何東西，但MIN(TimeStampStart)最方便。TimeStampStart上下四捨五入TimeStampEnd。讓我們呼叫結果列HoursStart和HoursEnd。

2）加入整數表 where Integer.Number >= HoursStart and Integer.Number <= HoursEnd。從現在開始，我們將 Integer.Number 稱為“HourSampled”。整數表會更大或更小，具體取決於為轉換為上述小時數而選擇的基線。創建一個名為 nnn（或您選擇的任何其他內容）的新列，其值為 1。

3. 按 Type & HourSampled 將 nnn 匯總到 columnSum中。如果需要，可以將 HourSampled 轉換回 DateTimeSampled。

儘管我已在單獨的步驟中對此進行了說明，但它可以在單個語句中進行編碼。這將比您的循環算法更快。

要更改採樣頻率，請更改將 TimeStampStart 轉換為整數的函式以及 Integer 表的相應內容。例如，一刻鐘是MINUTES(TimeStampStart)/15.

很抱歉，我的 Sybase 知識很少。我試圖在可能的地方引用正確的語法。

為了增加樂趣，您可以選擇

select
   Type,
   REPLICATE(Sum)
from #Process
order by
   Type,
   HourSampled

得到一個現成的ASCII條形圖！

引用自：https://dba.stackexchange.com/questions/61367