Niet-gehele resultaten krijgen in Data Explorer

The Stack Exchange Data Explorer allows SQL queries against a Stack Exchange database. The following query

select
  month(CreationDate) month,
  year(CreationDate) year,
  sum(case when lower(left(Title,2))='wh' then 1 else 0 end)/count(*) wh,
  (select sum(Score)/count(*)
   from Posts u
   where
     month(CreationDate)=month(t.CreationDate) and
     year(CreationDate)=year(t.CreationDate) and
     lower(left(Title,2))='wh' and
     PostTypeId=1 -- question
  ) wh_score,
  sum(Score)/count(*) score,
  (select sum(AnswerCount)/count(*)
   from Posts u
   where
     month(CreationDate)=month(t.CreationDate) and
     year(CreationDate)=year(t.CreationDate) and
     lower(left(Title,2))='wh' and
     PostTypeId=1 -- question
  ) wh_answers,
  sum(AnswerCount)/count(*) answers
from Posts t
where PostTypeId=1 -- question
group by month(CreationDate), year(CreationDate)
;

courtesy of Scorpi0 — yields all integer values in the results: everything gets either rounded or truncated (I don't know which). (This is especially annoying in the wh column, where every value is therefore 0.) Is there a way to force decimal or values?

0
ja ru de

1 antwoord

Zoals heel wat taal, als je 1/2 doet, voer je de verdeling van 1 op 2 uit en retourneer je het quotiënt, dus 0 hier.

n/m => n = m * q + r
1/2 => 1 = 2 * 0 + 1

Laten we pragmatisch zijn: vermenigvuldig gewoon met een decimaal, zoals dit:

(sum(AnswerCount) * 1.0)/count(*)

En je krijgt decimaal in plaats van int.

2
toegevoegd
Dank je! Dat werkt.
toegevoegd de auteur msh210, de bron