使用过度分区滚动总计时处理重复
我试图在每个日期（从一个月的第一天到日期列值所显示的，在输入表中显示的任何日期，都可以获取滚动金额列的总计。
输出要求
- “团队”列的分区
- 在每个月的1个重新启动滚动总计
问题1
我的下面查询是否正确以获取下面输出表中显示的所需输出要求？它似乎有效，但我必须确认。
SELECT
*,
SUM(amount) OVER (
PARTITION BY
team,
month_id
ORDER BY
date ASC
) rolling_amount_total
FROM input_table;
问题2
如何处理在输入表的前2行中显示的重复日期？每当有重复日期时，金额也是重复的。我在此处看到一个解决方案： https://stackoverflow.com/a/60115015061/6388651 没有运气删除它去删除它得到它的删除重复。我的非工作代码示例如下。
SELECT
*,
SUM(amount) OVER (
PARTITION BY
team,
month_id
ORDER BY
date ASC
) rolling_amount_total
FROM (
SELECT DISTINCT
date,
amount,
team,
month_id
FROM input_table
) t
Input Table
| date | amount | team | month_id |
|---|---|---|---|
| 2022-04-01 | 1 | A | 2022-04 |
| 2022-04-01 | 1 | A | 2022-04 |
| 2022-04-02 | 2 | A | 2022-04 |
| 2022-05-01 | 4 | B | 2022-05 |
| 2022-05- 02 | 4 | B | 2022-05 |
所需的输出表
| 量 | 量 | team team | team_id | rolling_amount_total |
|---|---|---|---|---|
| 2022-04-01 | 1 | A | 2022-04 | 1 |
| 2022-04-04-02 | 2 | A | 2022-04 | 3 |
| 2022-04 3 2022-05-01 | 4 | B | 2022-2022-2022-05 | 4 2022-05 4 |
| 2022-05 5 -02 | 4 | B | 2022-05 | 8 |
I'm trying to get the rolling amount column totals for each date, from the 1st day of the month to whatever the date column value is, shown in the input table.
Output Requirements
- Partition by the 'team' column
- Restart rolling totals on the 1st of each month
Question 1
Is my below query correct to get my desired output requirements shown in Output Table below? It seems to work but I must confirm.
SELECT
*,
SUM(amount) OVER (
PARTITION BY
team,
month_id
ORDER BY
date ASC
) rolling_amount_total
FROM input_table;
Question 2
How can I handle duplicate dates, shown in the first 2 rows of Input Table? Whenever there is a duplicate date the amount is a duplicate as well. I see a solution here: https://stackoverflow.com/a/60115061/6388651 but no luck getting it to remove the duplicates. My non-working code example is below.
SELECT
*,
SUM(amount) OVER (
PARTITION BY
team,
month_id
ORDER BY
date ASC
) rolling_amount_total
FROM (
SELECT DISTINCT
date,
amount,
team,
month_id
FROM input_table
) t
Input Table
| date | amount | team | month_id |
|---|---|---|---|
| 2022-04-01 | 1 | A | 2022-04 |
| 2022-04-01 | 1 | A | 2022-04 |
| 2022-04-02 | 2 | A | 2022-04 |
| 2022-05-01 | 4 | B | 2022-05 |
| 2022-05-02 | 4 | B | 2022-05 |
Desired Output Table
| date | amount | team | month_id | Rolling_Amount_Total |
|---|---|---|---|---|
| 2022-04-01 | 1 | A | 2022-04 | 1 |
| 2022-04-02 | 2 | A | 2022-04 | 3 |
| 2022-05-01 | 4 | B | 2022-05 | 4 |
| 2022-05-02 | 4 | B | 2022-05 | 8 |
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Q1。您的sum（）aver（）是正确的
Q2。在您的第一个查询中，从input_table 中替换
，使用：Q1. Your sum() over () is correct
Q2. Replace
from input_table, in your first query, with :