查找最少和最高的月收入

Question

我在组织一个表格时遇到了问题，这个表格显示了收入最高的月份和按收入排序最低的月份

所有信息都在同一个表中(Ordertable)

所以我有了订单日期和订单最终价格。

select orderdate as MonthsSales, Highestrevenue, Lowestrevenue
from 
(select months
, max (orderfinalprice) as Highestrevenue, min (orderfinalprice) as Lowestrevenue
From hologicOrder_T)
order by monthsales;

Answer 1

缺少的是基于orderdate的month分组。

select orderdate as MonthsSales, Highestrevenue, Lowestrevenue 
from 
    (select to_char(orderdate, 'Month') orderdate, max(orderfinalprice) as Highestrevenue
        , min (orderfinalprice) as Lowestrevenue 
    from hologicOrder_T
    group by to_char(orderdate, 'Month')) 
order by monthsales;

Answer 2

据我所知，您需要两个月的数据，一个收入最高，一个收入最低。

您可以通过以下方式获取：

Select max(case when minrn = 1 then month_ end) lowestrevenue_month, 
       max(case when minrn = 1 then totalrevenue end) lowestrevenue,
       max(case when maxrn = 1 then month_ end) highestrevenue_month, 
       max(case when maxrn = 1 then totalrevenue end) highestrevenue
From
(Select trunc(orderdate, 'month') month_,
       Sum(orderfinalprice) as totalrevenue,
       Row_nuumber() over (partition by trunc(orderdate, 'month') order by Sum(orderfinalprice)) as minrn,
       Row_nuumber() over (partition by trunc(orderdate, 'month') order by Sum(orderfinalprice) desc) as maxrn
  From hologicOrder_T
Group by trunc(orderdate, 'month') )
Where 1 in (minrn, maxrn);

您不应该使用to_char(orderdate, 'Month')，因为无论年份如何，该月的值都是相同的。

在输出中，highestrevenue_month和lowestrevenue_month将是本月的第一个日期，您可以使用to_char对其进行相应的格式化。

干杯！！