My application is a kind of POS system. The problem is with reports. Sales per product, per table, per staff,categories. Having 1 year date range reports are very slow because they have to sum lots of rows etc. So i am wondering if a no-sql database could help ,like having summaries per day or something.. But maybe its not easy because there could be for each item * products * categories * staff etc query. SO what could i do ?
我的应用程序是一种POS系统。问题在于报告。每个产品，每个表，每个员工，类别的销售额。有1年的日期范围报告是非常缓慢的，因为他们必须总结很多行等。所以我想知道一个无sql数据库是否可以帮助，如每天有什么摘要或者什么......但也许它不容易因为可能有每个项目*产品*类别*员工等查询。那我该怎么办？
1 个解决方案
#1
5
If you're comfortable with relational databases, I'd recommend sticking with them, and using daily aggregate tables for the reports you commonly use.
如果您对关系数据库感到满意，我建议您坚持使用它们，并将日常聚合表用于您常用的报告。
For example, if like to do sales reports grouped by product numbers, figure out what stats you're looking for (ie. quantity sold) and aggregate your raw data into day sized "buckets" by product number.
例如，如果要按产品编号分组销售报告，请计算出您要查找的统计数据（即销售数量），并按产品编号将原始数据汇总到日期大小的“桶”中。
+-----------+------------+------------+-------+-------+
| salesdate | productNum | totalSales | stat2 | stat3 |
+-----------+------------+------------+-------+-------+
If you do day-sized buckets at the end of every day, you will only have 30 buckets per month for your report, or 365 buckets per year. Much faster to summarize. I've done this with network performance metrics when building out dashboards (hour-sized buckets), and it greatly reduces query time. You can always dig into the raw data if need be, but for the average user who wants to see something at a glance, the aggregated buckets are enough.
如果您在每天结束时使用日间大小的水桶，您的报告每月只会有30个水桶，或者每年只有365个水桶。总结要快得多。在构建仪表板（小时存储桶）时，我已经使用网络性能指标完成了这项工作，并且大大缩短了查询时间。如果需要，您可以随时深入了解原始数据，但对于希望一目了然的普通用户来说，聚合桶就足够了。
You may also consider putting the summary tables in a separate database.
您还可以考虑将摘要表放在单独的数据库中。
Just keep in mind, if one of your stats in an average, the average of a series of averages is not the average for the overall range.
请记住，如果您的平均值之一，一系列平均值的平均值不是整个范围的平均值。
#1
5
If you're comfortable with relational databases, I'd recommend sticking with them, and using daily aggregate tables for the reports you commonly use.
如果您对关系数据库感到满意，我建议您坚持使用它们，并将日常聚合表用于您常用的报告。
For example, if like to do sales reports grouped by product numbers, figure out what stats you're looking for (ie. quantity sold) and aggregate your raw data into day sized "buckets" by product number.
例如，如果要按产品编号分组销售报告，请计算出您要查找的统计数据（即销售数量），并按产品编号将原始数据汇总到日期大小的“桶”中。
+-----------+------------+------------+-------+-------+
| salesdate | productNum | totalSales | stat2 | stat3 |
+-----------+------------+------------+-------+-------+
If you do day-sized buckets at the end of every day, you will only have 30 buckets per month for your report, or 365 buckets per year. Much faster to summarize. I've done this with network performance metrics when building out dashboards (hour-sized buckets), and it greatly reduces query time. You can always dig into the raw data if need be, but for the average user who wants to see something at a glance, the aggregated buckets are enough.
如果您在每天结束时使用日间大小的水桶，您的报告每月只会有30个水桶，或者每年只有365个水桶。总结要快得多。在构建仪表板（小时存储桶）时，我已经使用网络性能指标完成了这项工作，并且大大缩短了查询时间。如果需要，您可以随时深入了解原始数据，但对于希望一目了然的普通用户来说，聚合桶就足够了。
You may also consider putting the summary tables in a separate database.
您还可以考虑将摘要表放在单独的数据库中。
Just keep in mind, if one of your stats in an average, the average of a series of averages is not the average for the overall range.
请记住，如果您的平均值之一，一系列平均值的平均值不是整个范围的平均值。