問(wèn)題描述
我有以下結(jié)構(gòu)的表格:
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
我需要找到在線的最大并發(fā)用戶數(shù).在上表中，結(jié)果為 5，因?yàn)橛脩?set1={1,2,4,5,6} 和 set2={1,3,4,5,6} 在同一時(shí)期在線.
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 來(lái)計(jì)算這個(gè)嗎?
Do you have an idea how one could calculate this using T-SQL only?
推薦答案
顯然并發(fā)用戶數(shù)只會(huì)在用戶開(kāi)始或結(jié)束一個(gè)時(shí)期時(shí)發(fā)生變化，因此確定開(kāi)始和結(jié)束期間的并發(fā)用戶數(shù)就足夠了.所以，重用 Remus 提供的測(cè)試數(shù)據(jù)(謝謝 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
順便說(shuō)一句，使用 DISTINCT 本身并不是一個(gè)錯(cuò)誤——只有濫用 DISTINCT 才是.DISTINCT 只是一個(gè)工具，在這種情況下使用它是完全正確的.
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 的問(wèn)題:如何僅使用 T-SQL 來(lái)計(jì)算".請(qǐng)注意，該問(wèn)題并未提及性能.
I was answering the OP's question: "how one could calculate this using T-SQL only". Note that the question does not mention performance.
如果問(wèn)題是這樣的:如果數(shù)據(jù)存儲(chǔ)在 SQL Server 中，確定最大并發(fā)的最快方法是什么"，我會(huì)提供不同的答案，如下所示:
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:
考慮以下替代方案
- 寫(xiě)游標(biāo)
- 編寫(xiě)一個(gè) CLR 游標(biāo)
- 在客戶端寫(xiě)一個(gè)循環(huán)
- 使用具有合適游標(biāo)的 RDBMS，例如 Oracle 或 PostgreSql
- 為了獲得最佳性能，請(qǐng)以不同的方式設(shè)計(jì)您的表格，以便您可以在一次索引查找中檢索答案.如果我需要提供最佳性能，這就是我在我的系統(tǒng)中所做的.
如果問(wèn)題是使用 T-SQL 查詢確定最大并發(fā)的最快方法是什么"，我可能根本不會(huì)回答.原因是:如果我需要非常好的性能，我不會(huì)在 T-SQL 查詢中解決這個(gè)問(wèn)題.
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.
這篇關(guān)于在一條 SQL 記錄中查找并發(fā)用戶數(shù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！