COUNT(col) 때문에 바뀐 비율의 뜻

어떤 보고서가 배송일이 있는 주문의 비율을 COUNT(shipped_at) * 1.0 / COUNT(*)로 보여 줬습니다. 한동안은 맞았습니다. 그러다 대기 중인 주문이 shipped_at이 NULL인 채로 같은 테이블에 들어오기 시작했고 비율이 떨어졌습니다. 쿼리는 하나도 바뀌지 않았습니다.

COUNT(*)는 입력 행을 셉니다. COUNT(shipped_at)는 그 값이 NULL이 아닌 행을 셉니다. 비율은 처음부터 "전체 행 중 날짜가 있는 행"이었고, 그 밑에서 행 집합이 바뀐 것입니다.

확인할 것이 세 가지 있고, 서로 같지 않습니다.

1. 분모에 이름을 붙입니다. COUNT(*) FILTER (WHERE status <> 'pending')는 이 비율이 어떤 행에 대한 것인지 말해 줍니다.
2. 나눗셈을 조심합니다. PostgreSQL에서 count는 bigint를 돌려주므로, COUNT(a) / COUNT(*)는 1보다 작은 비율을 전부 0으로 잘라 버립니다. * 1.0이 붙은 이유입니다.
3. 그 열에서 NULL이 무슨 뜻인지 묻습니다. 아직 배송 전과 배송일 미상은 다른 사실이고, 개수로는 둘을 구별할 수 없습니다.

배송된 주문 둘, 대기 주문 둘만 있어도 두 계산이 눈에 띄게 갈립니다.

SELECT COUNT(shipped_at) * 1.0 / COUNT(*) AS naive,
       COUNT(shipped_at) * 1.0
         / COUNT(*) FILTER (WHERE status <> 'pending')
         AS shipped_share
FROM orders;
-- naive 0.5, shipped_share 1.0

R에도 이 갈림이 있습니다. length(x)는 NA 항목까지 세고 sum(!is.na(x))는 세지 않습니다. 보고서를 R로 만들 때 저는 비율 옆에 sum(is.na(x))를 함께 찍어서, 분자가 몇 행을 건너뛰었는지 읽는 사람이 볼 수 있게 합니다.