如何在SQL中獲取每分鐘的插入次數

我有一張看起來像這樣的桌子

我想獲得每分鐘發生的插入次數，所以輸出看起來像這樣

我怎樣才能做到這一點？這是我每天給我插入的代碼，但我不能讓它每分鐘作業

Select Count(CreatedDate) as InsertsPerDay, Convert(varchar, CreatedDate, 101) as CreatedDate
From MyTable
Where DATEDIFF(day, CreatedDate, GETDATE())) < 30
Group By Convert(varchar, CreatedDate, 101)
Order By InsertsPerDay DESC

uj5u.com熱心網友回復：

使用子查詢和滯后

declare @tmp as table(id int,   name varchar(20),   CreatedDate datetime)

insert into @tmp values(
1,'test1','2014-06-30 09:00:00')
,(1,'test2','2014-06-30 09:01:10')
,(1,'test3','2014-06-30 09:01:23')
,(1,'test4','2014-06-30 09:01:43')
,(1,'test5','2014-06-30 09:02:02')
,(1,'test6','2014-06-30 09:02:34')
,(1,'test7','2014-06-30 09:03:22')
,(1,'test8','2014-06-30 09:03:28')
,(1,'test9','2014-06-30 09:04:14')
,(1,'test1','2014-06-30 09:04:22')
,(1,'test11','2014-06-30 09:04:28')

select
IsNull(sum(case when Seconds between 0 and 60 then 1 end),0) Minute_One,
IsNull(sum(case when Seconds between 61 and 60*2 then 1 end),0) Minute_Two,
IsNull(sum(case when Seconds > 60*2 then 1 end),0) Minute_Others
from 
(
select
(DATEPART(HOUR, DiffCreatedDate) * 3600)  
(DATEPART(MINUTE, DiffCreatedDate) * 60)  
(DATEPART(SECOND, DiffCreatedDate)) Seconds 
from
(
select
CreatedDate-PriorCreatedDate DiffCreatedDate
from
(
select 
CreatedDate,
lag(CreatedDate,1) over(order by CreatedDate) PriorCreatedDate
from @tmp
)x
)y
)z
--order by Seconds

uj5u.com熱心網友回復：

DECLARE @Mytimes TABLE
(
    id INT IDENTITY NOT NULL PRIMARY KEY,
    name VARCHAR(10),
    CreatedDate DATETIME
);

INSERT INTO @Mytimes
(
    [name],
    CreatedDate
)
VALUES
('test1', '2014-06-30 09:00:00'),
('test2', '2014-06-30 09:01:10'),
('test3', '2014-06-30 09:01:23'),
('test4', '2014-06-30 09:01:43'),
('test5', '2014-06-30 09:02:02'),
('test6', '2014-06-30 09:02:34'),
('test7', '2014-06-30 09:03:22'),
('test8', '2014-06-30 09:03:28'),
('test9', '2014-06-30 09:04:14'),
('test10', '2014-06-30 09:04:22'),
('test11', '2014-06-30 09:04:28');

WITH TALLY
AS (SELECT TOP (1440)
           ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
    FROM sys.all_columns t1
        CROSS JOIN sys.all_columns t2),
     ranges
AS (SELECT CAST(DATEADD(MINUTE, N - 1, '00:00') AS TIME(0)) AS [from],
           CAST(DATEADD(MINUTE, N, '00:00') AS TIME(0)) AS [to]
    FROM TALLY),
     myTimes
AS (SELECT CAST(CreatedDate AS TIME(0)) ct
    FROM @Mytimes)
--SELECT r.[from],
--       r.[to],
SELECT MIN(t.ct) [from],
       MAX(t.ct) [to],
       COUNT(t.ct)
FROM ranges r
    -- If you want all minutes regardless there is inserts
    --LEFT JOIN myTimes t
    INNER JOIN myTimes t
        ON t.ct >= r.[from]
           AND t.ct < r.[to]
GROUP BY r.[from],
         r.[to]
ORDER BY r.[from];

注意：在左連接的情況下，您需要編輯選擇以使用合并 min(),max() 次。IE：

...
SELECT MIN(COALESCE(t.ct, r.[from])) [from],
       MAX(COALESCE(t.ct, r.[to])) [to],
       COUNT(t.ct)
FROM ranges r
    LEFT JOIN myTimes t
        ON t.ct >= r.[from]
           AND t.ct < r.[to]
GROUP BY r.[from],
         r.[to]
ORDER BY r.[from];

uj5u.com熱心網友回復：

這可能適用于 2008 年。（但無法驗證）

Select 
  Count(CreatedDate) As [Inserts Per Min]
, Min(Cast(CreatedDate As Time(0))) As [Start Time]
, Max(Cast(CreatedDate As Time(0))) As [End Time]
From MyTable
--Where CreatedDate > DateAdd(month, -1, GetDate()) 
Group By Convert(SmallDateTime, Convert(Char(16), CreatedDate, 120))
Order By [Inserts Per Min] Desc;

關于db<>fiddle 的演示在這里

標籤：

上一篇：如何使用求和函式在SQL中舍入

下一篇：Angular-使用for-in回圈時出現型別不匹配錯誤