mysql - 以可读的方式计算每个用户的平均事务数

Question

我一直在为这些类型的查询而苦苦挣扎。所以，我希望有人检查我处理这些问题的方法。我被要求找出从第一笔交易开始的 12 小时内，每个用户平均执行了多少笔交易。

这是数据：

CREATE TABLE IF NOT EXISTS `transactions` (
`transactions_ts` timestamp ,
`user_id` int(6) unsigned NOT NULL,
`transaction_id` bigint NOT NULL, 
`item` varchar(200), PRIMARY KEY(`transaction_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `transactions` (`transactions_ts`, `user_id`, `transaction_id`,`item` ) VALUES
  ('2016-06-18 13:46:51.0', 13811335,1322361417, 'glove'),
  ('2016-06-18 17:29:25.0', 13811335,3729362318, 'hat'), 
  ('2016-06-18 23::07:12.0', 13811335,1322363995,'vase' ),
  ('2016-06-19 07:14:56.0',13811335,7482365143, 'cup'),
  ('2016-06-19 21:59:40.0',13811335,1322369619,'mirror' ),
  ('2016-06-17 12:39:46.0',3378024101,9322351612, 'dress'),
  ('2016-06-17 20:22:17.0',3378024101,9322353031,'vase' ),
  ('2016-06-20 11:29:02.0',3378024101,6928364072,'tie'),
  ('2016-06-20 18:59:48.0',13811335,1322375547, 'mirror');

我的方法如下（步骤和查询本身如下）：

1) 对于每个不同的 user_id，找到他们的第一个和 12 小时的交易时间戳。这是通过别名为 t1 的内部查询完成的

2）然后，通过内部连接到第二个内部查询（t2），基本上，我用第一步的两个变量“first_trans”和“right_trans”来扩充事务表的每一行。3) 现在，通过 where 条件，我只选择那些落在 first_trans 和 right_trans 时间戳指定的时间间隔内的事务时间戳

4) 步骤 3 中过滤的表现在聚合为每个用户的不同事务 id 计数

5) 上述 4 个步骤的结果是一个表格，其中每个用户都有一个事务计数，该计数从第一个时间戳开始落在 12 小时的间隔内。我将它包装在另一个选择中，该选择将用户的事务计数相加并除以用户数，得出每个用户的平均计数。

我很确定最终结果总体上是正确的，但我一直在想我可能会没有第 4 次选择。或者，也许，整个代码有点笨拙，而我的目标是使这个查询尽可能可读，而不一定是计算最优的。

select 

sum(dist_ts)/count(*) as avg_ts_per_user

from (

select 
count(distinct transaction_id) as dist_ts, 
us_id 
from 

(select 
user_id as us_id, 
min(transactions_ts) as first_trans, 
min(transactions_ts) + interval 12 hour as right_trans 

from transactions 
group by us_id ) 

as t1 

inner join 

(select * from transactions ) 
as t2 

on t1.us_id=t2.user_id

where transactions_ts >= first_trans
and transactions_ts <  right_trans
group by us_id

) as t3 

小提琴演示

标签： mysql

Answer 1

我认为本身没有错误。代码可以稍微简化（并整理如下）：

select sum(dist_ts)/count(*) as avg_ts_per_user
from (
    select count(distinct transaction_id) as dist_ts, us_id
    from (
        select user_id as us_id, min(transactions_ts) as first_trans, min(transactions_ts) + interval 12 hour as right_trans
        from transactions
        group by us_id
        ) as t1
    inner join transactions as t2
    on t1.us_id=t2.user_id and transactions_ts >= first_trans and transactions_ts <  right_trans
    group by us_id
) as t3

上面被(select * from transactions ) as t2简化了，我有点随意地将 where 子句条件移到了内部连接的 on 子句中。

我的小提琴演示

这是不使用内部连接的第二种方法：

select sum(cnt)/count(*) as avg_ts_per_user from (
    select count(*) as cnt, t.user_id
    from transactions t
    where t.transactions_ts >= (select min(transactions_ts) from transactions where user_id = t.user_id)
         and t.transactions_ts < (select min(transactions_ts) + interval 12 hour from transactions where user_id = t.user_id)
    group by t.user_id
) sq

另一个小提琴

您可能应该对这两个查询运行 EXPLAIN 以查看哪一个在您的服务器上运行得更好。另请注意，min(transaction_ts)为每个用户指定了两次。MySql 是否能够避免冗余计算？我不知道。一种可能性是创建一个由 和 组成的临时表user_id，min_transaction_ts以便计算一次值。这只有在你的表有很多行时才有意义，甚至可能没有。