代码之家 › 专栏 › 技术社区 › Ian

从不同用户会话列表中选择最早的日期和时间

greatest-n-per-group mysql sql

Ian · 技术社区 · 16 年前

我有一个记录网站访问者活动的用户访问会话表:

accessid, userid, date, time, url

我试图检索userid1234的所有不同会话,以及每个不同会话的最早日期和时间。

SELECT 
    DISTINCT accessid, 
    date, 
    time 
FROM 
    accesslog 
WHERE userid = '1234' 
GROUP BY accessid

这将为我提供每个不同accessid中随机行的日期和时间。我读过很多推荐使用min()和max()的帖子,所以我试着:

SELECT DISTINCT accessid, MIN(DATE) AS date, MIN(TIME) AS time FROM accesslog WHERE userid = '1234' GROUP BY accessid ORDER BY date DESC, time DESC

... 甚至。。。

SELECT DISTINCT accessid, MIN(CONCAT(DATE, ' ', TIME)) AS datetime FROM accesslog WHERE userid = '1234' GROUP BY accessid ORDER BY date DESC, time DESC

排序这种查询的诀窍是什么?

编辑-

奇怪的事情正在发生。。。。

查询似乎没有跨越个月!

示例数据集:

accessid    | userid    | date          | time
1           | 1234      | 2009-08-15    | 01:01:01
1           | 1234      | 2009-09-01    | 12:01:01
1           | 1234      | 2009-09-15    | 13:01:01
2           | 1234      | 2009-09-01    | 14:01:01
2           | 1234      | 2009-09-15    | 15:01:01

至少在我的实际数据表中,下面发布的查询可以找到以下内容 最早的 两个accessid各自的日期和时间:

accessid    | userid    | date          | time
1           | 1234      | 2009-09-01    | 12:01:01
2           | 1234      | 2009-09-01    | 14:01:01

... 我猜Accessid2的结果看起来正确的唯一原因是因为它在前一个月没有点击。

编辑2-

答案是肯定的,我快疯了。当将查询放在重复结构的表中时,它将处理上述示例数据。

这是(被截断的)原始数据。我包括了第一支热门歌曲,同一个月的另一支热门歌曲,下个月的第一支热门歌曲,然后是本月的最后一支热门歌曲。原始数据集在这些点之间有更多的点击,总共462行。

accessid                            | date          | time
cbb82c08d3103e721a1cf0c3f765a842    | 2009-08-18    | 04:01:42
cbb82c08d3103e721a1cf0c3f765a842    | 2009-08-23    | 23:18:52
cbb82c08d3103e721a1cf0c3f765a842    | 2009-09-17    | 05:12:16
cbb82c08d3103e721a1cf0c3f765a842    | 2009-09-18    | 06:29:59

... 查询返回2009-09-17值作为查询原始表时的最早值。但是,当我复制。。。。。。。。哦,球。

userid 领域

2 回复 | 直到 16 年前

Bill Karwin 16 年前

这是“每个组最大n个”问题的一种变体,该问题每周在StackOverflow上出现几次。

SELECT 
        a1.accessid, 
        a1.date, 
        a1.time 
FROM 
        accesslog a1
LEFT OUTER JOIN
        accesslog a2
  ON (a1.accessid = a2.accessid AND a1.userid = a2.userid
    AND (a1.date > a2.date OR a1.date = a2.date AND a1.time > a2.time))
WHERE a1.userid = '1234'
  AND a2.accessid IS NULL;

其工作方式是,我们试图找到一行(a2),该行具有相同的accessid和userid,并且日期或时间早于a1行。当我们找不到前面的行时,a1必须最早的一排。

关于你的评论,我只是用你提供的样本数据进行了尝试。以下是我得到的:

+----------+------------+----------+
| accessid | date       | time     |
+----------+------------+----------+
|        1 | 2009-08-15 | 01:01:01 | 
|        2 | 2009-09-01 | 14:01:01 | 
+----------+------------+----------+

我在MacOSX上使用的是MySQL 5.0.75。

martin.malek 16 年前

试试这个

SELECT 
    accessid, 
    date, 
    time 
FROM 
    accesslog 
WHERE userid = '1234' 
GROUP BY accessid
HAVING MIN(date)

对于userid='1234',它将返回所有唯一的访问,每个访问的最短时间为。