9-31 過去で最大のデータをサマリ

SQLパズル

TaxAreaAuthorityテーブル
taxAuthority  taxArea
------------  -------
city1         city1
city2         city2
city3         city3
county1       city1
county1       city2
county2       city3
state1        city1
state1        city2
state1        city3

TaxRatesテーブル
taxAuthority  dteffective  authTaxRate
------------  -----------  -----------
city1          1993/01/01          1.0
city1          1994/01/01          1.5
city2          1993/09/01          1.5
city2          1994/01/01          2.0
city2          1995/01/01          2.5
city3          1993/01/01          1.7
city3          1993/07/01          1.9
county1        1993/01/01          2.3
county1        1994/10/01          2.5
county1        1995/01/01          2.7
county2        1993/01/01          2.4
county2        1994/01/01          2.7
county2        1995/01/01          2.8
state1         1993/01/01          0.5
state1         1994/01/01          0.8
state1         1994/07/01          0.9
state1         1994/10/01          1.1

1994/11/01の、city2の、税金の税率の合計は、
city2の税率   = 2.0
county1の税率 = 2.5
state1の税率  = 1.1

よって、総税率は、
2.0+2.5+1.1 = 5.6
になります。

日と、taxAreaを指定して、
税金の税率の総税率を出力します

SQLパズル(日本語版)のパズル31 [税金の計算] を参考にさせていただきました
SQLパズル 第2版のパズル32 [税金の計算] を参考にさせていただきました

データ作成スクリプト

create table TaxAreaAuthority(
taxAuthority varchar2(7),
taxArea      varchar2(5),
primary key(taxAuthority,taxArea));

insert into TaxAreaAuthority values('city1','city1');
insert into TaxAreaAuthority values('city2','city2');
insert into TaxAreaAuthority values('city3','city3');
insert into TaxAreaAuthority values('county1','city1');
insert into TaxAreaAuthority values('county1','city2');
insert into TaxAreaAuthority values('county2','city3');
insert into TaxAreaAuthority values('state1','city1');
insert into TaxAreaAuthority values('state1','city2');
insert into TaxAreaAuthority values('state1','city3');

create table TaxRates(
taxAuthority varchar2(7),
dteffective  date,
authTaxRate  number(2,1),
primary key(taxAuthority,dteffective));

insert into TaxRates values('city1'  ,to_date('1993/01/01','yyyy/mm//dd'),1.0);
insert into TaxRates values('city1'  ,to_date('1994/01/01','yyyy/mm//dd'),1.5);
insert into TaxRates values('city2'  ,to_date('1993/09/01','yyyy/mm//dd'),1.5);
insert into TaxRates values('city2'  ,to_date('1994/01/01','yyyy/mm//dd'),2.0);
insert into TaxRates values('city2'  ,to_date('1995/01/01','yyyy/mm//dd'),2.5);
insert into TaxRates values('city3'  ,to_date('1993/01/01','yyyy/mm//dd'),1.7);
insert into TaxRates values('city3'  ,to_date('1993/07/01','yyyy/mm//dd'),1.9);
insert into TaxRates values('county1',to_date('1993/01/01','yyyy/mm//dd'),2.3);
insert into TaxRates values('county1',to_date('1994/10/01','yyyy/mm//dd'),2.5);
insert into TaxRates values('county1',to_date('1995/01/01','yyyy/mm//dd'),2.7);
insert into TaxRates values('county2',to_date('1993/01/01','yyyy/mm//dd'),2.4);
insert into TaxRates values('county2',to_date('1994/01/01','yyyy/mm//dd'),2.7);
insert into TaxRates values('county2',to_date('1995/01/01','yyyy/mm//dd'),2.8);
insert into TaxRates values('state1' ,to_date('1993/01/01','yyyy/mm//dd'),0.5);
insert into TaxRates values('state1' ,to_date('1994/01/01','yyyy/mm//dd'),0.8);
insert into TaxRates values('state1' ,to_date('1994/07/01','yyyy/mm//dd'),0.9);
insert into TaxRates values('state1' ,to_date('1994/10/01','yyyy/mm//dd'),1.1);
commit;

SQL

def inDate = to_date('1994/11/01','yyyy/mm//dd')
def intaxArea = 'city2'

--■■■分析関数を使う方法1■■■
select sum(authTaxRate) as authTaxRate
from (select distinct b.taxAuthority,
      Last_Value(authTaxRate)
      over(partition by b.taxAuthority order by b.dteffective
           Rows between Unbounded Preceding and Unbounded Following) as authTaxRate
        from TaxAreaAuthority a,TaxRates b
       where '&intaxArea' = a.taxArea
         and a.taxAuthority = b.taxAuthority
         and b.dteffective <= &inDate);

--■■■分析関数を使う方法2■■■
select sum(authTaxRate) as authTaxRate
from (select authTaxRate,b.dteffective,
      max(b.dteffective) over(partition by b.taxAuthority) as maxdteffective
        from TaxAreaAuthority a,TaxRates b
       where '&intaxArea' = a.taxArea
         and a.taxAuthority = b.taxAuthority
         and b.dteffective <= &inDate)
where dteffective = maxdteffective;

--■■■分析関数を使わない方法■■■
select sum(authTaxRate) as authTaxRate
  from TaxAreaAuthority a,TaxRates b
 where '&intaxArea' = a.taxArea
   and a.taxAuthority = b.taxAuthority
   and b.dteffective = (select max(c.dteffective)
                          from TaxRates c
                         where c.taxAuthority = b.taxAuthority
                           and c.dteffective <= &inDate);

解説

過去で最大のデータであることを条件として、
合計を求めてます

---