mysql 在SQL中按ID聚合两个时间戳之间的事务

cgvd09ve 于 2023-04-19 发布在 Mysql

关注(0)|答案(1)|浏览(133)

我尝试在sql表中计算id在不同时间窗口内的速度聚合。我有一个表：

TABLE transactions (
  transaction_id int,
  customer_id int,
  date Timestamp,
  should_aggregate boolean,
)

并且我正在尝试创建一个在交易时具有velocity特征的表

TABLE transaction_velocity (
  transaction_id int,
  customer_id int,
  date Timestamp,
  previous_1hour int,
  previous_1day  int,
  previous_1week int
)

例如，如果我们有：
| 事务ID|客户ID|日期|肩集料|
| --------------|--------------|--------------|--------------|
| 1|1|2023-01-10 01：00：00 +0000|真|
| 二|1|2019 -01-10 00：55：00 +0000|假的|
| 三|1|2019 -01- 19 00：00：00 00：00|真|
| 四|1|2019 -01-07 00：57：00 +0000|假的|
| 五|二|2019 -01-10 00：57：00 +0000|真|
生成的表格将是：
| 事务ID|客户ID|日期|前一个小时|前一天|前一周|
| --------------|--------------|--------------|--------------|--------------|--------------|
| 1|1|2023-01-10 01：00：00 +0000|1|1|三|
| 三|1|2019 -01- 19 00：00：00 00：00|0|0|1|
| 五|二|2019 -01-10 00：57：00 +0000|0|0|0|
本质上，如果标记为聚合，则每一行都是在3个时间窗口内对每个客户的过去交易进行聚合。我考虑过使用分区窗口，但不知道如何在时间戳上做到这一点。

mysql

来源：https://stackoverflow.com/questions/76037560/aggregations-of-transactions-between-two-timestamps-by-an-id-in-sql

1条答案

按热度按时间

rn0zuynd1#

正如你所指出的，你可以用一些窗口函数来做到这一点，但是我想我可能只推荐一个self join：

SELECT
 t1.transaction_id
 , t1.customer_id
 , t1.date
 , SUM(CASE WHEN t2.date > DATE_SUB(t1.date, INTERVAL 1 HOUR) THEN 1 ELSE 0 END) previous_1hour
 , SUM(CASE WHEN t2.date > DATE_SUB(t1.date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) previous_1day
 , SUM(CASE WHEN t2.date > DATE_SUB(t1.date, INTERVAL 1 WEEK) THEN 1 ELSE 0 END) previous_1week
FROM transactions t1 LEFT OUTER JOIN transactions t2
  ON t1.customer_id = t2.customer_id AND t2.date < t1.date
WHERE t1.should_aggregate
GROUP BY t1.transaction_id, t1.customer_id, t1.date

这只是简单地将具有相同customer_id和较小date的记录连接起来，如果date足够大，则select中的sum s将它们计数在区间内。
如果有人有一个简单的方法可以用窗口函数来做这件事，当然，使用它，但我认为上面的方法已经足够好了，很容易理解。（我只是想不出一个很好的方法来用窗口函数做这件事，我不建议反对。）
另一种可能是select中的相关子查询：

SELECT
  transaction_id
  , customer_id
  , date
  , (SELECT count(*) FROM transactions t2 WHERE t2.customer_id = t1.customer_id AND t2.date > DATE_SUB(t1.date, INTERVAL 1 HOUR) AND t2.date < t1.date) previous_1hour
  , (SELECT count(*) FROM transactions t2 WHERE t2.customer_id = t1.customer_id AND t2.date > DATE_SUB(t1.date, INTERVAL 1 DAY) AND t2.date < t1.date) previous_1day
  , (SELECT count(*) FROM transactions t2 WHERE t2.customer_id = t1.customer_id AND t2.date > DATE_SUB(t1.date, INTERVAL 1 WEEK) AND t2.date < t1.date) previous_1week
FROM transactions t1
WHERE should_aggregate

您可以看到这两个选项都生成了预期的输出in this Fiddle。

赞(0）回复(0）举报 2023-04-19

我来回答

mysql 在SQL中按ID聚合两个时间戳之间的事务

1条答案

相关问题

热门标签

最新问答