SQL按固定列值分组并计数

Question

SQL按固定列值分组并计数

5

我在SQL(MySQL)的数据导入脚本中遇到了一个问题，需要按类型对行进行分组以计数每个类型有多少行。迄今为止，这并不是真正的问题，因为我知道可以这样做：

SELECT 
  data.type, 
  COUNT(data.type) 
FROM data 
GROUP BY data.type;

所以，通过这样做，我得到了如下结果：

 -------------- --------------------- 
| type         | COUNT(data.type)    |
|--------------|---------------------|
| 0            |                   1 |
| 1            |                  46 |
| 2            |                  35 |
| 3            |                 423 |
| 4            |                  64 |
| 5            |                  36 |
| 9            |                   1 |
 -------------- ---------------------

我知道在type列中的值始终在0到9的范围内，就像上面的结果一样。因此，我希望不仅列出表格内容中存在的值，还要列出缺失的type值，并将它们的COUNT值设置为0。

根据上面的查询结果，期望的结果应该是：

 -------------- --------------------- 
| type         | COUNT(data.type)    |
|--------------|---------------------|
| 0            |                   1 |
| 1            |                  46 |
| 2            |                  35 |
| 3            |                 423 |
| 4            |                  64 |
| 5            |                  36 |
| 6            |                   0 |
| 7            |                   0 |
| 8            |                   0 |
| 9            |                   1 |
 -------------- ---------------------

我可以巧妙地在按组/计数表格内容之前插入每个类型的一行，并在INSERT时标志其他列以能够在DELETE后删除这些行。因此，我的导入脚本的步骤将变为：

TRUNCATE table;（如果表中有旧数据，则无法安全地导入新内容）
INSERT "control" rows;
LOAD DATA INFILE INTO TABLE;
GROUP/COUNT-1 the table content;
DELETE "control" rows;（因此我仍然可以使用表格内容）
执行任何其他作业；

但是，我正在寻找一种更清洁的方法来达到期望的结果。如果可能，希望只用一个查询而不需要大量的JOIN。

我将非常感谢任何建议或建议。非常感谢！

编辑

谢谢关于创建一个表以存储所有类型并加入它的答案。它确实解决了问题。我的方法也可以解决它，但是它会存储类型，就像您所做的那样。

因此，我有“另一个”问题，只是一个澄清，基于收到的答案和我希望的范围...是否有可能通过某些MySQL命令而不是创建新表来达到期望的结果？ /或插入这些类型？

实际上，我并没有看到存储类型来解决我的问题有任何问题...我只是想找到一个简化的命令...像'最佳实践'那样...某种过滤器...就像我可以运行的：

GROUP BY data.type(0,1,2,3,4,5,6,7,8,9)

它可以返回这些被过滤的值。

如果这个命令真的存在/可行，我很有兴趣学习它。

再次感谢您！

- Felypp Oliveira

最佳实践是拥有一个名为“types”的表，其中包含类型的所有有效值（以及其他信息，例如这些值的含义）。 - Gordon Linoff

请勿将答案编辑为您的问题。如果您有答案，请在下面作为答案发布。 - user229044

对于一个好问题点个赞。我曾经遇到过类似的问题，需要创建一个时间表，显示发生在上午9点、10点、11点等时间的事情。但如果这些时间没有发生任何事情，你的时间表就会出现空洞，这正是同样的问题。 - Laurence Frost

1

@meagar 我已经发布了答案...谢谢！ - Felypp Oliveira

4个回答

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

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

- Felypp Oliveira · Answer 1

好的...我想我找到了！谢谢大家！！！我接受了自己的答案。

我同意@GordonLinoff的评论，最佳实践是将类型值存储并描述它们，这样您可以保持简洁/可理解的数据库和查询。

但是，据我所学，如果您有一些可能是无关信息的数据，则最好以某种其他方式处理它而不是存储它。

因此，我开发了这个查询：

SELECT 
  SUM(IF(data.type = 0, 1, 0)) AS `0`, 
  SUM(IF(data.type = 1, 1, 0)) AS `1`, 
  SUM(IF(data.type = 2, 1, 0)) AS `2`, 
  SUM(IF(data.type = 3, 1, 0)) AS `3`, 
  SUM(IF(data.type = 4, 1, 0)) AS `4`, 
  SUM(IF(data.type = 5, 1, 0)) AS `5`, 
  SUM(IF(data.type = 6, 1, 0)) AS `6`, 
  SUM(IF(data.type = 7, 1, 0)) AS `7`, 
  SUM(IF(data.type = 8, 1, 0)) AS `8`, 
  SUM(IF(data.type = 9, 1, 0)) AS `9` 
FROM data;

虽然查询不是那么快速、优化和美观，但由于我要处理的数据规模较小（每次导入少于100,000行），所以“手动”执行GROUP/COUNT操作只需0.13秒即可在普通开发者机器上运行。

它与我的预期结果不同之处在于选择行和列的方式——我得到了1行10列的结果，标有匹配类型。此外，由于我们对类型值进行了标准化（并且我们肯定不会更改它），因此我现在可以使用类型名称作为列标签，而不是加入到一个包含类型信息的表中来选择结果中的第三列（实际上，这并不重要，因为它是基于某些标准的导入脚本）。

非常感谢大家的帮助！

- Gordon Linoff · Answer 2

0

假设您有一个包含所有有效类型的类型表：

SELECT t.type, 
       COUNT(data.type) 
FROM data join types t on data.type = t.type
GROUP BY t.type
order by t.type

你应该明确地包含order by，而不依赖于group by来按特定顺序生成结果。

- Gordon Linoff

首先，感谢您的帮助。我认为通过这种方法，只需将“控制”行插入不同的表中，您觉得呢？我的主要意图是找到像以下这样的东西：按数据类型分组（0、1、2、3、4、5、6、7、8、9）因为这些值是“分组和值过滤器”。 - Felypp Oliveira

你的查询没有提到任何关于值过滤器的内容。你可以使用带有union all的select语句创建值列表。 - Gordon Linoff

很抱歉关于“筛选”部分...我没有预料到答案将存储'types'，而我的方法是将它们存储起来，我试图避免这种情况。非常抱歉。 - Felypp Oliveira

@user1977836……所有三个答案本质上都是相同的。我的假设您有一个类型表。BlueFeet使用“union all”创建一个表，而sgeddes则假定您有数字表。如果这些答案没有回答您的问题，那么您可能需要修改问题或提出另一个问题。 - Gordon Linoff

- Taryn · Answer 3

最简单的方法是创建一个包含所有type值的表，然后在获取计数时加入该表进行连接：

select t.type,
  count(d.type)
from types t
left join data d
  on t.type = d.type
group by t.type

请参考带演示的SQL Fiddle

或者您也可以使用以下内容：

select t.type,
  count(d.type)
from
(
  select 0 type
  union all
  select 1 
  union all
  select 2
  union all
  select 3
  union all
  select 4
  union all
  select 5 
  union all
  select 6
  union all
  select 7
  union all
  select 8
  union all
  select 9 
) t
left join data d
  on t.type = d.type
group by t.type

请查看带演示的SQL Fiddle

- sgeddes · Answer 4

一个选项是使用静态数字表，其中包含0-9的值。不确定这是否是最优雅的方法，如果您正在使用SQL Server，则我可以想到另一种方法。

尝试类似于以下内容：

SELECT 
  numbers.number, 
  COUNT(data.type) 
FROM numbers 
left join data 
  on numbers.number = data.type
GROUP BY numbers.number;

还有SQL Fiddle。