問題描述
我有以下結構的表格:
UserID StartedOn EndedOn
1 2009-7-12T14:01 2009-7-12T15:01
2 2009-7-12T14:30 2009-7-12T14:45
3 2009-7-12T14:47 2009-7-12T15:30
4 2009-7-12T13:01 2009-7-12T17:01
5 2009-7-12T14:15 2009-7-12T18:01
6 2009-7-12T11:01 2009-7-12T19:01
1 2009-7-12T16:07 2009-7-12T19:01
我需要找到在線的最大并發用戶數.在上表中，結果為 5，因為用戶 set1={1,2,4,5,6} 和 set2={1,3,4,5,6} 在同一時期在線.
I need to find the maximal number of concurrent users that were on line. In the above table the result would be 5 because users set1={1,2,4,5,6} and set2={1,3,4,5,6} were online in the same period.
你知道如何僅使用 T-SQL 來計算這個嗎?
Do you have an idea how one could calculate this using T-SQL only?
推薦答案
顯然并發用戶數只會在用戶開始或結束一個時期時發生變化，因此確定開始和結束期間的并發用戶數就足夠了.所以，重用 Remus 提供的測試數據(謝謝 Remus):
Clearly the number of concurrent users only changes when a user either starts or ends a period, so it is enough to determine the number of concurrent users during starts and ends. So, reusing test data provided by Remus (thank you Remus):
DECLARE @Table TABLE
(
UserId int,
StartedOn datetime,
EndedOn datetime
);
insert into @table (UserId, startedOn, EndedOn)
select 1, '2009-7-12 14:01', '2009-7-12 15:01'
union all select 2, '2009-7-12 14:30', '2009-7-12 14:45'
union all select 3, '2009-7-12 14:47', '2009-7-12 15:30'
union all select 4, '2009-7-12 13:01', '2009-7-12 17:01'
union all select 5, '2009-7-12 14:15', '2009-7-12 18:01'
union all select 6, '2009-7-12 11:01', '2009-7-12 19:01'
union all select 1, '2009-7-12 16:07', '2009-7-12 19:01';
SELECT MAX(ConcurrentUsers) FROM(
SELECT COUNT(*) AS ConcurrentUsers FROM @table AS Sessions
JOIN
(SELECT DISTINCT StartedOn AS ChangeTime FROM @table
) AS ChangeTimes
ON ChangeTime >= StartedOn AND ChangeTime < EndedOn
GROUP BY ChangeTime
) AS ConcurrencyAtChangeTimes
-------
5
順便說一句，使用 DISTINCT 本身并不是一個錯誤——只有濫用 DISTINCT 才是.DISTINCT 只是一個工具，在這種情況下使用它是完全正確的.
BTW using DISTINCT per se is not a mistake - only abusing DISTINCT is. DISTINCT is just a tool, using it in this context is perfectly correct.
我正在回答 OP 的問題:如何僅使用 T-SQL 來計算".請注意，該問題并未提及性能.
I was answering the OP's question: "how one could calculate this using T-SQL only". Note that the question does not mention performance.
如果問題是這樣的:如果數據存儲在 SQL Server 中，確定最大并發的最快方法是什么"，我會提供不同的答案，如下所示:
If the questions was this: "what is the fastest way to determine maximum concurrency if the data is stored in SQL Server", I would provide a different answer, something like this:
考慮以下替代方案
- 寫游標
- 編寫一個 CLR 游標
- 在客戶端寫一個循環
- 使用具有合適游標的 RDBMS，例如 Oracle 或 PostgreSql
- 為了獲得最佳性能，請以不同的方式設計您的表格，以便您可以在一次索引查找中檢索答案.如果我需要提供最佳性能，這就是我在我的系統中所做的.
如果問題是使用 T-SQL 查詢確定最大并發的最快方法是什么"，我可能根本不會回答.原因是:如果我需要非常好的性能，我不會在 T-SQL 查詢中解決這個問題.
If the question was "what is the fastest way to determine maximum concurrency using a T-SQL query", I would probably not answer at all. The reason: if I needed really good performance, I would not solve this problem in a T-SQL query.
這篇關于在一條 SQL 記錄中查找并發用戶數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！