[sql] 반환 된 값이 null 인 경우 postgresql은 0을 반환합니다.

Program / 글쓴이

avg (price)를 반환하는 쿼리가 있습니다.

  select avg(price)
  from(
      select *, cume_dist() OVER (ORDER BY price desc) from web_price_scan
      where listing_Type='AARM'
        and u_kbalikepartnumbers_id = 1000307
        and (EXTRACT(Day FROM (Now()-dateEnded)))*24 < 48
        and price>( select avg(price)* 0.50
                    from(select *, cume_dist() OVER (ORDER BY price desc)
                         from web_price_scan
                         where listing_Type='AARM'
                           and u_kbalikepartnumbers_id = 1000307
                           and (EXTRACT(Day FROM (Now()-dateEnded)))*24 < 48
                        )g
                   where cume_dist < 0.50
                 )
        and price<( select avg(price)*2
                    from( select *, cume_dist() OVER (ORDER BY price desc)
                          from web_price_scan
                          where listing_Type='AARM'
                            and u_kbalikepartnumbers_id = 1000307
                            and (EXTRACT(Day FROM (Now()-dateEnded)))*24 < 48
                        )d
                    where cume_dist < 0.50)
     )s

  having count(*) > 5

값을 사용할 수없는 경우 0을 반환하는 방법은 무엇입니까?

답변

합체 사용

COALESCE(value [, ...])
The COALESCE function returns the first of its arguments that is not null.
Null is returned only if all arguments are null. It is often
used to substitute a default value for null values when data is
retrieved for display.

편집하다

다음 COALESCE은 쿼리 의 예입니다 .

SELECT AVG( price )
FROM(
      SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan
      WHERE listing_Type = 'AARM'
        AND u_kbalikepartnumbers_id = 1000307
        AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
        AND COALESCE( price, 0 ) > ( SELECT AVG( COALESCE( price, 0 ) )* 0.50
                                     FROM ( SELECT *, cume_dist() OVER ( ORDER BY price DESC )
                                           FROM web_price_scan
                                           WHERE listing_Type='AARM'
                                             AND u_kbalikepartnumbers_id = 1000307
                                             AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
                                         ) g
                                    WHERE cume_dist < 0.50
                                  )
        AND COALESCE( price, 0 ) < ( SELECT AVG( COALESCE( price, 0 ) ) *2
                                     FROM( SELECT *, cume_dist() OVER ( ORDER BY price desc )
                                           FROM web_price_scan
                                           WHERE listing_Type='AARM'
                                             AND u_kbalikepartnumbers_id = 1000307
                                             AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
                                         ) d
                                     WHERE cume_dist < 0.50)
     )s
HAVING COUNT(*) > 5

IMHO COALESCE는 AVG값을 수정하므로 와 함께 사용하면 안됩니다 . NULL알려지지 않은 것을 의미합니다. 에서 사용하는 것과는 다릅니다 SUM. 우리가 바꿀 경우이 예에서, AVG에 의해 SUM, 결과는 왜곡되지 않습니다. 합계에 0을 더하는 것은 누구에게도 해를 끼치 지 않지만 알 수없는 값에 대해 0으로 평균을 계산하면 실제 평균을 얻지 못합니다.

이 경우, 나는 추가 할 수 price IS NOT NULL에서 WHERE이러한 알 수없는 값을 방지하기 위해 절.

답변

(이 답변은 원래 질문에 모든 사례 별 세부 정보를 포함하지 않고 질문에 더 짧고 일반적인 예를 제공하기 위해 추가되었습니다).

여기에는 두 가지 다른 “문제”가 있습니다. 첫 번째는 테이블 또는 하위 쿼리에 행이없는 경우이고 두 번째는 쿼리에 NULL 값이있는 경우입니다.

내가 테스트 한 모든 버전에서 postgres와 mysql은 평균화 할 때 모든 NULL 값을 무시하고 평균화 할 것이 없으면 NULL을 반환합니다. NULL은 “알 수 없음”으로 간주되므로 일반적으로 의미가 있습니다. 이를 재정의하려면 병합을 사용할 수 있습니다 (Luc M에서 제안한대로).

$ create table foo (bar int);
CREATE TABLE

$ select avg(bar) from foo;
 avg
-----

(1 row)

$ select coalesce(avg(bar), 0) from foo;
 coalesce
----------
        0
(1 row)

$ insert into foo values (3);
INSERT 0 1
$ insert into foo values (9);
INSERT 0 1
$ insert into foo values (NULL);
INSERT 0 1
$ select coalesce(avg(bar), 0) from foo;
      coalesce
--------------------
 6.0000000000000000
(1 row)

물론, “from foo”는 “from (… 여기서 복잡한 논리 …) as foo”로 대체 될 수 있습니다.

이제 테이블의 NULL 행을 0으로 계산해야합니까? 그런 다음 avg 호출 내에서 병합을 사용해야합니다.

$ select coalesce(avg(coalesce(bar, 0)), 0) from foo;
      coalesce
--------------------
 4.0000000000000000
(1 row)

답변

이를 달성하는 두 가지 방법을 생각할 수 있습니다.

  • IFNULL () :

    IFNULL () 함수는 표현식이 NULL이면 지정된 값을 반환하고, 표현식이 NULL이면이 함수는 표현식을 반환합니다.

통사론:

IFNULL(expression, alt_value)

쿼리를 사용한 IFNULL ()의 예 :

SELECT AVG( price )
FROM(
      SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan
      WHERE listing_Type = 'AARM'
        AND u_kbalikepartnumbers_id = 1000307
        AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
        AND IFNULL( price, 0 ) > ( SELECT AVG( IFNULL( price, 0 ) )* 0.50
                                     FROM ( SELECT *, cume_dist() OVER ( ORDER BY price DESC )
                                           FROM web_price_scan
                                           WHERE listing_Type='AARM'
                                             AND u_kbalikepartnumbers_id = 1000307
                                             AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
                                         ) g
                                    WHERE cume_dist < 0.50
                                  )
        AND IFNULL( price, 0 ) < ( SELECT AVG( IFNULL( price, 0 ) ) *2
                                     FROM( SELECT *, cume_dist() OVER ( ORDER BY price desc )
                                           FROM web_price_scan
                                           WHERE listing_Type='AARM'
                                             AND u_kbalikepartnumbers_id = 1000307
                                             AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
                                         ) d
                                     WHERE cume_dist < 0.50)
     )s
HAVING COUNT(*) > 5

  • COALESCE ()

    COALESCE () 함수는 목록에서 널이 아닌 첫 번째 값을 리턴합니다.

통사론:

COALESCE(val1, val2, ...., val_n)

쿼리가있는 COALESCE ()의 예 :

SELECT AVG( price )
FROM(
      SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan
      WHERE listing_Type = 'AARM'
        AND u_kbalikepartnumbers_id = 1000307
        AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
        AND COALESCE( price, 0 ) > ( SELECT AVG( COALESCE( price, 0 ) )* 0.50
                                     FROM ( SELECT *, cume_dist() OVER ( ORDER BY price DESC )
                                           FROM web_price_scan
                                           WHERE listing_Type='AARM'
                                             AND u_kbalikepartnumbers_id = 1000307
                                             AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
                                         ) g
                                    WHERE cume_dist < 0.50
                                  )
        AND COALESCE( price, 0 ) < ( SELECT AVG( COALESCE( price, 0 ) ) *2
                                     FROM( SELECT *, cume_dist() OVER ( ORDER BY price desc )
                                           FROM web_price_scan
                                           WHERE listing_Type='AARM'
                                             AND u_kbalikepartnumbers_id = 1000307
                                             AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48
                                         ) d
                                     WHERE cume_dist < 0.50)
     )s
HAVING COUNT(*) > 5

답변