請問如何做每30分鐘內同時登入系統人數統計?

ms sql 統計

XYZ 2019-12-26 14:05:37 ‧ 2193 瀏覽

分享至

表一是原始資料，希望能做成表二(備註不需要顥示出來，只是表示每30分鐘內的人)
表二12/25,12/26 只是列4個範例其實是希望能做出,每天每30分鐘內同時登入系統人數統計

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

dragoncancer

iT邦新手 5 級 ‧ 2019-12-27 18:10:17

最佳解答

這大概是你要的，首先要有時間基準....
然後就是算登入人數 ...(登出不計算)

--建立查詢資料表
DECLARE  @TT TABLE
(
USERID varchar(2),
TS DateTime ,
ac varchar(10) 
)
--寫入測試資料
INSERT INTO @TT 
VALUES
('a','2019/12/25 10:20:38','login')
,('a','2019/12/25 10:51:26','logout')
,('a','2019/12/26 11:21:53','login')
,('a','2019/12/26 11:26:27','logout')
,('b','2019/12/25 08:38:38','login')
,('b','2019/12/25 09:52:25','logout')
,('b','2019/12/25 10:23:16','login')
,('b','2019/12/26 09:01:24','login')
,('b','2019/12/26 10:29:26','logout')
,('b','2019/12/26 10:29:34','logoin')
,('b','2019/12/26 11:02:43','logout')
,('c','2019/12/25 08:55:36','login')
,('c','2019/12/25 10:44:26','logout')
,('c','2019/12/25 10:51:04','login')
,('c','2019/12/26 08:47:04','login')
,('c','2019/12/26 09:31:32','logout')
,('c','2019/12/26 09:59:08','login')
,('c','2019/12/26 11:02:32','logout')

--建立比對基準表 
DECLARE  @DTV TABLE
(
id int,
StarTS DateTime,
EndTS DateTime 
)

DECLARE 
@id int
,@StarT  DateTime 
,@EndT DateTime
, @FinalT  DateTime


SET @StarT = '2019-12-25 00:00:00'
SET @FinalT = '2019-12-27 00:00:00'
SET @id = 1
--利用迴圈寫入基準資料
WHILE @StarT < @FinalT
BEGIN 
--30分鐘內 => 1799 秒 
	SET @EndT = DATEADD(s ,1799, @StarT ) 
	
	INSERT INTO @DTV
	VALUES (@id,@StarT,@EndT )
	
	SET @StarT = DATEADD(s ,1800, @StarT ) 
	SET @id = @id + 1
END


SELECT  S.id,
CONVERT (nvarchar,S.StarTS,111) AS D ,
CONVERT (nvarchar,S.StarTS,108) AS ST ,
CONVERT (nvarchar,S.EndTS,108) AS ET ,
COUNT(S.ID) AS qty
 FROM @TT AS A ,@DTV AS S
 WHERE A.TS BETWEEN S.StarTS AND S.EndTS  AND A.ac = 'login'
 GROUP BY S.id,S.StarTS,S.EndTS

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

XYZ iT邦新手 4 級 ‧ 2019-12-29 17:02:35 檢舉

不好意思才疏學淺, 看不懂您的語法, 請問--建立比對基準表(@DTV)看似是寫入每日的30分的時間區間,它的id 只是每日的30分的時間區間+1, @TT、@DTV它們怎麼建立聯連? @TT 沒傳資料給@DTV 為何可以計算login的次數?

listennn08 iT邦高手 5 級 ‧ 2019-12-30 15:24:43 檢舉

你都沒自己寫過當然看不懂
你把 COUNT 跟 GROUP BY 這兩段註解掉你就知道怎麼運作的了
@DTV 是暫存資料表要一起執行
所以會建議自己建一個對照的時間資料表才不會浪費效能
順便補一個加上 logout 的圖
直接拿 dragoncancer 大的程式碼用 join 加上logout

XYZ iT邦新手 4 級 ‧ 2020-01-05 22:19:00 檢舉

請問
若要將 dragoncancer大大寫的轉成Store Procedure,每天半夜跑到另一個table要怎麼改寫? 才不會出現要輸入參數?

USE [test]
GO
/****** Object: StoredProcedure [dbo].[testlog] Script Date: 2020/1/5 下午 10:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[testlog]
-- Add the parameters for the stored procedure here

@id int
,@StarT DateTime
,@EndT DateTime
, @FinalT DateTime

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @DTV TABLE
(
id int,
StarTS DateTime,
EndTS DateTime
)
-- Insert statements for procedure here
--SET @StarT = '2019-10-01 00:00:00'
--SET @FinalT = '2019-12-31 00:00:00'
--SET @id = 1
--利用迴圈寫入基準資料
WHILE @StarT < @FinalT
BEGIN
--30分鐘內 => 1799 秒
SET @EndT = DATEADD(s ,1799, @StarT )

INSERT INTO @DTV
VALUES (@id,@StarT,@EndT )

SET @StarT = DATEADD(s ,1800, @StarT ) 
SET @id = @id + 1

END

SELECT S.id,
CONVERT (nvarchar,S.StarTS,111) AS D ,
CONVERT (nvarchar,S.StarTS,108) AS ST ,
CONVERT (nvarchar,S.EndTS,108) AS ET ,
COUNT(S.ID) AS qty
FROM test_Logs AS A ,@DTV AS S
WHERE A.StartTime BETWEEN S.StarTS AND S.EndTS
GROUP BY S.id,S.StarTS,S.EndTS
order by CONVERT (nvarchar,S.StarTS,111), S.id,S.StarTS,S.EndTS
END

XYZ iT邦新手 4 級 ‧ 2020-01-06 09:40:20 檢舉

to listennn08,
請問要如何自己建一個對照的時間資料表(從108/04~118/12)?

登入發表回應

阿展展展

iT邦好手 1 級 ‧ 2019-12-26 14:13:56

這題的難點在於切時間(我覺得)

切完之後找 action = login
group by 後再 count 就完成惹

你寫了多少?

回應 10
分享
檢舉

看更多先前的回應...收起先前的回應...

ccutmis iT邦高手 2 級 ‧ 2019-12-26 15:55:17 檢舉

寫個腳本每三十分鐘強制重開伺服器，就不需要這麼麻煩了...

阿展展展 iT邦好手 1 級 ‧ 2019-12-26 15:56:59 檢舉

一.一... 來來來哩來

ckp6250 iT邦好手 1 級 ‧ 2019-12-26 15:59:25 檢舉

這題的難點在於切時間(我覺得)

阿大果然是行家，一眼看出重點來。
只不過，這回怎麼沒有轉包呢？

　　表二呈現出來的，其實是【每天每段固定時間內同時登入系統人數】，而非【每天每30分鐘內同時登入系統人數】，如果是『每30分鐘』的話，那麼，第二個人要和第一個人比，第三個人要和第二個人比，會很複雜。

　　我國的二代健保之補充保費之計算，就是這麼複雜，它的規定是【每４個月領取之獎金若超過ＸＸ元，則.....】，所以，在程式邏輯上，『1,2,3,4月』要加總驗算一次，『2,3,4,5月』要加總驗算一次，『3,4,5,6月』要加總驗算一次.....，『11,12,1,2月』要加總驗算一次.....無限循環，生生不息。

　　那麼，假如『3,4,5,6月』這一期超標了，依規定繳納補充保費之後呢，那下一次的計算就不再是『4,5,6,7月』了，而是『7,8,9,10月』重新開計。

　　不知是那位天才設定的規範，整死人。