表中特定行的每月平均数量SQL

Question

我有下表：

        MONTHNUMBER | MonthName | ProductName | QTY | AMOUNT | AVG qty/mos|
      |-------------|-----------|-------------|-----|--------|------------|
      | 1           | January   | Thingamabob | 11  | 100.00 |            |
      | 1           | January   | Widgets     | 18  | 150.00 |            |
      | 2           | February  | Thingamabob | 10  | 100.00 |            |
      | 2           | February  | Widgets     | 6   | 150.00 |            |
      | 3           | March     | Thingamabob | 8   | 100.00 |            |
      | 3           | March     | Widgets     | 20  | 150.00 |            |
      | 4           | April     | Thingamabob | 13  | 100.00 |            |
      | 4           | April     | Widgets     | 21  | 150.00 |            |
      | 5           | May       | Thingamabob | 10  | 100.00 |            |
      | 5           | May       | Widgets     | 17  | 150.00 |            |

是否可以获得每行MonthName的平均数量并将其添加到平均数量/mos列中？

我想要的表是这样的：

        MONTHNUMBER | MonthName | ProductName | QTY | AMOUNT | AVG qty/mos|
      |-------------|-----------|-------------|-----|--------|------------|
      | 1           | January   | Thingamabob | 11  | 100.00 |    14.5    |
      | 1           | January   | Widgets     | 18  | 150.00 |    14.5    |
      | 2           | February  | Thingamabob | 10  | 100.00 |      8     |
      | 2           | February  | Widgets     | 6   | 150.00 |      8     |
      | 3           | March     | Thingamabob | 8   | 100.00 |     14     |
      | 3           | March     | Widgets     | 20  | 150.00 |     14     |
      | 4           | April     | Thingamabob | 13  | 100.00 |    16.5    |
      | 4           | April     | Widgets     | 21  | 150.00 |    16.5    |
      | 5           | May       | Thingamabob | 10  | 100.00 |    13.5    |
      | 5           | May       | Widgets     | 17  | 150.00 |    13.5    |

我已经尝试了下面的脚本，我将得到与数量相同的结果。

SUM(QTY)/COUNT(DISTINCT(MonthName))

我希望使用SQL可以做到这一点。

Answer 1

您也可以在分区中使用monthname

    select a.*,avg(QTY) over(partition by MonthName) as avgqty
    from tab a

但我认为使用month and year会更好，两者都应该在partition by中，因为将来可能还需要您的数据

Answer 2

您可以使用avg窗口函数来完成此操作。

select t.*,avg(qty) over(partition by monthnumber) as avg_qty_per_mnth
from tbl t