mysql - 改进的表结构可查询季度支出数据

Question

我正在为我的公司工作的一个项目寻找关于最有效的表结构/SQL代码的建议。我不是一个专业的开发人员，所以一个简单的解决方案是可取的。
项目要求：
我想创建一个HTML表，按会计季度显示按供应商类别列出的合计支出，其中每个类别都是一个链接，可以单击该链接进行扩展，以获取供应商级别x业务部门级别的支出。
我已经准备好了。我用PHP和MySQL构建。但是，在某些情况下，我运行的扩展表的查询运行非常缓慢。我想彻底检查我的表结构和查询，使整个过程更加高效和灵活。
我所掌握的数据摘要
我每季度从每个业务部门获取原始数据，按供应商列出支出。我还有一个解码器环，它将供应商名称与供应商类别相匹配（例如星巴克->餐厅）。
我的当前进程
我将季度数据汇总到一个名为spendfile的表中，该表包含以下列：
供应商名称
标准供应商名称（更正供应商名称中的拼写错误等）
供应商类别
季度
花费
当用户（第一次）请求查看分类位于下的HTML表，并按季度在单独的列中花费时，我创建了一个名为treetable的临时表，其中包含以下列：
rowlabel（显示在表下面的类别/供应商的名称）
spendQ1（每个季度与供应商的支出分开列）
开支2
开支3
支出4
花费。。。
请注意，为了得到这个表，我需要选择所有唯一的供应商类别，然后按季度对每个季度的支出进行求和，最后将每个季度的季度总支出加入到类别列表中（因此我要进行5-6个联接，每个时间段1个联接）。
然后我呈现为html表（相当简单）
当用户单击某个类别以获取更多详细信息时，我将经历一个类似的过程：获取唯一的供应商列表、汇总花费（这次是针对特定类别中的所有供应商）并将所有时段作为单独的列连接。
最后一步是将新的向下钻取的数据合并到右边的treetable表（在所有供应商所属的类别下面）。
有人对更好的方法有什么想法吗？我想做的有意义吗？
谢谢

Answer 1

在不了解模式的情况下，很难提供具体的建议（比如可能使用的SQL）。不过，我会努力的。这里有一些一般性的建议。
试着“非常缓慢”地量化，这样你就可以知道你是否在改进东西。同时量化你处理的数据量。
只有当你确定你需要临时表时才使用它们DBMS中最昂贵（==慢）的事情之一是将数据写入表。如果你不需要临时表，就不要用它们。
如果可以，请创建一个SQL查询，为希望从原始数据以HTML格式显示的表生成正确的列和行。然后，在命令行mySQL客户机中运行该查询，并使用EXPLAIN命令让mySQL告诉您它要做什么。请看这里：http://dev.mysql.com/doc/refman/5.0/en/explain.html
下面是关于该查询如何工作的一些更具体的建议。假设您的输入表有这个模式。

vendorname
standard_vendor_name (corrects misspells in vendor name, etc.)
vendor_category (character string)
quarter (integer)
spend  (floating point)

假设结果是需要一个包含这些列的表

vendor_category
vendor (standardized spelling)
spend_q1
spend_q2
spend_q3
spend_q4

很抱歉，我不理解您关于如何在输出中处理供应商名称和类别的问题。我假设您只是简单地显示类别，然后显示每个供应商的名称，并按类别排序。
我也不明白您是如何将 quarter存储在输入表中的。假设你在2011年第1季度到2012年第2季度使用了类似于20111、20112、20113、20114、20121、20122的数字格式。
假设您想在html表中显示任意四个连续的季度，只是为了笑一笑。
所以，我们开始吧。
我们需要一个小的子查询来生成您需要的四分之一ID，它基于您要显示的最近一个季度的ID——您需要提供这个ID。

SELECT DISTINCT QUARTER
  FROM INFO
 WHERE QUARTER <= ~~~the most recent quarter~~~
 ORDER BY QUARTER DESC
 LIMIT 0,4

如果你总是从最近的季度开始，你可以简单地从这个小的子查询中去掉 WHERE QUARTER <= ~~~the most recent quarter~~~，它将收集你最近的数据。
其次，我们需要将季度子查询列表构建为子查询，以生成最新季度的数据。

SELECT I.QUARTER, I.VENDOR_CATEGORY, I.STANDARD_VENDOR_NAME, SUM(I.SPEND) SPEND
  FROM INFO I
  JOIN (        
    SELECT DISTINCT QUARTER
      FROM INFO
     WHERE QUARTER <= ~~~the most recent quarter~~~
     ORDER BY QUARTER DESC
     LIMIT 0,1
 ) Q ON I.QUARTER=Q.QUARTER
 GROUP BY I.QUARTER, I.VENDOR_CATEGORY, I.STANDARD_VENDOR_NAME

这是我们基本的季度查找构建块。在这一点上，很明显，您需要您的INFO表在QUARTER和VENDOR_CATEGORY列上有索引。
您可能需要这两个方面的复合索引，甚至（季度、供应商类别、标准供应商名称）都需要。但要让事情运转起来。然后看看解释输出。然后尝试添加其他索引。在继续之前，有必要修改索引来优化这个构建块查询。
我们还需要三个子查询，每个季度一个子查询。除了 LIMIT 0,1、 LIMIT 1,1、 LIMIT 3,1和 LIMIT 4,1之外，子查询与构建基块相同。
我们还需要显示所有供应商类别和标准供应商名称组合的主列表。此查询将弹出您正在考虑的任何季度中出现一次或多次的任何类别/供应商组合的结果。

SELECT DISTINCT I.VENDOR_CATEGORY, I.STANDARD_VENDOR_NAME
  FROM INFO I
  JOIN (        
    SELECT DISTINCT QUARTER
      FROM INFO
     WHERE QUARTER <= ~~~the most recent quarter~~~
     ORDER BY QUARTER DESC
     LIMIT 0,4
 ) Q ON I.QUARTER=Q.QUARTER 

不要做左加入这个项目，否则你会得到你所有的类别/供应商项目，包括那些你最近四个季度没有花过钱的项目。
现在我们需要把这一切放在一起。事情变得荒谬冗长（难道SQL不好玩吗？）我们必须把这些建筑块连接起来。这是我们的大查询的概要，带有注释以显示构建基块的位置。

SELECT A.VENDOR_CATEGORY, A.STANDARD_VENDOR_NAME, Q.SPEND, R.SPEND, S.SPEND, T.SPEND
  FROM (
     /* category combinations */
  )A
  LEFT JOIN (
     /* most recent quarter spend */
  )Q ON (           A.VENDOR_CATEGORY=Q.VENDOR_CATEGORY 
                AND A.STANDARD_VENDOR_NAME=Q.STANDARD_VENDOR_NAME)
  LEFT JOIN (
     /* second most recent quarter spend */
  )R ON (           A.VENDOR_CATEGORY=R.VENDOR_CATEGORY
                AND A.STANDARD_VENDOR_NAME=R.STANDARD_VENDOR_NAME)
  LEFT JOIN (
     /* third most recent quarter spend */
  )S ON (           A.VENDOR_CATEGORY=S.VENDOR_CATEGORY
                AND A.STANDARD_VENDOR_NAME=S.STANDARD_VENDOR_NAME)
  LEFT JOIN (
     /* fourth most recent quarter spend */
  )T ON (           A.VENDOR_CATEGORY=T.VENDOR_CATEGORY
                AND A.STANDARD_VENDOR_NAME=T.STANDARD_VENDOR_NAME)
ORDER BY A.VENDOR_CATEGORY, A.STANDARD_VENDOR_NAME

我将留给您将子查询插入到这个大纲中。
您可能已经使用了类似的方法来生成临时表。但是如果你做对了，你可以简单地使用这个大查询来生成你的报告。除非info表有大量的行，否则如果您正确地索引表，它将运行得相当快。
如果您确实拥有大量的行，那么您可能在一家大公司工作，该公司可以为一个更大的mySQL服务器提供快速的磁盘和多个千兆字节的RAM。这也会加速事情的发展，特别是在你编制索引之后。