9-29 ソートした数列で考える

SQLパズル

Salesテーブル
customer  saleDate
--------  ----------
Bill      1994/06/01
Fred      1994/06/01
Mary      1994/06/01
Bill      1994/06/02
Fred      1994/06/02
Bill      1994/06/03
Bill      1994/06/04
Bill      1994/06/05
Bill      1994/06/06
Bill      1994/06/07
Fred      1994/06/07
Mary      1994/06/08
Wendy     1999/10/05
Heidy     1999/10/10
Wendy     1999/10/15
Wendy     1999/10/20
Heidy     1999/10/25
Tiger     1999/10/30

customerごとで、
saleDateの最小と最大とレコード数と、saleDateの間隔の平均を出力する。
間隔がないcustomer(この場合は、Tiger)は、間隔の平均は、0とする

customerテーブルのプライマリキーは、customerとsaleDateとする

出力結果
customer  FirstSaleDate  LastSaleDate  SalesCount  AvgSalesWait
--------  -------------  ------------  ----------  ------------
Fred      1994/06/01     1994/06/07             2             1
Mary      1994/06/01     1994/06/08             2             3
Bill      1994/06/01     1994/06/07             7             7
Wendy     1999/10/05     1999/10/20             3           7.5
Heidy     1999/10/10     1999/10/25             2            15
Tiger     1999/10/30     1999/10/30             1             0

SQLパズル(日本語版)のパズル29 [販売間隔の平均(重要顧客)] を参考にさせていただきました
SQLパズル 第2版のパズル30 [買い物の平均サイクル] を参考にさせていただきました

データ作成スクリプト

create table Sales(
customer varchar2(5),
saleDate date,
primary key(customer,saleDate));

insert into Sales values('Bill', to_date('1994/06/01','yyyy/mm/dd'));
insert into Sales values('Fred', to_date('1994/06/01','yyyy/mm/dd'));
insert into Sales values('Mary', to_date('1994/06/01','yyyy/mm/dd'));
insert into Sales values('Bill', to_date('1994/06/02','yyyy/mm/dd'));
insert into Sales values('Fred', to_date('1994/06/02','yyyy/mm/dd'));
insert into Sales values('Bill', to_date('1994/06/03','yyyy/mm/dd'));
insert into Sales values('Bill', to_date('1994/06/04','yyyy/mm/dd'));
insert into Sales values('Bill', to_date('1994/06/05','yyyy/mm/dd'));
insert into Sales values('Bill', to_date('1994/06/06','yyyy/mm/dd'));
insert into Sales values('Bill', to_date('1994/06/07','yyyy/mm/dd'));
insert into Sales values('Fred', to_date('1994/06/07','yyyy/mm/dd'));
insert into Sales values('Mary', to_date('1994/06/08','yyyy/mm/dd'));
insert into Sales values('Wendy',to_date('1999/10/05','yyyy/mm/dd'));
insert into Sales values('Heidy',to_date('1999/10/10','yyyy/mm/dd'));
insert into Sales values('Wendy',to_date('1999/10/15','yyyy/mm/dd'));
insert into Sales values('Wendy',to_date('1999/10/20','yyyy/mm/dd'));
insert into Sales values('Heidy',to_date('1999/10/25','yyyy/mm/dd'));
insert into Sales values('Tiger',to_date('1999/10/30','yyyy/mm/dd'));
commit;

SQL

alter session set nls_date_format = 'yyyy/mm/dd';

select customer,min(SaleDate) as FirstSaleDate,
max(SaleDate) as LastSaleDate,
count(*) as SalesCount,
case when count(*) = 1 then 0
     else (max(SaleDate)-min(SaleDate)) / (count(*)-1) end as AvgSalesWait
from Sales
group by customer
order by min(SaleDate),customer;

解説

項数が2以上の、昇順にソートした数列で、
階差の平均をXとすると
末項 = 初項 + (項数 - 1) * X
が成立するので

    末項 = 初項 + (項数 - 1) * X
⇔  末項 - 初項 = (項数 - 1) * X
⇔  (項数 - 1) * X =  末項 - 初項
⇔  X = (末項 - 初項) / (項数 - 1)   (項数 >= 2 ⇒ 項数-1 >0 なので)

これと、

末項は、max(SaleDate)
初項は、min(SaleDate)
項数は、count(*)
で求まることを使って、saleDateの間隔の平均を求めてます

■■■■■■■■■■■■■■■■■■■■■■■■■■

SQLは集合志向なので、基本的には、集合で考えるのですが、
ソートした数列で考えることも、時には必要ということでしょうか

---