时间戳

首页 > 解决方案 > mysql中的尾随n天累积总和

mysql - mysql中的尾随n天累积总和

问题描述

我有这个结构：

+--------------------------------------+---------------------+--------+-------------+-------------+
| id                                   | start               | User   | billedHours | trail90dsum |
+--------------------------------------+---------------------+--------+-------------+-------------+
| 2e88f9f9-2543-11eb-9d57-02b150913215 | 2018-10-04 11:00:00 | User 1 |       15.30 |        NULL |
| 2e89af0a-2543-11eb-9d57-02b150913215 | 2018-10-09 12:00:00 | User 1 |        0.40 |        NULL |
| 2e8a400b-2543-11eb-9d57-02b150913215 | 2018-10-09 17:00:00 | User 2 |        0.60 |        NULL |
| 2e8ae87d-2543-11eb-9d57-02b150913215 | 2018-10-25 17:30:00 | User 2 |        0.30 |        NULL |
| 2e8ba472-2543-11eb-9d57-02b150913215 | 2018-10-27 15:00:00 | User 3 |        1.20 |        NULL |
| 2e975c93-2543-11eb-9d57-02b150913215 | 2018-10-29 17:30:00 | User 3 |        0.30 |        NULL |
| 2e980477-2543-11eb-9d57-02b150913215 | 2018-11-02 13:30:00 | User 1 |        1.90 |        NULL |
| 2e98a874-2543-11eb-9d57-02b150913215 | 2018-11-03 12:00:00 | User 2 |        0.70 |        NULL |
| 2e993a7d-2543-11eb-9d57-02b150913215 | 2018-11-04 13:30:00 | User 3 |        1.30 |        NULL |
| 2ea9fa03-2543-11eb-9d57-02b150913215 | 2018-11-11 11:00:00 | User 1 |        0.90 |        NULL |
+--------------------------------------+---------------------+--------+-------------+-------------+

我正在寻找一个 UPDATE 查询，它将在过去的 90 天内（从每个开始日期开始）为每个用户填充 trail90dsum 字段。

我能够构造一个带有用于报告数据的连接的 SELECT 查询，但我无法将其转换为在 UPDATE 语法中工作。

谢谢，菲利普

标签： mysqlsum

解决方案

感谢所有的帮助。这个查询完成了工作：

UPDATE mytable dst JOIN 
  (SELECT t1.user, t1.start, t1.billedHours, SUM(t2.billedHours) as last90
   FROM mytable as t1 JOIN mytable as t2 ON DATEDIFF(t1.start, t2.start) BETWEEN 0        
   AND 90 WHERE t1.user=t2.user
   GROUP BY t1.user, t1.start, t1.billedHours) src 
SET dst.trail90dsum=src.last90 
WHERE src.start=dst.start and src.user=dst.user

谢谢，-P

推荐阅读