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 小时间隔内的事务计数。我将其包装在另一个选择中，该选择对用户的交易计数进行求和，然后除以用户数量，得出每个用户的平均计数。

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

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 

Fiddle demo

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子句。

My Fiddle Demo

这是第二种不使用内部联接的方法:

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

Another Fiddle

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