MYSQLで複雑な条件で抽出する

Question

お世話になっております。

下記のようなtable_aがあり、以下のような条件でレコードを抽出したいと考えております。
① Company columnはAに絞る
② Yearが最大のもので絞る。
③ ただし、②のYear最大は少なくとも一つはvalueがNOT NULLが存在する場合の最大のyearとする

この条件の場合id4,id7の2つのカラムが抽出されると思います。
id4 -> company Aであり、Year2016でNOT NULLが存在するため,Yearは2016である。
id6-> company Aであり、Year2016でNOT NULLが存在するため,Yearは2016である。

例えばCompany Bのように2016のレコード自体が存在するが その場合のvalueがすべてNULL（NOT NULLのレコードが存在しない）であるため、Yearは2015が最大として,id８とid12のレコードだけに絞られる。

そこで以下のようなSQL文を考えたのですが、うまく意図した条件で絞ることができません。どなたか、上記の条件で絞ろうとする場合のSQL文をご助言願えますでしょうか。

よろしくお願い申し上げます。

table_a

id  company      name     Year    value
1   A                  asset1  2013   100
2   A                  asset1  2014   150
3   A                  asset1  2015   200
4   A                  asset1  2016   220
5   A                  asset2  2015   100
6   A                  asset2  2016    NULL 
7   A                  asset3  2016   60
8   B                  asset4  2015   100
9   B                  asset4  2016   NULL
10  B                  asset5  2016   NULL
11  B                  asset6  2014   30
12  B                  asset6  2015   25

SQL文

SELECT t1.* from table_a t1 JOIN (SELECT max(year) year 
from table_a GROUP BY company where value IS NOT NULL)
t2 using (year) where t1.company="a"

Comments

[KiyoshiMotoki]

「Company columnはAに絞る 」とき、「id4,id6」だけが抽出されて id7 が抽出の対象から外れるのはなぜですか？

[yambejp]

結果としてidがほしいのでしょうか？yearがほしいのでしょうか？

[SugiuraY]

大変失礼致しました、id4とid7になりますね。修正致しました。

[SugiuraY]

結果として、欲しいのはレコード(1行)です。

[hatena19]

結果が１行で欲しいということですか。 id4, id7 というように。それともidだけでなく該当するレコード全体が欲しいということ？

[SugiuraY]

はい、仰る通りです。

[hatena19]

欲しいのは、A:「結果が１行」なのか B:「該当するレコード全体」なのか、A または B で答えてください。

[SugiuraY]

Bになります。

Answer 1

記載されたSQLに対して、コメントを。

SELECT t1.* from table_a t1 JOIN (SELECT max(year) year 
from table_a GROUP BY company where value IS NOT NULL)
t2 using (year) where t1.company="a"

↑
まず、文法が違います。GROUP BY の後にWHERE句は書けません。

SELECT t1.* from table_a t1 JOIN (SELECT max(year) year 
from table_a where value IS NOT NULL GROUP BY company)
t2 using (year) where t1.company="a"

次に、
t1（NULLを含まないtable_aのはずが、table_aのまま）と
t2（company毎のNULLを含まないMAX YEARのはずが、companyがないただの年月リスト）による
紐づけがないので、t1に条件を加え、t2にcompanyを付与し、紐づけします。
（ついでに見やすくします）

SELECT t1.* 
from table_a t1 
		INNER JOIN (	SELECT company
							,	max( year ) as year
						from table_a
						where value IS NOT NULL 
						GROUP BY company
						) t2 ON(
				t2.company	= t1.company
			AND	t2.year		= t1.year
		)
where t1.company="a"
	and	t1.value IS NOT NULL

やりたかったのはこの形ですかね。
ちなみに、私なら、以下のSQLです。

SELECT t1.* 
from table_a t1
where	t1.company="a"
    and	t1.value IS NOT NULL
    and	t1.year	= (
					SELECT	MAX( t2.year )
					FROM	table_a t2
					WHERE	t2.company	= t1.company
						and	t2.value	IS NOT NULL
					)

Comments

[SugiuraY]

ご回答ありがとうございます。
t1.value IS NOT NULLについて、少し疑問なのです。
サブクエリでt2.value IS NOT NULLにした上で、where t1.year=t2.yearとしており、どちらもテーブルが同じtable_aである以上、t1.value IS NOT NULLを条件してする意味に理解が及びませんでした。あくまで明示的に示すといったことでしょうか？

[hatena19]

t1.value IS NOT NULL がないと、Yearが最大でvalueがNullのレコード(id=6)も抽出されちゃいますね。

[tomari_perform]

今回の回答は、「valueにNULLがあるデータを抽出しない」場合のSQLになります。
もちろん、抽出結果にNULLが含まれていても構わないのであれば、
「t1.value IS NOT NULL」は不要になります。
（他の回答者が含めていたので、私も流れに乗りました）

なお、抽出結果にNULLを含めない事が前提で、少し疑問に思われている場合、
恐らく同一テーブルを参照すれば、t2に条件を付与すると、
t1にも勝手に紐づくものと思われているのかもしれませんが、
異なるFROM句内のテーブルのデータに対して、勝手に紐づく事はありません。

分かりやすく言うと、
全く同じ形のビルが２つあったとしても、FROM句ごとに建っている場所が異なります。

そのため、１つのビルに対して、ある階の電気を消したとしても、
もう１つのビルの電気は付いたままになるんです。

今回のケースでいうと、
t1に対しては、（valueがNULLを含む）company=aのデータを抽出しており、
t2については、（company単位でvalueがNULLを除く）最大yearになります。
t1とt2を紐づける際、
t1.year = t2.year
だけでは、
t1.valueがNOT NULLのデータもNULLのデータも抽出されることになるため、
t1.value IS NOT NULL
を追加した感じです。

Answer 2

companyが"A" で、value が Null でないYearが最大のレコードを抽出

SELECT table_a.*
FROM table_a
WHERE 
company="A" AND
value is not null AND
year = (SELECT Max(Year)
FROM table_a
WHERE value Is Not Null AND company="A");

company毎に、value が Null でないYearが最大のレコードを抽出

SELECT a.*
FROM
table_a AS a INNER JOIN
(SELECT company, Max(Year) AS maxYear
 FROM table_a
 WHERE value Is Not Null
 GROUP BY company)  AS b 
 ON a.company = b.company AND a.year = b.maxYear
 WHERE a.value Is Not Null;

Comments

[SugiuraY]

ご回答いただきありがとうございます。
質問をうまくお伝えできなかったにもかかわらず、粘り強く聞いてくださり、ありがとうございます。
ご教示いただいた方法で、実現できました。もっとSQL文を勉強しないとなかなか実践で使用するレベルに至りませんね、、、精進いたします。よろしくお願い申し上げます。

Answer 3

create table table_a(id int not null primary key,company varchar(20),name varchar(20),year int,value int null);
insert into table_a values
(1,'A','asset1', 2013,100),
(2,'A','asset1',2014,150),
(3,'A','asset1',2015,200),
(4,'A','asset1',2016,220),
(5,'A','asset2',2015,100),
(6,'A','asset2',2016,NULL),
(7,'A','asset3',2016,60),
(8,'B','asset4',2015,100),
(9,'B','asset4',2016,NULL),
(10,'B','asset5',2016,NULL),
(11,'B','asset6',2014,30),
(12,'B','asset6',2015,25);

だとして

SELECT t1.company,group_concat(id) as id from table_a as t1 
INNER JOIN (
SELECT company,max(year) as  year 
from table_a where value IS NOT NULL GROUP BY company 
) as t2
on t1.year=t2.year and t1.company=t2.company
group by company;

追記

該当するレコードをすべてとりだすならこうしてください

SELECT t1.* from table_a as t1 
INNER JOIN (
SELECT company,max(year) as  year 
from table_a where value IS NOT NULL GROUP BY company 
) as t2
on t1.year=t2.year and t1.company=t2.company
WHERE t1.company='A';

Comments

[SugiuraY]

ご回答をいただき、ありがとうございます。また、言葉たらずの中、諸々汲み取っていただいてしまい、申し訳ございません。
ご教示いただいた方法で実現できました。
yambejp様のお力添え、いつも感謝をしております。
よろしくお願い申し上げます。