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

问题描述

我有一个包含 Numeric 类型的金额字段的表格.它包含不同的金额值.例如

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 是精确的！

与另一个答案所声称的不同，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 个 numeric 数字和两个小数，大约 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.

埃尔文 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

欧文 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

欧文 4

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

欧文 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 进行测试:

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

db<>fiddle 这里

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