mysql - 前20组排名查询-优化

coder 2023-10-13 原文

我正在创建一个报告结构，我需要为每个独特的公司 - 地区输出前 20 天的汇总统计数据。我已经完成了这个任务，但觉得我的代码过于复杂，我请求帮助优化它。

我在这个过程中涉及 2 个表。第一个列出所有可能的公司 - 地区 - 集团 - 子集团。第二个具有按组 - 子组划分的每小时统计数据。

SQL Fiddle 链接:http://sqlfiddle.com/#!9/29a7b/1
注意:目前正在获取 SELECT command denied to user '<user>'@'<ip>' for table 'table_stats'我的 SQL Fiddle 上的错误，也希望能帮助解决这个问题。

table_companies 声明和虚拟数据:

CREATE TABLE `table_companies` (
  `pk_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company` varchar(45) NOT NULL,
  `region` varchar(45) NOT NULL,
  `group` varchar(45) NOT NULL,
  `subgroup` varchar(45) NOT NULL,
  PRIMARY KEY (`pk_id`),
  UNIQUE KEY `pk_id_id_UNIQUE` (`pk_id`)
);

INSERT INTO table_companies
    (`pk_id`, `company`, `region`, `group`, `subgroup`)
VALUES
    (1, 'company1', 'region1', 'group1', 'subgroup1'),
    (2, 'company1', 'region1', 'group1', 'subgroup2'),
    (3, 'company1', 'region2', 'group2', 'subgroup3'),
    (4, 'company1', 'region3', 'group3', 'subgroup4'),
    (5, 'company2', 'region1', 'group4', 'subgroup5'),
    (6, 'company2', 'region3', 'group5', 'subgroup6'),
    (7, 'company2', 'region3', 'group6', 'subgroup7'),
    (8, 'company2', 'region4', 'group7', 'subgroup8'),
    (9, 'company2', 'region5', 'group8', 'subgroup9'),
    (10, 'company3', 'region6', 'group9', 'subgroup10'),
    (11, 'company3', 'region7', 'group10', 'subgroup11'),
    (12, 'company3', 'region8', 'group11', 'subgroup12'),
    (13, 'company4', 'region9', 'group12', 'subgroup13'),
    (14, 'company4', 'region10', 'group13', 'subgroup14'),
    (15, 'company5', 'region11', 'group14', 'subgroup15'),
    (16, 'company5', 'region12', 'group15', 'subgroup16')
;

table_stats 声明:
简化为仅包含 1 个组(子组)每天的几个小时。

CREATE TABLE `table_stats` (
  `pk_id` int(10) unsigned NOT NULL,
  `date_time` datetime NOT NULL,
  `group` varchar(45) NOT NULL,
  `subgroup` varchar(45) NOT NULL,
  `stat` int(10) unsigned NOT NULL,
  PRIMARY KEY (`pk_id`),
  UNIQUE KEY `pk_id_UNIQUE` (`pk_id`),
  UNIQUE KEY `om_unique` (`date_time`,`group`,`subgroup`)
);

INSERT INTO table_stats
    (`pk_id`, `date_time`, `group`, `subgroup`, `stat`)
VALUES
    (1, '2015-12-01 06:00:00', 'group9', 'subgroup10', 14),
    (2, '2015-12-01 12:00:00', 'group9', 'subgroup10', 14),
    (3, '2015-12-02 06:00:00', 'group9', 'subgroup10', 2),
    (4, '2015-12-02 12:00:00', 'group9', 'subgroup10', 51),
    (5, '2015-12-03 06:00:00', 'group9', 'subgroup10', 30),
    (6, '2015-12-03 12:00:00', 'group9', 'subgroup10', 6),
    (7, '2015-12-04 06:00:00', 'group9', 'subgroup10', 9),
    (8, '2015-12-04 12:00:00', 'group9', 'subgroup10', 77),
    (9, '2015-12-05 06:00:00', 'group9', 'subgroup10', 70),
    (10, '2015-12-05 12:00:00', 'group9', 'subgroup10', 7),
    (11, '2015-12-06 06:00:00', 'group9', 'subgroup10', 38),
    (12, '2015-12-06 12:00:00', 'group9', 'subgroup10', 5),
    (13, '2015-12-07 06:00:00', 'group9', 'subgroup10', 86),
    (14, '2015-12-07 12:00:00', 'group9', 'subgroup10', 73),
    (15, '2015-12-08 06:00:00', 'group9', 'subgroup10', 45),
    (16, '2015-12-08 12:00:00', 'group9', 'subgroup10', 14),
    (17, '2015-12-09 06:00:00', 'group9', 'subgroup10', 66),
    (18, '2015-12-09 12:00:00', 'group9', 'subgroup10', 38),
    (19, '2015-12-10 06:00:00', 'group9', 'subgroup10', 12),
    (20, '2015-12-10 12:00:00', 'group9', 'subgroup10', 77),
    (21, '2015-12-11 06:00:00', 'group9', 'subgroup10', 21),
    (22, '2015-12-11 12:00:00', 'group9', 'subgroup10', 18),
    (23, '2015-12-12 06:00:00', 'group9', 'subgroup10', 28),
    (24, '2015-12-12 12:00:00', 'group9', 'subgroup10', 74),
    (25, '2015-12-13 06:00:00', 'group9', 'subgroup10', 20),
    (26, '2015-12-13 12:00:00', 'group9', 'subgroup10', 37),
    (27, '2015-12-14 06:00:00', 'group9', 'subgroup10', 66),
    (28, '2015-12-14 12:00:00', 'group9', 'subgroup10', 59),
    (29, '2015-12-15 06:00:00', 'group9', 'subgroup10', 26),
    (30, '2015-12-15 12:00:00', 'group9', 'subgroup10', 0),
    (31, '2015-12-16 06:00:00', 'group9', 'subgroup10', 77),
    (32, '2015-12-16 12:00:00', 'group9', 'subgroup10', 31),
    (33, '2015-12-17 06:00:00', 'group9', 'subgroup10', 59),
    (34, '2015-12-17 12:00:00', 'group9', 'subgroup10', 71),
    (35, '2015-12-18 06:00:00', 'group9', 'subgroup10', 7),
    (36, '2015-12-18 12:00:00', 'group9', 'subgroup10', 73),
    (37, '2015-12-19 06:00:00', 'group9', 'subgroup10', 72),
    (38, '2015-12-19 12:00:00', 'group9', 'subgroup10', 28),
    (39, '2015-12-20 06:00:00', 'group9', 'subgroup10', 50),
    (40, '2015-12-20 12:00:00', 'group9', 'subgroup10', 11),
    (41, '2015-12-21 06:00:00', 'group9', 'subgroup10', 71),
    (42, '2015-12-21 12:00:00', 'group9', 'subgroup10', 4),
    (43, '2015-12-22 06:00:00', 'group9', 'subgroup10', 78),
    (44, '2015-12-22 12:00:00', 'group9', 'subgroup10', 69),
    (45, '2015-12-23 06:00:00', 'group9', 'subgroup10', 83),
    (46, '2015-12-23 12:00:00', 'group9', 'subgroup10', 55),
    (47, '2015-12-24 06:00:00', 'group9', 'subgroup10', 71),
    (48, '2015-12-24 12:00:00', 'group9', 'subgroup10', 20),
    (49, '2015-12-25 06:00:00', 'group9', 'subgroup10', 90),
    (50, '2015-12-25 12:00:00', 'group9', 'subgroup10', 26),
    (51, '2015-12-26 06:00:00', 'group9', 'subgroup10', 1),
    (52, '2015-12-26 12:00:00', 'group9', 'subgroup10', 73),
    (53, '2015-12-27 06:00:00', 'group9', 'subgroup10', 4),
    (54, '2015-12-27 12:00:00', 'group9', 'subgroup10', 18),
    (55, '2015-12-28 06:00:00', 'group9', 'subgroup10', 4),
    (56, '2015-12-28 12:00:00', 'group9', 'subgroup10', 30),
    (57, '2015-12-29 06:00:00', 'group9', 'subgroup10', 56),
    (58, '2015-12-29 12:00:00', 'group9', 'subgroup10', 53),
    (59, '2015-12-30 06:00:00', 'group9', 'subgroup10', 33),
    (60, '2015-12-31 12:00:00', 'group9', 'subgroup10', 8)
;

要优化的查询:

SELECT * FROM
    (
    SELECT t3.company,t3.region,t3.day, t3.day_stat,COUNT(*) as rank
    FROM
        (
            SELECT t2.company,t2.region,DAY(t1.date_time) as day,SUM(t1.stat) as day_stat
            FROM schema1.table_stats t1
            INNER JOIN table_companies t2
            ON t1.group=t2.group AND t1.subgroup=t2.subgroup
            WHERE
                MONTH(t1.date_time)=12 AND
                YEAR(t1.date_time)=2015
            group by t2.company,t2.region,DAY(t1.date_time)
            ORDER BY t2.company,t2.region,day_stat DESC
        ) t3
    JOIN
    (
            SELECT t2.company,t2.region,DAY(t1.date_time) as day,SUM(t1.stat) as day_stat
            FROM schema1.table_stats t1
            INNER JOIN table_companies t2
            ON t1.group=t2.group AND t1.subgroup=t2.subgroup
            WHERE
                MONTH(t1.date_time)=12 AND
                YEAR(t1.date_time)=2015
            group by t2.company,t2.region,DAY(t1.date_time)
            ORDER BY t2.company,t2.region,day_stat DESC
        ) t4
    ON
        t4.day_stat >= t3.day_stat AND
        t4.company = t3.company AND
        t4.region = t3.region
    GROUP BY t3.company,t3.region,t3.day_stat
    ORDER BY t3.company,t3.region,rank
    ) t5
WHERE t5.rank<=20
;

查询摘要:从 2 个最深的子查询开始，它首先连接两个表，分组并聚合 stat按公司、地区和日期。这也是它限制月份和年份的地方。然后它将此结果与自身的副本连接起来，以便能够生成排名。最后选择将每个子组的结果限制在前 20 个。

预期结果:
为以 SQL 声明呈现而致歉

INSERT INTO results
    (`company`, `region`, `day`, `day_stat`, `rank`)
VALUES
    ('company3', 'region6', 7, 159, 1),
    ('company3', 'region6', 22, 147, 2),
    ('company3', 'region6', 23, 138, 3),
    ('company3', 'region6', 17, 130, 4),
    ('company3', 'region6', 14, 125, 5),
    ('company3', 'region6', 25, 116, 6),
    ('company3', 'region6', 29, 109, 7),
    ('company3', 'region6', 16, 108, 8),
    ('company3', 'region6', 9, 104, 9),
    ('company3', 'region6', 12, 102, 10),
    ('company3', 'region6', 19, 100, 11),
    ('company3', 'region6', 24, 91, 12),
    ('company3', 'region6', 10, 89, 13),
    ('company3', 'region6', 4, 86, 14),
    ('company3', 'region6', 18, 80, 15),
    ('company3', 'region6', 5, 77, 16),
    ('company3', 'region6', 21, 75, 17),
    ('company3', 'region6', 26, 74, 18),
    ('company3', 'region6', 20, 61, 19),
    ('company3', 'region6', 8, 59, 20)
;

tl;dr:很抱歉发了这么长的帖子。求优化http://sqlfiddle.com/#!9/29a7b/1 .

最佳答案

我所做的修改:

完全修改了您的查询
在 group,subgroup 的 table_companies 表中添加了复合索引
在 group, subgroup 的 table_stats 表中添加了复合索引

修改后的查询:

SELECT 
    C.company,
    C.region,
    DAY(S.date_time) day,
    SUM(S.stat) day_stat
FROM table_companies C
INNER JOIN table_stats S
ON C.`group` = S.`group` AND C.subgroup = S.subgroup
WHERE MONTH(S.date_time) = 12 AND YEAR(S.date_time) = 2015
GROUP BY C.company, C.region, DAY(S.date_time)
ORDER BY day_stat DESC
LIMIT 20;

WORKING DEMO

_{结果集中没有排名列。由于结果是根据排名降序排列的，因此您可以隐式地将行在结果集中的位置视为排名。不过，如果您真的需要排名列，那么这里有一个 working demo其中的}

综合指数(table_companies):

ALTER TABLE `table_companies` ADD INDEX `idx_table_compnaies_group_subgroup` (
    `group`,
    `subgroup`
);

综合指数(table_stats):

ALTER TABLE `table_stats` ADD INDEX `idx_table_stats_group_subgroup` (
`group`,
`subgroup`
);

解释结果:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  S   ALL idx_table_compnaies_group_subgroup              60  Using where; Using temporary; Using filesort
1   SIMPLE  C   ref idx_table_companies_group_subgroup  idx_table_companies_group_subgroup  57  schema1.S.group,schema1.S.subgroup  1   Using index condition

_{好消息是 MySQL 可以使用这些索引(因为它们在可能的键下)。虽然它将 ALL 显示为 table_companies 的类型。我只能说这是一小部分数据。您不能根据少量数据来判断性能。}

更多:

我猜你在那些表中有主键。如果您没有，请创建。

编辑:

SELECT 
    C.company,
    C.region,
    tt.day,
    tt.total AS day_stat,
    tt.rank
FROM table_companies C 
INNER JOIN 
(
SELECT 
t.*,
IF(t.businessUnit = @sameBusinessUnit, @rn := @rn + 1, @rn := 1) AS rank,
@sameBusinessUnit := t.businessUnit
FROM 
(
    SELECT 
       S1.`group`,
       S1.subgroup,
       CONCAT(S1.`group`,S1.subgroup) AS businessUnit,
       DAY(S1.date_time) AS day,
       SUM(S1.stat) total
    FROM table_stats S1
    GROUP BY S1.group,S1.subgroup,DAY(S1.date_time)
    ORDER BY total DESC
)AS t
CROSS JOIN (SELECT @rn := 1, @sameBusinessUnit := '') var
) AS tt
ON C.`group`=tt.`group` AND C.subgroup = tt.subgroup
WHERE tt.rank <= 20
ORDER BY tt.`group`,tt.`subgroup`,tt.rank;

WORKING DEMO(Version 2.0)

关于mysql - 前20组排名查询-优化，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/38667935/

排名 mysql 39 subgroup group optimization

有关mysql - 前20组排名查询-优化的更多相关文章

ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
使用canal同步MySQL数据到ES - 2
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
kvm虚拟机安装centos7基于ubuntu20.04系统 - 2
需求：要创建虚拟机，就需要给他提供一个虚拟的磁盘，我们就在/opt目录下创建一个10G大小的raw格式的虚拟磁盘CentOS-7-x86_64.raw命令格式：qemu-imgcreate-f磁盘格式磁盘名称磁盘大小qemu-imgcreate-f磁盘格式-o?1.创建磁盘qemu-imgcreate-fraw/opt/CentOS-7-x86_64.raw10G执行效果#ls/opt/CentOS-7-x86_64.raw2.安装虚拟机使用virt-install命令，基于我们提供的系统镜像和虚拟磁盘来创建一个虚拟机，另外在创建虚拟机之前，提前打开vnc客户端，在创建虚拟机的时候，通过vnc
sql - 查询忽略时间戳日期的时间范围 - 2
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。最佳答案您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时
ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2
我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin
ruby-on-rails - solr 清理查询 - 2
我在Rails上使用带有ruby的solr。一切正常，我只需要知道是否有任何现有代码来清理用户输入，比如以?开头的查询。或* 最佳答案我不知道执行此操作的任何代码，但理论上可以通过查看parsingcodeinLucene来完成并搜索thrownewParseException(只有16个匹配!)。在实践中，我认为您最好只捕获代码中的任何solr异常并显示“无效查询”消息或类似信息。编辑:这里有几个“sanitizer”:http://pivotallabs.com/users/zach/blog/articles/937-s
ruby-on-rails - Rails 3 在一个查询中包含多个表 - 2
我正在为锦标赛开发一个Rails应用程序。我在这个查询中使用了三个模型:classPlayertruehas_and_belongs_to_many:tournamentsclassTournament:destroyclassPlayerMatch"Player",:foreign_key=>"player_one"belongs_to:player_two,:class_name=>"Player",:foreign_key=>"player_two"在tournaments_controller的显示操作中，我调用以下查询:Tournament.where(:id=>params
ruby-on-rails - Sunspot:如何对具有不同值的多个字段进行全文查询？ - 2
我想用sunspot重现以下原始solr查询q=exact_term_text:fooORterm_textv:foo*ORalternate_text:bar*但我无法通过标准的太阳黑子界面理解这是否可能以及如何实现，因为看起来:fulltext方法似乎不接受多个文本/搜索字段参数我不知道将什么参数作为第一个参数传递给fulltext，就好像我通过了"foo"或"bar"结果不匹配如果我传递一个空参数，我得到一个q=*:*范围过滤器(例如with(:term).starting_with('foo*')(顾名思义)作为过滤器查询应用，因此不参与评分。似乎可以手动编写字符串(或者可能使
ruby - 如何使用 ruby mysql2 执行事务 - 2
我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

mysql - 前20组排名查询-优化

有关mysql - 前20组排名查询-优化的更多相关文章

随机推荐