在 PostgreSQL 中计算费率 [英] Calculating rates in PostgreSQL

问题描述

尝试做一些非常简单的事情并通过邮寄来计算signup_flow率.

Trying to do something very simple and calculate the signup_flow rate by mailing.

我有一列名为 signup_flow - 每行都有一个整数值 1 或 0.尝试计算每次邮寄的 signup_flow 率.

I have a column called signup_flow - each row has an integer value of either 1 or 0. Trying to calculate the rate of signup_flow per mailing.

我正在使用以下查询.SUM(signup_flow) 和 COUNT(signup_flow) 每次邮件返回正确的值.当我尝试做一个简单的计算字段 (sum/count*100) 时，它返回 0.这是我第一次使用 Postgres，它不支持计算吗?

I am using the following query. SUM(signup_flow) and COUNT(signup_flow) return the correct values per mailing. When I try to do a simple calculated field (sum/count*100) it returns 0. This is my first time working with Postgres, does it not support calculations?

  SELECT mailing, SUM(signup_flow), COUNT(signup_flow),   
         ((SUM(signup_flow)/COUNT(signup_flow))*100) AS rate
    FROM mail_2017_analytic_file
GROUP BY mailing;

结果:

mailing             sum      count   rate
DEC 17 RRD Mailing  2535    1085476  0
JAN 17 RRD Mailing  8275    3695017  0
MAR 17 RRD Mailing  7230    3595594  0
MAY 17 RRD Mailing  5616    2672981  0
JULY 17 RRD Mailing 7837    3741944  0
AUG 17 RRD Mailing  9272    4604807  0
OCT 17 RRD Mailing  7982    4996146  0

推荐答案

改用这个表达式:

    SUM(signup_flow) * 100.0 / COUNT(signup_flow) AS rate

像@Richard建议一样，您的问题是整数除法，其中小数位数被截断.说明书:

Like @Richard suggested, your problem is integer division, where fractional digits are truncated. The manual:

/ ...除法(整数除法截断结果)

/ ... division (integer division truncates the result)

当你得到的只是小数位数时，这会破坏乐趣.

This spoils the fun when all you would get is fractional digits.

在除法之前乘以 100 只有在将 rate 提高到 1 或更多时才有帮助".(它仍然是整数除法.)乘以 100.0 可以解决问题，因为带有小数位的数字常量被假定为 numeric 类型，而仅由数字(且足够小)组成的数字常量被假定为 integer 类型.

Multiplying by 100 before the division only "helps" if it brings up the rate to 1 or more. (It's still integer division.) Multiplying by 100.0 does the trick, because a numeric constant with a fractional digit is assumed to be type numeric, while a numeric constant consisting of only digits (and small enough) is assumed to be type integer.

或者，将至少一个涉及的数字显式转换为 float 或 numeric，这会强制整个计算使用非整数类型.

Alternatively, cast at least one involved number to float or numeric explicitly, which forces the non-integer type on the whole calculation.

您可能想要round() 您的结果为给定数量的小数位数(适用于 numeric):

    round(SUM(signup_flow) * 100.0 / COUNT(signup_flow), 2) AS rate

相关(有更多解释和链接):

Related (with more explanation and links):

• 将数字结果转换为带十进制数字的百分比

这篇关于在 PostgreSQL 中计算费率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！