- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
假设您有一个包含数十万行的“汽车”表,你想做一个 GROUP BY:
SELECT CarID
, CarName
, COUNT(*) AS Total
FROM dbo.tbl_Cars
GROUP BY CarID
, CarName
分组后的结果类似于:
CarID CarName Total
1872 Olds 202,121
547841 BMW 175,298
9877 Ford 10,241
一切都很好。但我的问题是,获得这些信息的最佳方式是什么?Total 和 MAX Total 放到一张表中,在性能和干净的编码,所以你会得到如下结果:
CarID CarName Total Max Total
1872 Olds 202,121 202,121
547841 BMW 175,298 202,121
9877 Ford 10,241 202,121
一种方法是将 GROUP 结果放入临时表中,然后将临时表中的 MAX 获取到局部变量中。但我想知道做到这一点的最佳方法是什么。
<小时/>更新
公用表表达式似乎写起来最优雅,但与@EBarr 类似,我有限的测试表明性能明显较慢。所以我不会选择 CTE。
正如 @EBarr 为 COMPUTE
选项提供的链接所示,该功能已被弃用,这似乎也不是最好的路线。
局部变量的 MAX 值的选项和使用临时表可能是我走的路线,因为我不是意识到它的性能问题。
关于我的用例的更多细节:它可能最终会成为系列其他问题。但足以说明我正在加载将大量数据子集放入临时表中(因此 tbl_Cars 的子集是进入#tbl_Cars,甚至#tbl_Cars可能会被进一步过滤并对其执行聚合),因为我必须执行多个过滤以及在单个存储过程中对其进行聚合查询返回多个结果集。
<小时/>更新2
@EBarr 对窗口函数的使用很好而且简短。注意 self :如果使用 RIGHT JOIN
到外部引用表,则 COUNT()
函数应从 tbl_Cars 中选择一列,而不是 '*'
。
SELECT M.MachineID
, M.MachineType
, COUNT(C.CarID) AS Total
, MAX(COUNT(C.CarID)) OVER() as MaxTotal
FROM dbo.tbl_Cars C
RIGHT JOIN dbo.tbl_Machines M
ON C.CarID = M.CarID
GROUP BY M.MachineID
, M.MachineType
就速度而言,似乎还不错,但到什么程度你必须担心阅读量?
最佳答案
从机制上讲,有几种方法可以做到这一点。您可以使用临时表/表变量。另一种方法是使用嵌套查询和/或 CTE,如 @Aaron_Bertrand 所示。第三种方法是使用窗口函数,例如...
SELECT CarName,
COUNT(*) as theCount,
MAX(Count(*)) OVER(PARTITION BY 'foo') as MaxPerGroup
FROM dbo.tbl_Cars
GROUP BY CarName
不受欢迎(阅读废弃的)第四种方法是使用 COMPUTE 关键字......
SELECT CarID, CarName, Count(*)
FROM dbo.tbl_Cars
GROUP BY CarID, CarName
COMPUTE MAX(Count(*))
COMPUTE
关键字生成的总计显示为结果集末尾的附加摘要列 ( see this )。在上面的查询中,您实际上会看到两个记录集。
最快
现在,下一个问题是什么是“最好/最快/最简单”。我立即想到一个indexed view
。正如@Aaron 温柔地提醒我的那样,索引 View 有各种各样的限制。然而,上述策略允许您在 SELECT...FROM..GROUP BY 上创建索引 View 。然后从索引 View 中选择应用 WINDOWED FUNCTION 子句。
但是,如果不了解更多有关您的设计的信息,任何人都很难告诉您什么是最好的。您将从索引 View 中获得快速查询。不过,这种性能是有代价的。价格是维护费用。如果基础表是大量插入/更新/删除操作的目标,则索引 View 的维护将降低其他区域的性能。
如果您分享更多有关您的用例和数据访问模式的信息,人们将能够分享更多见解。
<小时/>微观性能测试
因此,我生成了一个小数据脚本,并查看了 CTE 性能与窗口函数的 SQL 分析器编号。这是一个微观测试,因此请在实际负载下尝试系统中的一些实数。
数据生成:
Create table Cars ( CarID int identity (1,1) primary key,
CarName varchar(20),
value int)
GO
insert into Cars (CarName, value)
values ('Buick', 100),
('Ford', 10),
('Buick', 300),
('Buick', 100),
('Pontiac', 300),
('Bmw', 100),
('Mecedes', 300),
('Chevy', 300),
('Buick', 100),
('Ford', 200);
GO 1000
此脚本生成 10,000 行。然后,我多次运行以下四个查询中的每一个:
--just group by
select CarName,COUNT(*) countThis
FROM Cars
GROUP BY CarName
--group by with compute (BAD BAD DEVELOPER!)
select CarName,COUNT(*) countThis
FROM Cars
GROUP BY CarName
COMPUTE MAX(Count(*));
-- windowed aggregates...
SELECT CarName,
COUNT(*) as theCount,
MAX(Count(*)) OVER(PARTITION BY 'foo') as MaxInAnyGroup
FROM Cars
GROUP BY CarName
--CTE version
;WITH x AS (
SELECT CarName,
COUNT(*) AS Total
FROM Cars
GROUP BY CarName
)
SELECT x.CarName, x.Total, x2.[Max Total]
FROM x CROSS JOIN (
SELECT [Max Total] = MAX(Total) FROM x
) AS x2;
运行上述查询后,我在上面的“just group by”查询上创建了一个索引 View 。然后我在执行 MAX(Count(*)) OVER(PARTITION BY 'foo'
的索引 View 上运行查询。 .
平均结果
Query CPU Reads Duration
--------------------------------------------------------
Group By 15 31 7 ms
Group & Compute 15 31 7 ms
Windowed Functions 14 56 8 ms
Common Table Exp. 16 62 15 ms
Windowed on Indexed View 0 24 0 ms
显然,这是一个微基准,只有轻微的指导意义,因此请充分考虑它的值(value)。
关于sql - t-sql GROUP BY 和 COUNT,然后包含 COUNT 中的 MAX,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9217486/
我想编写一个 linq 表达式,该表达式将返回不包含特定值的 ID。例如,我想返回所有不具有 Value = 30 的不同 ID。 ID, Value 1, 10 1, 20 1, 30 2,
我正在尝试使用 Regexp 匹配 Nmap 命令的输出。可以有两种不同的格式。 第一种格式(当 nmap 可以找到主机名时) Nmap scan report for 2u4n32t-n4 (192
我正在 Visual Studio 2012 上使用 C# 开发一个软件。我使用 MySQL Connector 6.9.1 进行 MySQL 连接。我的软件在我的操作系统(Win8 x64)上运行顺
在 Django 中(使用 django.contrib.auth 时)我可以添加一个 Group到另一个 Group ?即一个Group成为另一个成员(member) Group ? 如果是这样,我
我试图通过使用动态组参数对数据进行分组来循环。 我们可以在循环的 WHERE 条件上使用动态查询,但我不知道是否可以在组条件中使用动态字符串。 以下是用户决定按哪个字段分组,然后根据决定放置其他逻辑的
我有这样的字符串 s = 'MR1|L2-S1x' 模式总是相同的:一个或两个字符,在 [|.+:x-] 中可选地后跟一个数字和一个分隔符。此模式可以重复 6 次。 所以匹配模式很明确。 p = r'
我有一个带有时间戳字段“bar”的表“foo”。如何仅获取查询的最旧时间戳,例如: SELECT foo.bar from foo?我尝试执行以下操作: SELECT MIN(foo.bar) fro
在我的 Django 项目中,我有一个 user_manage 应用程序。 我在 user_manage 应用的 model.py 中创建了一个名为 UserManage 的模型: from djan
所以我有这样的输入: 还有一个模板指令,例如: 看来我只获得了 foo 和 bar 的组。 (为什么?我预计我可能会得到第三组 current-group-key() = '')。
我正在尝试扩展 django.contrib.auth 并遇到将用户添加到组中的情况,这可以通过两种方式完成。我只是想知道为什么会这样,以及其中一种相对于另一种的优势是什么。 最佳答案 他们做完全相同
我使用的是旧的 PHP 脚本,并且此查询有错误。由于我没有使用 mysql 的经验,因此无法修复它。 "SELECT COUNT(p.postid) AS pid, p.*, t.* FROM ".T
我有几行 Objective-C 代码,例如: ABAddressBookRef addressBook; CFErrorRef error = NULL; addressBook = ABAddre
我正在使用 MariaDB IMDB 电影数据集,我试图解决以下问题。电影表包含 id、名称、排名和年份列 A decade is a sequence of 10 consecutive years
让我从数据开始,以便更好地描述我的需求。我有一个名为 SUPERMARKET 的表,其中包含以下字段: Field 1: StoreID Field 2: ProductCategory Field
你好我有这个查询: SELECT DISTINCT a.id, a.runcd, (SELECT SUM(b.CALVAL) FROM GRS b WHERE b.PCode=11000 AND a.
我想在 xquery 中使用 Group By。有人可以告诉我如何在 Marklogic 中使用 Group By 吗? 最佳答案 或者,您可以使用 xdmp:xslt-invoke 调用 XSLT或
因此,当通过 from sequelize 请求组时,如下所示: return models.WorkingCalendar .findAll({
我希望我解释正确。 我有 2 个表,有 第一个表(table1) +------------+------+-------+-------+ | Date | Item | Block |
我的表 MYTABLE 有 2 列:A 和 B 我有以下代码片段: SELECT MYTABLE.A FROM MYTABLE HAVING SUM(MYTABLE.B) > 100
我有一个简单的行分组查询,需要 0.0045 秒。 300.000 行 从表 GROUP BY cid 中选择 cid 当我添加 MAX() 进行查询时,需要 0.65 秒才能返回。 从表 GROUP
我是一名优秀的程序员,十分优秀!