在PostgreSQL小数点后获取非零的记录 [英] Fetch records that are non zero after the decimal point in PostgreSQL

问题描述

我有一个表格，其金额字段类型为数字。它包含不同的金额值。例如，

I have a table with an amount field of type Numeric. It contains different amount values. For example

5.00
7.13
8.86
6.00
1.00

...等。

仅获取小数点后非零的那些记录。
，即仅提取与金额相对应的记录

I've to fetch only those records that are nonzero after the decimal point. ie, fetch only the records corresponding to the amounts

7.13
8.86

我该怎么办？

推荐答案

数字是准确的！

与其他答案不同， numeric 是不是浮动地址，点类型 ，但 任意精度类型 （由SQL标准定义）。存储是精确。我引用了手册：

numeric is exact!

Unlike claimed by another answer, numeric is not a floating-point type, but an arbitrary precision type as defined by the SQL standard. Storage is exact. I quote the manual:

numeric类型可以存储具有大量数字
的数字并精确地执行计算。尤其建议
存储货币量和其他需要精确度的数量。

The type numeric can store numbers with a very large number of digits and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required.

答案

问题的自然选择是函数 trunc（） 。它会将截断为零-基本上保留整数部分，而丢弃其余部分。

Answer

The natural candidate for your question is the function trunc(). It truncates toward zero - basically keeping the integer part while discarding the rest. Fastest in a quick test, but the difference is insubstantial among the top contenders.

SELECT * FROM t WHERE amount <> trunc(amount);

floor（）会缩短至下一个整数，它与负数有所不同：

floor() truncates to the next lower integer, which makes a difference with negative numbers:

SELECT * FROM t WHERE amount <> floor(amount);

如果您的数字适合 整数 / bigint 也可以只投射：

If your numbers fit into integer / bigint you can also just cast:

SELECT * FROM t WHERE amount <> amount::bigint;

此四舍五入为整数，与上述不同。

This rounds to full numbers, unlike the above.

已在PostgreSQL 9.1.7中进行了测试。临时表中有10k个数字数字有两个小数位，大约1％的临时表具有 .00 。

Tested with PostgreSQL 9.1.7. Temporary table with 10k numeric numbers with two fractional digits, around 1% have .00.

CREATE TEMP TABLE t(amount) AS
SELECT round((random() * generate_series (1,10000))::numeric, 2);

在我的情况下，正确的结果是：9890行。通过 EXPLAIN ANALYZE 进行10次运行的最佳时间。

Correct result in my case: 9890 rows. Best time from 10 runs with EXPLAIN ANALYZE.

Erwin 1

SELECT count(*) FROM t WHERE amount <> trunc(amount)          -- 43.129 ms

mvp 2 / qqx

SELECT count(*) FROM t WHERE amount != round(amount)          -- 43.406 ms

Erwin 3

SELECT count(*) FROM t WHERE amount <> amount::int            -- 43.668 ms

mvp 1

SELECT count(*) FROM t WHERE round(amount,2) != round(amount) -- 44.144 ms

Erwin 4

SELECT count(*) FROM t WHERE amount <> amount::bigint         -- 44.149 ms

Erwin 2

SELECT count(*) FROM t WHERE amount <> floor(amount)          -- 44.918 ms

Nandakumar V

SELECT count(*) FROM t WHERE amount - floor(amount) > .00     -- 46.640 ms

在Postgres 12中大多数情况下仍然适用（现在所有内容的速度都快10倍以上）。用100k行而不是10k行进行测试：

db<> fiddle 这里

Mostly still true in Postgres 12 (except everything's > 10x faster now). Test with 100k rows instead of 10k:
db<>fiddle here

这篇关于在PostgreSQL小数点后获取非零的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！