[SQL] IN句の検索リストに列名を指定する

SQLのIN句の説明や実際の使用例を見ていると

[列名] IN ([検索リスト])

という記述が多い。
しかし、検索値をIN句の前に持ってきて列名を列挙することもできる。

[検索値] IN ([列名リスト])

この用法は、類似する情報を複数のカラムで持つような場合に使える。

--最高音か最低音がC#
'C#' IN (top_note, bottom_note)

あまり使いどころが無さそうに見えるが、正規化されていないテーブル設計を前にすると結構使えたり…。

SQLアンチパターン

posted with カエレバ

Bill Karwin オライリージャパン 2013-01-26

[SQL] sum (case when ～)で異なる条件の集計をまとめて取得する

既存システムに参照だけのview（画面、帳票）を追加。
項目ごとの表示桁数はフォントや枠サイズを基準に定められたが、現行でその桁数を超えるデータがどれだけあるのかわからないというので調査することになった。
（順序が逆な気がするが）

やりたいこと

• 表示する項目の文字数が指定の桁を超えるレコードの数を集計する
• 対象の項目は複数あり、それぞれで指定の桁数(＝条件)は異なる
• 文字数制限とは別に、いくつかの条件で絞込も行う

最初に考えたのは以下のような感じ。
条件ごとにcountした結果を愚直に結合。

select *

from
 -- 名称が10文字を超えるレコードの件数
 (select
   '名称' as column_name
  ,count(*) as product_count
 from
  t_product p
 where
  p.color = 'red'  -- 共通条件
  and length(p.name) > 10
 )

 union all

 -- コードが4文字を超えるレコードの件数
 (select
   'コード' as column_name
  ,count(*) as product_count
 from
  t_product p
 where
  p.color = 'red'  -- 共通条件
  length(p.cd) > 4
 )

 -- 以下、似たような条件が続く...

実行計画とったら非常に遅い、かつwhere句の条件が重複しているなど"何か悪いことをしている"感が凄かったので、条件付きで集計するにはSUM(CASE WHEN ...)を参考に考えなおした。

select
  count(*) as 総数
 ,sum(case when length(p.name) > 10 then 1 else 0 end) as 名称
 ,sum(case when length(p.cd) > 4 then 1 else 0 end) as コード
 -- 以下、似たような条件が続く...
 
from
 t_product p
where
 p.color = 'red'

もともとwhere句に書いていた項目ごとの条件をcase whenにブチ込みつつ、共通条件は共通化させる。だいぶすっきりしつつ実行計画もまぁまともな数値が出たので良かった。

これにさらに区分ごとの条件なんかを足していき、最終的には以下のようなSQLをブン投げて調査を終えた。

select
  count(*) as 総数
 ,case when p.type = 'a' -- aの場合はnameのlengthだけで判断
            then sum(case when length(p.name) > 10 then 1 else 0 end)
       when p.type = 'b' -- bの場合はnameとcolorを結合したlengthで判断
            then sum(case when length(p.name || p.color) > 15 then 1 else 0 end)
       else -1 -- a,b以外のtypeの場合は集計対象外
       end as 名称
 ,sum(case when length(p.cd) > 4 then 1 else 0 end) as コード
 -- 以下、似たような条件が続く...
 
from
 t_product p
where
 p.color = 'red'
group by
 p.type
order by
 p.type

今回はまさしく↓状態だった

「カウントする＝COUNT関数」しか頭になく…(以下略)

【環境】

oracle 11g

【参考】

条件付きで集計するにはSUM(CASE WHEN ...)
MySQLでの条件付きSUM，COUNT

【2014/6/7追記】

上記のテクニックが最近読み始めた『達人に学ぶSQL徹底指南書』に載っていた。

曰く、

WHERE句で条件分岐させるのは素人のやること。プロはSELECT句で分岐させる。

実践的なパターンが多く掲載されており、常々SQL力の不足を感じていた自分には丁度良い。

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

posted with カエレバ

ミック 翔泳社 2008-02-07