Oracleを使用して過去12か月のカウントとそれらの差異をフェッチする

2020-05-22 sql oracle plsql oracle-sqldeveloper
with sales as
(
select  COUNT(sale) As Number_of_sale, 
TO_CHAR(dates,'YYYY-MON') As Period

  from orders
    where dates between date '2020-03-01' and date '2020-03-31'
group by TO_CHAR(dates,'YYYY-MON') 

union all
select  COUNT(sale) As Number_of_sale,
TO_CHAR(dates,'YYYY-MON') As Period

  from orders
  where dates between date '2020-04-01' and date '2020-04-30'
group by TO_CHAR(dates,'YYYY-MON') 

)
select  Number_of_sale, period,
case when to_char(round((Number_of_sale-lag(Number_of_sale,1, Number_of_sale) over (order by period ))/ Number_of_sale*100,2), 'FM999999990D9999') <0 
then to_char(round(abs( Number_of_sale-lag(Number_of_sale,1, Number_of_sale) over (order by period ))/ Number_of_sale*100,2),'FM999999990D9999')||'%'||'  (Increase) '
when to_char(round((Number_of_sale-lag(Number_of_sale,1,Number_of_sale) over (order by period ))/Number_of_sale*100,2),'FM999999990D9999')>0 
then to_char(round(abs(Number_of_sale-lag(Number_of_sale,1, Number_of_sale) over (order by period ))/Number_of_sale*100,2),'FM999999990D9999')||'%'||'  (Decrease) '
END as variances
        from sales
        order by variances asc;

私が得ている出力

Number_of_sale  |  Period        |Variances
   50           |   2020-Mar         |  100%(increase)
   100          |   2020-Apr         |  Null

出力必要なもの:-過去12か月と、先月との差異が必要です。

Answers

クエリのWITHビットには冗長なユニオン化が少し含まれており、期間の終わりに時刻がある日付を除外する場合があります。代わりにこれを考慮してください:

select  COUNT(sale) As Number_of_sale, 
TO_CHAR(dates,'YYYY-MON') As Period

  from orders
    where dates >= date '2019-03-01' and dates < date '2020-03-01'
group by TO_CHAR(dates,'YYYY-MON') 



連続する日付範囲を結合する必要はなく、より長い日付範囲を使用するだけです。不満は2か月しか残っていないということでしたが、それで十分です。 (3月1日から3月31日、4月1日から4月30日までの組合は2ヶ月です)

まず、日付を日付として扱う方法を学ぶ必要があります。非常にまれなケースでのみ、文字列への変換が必要です(最終的な表示結果を除く)。これはそれらの1つではありません。あなたが持っているすべてのものは日付として働くことができます。 これで、不満は12か月ありますが、クエリでは2か月しか選択されません。最後の12が必要な場合は、12をすべて選択する必要があります。分散が必要だが、統計的な分散は計算されず、月ごとの差が計算されます(1か月が前の月とどれだけ異なるか)。

with parms  as (select trunc(date '&period_end_date','mon') dt from dual) 
   , sales as 
    ( select count(*) cnt 
           , trunc(dates,'mon') period
        from orders 
        cross join parms
       where trunc(dates,'mon') between add_months(dt, -12) 
                                         and last_day(dt)
       group by trunc(dates,'mon')
    ) 
select to_char(period, 'yyyy-Mon')  period
     , cnt                          number_of_sales 
     , to_char (round(abs(cnt - lag(cnt) over (order by period)) / cnt*100,2),'FM999999990D9999') ||
         case when cnt - lag(cnt) over (order by period) < 0 then ' %(Increase)'
              when cnt - lag(cnt) over (order by period) > 0 then ' %(Decrease)'
              else null
         end variances
  from sales  
order by variances asc;

使い方 parms cte(オラクルの「サブクエリファクタリング」)は、基本的に私が怠惰で、パラメーター値を2回以上入力したくないためです。ただし、入力した実際の日付に関係なく、その月の1日が「返される」という利点もあります。 sales cteは、前の12か月(add_months(dt、-12))からの各月の売上数と、パラメータdateの最終日(last_day(dt)をカウントします。これらの各関数は、2月29日と異なる個々の月の日数。日付列を月の最初に切り捨てた後、各月の販売数を取得します。
次にメインクエリは、単純化されたケースで月ごとの差を計算し、増減を決定します(同じままにしますが、逆になる可能性があるか、ラグの代わりにリードが必要かと思います)。

免責事項:サンプルデータではなくテーブル定義を実際に提供しなかったため、テストされていません。

Related