oracle SQL收入分析[已关闭]

ryoqjall 于 2023-04-29 发布在 Oracle

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

已关闭，此问题需要details or clarity。目前不接受答复。
**想改善这个问题吗？**通过editing this post添加详细信息并澄清问题。

8天前关闭
Improve this question
我希望将收入分解为（x）个产品的贡献

select
      pro_id,
      to_char(so_date,'YYYY') as year,
      sum(sod_qty*sod_price_unit) over(partition by to_char(so_date,'YYYY'))
    from all_sales;

下面是我得到的输出，它将显示每个特定年份的总销售额：
PRO_ID|年份|总销量S0010|二零二三年|33962.8 S0011|二零二三年|33962.8 S0002|二零二三年|33962.8

SELECT
pro_id, pro_name,
to_char(so_date, 'YYYY') As year,
SUM(SOD_QTY*SOD_PRICE_UNIT) AS total_sales
FROM all_sales
GROUP BY pro_id, pro_name, so_date
Order by pro_id, pro_name;

下面的输出显示了每种产品每年的销售额：

我不知道如何用百分比来计算

oracle

来源：https://stackoverflow.com/questions/76065080/sql-revenue-analysis

1条答案

按热度按时间

pepwfjgg1#

按年份（而不是基础日期）分组，然后使用分析函数计算该年的总销售额，并使用其计算百分比：

SELECT pro_id,
       pro_name,
       EXTRACT(YEAR FROM so_date) As year,
       SUM(SOD_QTY*SOD_PRICE_UNIT) AS total_sales,
       SUM(SOD_QTY*SOD_PRICE_UNIT)
       / SUM(SUM(SOD_QTY*SOD_PRICE_UNIT))
           OVER (PARTITION BY EXTRACT(YEAR FROM so_date))
       * 100 AS percentage_sales
FROM   all_sales
GROUP BY
       pro_id,
       pro_name,
       EXTRACT(YEAR FROM so_date)
ORDER BY
       pro_id,
       pro_name;

赞(0）回复(0）举报 2023-04-29

我来回答

oracle SQL收入分析[已关闭]

1条答案

相关问题

热门标签

最新问答