SQL汇总日期范围

发布于 2025-02-13 11:59:22 字数 686 浏览 1 评论 0原文

谁回答这个，非常感谢您！

这是我的数据的一个小片段：

DATE          Score  Multiplier  Weighting
2022-01-05      3       4           7      
2022-01-05      4       7           8      
2022-01-06      5       2           4      
2022-01-06      3       4           7      
2022-01-06      4       7           8    
2022-01-07      5       2           4

这些数据的每一行都是“发生”的事情，并且在同一天发生多个事件。

我需要做的是在过去3个月中以这些数据的滚动平均值。

因此，仅在2022-01-05中，我的加权平均（调整后）是：

DATE                 ADJUSTED
2022-01-05    [(3*4) + (4*7)]/(7+8)

除了我在过去的三个月内需要这样做（所以在2022年1月5日，我需要加权平均值 - 使用“加权”列 - 前3个月也可以使用前90天。

不知道这是否足够清楚，但会很感激任何帮助。

谢谢你！

原文

Whoever answers this thank you so, so much!

Here's a little snippet of my data:

DATE          Score  Multiplier  Weighting
2022-01-05      3       4           7      
2022-01-05      4       7           8      
2022-01-06      5       2           4      
2022-01-06      3       4           7      
2022-01-06      4       7           8    
2022-01-07      5       2           4

Each row of this data is when something "happened" and multiple events occur during the same day.

What I need to do is take the rolling average of this data over the past 3 months.

So for ONLY 2022-01-05, my weighted average (called ADJUSTED) would be:

DATE                 ADJUSTED
2022-01-05    [(3*4) + (4*7)]/(7+8)

Except I need to do this over the previous 3 months (so on Jan 5, 2022, I'd need the rolling weighted average -- using the "Weighting" column -- over the preceding 3 months; can also use previous 90 days if that makes it easier).

Not sure if this is a clear enough description, but would appreciate any help.

Thank you!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只是在用心讲痛 2025-02-20 11:59:22

如果我正确解释了这一点，我相信一个询问的组将满足需求：

示例数据

CREATE TABLE mytable(
   DATE       DATE  NOT NULL
  ,Score      INTEGER  NOT NULL
  ,Multiplier INTEGER  NOT NULL
  ,Weighting  INTEGER  NOT NULL
);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-05',3,4,7);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-05',4,7,8);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-06',5,2,4);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-06',3,4,7);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-06',4,7,8);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-07',5,2,4);

查询

select
      date
    , sum(score)                                                    sum_score
    , sum(multiplier)                                               sum_multiplier
    , sum(weighting)                                                sum_weight
    , (sum(score)*1.0 + sum(multiplier)*1.0) / (sum(weighting)*1.0) ADJUSTED
from mytable
group by date

结果

+------------+-----------+----------------+------------+-------------------+
|    date    | sum_score | sum_multiplier | sum_weight |     ADJUSTED      |
+------------+-----------+----------------+------------+-------------------+
| 2022-01-05 |         7 |             11 |         15 | 1.200000000000000 |
| 2022-01-06 |        12 |             13 |         19 | 1.315789473684210 |
| 2022-01-07 |         5 |              2 |          4 | 1.750000000000000 |
+------------+-----------+----------------+------------+-------------------+

db＆lt;＆gt; fiddle

7c120d161f3d0ff45eb3dde3d9b5e50d零值问题查询Ablove

IF I have interpreted this correctly I believe a GROUP BY query will meet the need:

sample data

CREATE TABLE mytable(
   DATE       DATE  NOT NULL
  ,Score      INTEGER  NOT NULL
  ,Multiplier INTEGER  NOT NULL
  ,Weighting  INTEGER  NOT NULL
);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-05',3,4,7);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-05',4,7,8);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-06',5,2,4);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-06',3,4,7);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-06',4,7,8);
INSERT INTO mytable(DATE,Score,Multiplier,Weighting) VALUES ('2022-01-07',5,2,4);

query

select
      date
    , sum(score)                                                    sum_score
    , sum(multiplier)                                               sum_multiplier
    , sum(weighting)                                                sum_weight
    , (sum(score)*1.0 + sum(multiplier)*1.0) / (sum(weighting)*1.0) ADJUSTED
from mytable
group by date

result

+------------+-----------+----------------+------------+-------------------+
|    date    | sum_score | sum_multiplier | sum_weight |     ADJUSTED      |
+------------+-----------+----------------+------------+-------------------+
| 2022-01-05 |         7 |             11 |         15 | 1.200000000000000 |
| 2022-01-06 |        12 |             13 |         19 | 1.315789473684210 |
| 2022-01-07 |         5 |              2 |          4 | 1.750000000000000 |
+------------+-----------+----------------+------------+-------------------+

db<>fiddle here

Note: I have not attempted to avoid possible divide by 0 or any NULL value problems in the query ablove

回复收藏 0 原文

~没有更多了~