sql - 大型数据集的排序算法

Question

我不知道术语排序是否最合适，但无论如何我有一个大型数据集，其中包含列 userA、userB、日期、interactionDuration。换句话说，数据集包含描述哪些用户正在交互、交互持续了多长时间以及交互日期的行。

目标是根据以下逻辑将这些交互分为相关和不相关或重要和不重要:如果交互时间长，我们认为它很重要，如果交互时间短，则它是偶然的和“无关的”。另一方面，如果同样的两个人经常互动(每天或每周)，并且如果单次互动持续时间不长，我们会认为这很重要，因为这不是偶然的。

数据存储在 MS SQL Server 2008 中，我想知道是否有可能通过使用 SQL 查询以某种方式计算不同人对之间的交互间隔(以天为单位)，或者是否有必要编写自定义例程。

无论如何，如果能听到有关如何执行排序的任何建议，即根据交互次数、交互持续时间和交互之间的间隔将对分组，我将非常感激 - 是否可以计算两个连续记录之间的时间跨度(按日期排序)，然后通过仅使用 SQL 查询来选择经常交互和/或长时间交互的对组，或者是否需要编写自定义算法？

编辑1

这些是示例行:

user_A                                      user_B                                      interaction_duration    interaction_date_date
00002781A2ADA816CDB0D138146BD63323CCDAB2    0E3F0A58C6BD5202D1B0D4D4F6A0B05E7A156AC7    532                     2010-09-04
00002781A2ADA816CDB0D138146BD63323CCDAB2    47DFCA60C7D2D9B32ACBE650BCBA53F3DA57CB7B    29                      2010-09-15
00002781A2ADA816CDB0D138146BD63323CCDAB2    8F4ADF3CC26905AF69F930C546954D87DB536E24    83                      2010-09-04
00002781A2ADA816CDB0D138146BD63323CCDAB2    8F4ADF3CC26905AF69F930C546954D87DB536E24    10                      2010-09-01
00002D2354C7080C0868CB0E18C46157CA9F0FD4    372BD190F61BAACDFA2512B71B0C62BCD654D5D1    50                      2010-09-23
00002D2354C7080C0868CB0E18C46157CA9F0FD4    372BD190F61BAACDFA2512B71B0C62BCD654D5D1    105                     2010-09-18
00002D2354C7080C0868CB0E18C46157CA9F0FD4    372BD190F61BAACDFA2512B71B0C62BCD654D5D1    44                      2010-09-08
00002D2354C7080C0868CB0E18C46157CA9F0FD4    372BD190F61BAACDFA2512B71B0C62BCD654D5D1    50                      2010-09-04
00002D2354C7080C0868CB0E18C46157CA9F0FD4    372BD190F61BAACDFA2512B71B0C62BCD654D5D1    21                      2010-09-02

按交互频率(稀有度)对上述记录进行排序的最佳方法是什么？是否可以使用 sql 查询对其进行排序？

谢谢!

Answer 1

如果我没理解错的话，给定这些数据(前 10 行):

User1   User2   Stamp   Duration (secs)
d   g   09/01/2010  20
b   g   13/01/2010  37
c   k   29/01/2010  15
c   k   12/01/2010  39
e   h   27/01/2010  33
b   i   23/01/2010  46
b   g   04/01/2010  44
b   k   05/01/2010  12
b   k   02/01/2010  9

您想知道哪些用户在给定时间段内经常或长时间互动。我假设您使用的是 MS SQL Server 2005 或更高版本。

3 到 7 天内超过 10 次对话:

WITH Contact(u1, u2, dt) AS (
    SELECT DISTINCT
        user1, user2, stamp
    FROM
        Interactions
)
SELECT
    u1, u2, dt as FirstConversation, MAX(stamp) as LastConversation,  
    COUNT(Duration) AS conversations
FROM
    Interactions INNER JOIN Contact
    ON user1 = u1 AND User2 = u2 AND Stamp >= dt
GROUP BY
    u1, u2, dt
HAVING
    COUNT(duration) > 10 AND 
    DATEDIFF("d", MIN(stamp), MAX(stamp)) BETWEEN 3 and 7
ORDER BY
    1, 2, 3

WITH 语句为每对用户计算他们交互的日期，忽略一天中的多次交互。这是我们在满足我们设置的条件的日期之后查找对话的基线。用“Stamp >= dt”来表示，意思是“显示接触后有交互的接触”。

4 天内超过 2 分钟:

WITH Contact(u1, u2, dt)AS (
    SELECT DISTINCT
        user1, user2, stamp
    FROM
        Interactions
)
SELECT
    u1, u2, dt as FirstConversation, MAX(stamp) as LastConversation,  
    SUM(Duration) AS timespent
FROM
    Interactions INNER JOIN Contact
    ON user1 = u1 AND User2 = u2 AND Stamp >= dt
GROUP BY
    u1, u2, dt
HAVING
    SUM(Duration) > 120 AND 
    DATEDIFF("d", MIN(stamp), MAX(stamp)) BETWEEN 0 and 4
ORDER BY
    1, 2, 3

HTH?