iT邦幫忙

0

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

XYZ 2019-12-26 14:05:371437 瀏覽

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

表一是原始資料,希望能做成表二(備註不需要顥示出來,只是表示每30分鐘內的人)
表二12/25,12/26 只是列4個範例其實是希望能做出,每天每30分鐘內同時登入系統人數統計
https://ithelp.ithome.com.tw/upload/images/20191226/201081576187dAy8I5.jpg

https://ithelp.ithome.com.tw/upload/images/20191226/20108157RusD35ChNt.jpg

0
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

https://ithelp.ithome.com.tw/upload/images/20191227/20051841Jacxan1Ywi.jpg

看更多先前的回應...收起先前的回應...
XYZ iT邦新手 5 級 ‧ 2019-12-29 17:02:35 檢舉

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

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

XYZ iT邦新手 5 級 ‧ 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邦新手 5 級 ‧ 2020-01-06 09:40:20 檢舉

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

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

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

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

你寫了多少?

看更多先前的回應...收起先前的回應...
ccutmis iT邦高手 4 級 ‧ 2019-12-26 15:55:17 檢舉

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

一.一... 來來來哩來

ckp6250 iT邦研究生 1 級 ‧ 2019-12-26 15:59:25 檢舉
這題的難點在於切時間(我覺得)

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

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

  我國的二代健保之補充保費之計算,就是這麼複雜,它的規定是【每4個月領取之獎金若超過XX元,則.....】,所以,在程式邏輯上,『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月』重新開計。

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

不不不 我是菜雞 /images/emoticon/emoticon36.gif
健保根本天才之設定/images/emoticon/emoticon40.gif

SQL 的東西應該是被秒殺了啦~ 但是這題只有題目 不知道發問者到底有沒有自己想過QQ

ckp6250 iT邦研究生 1 級 ‧ 2019-12-26 17:36:16 檢舉

如果是『每30分鐘』的話,就不見得是秒殺啦。

不是應該要找時間內的 login logout 做差集 有 login 但沒 logout 再計算 count 嗎 /images/emoticon/emoticon33.gif

可是瑞凡 我連 login 都抓不到/images/emoticon/emoticon02.gif

ckp6250 iT邦研究生 1 級 ‧ 2019-12-27 10:50:19 檢舉

我很想知道,logout 的時間是怎麼抓到的?
使用者會乖乖地去按【登出】嗎?
直接關瀏覽器或直接把蓋子蓋上不玩的,怎麼記錄 logout 時間?

管他的 反正我只處理表 阿不對 我連表都還沒處理好 /images/emoticon/emoticon46.gif

0
77012904
iT邦新手 3 級 ‧ 2019-12-31 11:35:43

只想求答案就免了.

我這裡只告訴你方法.

用函數從時間欄位中取出「分鐘」的部分,除以30取整數,這樣0~29分都會得到0,30~59分都會得到1,如此便能區分出每半個小時

再把原本時間欄位中的資料,取出 yyyy-mm-dd hh,然後接上前一步驟算出來的商數,就會變成yyyy-mm-dd hh:0 以及 yyyy-mm-dd hh:1,再依此欄位去做count、group by

我要發表回答

立即登入回答