一些 SQL 片段

WITH GroupStats AS (
    SELECT 
        groupId,
        COALESCE(groupName, '') as groupName,
        COUNT(*) as total_messages,
        (MAX(timeStamp) - MIN(timeStamp))/1000.0 as time_span_seconds,
        MIN(timeStamp)/1000 as first_message,
        MAX(timeStamp)/1000 as last_message
    FROM Messages 
    GROUP BY groupId
)
SELECT 
    groupId,
    groupName,
    total_messages,
    ROUND(time_span_seconds/86400.0, 1) as days_span,
    ROUND(CAST(total_messages AS FLOAT) * 86400 / time_span_seconds, 2) as messages_per_day,
    datetime(first_message, 'unixepoch') as first_message_time,
    datetime(last_message, 'unixepoch') as last_message_time
FROM GroupStats
WHERE time_span_seconds > 0
ORDER BY messages_per_day DESC;

分析群聊频率