ある列の値が平均未満の行を抽出

ある列、例えばcolの値が、そのテーブルのcolの平均値未満の行を抽出したい場合、ちょっと考えただけでもいくつかの方法が思いつく。

まずは、実行できないものから。

SELECT
    *
FROM
    SomeTable
WHERE
    -- WHEREの評価段階ではavgは計算できない
    col < avg(col)

SQLでは、WHEREの評価段階では集計関数は計算できないため、上のSQLはエラーとなる。

SELECT
    *
FROM
    SomeTable
WHERE
    -- 引数エラー的な
    col < avg(SELECT col FROM SomeTable)

これはavgの使い方を間違えているため、エラーとなる。
確かに、こんな書き方が出来てもいいんじゃないかなぁ、と思わなくもないけど。

上のような書き方は出来ないが、サブクエリ内にavgを移動させれば欲しい結果が手に入る。

SELECT
    *
FROM
    SomeTable
WHERE
    -- サブクエリを使えばOK
    col < (SELECT avg(col) FROM SomeTable)

ほかにも、ちょっとひねって、EXISTSとHAVINGを使った方法も考えられる。

SELECT
    *
FROM
    SomeTable P
WHERE
    -- ちょっとひねった方法
    EXISTS(SELECT * FROM SomeTable C HAVING P.col < avg(col))

標準SQLではなくなるが、分解してしまうのも一つの手だろう*1。

-- 分解
declare @avg as float
SELECT @avg = avg(col) FROM SomeTable
SELECT * FROM SomeTable WHERE col < @avg

番外編として、自分で平均を計算しようとするような方法も・・・

-- ありえないと言い切れないところが怖い
declare @total as int
declare @count as int
declare @tmp as int
set @total = 0
set @count = 0

declare cur cursor for
  SELECT col FROM SomeTable

open cur
fetch cur into @tmp

while @@fetch_status = 0 begin
  set @total = @total + @tmp
  set @count = @count + 1
  fetch cur into @tmp
end

close cur
deallocate cur

SELECT
    *
FROM
    SomeTable
WHERE
    col < (@total / @count)

countやsumを自分で実装してしまうような人はそんなにいないけど、max、min、avgあたりはあり得ないとは言い切れないんだよなぁ。

*1:ここではT-SQLを使用