您可以利用 MySQL 用户变量来模拟分析函数。 (还有一些其他方法,例如使用半连接或使用相关子查询。如果您认为它们可能更合适,我也可以为这些方法提供解决方案。)
要模拟“运行总计”分析函数,请尝试以下操作:
SELECT t.user_id
, t.starttime
, t.order_number
, IF(t.order_number IS NOT NULL,
@tot_dur := 0,
@tot_dur := @tot_dur + t.visit_duration_seconds) AS tot_dur
FROM visit t
JOIN (SELECT @tot_dur := 0) d
ORDER BY t.user_id, t.start_time
这里的“技巧”是使用 IF 函数来测试是否order_number
一片空白。当它为空时,我们将持续时间值添加到变量中,否则,我们将变量设置为零。
我们使用内联视图(别名为d
,以确保 @tot_dur 变量初始化为零。
注意:像这样使用 MySQL 用户变量时要小心。在上面的 SELECT 语句中,SELECT 列表中的变量赋值发生在 ORDER BY 之后,因此我们可以获得确定性行为。
该查询不处理 user_id 中的“中断”。为此,我们需要前一行中的 user_id 值。我们可以将其保存在另一个用户变量中。操作的顺序是确定的,在覆盖前一行的 user_id 之前,我们需要注意进行累积。
我们需要对列重新排序,以便 user_id 出现在 tot_dur 之后(或者包含 user_id 列的第二个副本)
SELECT t.user_id
, t.starttime
, t.order_number
, IF(t.order_number IS NULL,
@tot_dur := IF(@prev_user_id = t.user_id,@tot_dur,0) + t.visit_duration_seconds,
@tot_dur := 0
) AS tot_dur
, @prev_user_id := t.user_id AS prev_user_id
FROM visit t
JOIN (SELECT @tot_dur := 0, @prev_user_id := NULL) d
ORDER BY t.user_id, t.start_time
返回的值在user_id
and prev_user_id
列是相同的。可以删除该“额外”列,或者可以通过将查询(作为内联视图)包装在另一个查询中来重新排序列,尽管这会带来性能成本:
SELECT v.user_id
, v.starttime
, v.order_number
, v.tot_dur
FROM (SELECT t.starttime
, t.order_number
, IF(t.order_number IS NULL,
@tot_dur := IF(@prev_user_id = t.user_id,@tot_dur,0) + t.visit_duration_seconds,
@tot_dur := 0
) AS tot_dur
, @prev_user_id := t.user_id AS user_id
FROM visit t
JOIN (SELECT @tot_dur := 0, @prev_user_id := NULL) d
ORDER BY t.user_id, t.start_time
) v
该查询表明 MySQL 可以返回指定的结果集。但为了获得最佳性能,我们希望仅在内联视图中运行查询(别名为v
),并在检索行时在客户端处理列的重新排序(将 user_id 列放在前面)。
其他两种常见的方法是使用半连接和使用相关子查询,尽管这些方法在处理大型集合时可能会消耗更多资源。