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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL查询帮助：如何处理大数据集中每天最近一行的数据，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5458字，纯文字阅读大概需要8分钟。

内容图文

我有一些有点复杂的表,我需要做一些SQL查询构造/优化.目前用于获取我们需要的结果的很多逻辑正在应用层完成,由于全表遍历等导致性能糟糕.SQL不是我的强项,所以我想我会达到向SO人群展示是否有人可以伸出援助之手.

基础设施背景

> DB是MySQL5
>我们使用Java通过Hibernate访问这些数据
>这些表格的大部分内容都是相对静态的,“销售人员 – 每小时表现”表格除外,该表格包含每天每小时给定销售人员有效的行(例如,已拨打或接听电话)这个销售人员一整天的表现的运行记录.鉴于有问题的公司的销售人员数量,这个表每天可以增长20,000行.

数据对象

我已经创建了表格设置的简化版本,其中包含了相关数据. “真实”表格包含大约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的销售业绩“仪表板”用户界面,为公司,部门和各个销售人员提供单独的绩效概览.
>除了数据集之外,用户界面大致相似：“公司”仪表板汇总每个公司部门中所有销售人员的所有数据,并为每个公司输出一行,而特定公司的部门仪表板汇总该部门中每个销售人员的数据和每个部门的一行.
>用户界面允许用户为报告仪表板选择日期范围,并按任何列进行排序.显示的列包括：

(公司|部门|销售人员)名称,已发布订单总数,总销售收入,呼入总计,呼出总计.

我的问题/请求SO：

“遗产”方法(当输出到每日期刊时是可耻但有点可接受的)是以编程方式迭代每个相关对象的性能数据(例如,分部中的每个销售人员)公司),在指定日期范围内的每个给定日期找到“最后一个”,并对数据求和.但是,鉴于海量数据集以及需要在UI中“实时”呈现此数据,我需要有关如何针对此数据集构建高效SQL查询的指导/示例,这将允许分页和排序.

>某种灵魂请给我一个合理的查询,它可以获得给定日期范围内每个销售人员绩效数据列的总和(请记住,对于每一天,用于总和的行是最后一个那天的销售人员的日期.
>对一系列销售人员(例如,给定公司中的所有销售人员)执行查询#1的查询,该查询支持特定列的分页和排序？

我希望我已经包含了足够的细节以明确我的要求…如果您需要任何其他信息,请告诉我.

非常感谢SO SQL众神！

更新：

从salesPerson添加了缺失的密钥 – >分裂&来自分部 – >公司.此外,固定数据类型“timestamp”为DATETIME而不是VARCHAR.

解决方法:

不知道公司和部门加入的地方……但这里是：

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

您可以编辑和注释掉的’％’行之类的sp.name,以添加您需要的任何销售人员过滤器.

这样做是这样的：它会在每天的时间内建立一个最佳时间戳表.如果sales_history_performance中的ID对于以后的条目可靠地更大,请使用它,因为您不太可能获得重复项.无论如何,它将每个销售人员加入到表中,总结了所有度量标准列.如果您想获得公司范围的号码,您可以将销售人员从外部查询中取出.就像这样,它将归还所有销售人员.

更新：我在公司和部门添加.这是一个非常通用的查询.如果你想限制部门/公司/销售人员,你可以在外部查询的WHERE子句中这样做,尽管你可以在内部查询中获得一些性能提升 – 它只是一个有点难以维持.

内容总结

以上是互联网集市为您收集整理的MySQL查询帮助：如何处理大数据集中每天最近一行的数据全部内容，希望文章能够帮你解决MySQL查询帮助：如何处理大数据集中每天最近一行的数据所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---