如何在SQL中找到列的模式? [英] How do I find the mode of a column in SQL?

问题描述

这是表格及其值:

CREATE TABLE Weapon(id INT,
Name VARCHAR(80),
Style VARCHAR(80));

Insert into Weapon values (1, 'Seismic Wand', 'Magic');
Insert into Weapon values (2, 'Nox Bow', 'Ranged');
Insert into Weapon values (3, 'Drygore', 'Melee');
Insert into Weapon values (4, 'Chaotic Staff', 'Magic');
Insert into Weapon values (5, 'Staff of Darkness', 'Magic');
Insert into Weapon values (6, 'Tetsu Katana', 'Melee');

这是我的查询尝试.

SELECT Name, Count(*) AS Style
FROM Weapon
GROUP BY Name
HAVING Count(*) >= ALL
   (SELECT Count(*)
   FROM Weapon
   GROUP BY Name);

它不显示模式，是Style列的最重复的值.

It is not displaying the mode, the most repeated value of Style column.

如何使其显示模式?

推荐答案

更新:

为了回应@vkp对可能将多个Style作为数据集模式的可能性的合理关注，下面是一个更好的查询:

In response to @vkp's legitimate concerns about the potential for multiple Styles being the mode of the data set, here is a better query:

SELECT Style AS Mode, COUNT(*) AS Count FROM Weapon GROUP BY Style HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM Weapon GROUP BY Style);

+-------+-------+ | Mode | Count | +-------+-------+ | Magic | 3 | +-------+-------+

+-------+-------+ | Mode | Count | +-------+-------+ | Magic | 3 | +-------+-------+

这将返回数据集的模式，并处理多个Style是模式而无需目视检查结果的情况.

This will return the mode of the data set and will handle the case where multiple Styles are the mode without requiring visual inspection of the results.

例如，构建原始表，

INSERT INTO Weapon VALUES (7, 'Rock', 'Melee');(增加近战"的数量，使其与魔术"并列)

INSERT INTO Weapon VALUES (7, 'Rock', 'Melee'); (to increase the count of 'Melee' so it will tie with 'Magic')

如果我们运行新的模式查找查询，则会得到以下结果:

if we run the new mode-finding query we are given these results:

+-------+-------+ | Mode | Count | +-------+-------+ | Magic | 3 | | Melee | 3 | +-------+-------+

+-------+-------+ | Mode | Count | +-------+-------+ | Magic | 3 | | Melee | 3 | +-------+-------+

我发布的原始答案(保留在下面)将在表格顶部产生模式，并要求用户选择所需的值.我认为这种新的解决方案效果更好.另外，我意识到，当数据集的模式实际上是Style的 name (魔术，近战等)，不是样式出现的次数.

The original answer I posted (retained below) will produce the mode(s) at the top of the table and requires the user to pick out the value(s) they need. This new solution works better in my opinion. Also, I realize that originally I had labeled my "Count" column as "Mode" when really the mode of the data set is the name of the Style (Magic, Melee, etc), not the number of times the Style occurs.

原始答案:

对于样式"列的模式，

SELECT Style, COUNT(*) AS Mode FROM Weapon GROUP BY Style ORDER BY COUNT(*) DESC;

+--------+------+ | Style | Mode | +--------+------+ | Magic | 3 | | Melee | 2 | | Ranged | 1 | +--------+------+

+--------+------+ | Style | Mode | +--------+------+ | Magic | 3 | | Melee | 2 | | Ranged | 1 | +--------+------+

模式将在表格的顶部.

感谢 http://www.xarg.org/2012/07/statistical-functions-in-mysql/

这篇关于如何在SQL中找到列的模式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！