mysql - 获取从订阅到付款的平均时间

Question

我有两张 table 。首先是订户。订户也被指定到一个类别。第二张表是订户支付的款项。我想知道从订阅到订阅者首次付款之间的平均时间(可以多次付款)。

这是一段 SQL，但它还没有完成我想要的 - 虽然我觉得我很接近 ;)

SELECT category,
AVG(TIMESTAMPDIFF(HOUR, subs.timestamp, MIN(payments.timestamp)))
FROM subs
JOIN payments ON (payments.user_id = subs.user_id)
GROUP BY category

现在我得到“组函数的无效使用”- 因为 MIN 函数，所以这是不对的。我现在该怎么办？提前致谢!

Answer 1

SELECT category,
AVG(TIMESTAMPDIFF(HOUR, subs.timestamp, p.timestamp))
FROM subs
JOIN ( SELECT user_id
            , min(timestamp) timestamp
         FROM payments 
       GROUP BY user_id
     ) p
ON p.user_id = subs.user_id
GROUP BY category

如果您需要使用此查询的结果更新另一个表，您可以这样做(未经测试，因此可能存在语法错误，但希望您能理解)。我假设 another_table 有 category 和 avg_hrs_spent 列。

UPDATE another_table
   SET avg_hrs_spent =
     (
       SELECT a.avg_hrs_spent FROM
       (
         (SELECT category,
                 AVG(TIMESTAMPDIFF(HOUR, subs.timestamp, p.timestamp)) avg_hrs_spent
            FROM subs
            JOIN ( SELECT user_id
                        , min(timestamp) timestamp
                     FROM payments 
                   GROUP BY user_id
               ) p
            ON p.user_id = subs.user_id
            GROUP BY category) a
       )
       WHERE a.category = another_table.category
     )