直近2年度連続で減少していない年度を抽出

質問

以下のテーブルの様に時系列が抜けているテーブルから直近2年度連続で減少していない年度を
抽出しようとしています。

/*
year|sale
---------
1990|50
1992|50
1993|52
1994|55
1997|55
*/

/*
CREATE TABLE sales2
(year INTEGER NOT NULL ,
sale INTEGER NOT NULL ,
PRIMARY KEY (year));

INSERT INTO sales2 VALUES (1990, 50);
INSERT INTO sales2 VALUES (1992, 50);
INSERT INTO sales2 VALUES (1993, 52);
INSERT INTO sales2 VALUES (1994, 55);
INSERT INTO sales2 VALUES (1997, 55);
*/

以下の記述で直近の年度までなら表示できているとは思うのですが2年連続となると
やり方が思いつきません。誰かご教授いただけないでしょうか？

select
year,sale
from
sales2 s1
where
sale >=(select sale from sales2 s2 where s2.year =
(select max(year) from sales2 s3 where s1.year > s3.year))
order by
year
;

回答No.1

Siegrune · ベストアンサー

>時系列が抜けている
抜けている年度の値はどうするのでしょう？
例：
1993 56
1994 55
1995 ?(抜け)
1996 ?(抜け)
1997 53

1997は２年連続減少？
２年連続減少とは限りませんが。
（抜けが0だと考えると、２年連続減少ではない。データのある年度でいうと２年連続減少である。）

というのを気にしないで、単にあるレコードのみで判定するのなら、
前々のレコードを調べたいなら、
(select max(year) from sales2 s3 where s1.year > s3.year))
で求めたmax(year)より小さいmax(year)を求めれば出てくるわけですから、

select
year,sale
from
sales2 s1
where
sale >=(select sale from sales2 s2 where s2.year =
(select max(year) from sales2 s3 where s1.year > s3.year))
or
sale >=(select sale from sales2 s2 where s2.year =
(select max(year) from sales2 s3 where
(select max(year) from sales2 s4 where s1.year > s4.year)
> s3.year)
)
order by
year
;
で求まります。
(未検証ですが。)

お礼

ご回答ありがとうございます！
なるほど。こういう記述になるんですね。
理解するのに時間がかかってしまいましたが
望み通りの抽出ができました。

回答No.2

mitoneko

以前、連続２年上昇を考えたことがありますよね。
　連続２年減少なら、簡単です。前回のＳＱＬの不等号をひっくり返すだけ。
　本当に問題の表現が正確であるのならば、

select sm.year, sm.sale
from sales sm
where not exists(
　　　　select sc.year, sc.sale
　　　　from sales sc join sales s1 on s1.year = sc.year -1
　　　　　　　　　　　　join sales s2 on s2.year = sc.year - 2
　　　　where s1.sale < s2.sale and sc.sale < s1.sale and sm.year=sc.year)

　で、いけるともおいます。
　サブクエリの中でやっていることは、２年連続で減少しているレコードの一覧の抽出です。前回のＳＱＬと同じ方法ですね。
　これをnot existsで否定しています。

　さて、恒例の問題への条件です。
　今回は、「減少していない」という表現だったので、前年度・前々年度が存在しない場合も含みます。実は、そのために、ＳＱＬが少々複雑な表現になっています。こうしないと、ＮＵＬＬの扱いが面倒なんです。存在しない年度との比較結果は、「減少していません」としています。増加もしていないし、同値でも無いんですけどね。
　yearがきちんと一意であるか一意化できることが前提になるのは、前回の２年連続増加と同じです。

お礼

ご回答ありがとうございます。
色んな記述方法があるんですね～。
まだSQLを学び始めたばかりなので
サブクエリには苦戦しっぱなしです。