MySQL查询帮助：如何处理大数据集中每天最新一行的数据

Question

MySQL查询帮助：如何处理大数据集中每天最新一行的数据

sqlmysqlperformanceoptimization

4

我有一些比较复杂的表格需要进行SQL查询构造/优化。目前，我们获取所需结果所使用的大部分逻辑都在应用程序层面完成，这导致了可怕的性能问题，因为需要全表遍历等操作。由于SQL不是我的强项，所以我想向SO社区寻求帮助。

基础设施背景：

DB是MySQL5
我们使用Java通过Hibernate访问这些数据
这些表中的大多数内容都是相对静态的，除了“销售人员每小时表现”表格外，该表格包含给定销售人员活跃时间（例如，已打出或接收电话）的每个小时的一行，并显示该销售人员在整个一天内的绩效“累计”。考虑到涉及的公司销售人员数量，该表格每天可能会增加20K+行。

数据对象

我创建了一个简化版本的表格设置，其中包括相关数据。 “真正”的表格中有约20家公司，300个部门，20K名销售人员和数百万条销售人员绩效数据记录。

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);

业务需求：

填充一组基于Web的销售绩效“仪表板”UI，为公司、部门和个人销售人员提供单独的绩效概述。
这些UI在很大程度上相似，除了数据集： “公司”仪表板汇总每个公司部门所有销售人员的数据，并输出每个公司的一行；而特定公司的“部门”仪表板汇总该部门中每个销售人员的数据，并输出每个部门的一行。
这些UI允许用户选择报告仪表板的日期范围，并按任何列进行排序。显示的列包括：

(公司|部门|销售人员)名称、总下达订单数、总销售收入、总呼入电话数、总呼出电话数。

我的问题/请求：

“传统”的方法（当输出为日报时有点可耻但还算可以接受）是通过编程迭代相关对象的性能数据（例如，公司部门中的每个销售人员），找到指定日期范围内每天的“最后一个”，并对数据进行求和。然而，考虑到海量数据集以及需要在UI中“实时”呈现此数据，我需要指导/示例来构建针对此数据集的高效SQL查询，以允许分页和排序。

是否有善良的灵魂能够展示一个合理的查询，该查询获取给定日期范围内每个销售人员绩效数据列的总和（请记住，对于每一天，用于求和的行是该销售人员当天的最后一行）。
执行查询＃1，涵盖一系列销售人员（例如，给定公司中的所有销售人员），并支持特定列的分页和排序的查询？

希望我已经包含了足够的细节来清楚地表明我的要求……如果您需要任何其他信息，请告诉我。

非常感谢SO SQL大神们！

更新：

从salesPerson -> division和division -> company添加了缺失的键。此外，修正了“时间戳”的数据类型为DATETIME而不是VARCHAR。

- DarkSquid

感谢大家迄今为止的帮助 - 我已经修复了缺少的外键并修复了时间戳数据类型（它实际上是日期而不是 varchar）。 - DarkSquid

2个回答

0

记住，对于每一天，用于求和的行是该天按日期倒数的最后一行，针对该销售人员)。这个信息很难理解。我在想你是否在说，一天的总和存储在销售员每小时表中，在同一个表中混合了日汇总和小时汇总。您的示例与部门和公司没有关系。但是，为了分解给定日期范围内每个人每天的销售额:

select s.name,substring(timestamp,1,11) as day,sum(callsInBound),sum(callsOutBound),sum(issuedOrders),sum(salesRevenue) 
from salesperson_hourly_performance facts , salesperson s  
where facts.salesPersonId = s.id and  timestamp >= "2009-05-03 00:00:00" and timestamp < "2009-05-07 00:00:00" 
group by s.name,day 
order by day asc;
+-----------+-------------+-------------------+--------------------+-------------------+-------------------+
| name      | day         | sum(callsInBound) | sum(callsOutBound) | sum(issuedOrders) | sum(salesRevenue) |
+-----------+-------------+-------------------+--------------------+-------------------+-------------------+
| bob jones | 2009-05-03  |               101 |                125 |                93 |        72836.7372 |
| bob jones | 2009-05-04  |                19 |                 17 |                 6 |         4200.7100 |
| bob jones | 2009-05-06  |                 0 |                  2 |                 1 |          120.0000 |
+-----------+-------------+-------------------+--------------------+-------------------+-------------------+

将时间戳存储为实际的时间戳/日期时间类型，可以更轻松地处理日期和时间。如果确实必须使用varchar列，则有mysql函数可将字符串转换为日期时间，这可能有助于您的查询。

编辑，我真的不会在此表中混合粒度。保留一张表用于日总结，另一张表用于小时。

如果您只需要每天具有最大日期的行，请使用例如

SELECT   p.name,
         Substring(TIMESTAMP,1,11) AS DAY,
         Sum(callsinbound),
         Sum(callsoutbound),
         Sum(issuedorders),
         Sum(salesrevenue)
FROM     (SELECT   sh.salespersonid,
                   Substring(sh.TIMESTAMP,1,11) AS DAY,
                   Max(TIMESTAMP)               AS max_ts
          FROM     salesperson_hourly_performance sh
          GROUP BY sh.salespersonid,
                   DAY) t
         INNER JOIN salesperson_hourly_performance shp
           ON t.salespersonid = shp.salespersonid
              AND t.max_ts = shp.TIMESTAMP
         INNER JOIN salesperson p
           ON shp.salespersonid = p.id
GROUP BY p.name,
         DAY;

在需要的地方添加where子句，例如第一个查询

- nos

-1：这将总结当天的每一行。它只需要汇总那天最新时间戳的行（据推测，数据是累积余额）。 - Eric

很抱歉表达不够清晰。我试图/未能说明的是，对于任何给定日期，将会有X行销售员小时绩效数据。应该使用的是当天的“最后一个”（例如，最接近23:59:59的）。可以忽略此查询目的之外的其他行。另外，我已更新了表格，因为时间戳确实是DATETIME类型。我会尝试理解这个 - 感谢您的帮助！ - DarkSquid

这里的最后一个查询基本上就是Eric写的，我猜。直到现在才看到。 - nos

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Eric · Accepted Answer

不知道公司和部门在哪里加入...但是这里是：

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子句中执行，虽然在内部查询中执行可能会带来一些性能提升，但维护起来有点困难。