我有一些复杂的表，我需要对其进行一些 SQL 查询构建/优化。目前很多用于获取我们需要的结果的逻辑都是在应用层完成的，由于全表遍历等导致性能很差。SQL不是我的强项，所以我想我会达到到 SO 人群中看看是否有人可以伸出援手。

基础设施背景:

1. 数据库是MySQL5
2. 我们使用 Java 通过 Hibernate 访问这些数据
3. 这些表格的大部分内容都是相对静态的，“销售人员每小时绩效”表格除外，该表格包含一行，表示给定销售人员每天的每个小时处于事件状态(例如，调用或接听电话) 以及该销售员一整天表现的运行记录。考虑到相关公司的销售人员数量，该表每天可以增长 20K+ 行。

数据对象

我创建了一个合并了相关数据的表格设置的简化版本。 “真实”的表有大约20家公司、300个部门、2万名销售人员，以及数百万条销售人员绩效数据记录。

CREATE TABLE  `so_test`.`company` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

INSERT INTO company VALUES (7, 'CompanyXX');

CREATE TABLE  `so_test`.`division` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  `campanyId` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

INSERT INTO division VALUES (17, 'APAC #1');

CREATE TABLE  `so_test`.`salesperson` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `divisionId` int(10) unsigned NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=213860 DEFAULT CHARSET=latin1;

INSERT INTO salesperson VALUES (213859, 'bob jones');

CREATE TABLE  `so_test`.`salesperson_hourly_performance` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `timestamp` DATETIME NOT NULL,
  `salesPersonId` int(10) unsigned NOT NULL,
  `callsInBound` int(10) unsigned NOT NULL,
  `callsOutBound` int(10) unsigned NOT NULL,
  `issuedOrders` int(10) unsigned NOT NULL,
  `salesRevenue` decimal(10,4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=552395 DEFAULT CHARSET=latin1;

INSERT INTO salesperson_hourly_performance VALUES (552394, '2009-05-03 22:00:00', 213859, 15, 17, 14, 10798.0478),
(551254, '2009-05-03 21:00:00', 213859, 14, 16, 13, 9802.3620),
(551115, '2009-05-03 20:00:00', 213859, 13, 14, 12, 9183.8250),
(550072, '2009-05-03 19:00:00', 213859, 11, 13, 11, 8490.8678),
(549613, '2009-05-03 18:00:00', 213859, 10, 11, 9, 7230.1125),
(549389, '2009-05-03 17:00:00', 213859, 9, 10, 8, 6486.2173),
(548861, '2009-05-03 16:00:00', 213859, 7, 9, 7, 5537.8553),
(548059, '2009-05-03 15:00:00', 213859, 6, 8, 6, 4663.8469),
(547466, '2009-05-03 14:00:00', 213859, 5, 7, 5, 4082.6388),
(546729, '2009-05-03 13:00:00', 213859, 4, 6, 4, 3057.7368),
(546611, '2009-05-03 12:00:00', 213859, 3, 5, 2, 1751.6135),
(545642, '2009-05-03 11:00:00', 213859, 2, 4, 2, 1751.6135),
(545558, '2009-05-03 10:00:00', 213859, 1, 3, 0, 0.0000),
(545072, '2009-05-03 09:00:00', 213859, 1, 2, 0, 0.0000),
(565071, '2009-05-04 13:00:00', 213859, 19, 17, 6, 4200.1710),
(575070, '2009-05-06 14:00:00', 213859, 0, 2, 1, 120.0000);

业务需求:

1. 填充一组基于网络的销售业绩“仪表板”用户界面，为公司、部门和个人销售人员提供单独的业绩概览。
2. 用户界面在很大程度上彼此相似，除了数据集:“公司”仪表板汇总了公司每个部门的所有销售人员的所有数据，并为每个公司输出一行，而部门仪表板则用于特定公司聚合该部门每个销售人员的数据，每个部门一行。

3. 用户界面允许用户为报告仪表板选择日期范围并按任何列排序。显示的列包括:

   (公司|部门|销售人员)姓名、发出的订单总数、销售收入总额、呼入电话总数、呼出电话总数。

我的问题/对 SO 的请求:

“传统”方法(当输出到日报时，这是可耻的，但有点勉强可以接受)是以编程方式迭代每个相关对象(例如，每个销售人员)的性能数据公司的一个部门)，找到指定日期范围内每一天的“最后一个”数据，并对数据求和。但是，考虑到庞大的数据集和在 UI 中“实时”呈现此数据的需要，我需要有关如何针对此数据集构建高效 SQL 查询的指导/示例，以允许分页和排序。

1. 请哪位好心人告诉我一个合理的查询，该查询获取给定日期范围内每个销售人员绩效数据列的总和(请记住，对于每一天，用于总和的行是该销售人员当天的最后一个)。

2. 对一定范围的销售人员(例如，给定公司的所有销售人员)执行查询 #1 并支持对特定列进行分页和排序的查询？

我希望我提供了足够的详细信息来阐明我的问题...如果您需要任何其他信息，请告诉我。

非常感谢SO SQL大神!

更新:

添加了 salesPerson -> 部门和部门 -> 公司缺少的键。此外，将“timestamp”的数据类型固定为 DATETIME 而不是 VARCHAR。

最佳答案

不知道 Company 和 Division 在哪里加入...但这里是:

select
    c.name as company,
    d.name as division,
    s.name as salesperson,
    sum(h.callsinbound) as callsinboundsum,
    sum(h.callsoutbound) as callsoutboundsum,
    sum(h.issuedorders) as issuedorderssum,
    sum(h.revenue) as revenuesum
from
    sales_history_performance h
    inner join
        (select
            th.salespersonid,
            date(th.timestamp) as my_date,
            max(th.timestamp) as max_time
        from
            sales_history_performance th
            inner join salesperson ts on
                th.salespersonid = ts.id
        where
            th.timestamp between '5/1/2009' and '5/3/2009' --inclusive in MySQL
        group by
            th.salespersonid,
            date(th.timestamp)
        ) t on
      h.salespersonid = t.salespersonid
      and h.timestamp = t.max_time
    inner join salesperson s on
        h.salespersonid = s.id
    inner join division d on
        s.divisionid = d.id
    inner join company c on
        d.companyid = c.id
group by
    c.name,
    d.name,
    s.name
order by 1,2,3

您可以编辑注释掉的 and sp.name like '%' 行，以添加您需要的任何销售人员过滤器。

因此，它所做的是:它出去并构建一个包含每天最高时间戳的表。如果 sales_history_performance 中的 ID 对于以后的条目确实较大，请使用它，因为您不太可能获得重复项。不管怎样，然后它将每个销售人员加入到总结所有指标列的表格中。如果您想获得公司范围内的号码，您可以将销售人员从外部查询中取出。这样，它将返回所有销售人员。

更新:我添加了公司和部门。这是一个非常通用的查询。如果您想限制部门/公司/销售人员，您可以在外部查询的 WHERE 子句中这样做，尽管您可以通过在内部查询中这样做来获得一些性能提升——这只是一个有点难维护。

关于sql - MySQL查询帮助: how to deal with data in most-recent-row-per-day from a big dataset，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/988982/