尝试使用group by计算运行总计，但没有要求和的现有数值列

dhxwm5r4 于 2021-07-29 发布在 Java

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

我想知道是否有人能帮我。我得到了下面的表格结构，我试图得到一个产品总数，按日期和产品分组，也就是说，对于date\u ordered中的每个不同日期，我希望列出每个不同的产品，以及它在该日期之前（包括该日期）出现的次数之和。

+-----------+------------+-------------+-----+
| Reference | Product    | Date_Orderd | ... |
+===========+============+=============+=====+
| x-123123  | Product 1  | 02/02/2020  | ... |
+-----------+------------+-------------+-----+
| x-123124  | Product 2  | 02/02/2020  | ... |
+-----------+------------+-------------+-----+
| x-123125  | Product 3  | 02/02/2020  | ... |
+-----------+------------+-------------+-----+
| ...       | ...        | ...         | ... |
+-----------+------------+-------------+-----+
| x-123241  | Product 2  | 24/03/2020  | ... |
+-----------+------------+-------------+-----+
| x-123242  | Product 1  | 25/03/2020  | ... |
+-----------+------------+-------------+-----+
| ...       | ...        | ...         | ... |
+-----------+------------+-------------+-----+
| x-123620  | Product 10 | 02/05/2020  | ... |
+-----------+------------+-------------+-----+
| x-123621  | Product 7  | 02/05/2020  | ... |
+-----------+------------+-------------+-----+

我遇到的问题是我找到的所有例子（例如。https://codingsight.com/calculating-running-total-with-over-clause-and-partition-by-clause-in-sql-server/ , https://asktom.oracle.com/pls/asktom/f?p=100：11:0:：：p11\问题编号：1793764100346222947， https://medium.com/better-programming/4-ways-to-calculate-a-running-total-with-sql-986d0019185c)似乎假设表中有一列要求和的数值。
有人知道有没有办法得到我需要的数据吗？
提前干杯。

sql window-functions count pivot oracle

来源：https://stackoverflow.com/questions/63125150/oracle-sql-trying-to-calculate-running-total-with-group-by-without-having-an-e

1条答案

按热度按时间

34gzjxbg1#

如果给定产品从未在同一数据上订购过两次，则可以使用窗口计数：

select
    t.*,
    count(*) over(partition by reference order by date_ordered) running_count
from mytable t

如果存在重复项，则需要聚合：

select  
    reference,
    date_ordered,
    sum(count(*)) over(partition by reference order by date_ordered) running_count
from mytable
group by reference, date_ordered

最后：如果要生成日期和产品的所有组合，以及相关的runnig计数，则可以执行以下操作：

select
    r.reference,
    d.date_ordered,
    sum(count(t.reference)) over(partition by r.reference order by d.date_ordered) running_count
from (select distinct date_ordered from mytable) d
cross join (select distinct reference from mytable) r
left join mytable t 
    on t.date_ordered = d.date_ordered and t.reference = r.reference
group by d.date_ordered, r.reference

赞(0）回复(0）举报 2021-07-29

我来回答

尝试使用group by计算运行总计，但没有要求和的现有数值列

1条答案

相关问题

热门标签

最新问答