SQL中聚合功能的划分不符合预期 [英] Division with Aggregate Functions in SQL Not Behaving as Expected

问题描述

我正在尝试在SQL Server 2008 R2中做一些交叉表。那部分还好，但是，如果我尝试获取每个单元格的百分比，就会遇到问题。

I'm trying to do some crosstabs in SQL Server 2008 R2. That part is alright, however, if I try to get percentages for each cell, I run into a problem.

这里有一个精炼的用例：一项调查，人们给出他们最喜欢的颜色和他们最喜欢的水果。我想知道有多少喜欢给定的水果和给定的颜色：

Here is a distilled use case: A survey where people give their favorite color and their favorite fruit. I'd like to know how many like a given fruit AND a given color:

with survey as (
    select 'banana' fav_fruit, 'yellow' fav_color
     union select 'banana', 'red'
     union select 'apple', 'yellow'
     union select 'grape', 'red'
     union select 'apple', 'blue'
     union select 'orange', 'purple'
     union select 'pomegranate', 'green'
)
select
    s.fav_color, 
    sum(case 
          when s.fav_fruit = 'banana' then 1
          else 0
        end) as banana, 
    sum(case 
           when s.fav_fruit = 'banana' then 1
           else 0
         end) / sum(1)   -- why does division always yield 0? "+", "-", and "*" all behave as expected.
         * 100 as banana_pct,
     sum(1) as total
from 
    survey s
group by
    s.fav_color;

结果：

fav_color   banana banana_pct  total
------------------------------------
blue        0      0            1
green       0      0            1
purple      0      0            1
red         1      0            2
yellow      1      0            2

我的期望：

fav_color   banana banana_pct  total
------------------------------------
blue        0      0           1
green       0      0           1
purple      0      0           1
red         1      50          2
yellow      1      50          2

请帮助我获得期望的结果？

Please help me to get what I was expecting?

推荐答案

您正在使用SQL Server 。这是一个复制该问题的简单得多的示例：

You are using SQL Server. Here is a much simpler example that replicates the issue:

select 1/2

SQL Server执行整数除法。

SQL Server does integer division.

用诸如 sum（1.0）或 sum（以浮点形式铸造1）或 sum（1e0）而不是 sum（1）。

至少与我的预期相反，SQL Server存储带小数点的数字作为数字/十进制类型（请参见此处），而不是 float 。固定的小数位数可能会影响后续操作。

Contrary to my expectation at least, SQL Server stores numbers with decimal points as numeric/decimal type (see here) rather than float. The fixed number of decimal spaces might affect subsequent operations.

这篇关于SQL中聚合功能的划分不符合预期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！