75209


下記で定義されるようなテーブルに、各年度(year)の収益(profit)が保持されています。  
year は、ソートすると連続しているとします。(例: 2018, 2019, 2020; 反例: 2017, 2019, 2020)

[code]

    CREATE TABLE profits
    (
        year integer NOT NULL,
        profit integer NOT NULL,
        CONSTRAINT profits_pkey PRIMARY KEY (year)
    );
    
[/code]

このようなテーブルがあったとき、以下のデータを select したいです。

  * 元の profit がすべて 0 以上ならば、`year, profit` がそのまま select
  * profit が負の値である year については、その年の profit は 0 として select
  * profit が負の値であった year(year0とする) は、その年以降の正の profit を持つものの中で、year0 から近い順に profit を相殺していく。相殺された year においては、完全に相殺されれば profit は 0 になり、相殺されきらなければその残りが profit となる。ただし、相殺は「元の year + 3」までしか起こらないとする。
  * 一度相殺された profit を別の年に重複して相殺することはできない。

# 例1

[code]

    # 元テーブル year: profit
    2017: -20
    2018: 10
    2019: 30
    
    ==>
    # 欲しいクエリ結果
    2017: 0
    2018: 0
    2019: 20
    
[/code]

# 例2

[code]

    # 元テーブル year: profit
    2016: -40
    2017: -20
    2018: -10
    2019: -30
    2020: 90
    
    ==>
    # 欲しいクエリ結果
    2016: 0
    2017: 0
    2018: 0
    2019: 0
    2020: 30
    
[/code]

# 例3

[code]

    # 元テーブル year: profit
    2016: -10
    2017: -10
    2018: 10
    2019: -20
    2020: 40
    
    ==>
    # 欲しいクエリ結果
    2016: 0
    2017: 0
    2018: 0
    2019: 0
    2020: 10
    
[/code]

# 質問

  * これはどのようなクエリを書くと実現できますか？
  * (できれば) 計算量的に最適化するには？
  * (できれば) 一般 N 年間の通算に対しては？

# 背景

いわゆる、３年間の損失の繰越控除的な計算を、ふと、sql
に落とそうと思った時に、どうやって実現するのか、何が計算量的に最適かが、自明ではないので、質問しています。

# 免責

数値計算の問題として定式化するためにいろいろ簡略化しており、実際の税務としてどうこう、という意図は一切ございません。

# 追記@2021/04/11

重複相殺をできないようにするケースを追加しました (相殺の定義が不明瞭でした)


CC BY-SA 4.0
2020-03-31T13:44:44.507
65258
2021-04-16T02:37:34.850
2021-04-12T04:46:53.747
3060
754
question
sql postgresql
３年間の、損失の繰越控除を postgresql にてクエリで実現するには？


各行に過去3年分のprofitを LEFT JOIN で追加してあげれば計算できますね

[code]

        SELECT
          T1.year,
          GREATEST(T1.profit + T1.profit_1 + T1.profit_2 + T1.profit_3, 0)
        FROM (
          SELECT
            T1.year,
            T1.profit,
            COALESCE(T2.profit, 0) AS profit_1,
            COALESCE(T3.profit, 0) AS profit_2,
            COALESCE(T4.profit, 0) AS profit_3
          FROM
            profits T1
          LEFT JOIN
            profits T2 ON (T1.year -1 = T2.year)
          LEFT JOIN
            profits T3 ON (T1.year -2 = T3.year)
          LEFT JOIN
            profits T4 ON (T1.year -3 = T4.year)
        ) T1;
    
[/code]

（ちょっと仕様がよくわからなかったので profit の計算は正しくないです）  
（値さえ取得できてしまえば、あとは分岐するだけなので大丈夫でしょう）

結合を3回行いますが、必ず1行にしかマッチしないのでprofitテーブルの行数を越えることはなく、計算量的には大したことないと思います

この方法では一般 N 年間の通算はできないので、再帰共通テーブル式で実現してみます

[code]

        WITH RECURSIVE accumulated(current_year, year, profit) AS (
          SELECT
            year,
            year,
            profit
          FROM
            profits
    
          UNION ALL
    
          SELECT
            T1.year,
            accumulated.year,
            T1.profit + accumulated.profit
          FROM
            profits T1,
            accumulated
          WHERE
            T1.year = accumulated.current_year + 1
          AND
            T1.year <= accumulated.year + 3
        )
        SELECT
          current_year AS year,
          GREATEST(profit, 0) AS profit
        FROM (
          SELECT
            current_year,
            year,
            profit,
            rank() OVER (PARTITION BY current_year ORDER BY year) AS rank
          FROM
            accumulated
        ) T1
        WHERE
          rank = 1
        ;
    
[/code]

（相変わらず「相殺」の意味するところが分からないので、計算は適当です）  
N をパラメータ化できているので、自由に変更できます  
この方式では N * (profitテーブルの行数) の大きさを持つ中間テーブルが発生します  
処理が手続き式に記述されているので、条件の記述も簡単にできます  
計算量についても、単純な N 回のループ計算ですから大して問題にはならないんじゃないでしょうか


CC BY-SA 4.0
2020-04-01T02:08:40.613
65269
2020-04-01T02:08:40.613
9796
65258
answer


このようにすると、自分が欲しかった値が計算できることを確認しました。計算量は、`O(Y*N*logN)` (Y: 年の行数, N: 繰越年数)
で求まっているはずです。(`logN`はソートが間に入るため)

[code]

    WITH RECURSIVE
    case1 (year, profit) as (
       values (2017, -20::bigint),
              (2018, 10::bigint),
              (2019, 30::bigint)
    ),
    case2 (year, profit) as (
       values (2016, -40::bigint),
              (2017, -20::bigint),
              (2018, -10::bigint),
              (2019, -30::bigint),
              (2020, 90::bigint)
    ),
    case3 (year, profit) as (
       values (2016, -10::bigint),
              (2017, -10::bigint),
              (2018, 10::bigint),
              (2019, -20::bigint),
              (2020, 40::bigint)
    ),
    profits as (
       select * from case3 -- 試したい case を切り替える
    ),
    acc as (
       select
         "year",
         greatest(profit, 0) as profit,
         case when profit >= 0 then '[]'::jsonb
              else json_build_array(json_build_object('year', year, 'value', profit))::jsonb
         end as residue
       from profits
       where year = (select min(year) from profits)
    
       union all
    
       select
         profits.year,
         calc.profit,
         calc.next_residue
       from acc
       inner join profits on acc.year + 1 = profits.year
       cross join lateral ( select greatest(profits.profit, 0) as gain, least(profits.profit, 0) as loss ) as t
       cross join lateral (
           with offset_calc1 as (
                select prev_residue.year, prev_residue.value as residue_before, least(gain + sum(prev_residue.value) over (order by year), 0)::bigint as acc_residue_after
                from jsonb_to_recordset(acc.residue) as prev_residue(year integer, value bigint)
                where profits.year - 3 <= prev_residue.year
           ),
           offset_calc2 as (
               select year, residue_before, acc_residue_after - lag(acc_residue_after, 1, 0::bigint) over (order by year) as residue_after
               from offset_calc1
           ),
           next_residue as (
               select year, residue_after as "value"
               from offset_calc2
    
               union all
    
               select profits.year, loss as "value"
           )
           select
             coalesce((select json_agg(next_residue)::jsonb from next_residue where "value" < 0), '[]'::jsonb) as next_residue, 
             (gain - (select sum(residue_after - residue_before) from offset_calc2))::bigint as profit
       ) as calc
    )
    
    select * from acc;
    
[/code]

_実行結果_

[code]

     year | profit |                           residue                            
    ------+--------+--------------------------------------------------------------
     2016 |      0 | [{"year": 2016, "value": -10}]
     2017 |      0 | [{"year": 2016, "value": -10}, {"year": 2017, "value": -10}]
     2018 |      0 | [{"year": 2017, "value": -10}]
     2019 |      0 | [{"year": 2017, "value": -10}, {"year": 2019, "value": -20}]
     2020 |     10 | []
    (5 rows)
    
[/code]


CC BY-SA 4.0
2021-04-11T07:14:40.037
75209
2021-04-16T02:37:34.850
2021-04-16T02:37:34.850
754
754
65258
answer