由网友(情话烫舌)分享简介：店主表有以下字段：id (bigint),amount (numeric(19,2)),createddate (timestamp)让我们说，我有上面的表格。我想昨天的记录和生成的报告具有打印分钱的金额。Let's say, I have the above table. I want to get the...

店主表有以下字段：

id (bigint),amount (numeric(19,2)),createddate (timestamp)

让我们说，我有上面的表格。我想昨天的记录和 生成的报告具有打印分钱的金额。

Let's say, I have the above table. I want to get the records for yesterday and generate a report by having the amount printed to cents.

做的一种方法是在我的Java应用程序进行计算并执行一个简单的查询

Date previousDate ;// $1 calculate in application

Date todayDate;// $2 calculate in application

select amount where createddate between $1 and $2 

和然后通过记录回路和转换量美分在我的Java应用程序，并生成报告

and then loop through the records and convert amount to cents in my java application and generate the report

另一种方式是像SQL查询本身进行计算：

select cast(amount * 100 as int) as "Cents"
from shopkeeper  where createddate  between date_trunc('day', now()) - interval '1 day'  and  date_trunc('day', now())

和然后通过记录回路和生成报告

and then loop through the records and generate the report

在一个办法，我所有的处理都在Java应用程序和一个简单的查询被触发。 在其他情况下，所有的转换和计算在SQL查询完成。

In one way , all my processing is done in java application and a simple query is fired. In other case all the conversions and calculations is done in Sql query.

上面用例仅仅是一个例子，在实际的方案中的表可以具有所需要的类似种类的处理许多列

The above use case is just an example, in a real scenario a table can have many columns that require processing of the similar kind.

能否请你告诉我，这方法是在性能方面和其他方面，为什么好？

推荐答案

这取决于很多因素 - 但最关键的是：

It depends on a lot of factors - but most crucially:

在复杂的计算（preFER做复杂的一手交钱一手交货的应用程序服务器上，因为这秤的出的;而不是一个数据库服务器，该服务器扩展的了） 在数据量（如果您需要访问/聚合了大量的数据，做它在数据库服务器将节省带宽和磁盘IO如果能聚集索引内完成） 在方便（SQL并不是复杂的工作最好的语言 - 尤其是不太适合的程序性工作，但非常好，集为基础的工作，糟糕的错误处理，虽然） complexity of calculations (prefer doing complex crunching on an app-server, since that scales out; rather than a db server, which scales up) volume of data (if you need to access/aggregate a lot of data, doing it at the db server will save bandwidth, and disk io if the aggregates can be done inside indexes) convenience (sql is not the best language for complex work - especially not great for procedural work, but very good for set-based work; lousy error-handling, though)

与往常一样，如果你的执行的将数据返回到应用服务器，最大限度地减少行和列将是你的优势。确保查询被调整并适当索引将有助于这两种情况。

As always, if you do bring the data back to the app-server, minimising the columns and rows will be to your advantage. Making sure the query is tuned and appropriately indexed will help either scenario.

回复您注意：

然后通过记录回路

循环的通过记录几乎总是错的事情在SQL - 编写一个基于集合的操作是preferred

Looping through records is almost always the wrong thing to do in sql - writing a set-based operation is preferred.

作为一般规则的，我preFER保持数据库的工作到最低限度存储这些数据，读取这些数据 - 然而，总有一些场景例子，一个优雅的查询在服务器可以节省大量的带宽。

As a general rule, I prefer to keep the database's job to a minimum "store this data, fetch this data" - however, there are always examples of scenarios where an elegant query at the server can save a lot of bandwidth.

还认为：如果这是计算成本，可以在某处缓存

Also consider: if this is computationally expensive, can it be cached somewhere?

如果你想要一个的准确的哪个更好; code这两种方法，并进行比较（请注意，无论是初稿可能不是100％调整）。但是因素的典型用法是：如果在现实中，它被立刻叫5次（另发），然后模拟的是：不比较只是一个单一的1对这些VS 1的那些

If you want an accurate "which is better"; code it both ways and compare it (noting that a first draft of either is likely not 100% tuned). But factor in typical usage to that: if, in reality, it is being called 5 times (separately) at once, then simulate that: don't compare just a single "1 of these vs 1 of those".