【PostgreSQL】欠損値の回避

Question

実現したいこと

商品全体の利益率平均を表示したい

前提

PostgreSQLにおいて、欠損値が含まれている場合の対処について、
本来ならCOALESCEなどでNULL回避をすると思うのですが、それ以外で記述されている
クエリを見て、どのような原理なのかわかりません。

該当のソースコード

PostgreSQL
SELECT
    AVG((unit_price * 1.0 - unit_cost) / unit_price) AS unit_profit_rate
FROM
    tbl
;

補足情報（FW/ツールのバージョンなど）

上記のクエリで問題なく動作しており、
AVG((COALESCE(unit_price, 1.0) - unit_cost) / COALESCE(unit_price, 1.0)) AS unit_profit_rate
と記述したのと同じ結果が出力されるのですが、
なぜ1を掛けることで欠損値を回避できているのかわかりません。
初歩的な質問かもしれませんが、お分かりになる方いらっしゃいましたらご教示いただけますと幸いです。

Answer 1

欠損値というのがNullを指しているなら、Null値は集約対象とはならないからです。
集約関数

avg ( smallint ) → numeric
avg ( integer ) → numeric
avg ( bigint ) → numeric
avg ( numeric ) → numeric
avg ( real ) → double precision
avg ( double precision ) → double precision
avg ( interval ) → interval
すべての非NULL入力値の平均（算術平均）を計算します。

「1を掛ける」ことは関係ありません。
逆に、COALESCE()によってNullを回避した場合は結果が変わるはずです。

AVG((COALESCE(unit_price, 1.0) - unit_cost) / COALESCE(unit_price, 1.0)) AS unit_profit_rate
と記述したのと同じ結果が出力されるのですが、

上記で結果が変わらないなら、unit_costがNullなのではないでしょうか。

Comments

[YM-16]

回答ありがとうございます。
テーブルをよく調べるとNULLではなく、そもそも値が入っていない状態の行が含まれていたようです。
（unit_price,unit_costともにNULLはありませんでした）

1を掛けることは関係ないのであれば、意味のない処理なのでしょうか…

[sazi]

> テーブルをよく調べるとNULLではなく、そもそも値が入っていない状態の行が含まれていたようです。
カラムの値がNullではなくて、行自体が存在しないという事ですか？
その場合も外部結合等で集計対象にしているなら値はNullとなり結果は変わりません。

> 1を掛けることは関係ないのであれば、意味のない処理なのでしょうか？
1.0は暗黙の型変換でnumericになります。
演算している他の項目の型が分かりませんが、除算があるので小数点以下の誤差を防ぐ為に、numericによる演算にしたかったのではないでしょうか

[YM-16]

＞カラムの値がNullではなくて、行自体が存在しない
id,unit_price,unit_cost
値,NONE,NONE
このような形でNULLではなくNONEと表示されているカラムがありました。
特に結合などはしておりません。

＞1.0は暗黙の型変換でnumericになります。
こちら知りませんでした、ありがとうございます。

NULLについて私がまだ理解しきれていない部分はありますが、1を掛ける部分の意味を教えていただいたのでいったん解決とさせていただきます。
ご教示ありがとうございました。

[sazi]

> このような形でNULLではなくNONEと表示されているカラムがありました。
どのように表示されるかは使用するDBツールによります。
多分、NONE=NULLだと思いますが。