【GAS】QUERY関数で補集合を求める

　GASというよりGSS（グーグルスプレッドシート）の話なんですけど【GSS】って書いても？かなと思い、広義のGASということでお願いします。

　いま、たとえば商品の購入実績の履歴がずらーっとあって（集合A）、もう片方にはいま在庫がある商品が並んでいる（集合B）ものとします。そして集合Aから集合Bを取り除いた集合を求める、換言すればB列から在庫なしのものを抽出するということです。

　もっとも集合Bにあるものは必ず集合Aにも存在するという前提がありますので純粋な意味での補集合にはならないですが大目に見て下さい。

　スプレッドシートで表現するとこのような感じでしょうか。F列が抽出結果です。

　これを1式で実現します。セルF2に入っている式です。

=QUERY(QUERY({UNIQUE(B2:C);D2:E},"select Col1,count(Col2) where Col1 != '' group by Col1"),"select Col1 where Col2=1")

// 適当に改行して見やすくすると

=QUERY(
　QUERY(
　　{
　　UNIQUE(B2:C); // #1 
　　D2:E
　　}, // #2
　"select Col1,count(Col2) where Col1 != '' group by Col1"), //#3 カウント
"select Col1 where Col2=1") // #4 カウントが1のものを抽出

　長い式なので分解して考えてみます。

#1 B列をユニークな形に整形します。
　UNIQUE(B2:C)　＊補足

#2 D列と和結合します。
　{#1;D2:E}　＊補足

#3 果物名でgroup byしてカウントします。
　QUERY(#2,"select Col1,count(Col2) where Col1 != '' group by Col1") ＊補足
　これにより、カウントが２のもの（B列にもあってDれつにもあるということ）は在庫あり、1のもの（B列にしかない）は在庫なしというふうに分けられます。

#4 あとは#3をQUERYの選択範囲としてカウントが1のものを抽出すれば完成です。
　QUERY(#3,"select Col1 where Col2=1")

　このようにQUERY関数の選択範囲はセル範囲を直接選択するだけではなく、他のQUERY関数の実行結果をセル上に出力することなくそのまま選択範囲にすることができるという点に注目して下さい。通常のSQLで言うところのインラインビューに相当することが使えるということです。

　＊補足
　そしてここの範囲のとり方ですが、一見なぜ果物だけ、つまりB列とD列だけでいいんじゃないのか？C列とE列関係は空列だし関係ないと思われたかもしれません。実は、count()するとき、同じ列を選ぶことが出来ないのです。select Col1, count(Col1) group by Col1といったような形はできないのです。そこで、意図的に他の列を巻き込んでその列をcount()することでうまくいきます。

　それから和結合を使っているのでselect A,B・・・ではなく、select Col1,Col2・・・のようにすべきことも留意してください。

　これが使いこなせるようになるといろいろ応用範囲が広がってくると思います！もちろん、作業列を作ったりして頑張れば同じことはできるんですけど。。。