计算大型表上的任何数据的最佳选择是什么？
场景如下：
有一个包含数百个企业的表，假设每个企业都创建自己的每日数据（100 到 200 行）。
例如，每个企业每天收到 150 个订单。这些数据记录在数据库中。
每个月底，由于企业创建的数据规模会扩大数据库，我认为企业的统计报表结果会更长。
例如，A 公司在月底生成了 3000 行数据，而 B 公司生成了 4000 行数据。
到年底，A公司生产的行数为36000行，B公司生产的行数为48000行。
当企业想看到自己每月的收入和销售额时，却找不到。对于他们来说，逐一浏览 84000 行的数据库并计算所需的数据是没有意义的。因为我只举了两个企业的例子，想象一下如果有数百个企业怎么办？
我想到了一个解决方案，我想咨询一个合乎逻辑的解决方案，因为我不知道在大型应用程序中如何制定解决方案。
当企业每天生成数据时，我可以通过计算他们生成的数据并将其保存在单独表中的单行中来每天更新此统计报告。
到了月底，我认为他可以快速找到相关行，而不必滚动数千行。
计算大型表上的任何数据的最佳选择是什么？
The scenario is as follows:
There is a table with hundreds of businesses, let's say each business creates its own daily data of 100 to 200 rows.
For example, each business receives 150 orders per day. These data are recorded in the database.
At the end of each month, since the size of the data created by the enterprises will enlarge the database, I think the result of the statistical reporting of a business will be longer.
For example, firm A produced 3000 rows of data at the end of the month, while firm B produced 4000 rows.
At the end of the year, the number of rows produced by Company A will be 36000, and the number of rows produced by Company B will be 48000.
When enterprises want to see their monthly earnings and how many sales they make, it doesn't make sense for them to navigate through a database of 84000 rows one by one and calculate the desired data. Because I only gave this example for two businesses, imagine if there were hundreds?
I thought of a solution to this, I want to consult a logical solution, because I do not know how solutions are made in large applications.
While businesses are producing daily data, I can update this statistical report every day by calculating the data they produce and keeping it in a single row in a separate table.
At the end of the month, I think that he can quickly find the relevant line without having to scroll through thousands of lines.
What is the best option for calculating any data on large tables?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在这个计划中，84000 条记录是一个表，无需担心，您可以使用大多数 RDBM 数据库引擎来处理它。如果您正在谈论数百万条记录，那么您很可能想要查看数据仓库策略。 SQL Server 有一个叫做 Analysis Services 的东西，它可以获取你的数据并创建预先计算的聚合，以便报告计算更快，我希望大多数数据库供应商都会有类似的东西。
对于原始数据库将使用的事务处理（OLTP - 在线事务处理），您需要第三范式的数据。出于分析目的（OLAP - 在线分析处理），您需要非规范化数据，因为如果您不需要跨越多个表，聚合可以更快地完成。客户->区域->区域和变体->产品->类型->超类型。
In the scheme of things 84000 records is a table is nothing to worry about, you can process that just fine with most RDBMs database engines. If you are talking about millions of records, then you most likely want to look at Data Warehouse strategies. SQL server has something called Analysis Services which can take your data and create pre-calculated aggregates so that report computations are faster, I expect most database vendors will have something similar.
For transacction processing which is what your original database will use (OLTP - Online Transaction Processing) you want data in 3rd Normal Form. For analytical purposes (OLAP - Online Analystical Processing) you want de-normalised data as the aggregations can be done faster if you don't need to jump across multiple tables. Customer->territory->Region and variant->product->type->supertype.